AzureActivity - When my cosmos account was actually deleted?

%3CLINGO-SUB%20id%3D%22lingo-sub-2907155%22%20slang%3D%22en-US%22%3EAzureActivity%20-%20When%20my%20cosmos%20account%20was%20actually%20deleted%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2907155%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3EIf%20I%20run%20below%20TQL%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAzureActivity%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%3ETimeGenerated%3C%2FSPAN%3E%3CSPAN%3E%20%26gt%3B%20%3C%2FSPAN%3E%3CSPAN%3Eago%3C%2FSPAN%3E%3CSPAN%3E(24h)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%3EOperationNameValue%3C%2FSPAN%3E%20%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E%22MICROSOFT.DOCUMENTDB%2FDATABASEACCOUNTS%2FDELETE%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EActivityStatusValue%3C%2FSPAN%3E%3CSPAN%3E%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Success%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%20%3CSPAN%3Ep%3C%2FSPAN%3E%3CSPAN%3E%3D%3C%2FSPAN%3E%3CSPAN%3Eparse_json%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EProperties%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%20%3CSPAN%3EResourceDeleted%3C%2FSPAN%3E%3CSPAN%3E%3D%3C%2FSPAN%3E%3CSPAN%3Esplit%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E_ResourceId%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E%22%2F%22%3C%2FSPAN%3E%3CSPAN%3E%2C%208)%5B0%5D%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%20%3CSPAN%3ETimeGenerated%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3EOperationNameValue%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3EActivityStatusValue%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3EResourceDeleted%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eorder%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%20%3CSPAN%3ETimeGenerated%3C%2FSPAN%3E%20%3CSPAN%3Edesc%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%E2%80%99m%20getting%20multiple%20Success%20rows%20for%20same%20deleted%20resource%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DimitriArtemov_0-1635750885329.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F322097iA0DF52D95DE65E66%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22DimitriArtemov_0-1635750885329.jpeg%22%20alt%3D%22DimitriArtemov_0-1635750885329.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20go%20to%20portal%2C%20I%20see%20that%20resource%20deletion%20shown%20as%20if%20they%20use%20MAX%20from%20available%20timegenerated%20(Time%20stamp)%20values%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DimitriArtemov_1-1635750885345.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F322098i554E2C4281B23757%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22DimitriArtemov_1-1635750885345.jpeg%22%20alt%3D%22DimitriArtemov_1-1635750885345.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20if%20I%20use%20TQL%20as%20below%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAzureActivity%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%3ETimeGenerated%3C%2FSPAN%3E%3CSPAN%3E%20%26gt%3B%20%3C%2FSPAN%3E%3CSPAN%3Eago%3C%2FSPAN%3E%3CSPAN%3E(24h)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%3EOperationNameValue%3C%2FSPAN%3E%20%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E%22MICROSOFT.DOCUMENTDB%2FDATABASEACCOUNTS%2FDELETE%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3EActivityStatusValue%3C%2FSPAN%3E%3CSPAN%3E%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Success%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%20%3CSPAN%3Ep%3C%2FSPAN%3E%3CSPAN%3E%3D%3C%2FSPAN%3E%3CSPAN%3Eparse_json%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EProperties%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%20%3CSPAN%3EResourceDeleted%3C%2FSPAN%3E%3CSPAN%3E%3D%3C%2FSPAN%3E%3CSPAN%3Esplit%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E_ResourceId%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E%22%2F%22%3C%2FSPAN%3E%3CSPAN%3E%2C%208)%5B0%5D%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%20%3CSPAN%3ETimeGenerated%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3EOperationNameValue%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3EActivityStatusValue%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3EResourceDeleted%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eorder%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%20%3CSPAN%3ETimeGenerated%3C%2FSPAN%3E%20%3CSPAN%3Edesc%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%3EDeleteTime%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3Emax%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ETimeGenerated%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%20%3CSPAN%3EOperationNameValue%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3EActivityStatusValue%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3Etostring%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EResourceDeleted%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%E2%80%99m%20getting%20same%20result%20as%20in%20a%20portal%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DimitriArtemov_2-1635750885351.jpeg%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F322096i8666407F98F6DE01%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22DimitriArtemov_2-1635750885351.jpeg%22%20alt%3D%22DimitriArtemov_2-1635750885351.jpeg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EMy%20question%3A%20why%20do%20we%20have%20multiple%20success%20rows%20and%20is%20MAX%20would%20be%20correct%20to%20get%20actual%20resource%20deletion%20(we%20will%20use%20the%20query%20for%20Alerting)%3F%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

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

DimitriArtemov_0-1635750885329.jpeg

 

If I go to portal, I see that resource deletion shown as if they use MAX from available timegenerated (Time stamp) values

DimitriArtemov_1-1635750885345.jpeg

 

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

DimitriArtemov_2-1635750885351.jpeg

 

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

1 Reply

@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).
Screenshot 2021-11-01 172340.png

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,OperationNameValueActivityStatusValueResourceDeleted) by OperationNameValue