Getting an error "BadArgumentError- 'where' operator: Failed to resolve table or column expression named 'AzureDiagnostics'"

Question

Getting an error "BadArgumentError- 'where' operator: Failed to resolve table or column expression named 'AzureDiagnostics'"

Jatin Lingwal 20

Getting an error while creating an alert on the Monitor dashboard under the condition of the custom log search query. The query is " AzureDiagnostics
| where Category == "Authentication" and OperationName == "CredentialCreated" and Result == "Success"
| where TimeGenerated > ago(30d)
| where CredentialEndDate < now() + 7d "

and error "BadArgumentError- 'where' operator: Failed to resolve table or column expression named 'AzureDiagnostics'"

Accepted answer

0 additional answers

Your answer

Answer 1

AnuragSingh-MSFT 21,546 Moderator

@Jatin Lingwal , thank you for posting this question on Microsoft Q&A.

I understand that you are trying to query AzureDiagnostics table in Log Analytics workspace. The error that you are getting is because AzureDiagnostics table does not have the following columns as used in the query:

CredentialEndDate
Result

To resolve this error, use only those columns names in query with where operator, which are available in this table. To get the list columns available with this table, please see Azure Monitor Logs: AzureDiagnostics.

Hope this helps.

If the answer did not help, please add more context/follow-up question for it, and we will help you out. Else, if the answer helped, please click Accept answer so that it can help others in the community looking for help on similar topics.

Jatin Lingwal 20 Reputation points

2023-05-25T14:42:15.5266667+00:00

Thanks for the response, but I cannot get where to insert CredentialEndDate and Result. Could you please correct me
AnuragSingh-MSFT 21,546 Reputation points Moderator

2023-05-26T07:41:51.6733333+00:00

Jatin Lingwal, thank you for the reply. Can you please share more details about this issue? Where are you getting these values (CredentialEndDate and Result) from?

If you could share the requirement with sample scenario/screenshot, it will help us better understand the issue.
Jatin Lingwal 20 Reputation points

2023-05-26T09:04:48.76+00:00
Hi , AnuragSingh-MSFT. I appreciate your support. I am making a solution for the App registration expiration date. I got steps from the Azure support executive. So in those steps, this custom query was given.

STEPS:

To monitor app registration secrets in Azure, you can use Azure Monitor. Azure Monitor provides a centralized platform for monitoring and analyzing data from various Azure resources, including app registrations.

To monitor app registration secrets, you can create an Azure Monitor alert that triggers when a secret is about to expire or has expired. Here is an example of how to create an Azure Monitor alert for app registration secrets using the Azure portal[2]:

In the Azure portal, navigate to your app registration and select Monitoring from the left-hand menu.

Select Alerts and then select New alert rule.

In the Create alert rule dialog, provide a name and description for the alert.

Under Resource, select your app registration.

Under Condition, select Add condition and then select Signal logic.

In the Signal logic dialog, select Custom log search.

In the Custom log search dialog, enter the following query to search for app registration secrets that are about to expire or have expired:

AzureDiagnostics
| where Category == "Authentication" and OperationName == "CredentialCreated" and Result == "Success"
| where TimeGenerated > ago(30d)
| where CredentialEndDate < now() + 7d

This query searches for app registration secrets that were created successfully, have an expiration date within the next 7 days, and were created within the last 30 days.

Select Done to save the query and return to the Signal logic dialog.

Select Done to save the signal logic and return to the Add condition dialog.

Under Threshold, set the threshold to 1 and the frequency to 5 minutes.

Under Actions, select Add action group and then select Create action group.

In the Create action group dialog, provide a name and description for the action group.

Under Actions, select Add action and then select Email/SMS/Push/Voice.

In the Email/SMS/Push/Voice dialog, provide the necessary information to send an email notification when the alert is triggered.

Select OK to save the action and return to the Create action group dialog.

Select OK to save the action group and return to the Add condition dialog.

Select Done to save the alert rule.

This alert rule triggers an email notification when an app registration secret is about to expire or has expired. You can customize the query to search for secrets with different expiration dates or other criteria.

Note that you need the necessary permissions to create an Azure Monitor alert and access the app registration secrets**.**

If possible, could you also correct the steps if something is wrong?

And It will be very helpful if you also help me with the last step.
AnuragSingh-MSFT 21,546 Reputation points Moderator

2023-05-29T05:28:14.8333333+00:00
Jatin Lingwal, thank you for the reply. I cannot follow the steps mentioned above and I am not sure if this is complete/correct. For example, I can't follow the step mentioned as "In the Azure portal, navigate to your app registration and select Monitoring from the left-hand menu. "

The query being used is definitely wrong as the "AzureDiagnostics" table does not have the columns referenced. For details, see Azure diagnostics table reference.

The steps are correct for creating log-based alert rule, but the log search query itself seems wrong.

Also, in the answer, it is mentioned that "This query searches for app registration secrets that were created successfully, have an expiration date within the next 7 days, and were created within the last 30 days." <-- This means that, even if the steps here were correct, you will only be able to monitor the AppRegistrations created in the last 30 days - which to me seems to be a very short period for secret expiration.

Please correct me if I missed something or if you are able to follow the steps as highlighted above. I would suggest reaching out to the support executive who gave you these steps for clarification.

Based on my knowledge, there is no direct query-based way to create alert for this scenario. You would either have to use Logic Apps or Azure Automation/PowerShell based solution script to monitor it. Here are some

Get notified on expiring Azure App Registration client secrets

PowerShell based solution for monitoring App Registration Expiration

Hope it helps.
Jatin Lingwal 20 Reputation points

2023-05-29T09:58:55.1333333+00:00
Thank you so much AnuragSingh-MSFT; I have understood your points, and I tried these two other options too. There is also a "**PowerShell solution. "**I am getting an error.

Write-Error: Automation.Sandbox.AssetManagement.Cmdlets.AssetManagementClient.AssetManagementClientException: {"type":"https://tools.ietf.org/html/rfc7231#section-6.6.1","title":"An error occurred while processing your request.","status":500,"traceId":"00-d5f20061394818837a568d922291295c-3af7bede76f90416-00"} at Automation.Sandbox.AssetManagement.Cmdlets.AssetManagementClient.AssetManagementHttpClient.GenerateAssetManagementClientException(HttpResponseMessage response) in C:__w\1\s\src\Automation.Sandbox.AssetManagement.Cmdlets\AssetManagementClient\AssetManagementHttpClient.cs:line 206 at Automation.Sandbox.AssetManagement.Cmdlets.AssetManagementClient.AssetManagementHttpClient.GetAsset[T] in C:__w\1\s\src\Automation.Sandbox.AssetManagement.Cmdlets\AssetManagementClient\AssetManagementHttpClient.cs:line 127 at Automation.Sandbox.AssetManagement.Cmdlets.AssetManagementClient.AssetManagementHttpClient.GetVariableValue(String name) in C:__w\1\s\src\Automation.Sandbox.AssetManagement.Cmdlets\AssetManagementClient\AssetManagementHttpClient.cs:line 75 at Automation.Sandbox.AssetManagement.Cmdlets.AssetCmdlets.GetAutomationVariableCmdlet.CmdletImplementation() in C:__w\1\s\src\Automation.Sandbox.AssetManagement.Cmdlets\AssetCmdlets\GetAutomationVariableCmdlet.cs:line 20 at Automation.Sandbox.AssetManagement.Cmdlets.AssetCmdlets.AssetBaseCmdlet.ProcessRecord() in C:__w\1\s\src\Automation.Sandbox.AssetManagement.Cmdlets\AssetCmdlets\AssetBaseCmdlet.cs:line 32

I have also stated this issue in the community, but there has been no reply.

I know I have stretched this so far. If you could let me know

Logic I have also created but no solution for multip app. I have tried for the single app it works fine, but for 30 plus apps I am not sure about the logic

All solution is for one single app secret, but when there are multiple. Where would that condition go?
AnuragSingh-MSFT 21,546 Reputation points Moderator

2023-05-29T11:20:53.8666667+00:00

Jatin Lingwal, thank you very much for the reply. These are 2 different solutions using 2 different Azure Services, I would suggest posting them as 2 new questions - one as the Azure Automation question for PowerShell script) and other as the Logic Apps questions. Also, if you post a reply to a very old discussion, there are chances that no one is following that, or the original author does not even have access to that id/forum.

By creating new questions on Microsoft Q&A, you would get the attention of community members who are closely monitoring tags of those service areas (for example "Azure Automation" and "Logic Apps"). This also helps scope the discussion to a single topic.

If the discussion on this thread helped, please click Accept answer so that it can help others in the community looking for help on similar topics.
Ronen 10 Reputation points

2024-05-22T16:40:28.1166667+00:00

So why do you provide a built in query which doesn't work?

Share via

Getting an error "BadArgumentError- 'where' operator: Failed to resolve table or column expression named 'AzureDiagnostics'"

0 additional answers

Your answer