Azure Activity - any good kql queries?

Iron Contributor

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:



| 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"
| 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 Replies
best response confirmed by SocInABox (Iron Contributor)



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). 

//| 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.