Hello,
If I run below TQL
AzureActivity
| where TimeGenerated > ago(24h)
| where OperationNameValue has "MICROSOFT.DOCUMENTDB/DATABASEACCOUNTS/DELETE"
| where ActivityStatusValue == "Success"
| extend p=parse_json(Properties)
| extend ResourceDeleted=split(_ResourceId, "/", 8)[0]
| project TimeGenerated, OperationNameValue, ActivityStatusValue, ResourceDeleted
| order by TimeGenerated desc
I’m getting multiple Success rows for same deleted resource
If I go to portal, I see that resource deletion shown as if they use MAX from available timegenerated (Time stamp) values
So, if I use TQL as below
AzureActivity
| where TimeGenerated > ago(24h)
| where OperationNameValue has "MICROSOFT.DOCUMENTDB/DATABASEACCOUNTS/DELETE"
| where ActivityStatusValue == "Success"
| extend p=parse_json(Properties)
| extend ResourceDeleted=split(_ResourceId, "/", 8)[0]
| project TimeGenerated, OperationNameValue, ActivityStatusValue, ResourceDeleted
| order by TimeGenerated desc
| summarize DeleteTime = max(TimeGenerated) by OperationNameValue, ActivityStatusValue, tostring(ResourceDeleted)
I’m getting same result as in a portal
My question: why do we have multiple success rows and is MAX would be correct to get actual resource deletion (we will use the query for Alerting)?
To help with these I use the (little known) "Group Columns" feature, then drag and drop a column heading, you can then simply scroll to teh right to see what maybe different in each column (or drag and drop other columns into the group).
Ultimately many solutions log multiple rows, often looking almost the same apart from the timestamp.
Myself I'd use arg_max to see the last row from each
...
| extend p=parse_json(Properties)
| extend ResourceDeleted=split(_ResourceId, "/", 8)[0]
| summarize arg_max(TimeGenerated,*) by OperationNameValueNote: You can replace the "*" with specific named columns e.g.
| summarize arg_max(TimeGenerated,OperationNameValue, ActivityStatusValue, ResourceDeleted) by OperationNameValue