question

MohammedAltamashKhan-3285 avatar image
0 Votes"
MohammedAltamashKhan-3285 asked GeorgeMoise-0315 answered

KQL Query to verify diagnostic logs

Hello Folks

If i set a diagnostic setting for example of a storage account , how can i verify the log is coming to sentinel in last few min or hrs .
Usually we use Azure diagnostic and Azure activity then pipe to build a single query but i need a multipurpose one.

I need to verify all type of resources log are coming to Sentinel , without changing much in query . Like altering the name of resource before running query.
Need a query in way that i can edit the name of resource and can able to verify another type of resources as well.

azure-monitormicrosoft-sentinel
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GeorgeMoise-0315 avatar image
1 Vote"
GeorgeMoise-0315 answered MohammedAltamashKhan-3285 commented

Hello @MohammedAltamashKhan-3285
Here are some Kusto Queries to help you analyzing Azure Diagnostic logs:

  1. Find what type of resources (and how many) are sending Diagnostic Logs to your Log Analytics Workspace:

    AzureDiagnostics
    | summarize Resources = dcount(ResourceId) by ResourceType
    | order by Resources desc

  2. Find the ResourceID of all Resources sending Azure Diagnostics for a specified Resource Type:

    AzureDiagnostics
    | where ResourceType == "
    a type resulted from the previous query"
    | distinct ResourceId

  3. Return all Azure Diagnostics records sent by a specified Resource (using a Resource Id from the previous query):

    AzureDiagnostics
    | where ResourceId == "
    a resource id returned from the previous query"

Note: I recommend you use the ResourceId property to filter for a specific resource (rather than the Resource (Name), as you might have multiple resources with the same name, but the ResourceId is unique)

I hope that the above helps and that this is what you were looking for.


BR,
George









· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @GeorgeMoise-0315

Basically we are using all type of Diagnostic setting policy in our environment for storage account , logic app , host pool , vnet , sql .

Unlike sentinel Data connector which show " last log received " for connector.

But in this case i need to modify the KQL Query alot of time for each and every type of resources. I wish microsoft can really do something about this .
I need a query in a way that i dont need to modify it much to check the diagnostic setting is working and logs are receiving , like by just replacing the name of resources and "Run" in the query.

resource display name are unique in my environment , no duplication . Another reason i dont wana use ResourceID bcoz every time i have to go resource individually and open JSON view and copy the resource ID.

But anyway thanks for your efforts

0 Votes 0 ·
GeorgeMoise-0315 avatar image
1 Vote"
GeorgeMoise-0315 answered

Hi again,
You can add the following to your query:

| summarize max(TimeGenerated) by Resource

or by ResourceId or ResourceType to see the timestamp of the newest ingested record for your grouping category.


BR,
George

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.