Forum Discussion
Azure Activity - any good kql queries?
Hi there,
just wondering if there are any good Azure Activity queries out there, eg:
- User related changes
- non-user related changes (managed identities etc)
The out of the box sentinel workbooks don't seem to get very granular on Azure activity - just high level metrics.
here's an example for tracking user related changes:
AzureActivity
| extend scope = parse_json(tostring(Authorization)).scope
| extend RoleDefinitionId_ = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).Properties)).RoleDefinitionId)
| extend PrincipalId_ = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).Properties)).PrincipalId)
| where Authorization contains "subscriptions" or scope contains "resourceGroups"
| extend resource_ = tostring(parse_json(Properties).resource)
| where ResourceProviderValue <> ""
| where ResourceProviderValue <> "MICROSOFT.OPERATIONSMANAGEMENT"
| where ResourceProviderValue <> "MICROSOFT.OPERATIONALINSIGHTS"
| where OperationNameValue <> "MICROSOFT.EVENTGRID/REGISTER/ACTION"
| summarize by Caller, CategoryValue, Level, OperationNameValue, ResourceProviderValue, ActivityStatusValue, RoleDefinitionId_, PrincipalId_
(and if there's a kql query for resolving the RoleDefinitionId UUID that would be cool).
Your feedback is appreciated!
One option is to use ARG, here is part of a query I use in a Workbook (but it depends on the type of change you are looking for).
resourcechanges //| where subscriptionId =='{Subscription}' | extend timestamp_ = properties.changeAttributes.timestamp, changeType_ = properties.changeType | where timestamp_ > ago(1d) | where properties.targetResourceType startswith "MICROSOFT.OPERATION" | project properties.targetResourceType, properties.changeAttributes.changesCount, changeType_, timestamp_, subscriptionId, properties //| where changeType_ =='Create' // 'Update','Delete' | summarize count(), arg_max(todatetime(timestamp_), *) by subscriptionId, tostring(properties_targetResourceType) | order by count_ desc
One option is to use ARG, here is part of a query I use in a Workbook (but it depends on the type of change you are looking for).
resourcechanges //| where subscriptionId =='{Subscription}' | extend timestamp_ = properties.changeAttributes.timestamp, changeType_ = properties.changeType | where timestamp_ > ago(1d) | where properties.targetResourceType startswith "MICROSOFT.OPERATION" | project properties.targetResourceType, properties.changeAttributes.changesCount, changeType_, timestamp_, subscriptionId, properties //| where changeType_ =='Create' // 'Update','Delete' | summarize count(), arg_max(todatetime(timestamp_), *) by subscriptionId, tostring(properties_targetResourceType) | order by count_ desc- I see, so you made a function to filter out the resource changes first, and then used this 2nd query to list all changes by subscription.
Thanks!
