Azure Activity - any good kql queries?

[SocInABox]

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!

[#2 · Accepted solution]

@SocInABox 

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

[#3]

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!