Forum Discussion

Dimitri Artemov's avatar
Dimitri Artemov
Former Employee
Nov 01, 2021

AzureActivity - When my cosmos account was actually deleted?

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

azure monitor

1 Reply

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Nov 01, 2021

    Dimitri Artemov 

     

    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 OperationNameValue

    Note: You can replace the "*" with specific named columns e.g.
    | summarize arg_max(TimeGenerated,OperationNameValue, ActivityStatusValue, ResourceDeleted) by OperationNameValue


Resources