Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community
SOLVED

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:

 

 

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

 

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!
1 best response

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

 

View solution in original post