Forum Discussion

Copper Contributor

Nov 30, 2017

Solved

Remove duplicates from query

Hi, hope somebody can help me as I'm a bit stuck in my understanding of the query language. So I'm trying to get some creation events for App Services, though there seems to be multiple entries for...

azure monitor

Query Language

Stanislav_Zhelyazkov
Dec 01, 2017
So does arg_max() does the job? From my understanding is the thing that will work for you.

Morten Lerudjordet

Copper Contributor

Dec 01, 2017

Thanks Stan, sorry for the bad description.

So my original query to get information about deleted App Services returns multiple events for the deletion of the same App. Though these have the same CorrelationId. Therefore I was trying with my limited knowledge of the query language to only return 1 record that has the same CorrelationId. Though still get all the columns the event contains.

I'm trying to use this to drive a webhook and trigger a runbook to do some house cleaning on both creation and deletion of App Services.

My thinking is that the best way to drive the runbook was to use the query language to filter out all the data that the runbook does not need to make its logic work.

Hopefully this makes more sense?

Morten Lerudjordet

Copper Contributor

Dec 01, 2017

If one just ignores time at the moment this almost gets me there but it will only retain CorrelationId on the result output, but I need to get all the columns (or use project to choose the ones I need)

AzureActivity 
| where OperationName == 'Delete website' and ActivityStatus == 'Succeeded' and ResourceProvider == 'Azure Web Sites' 
| summarize by CorrelationId

Morten Lerudjordet
Copper Contributor
Dec 01, 2017
Think I maybe figured it out?

Something like this seems to work

AzureActivity | where OperationName == 'Delete website' and ActivityStatus == 'Succeeded' and ResourceProvider == 'Azure Web Sites' | project ResourceId, CorrelationId, TimeGenerated | summarize arg_max(TimeGenerated, *) by CorrelationId
- Stanislav_Zhelyazkov
  MVP
  Dec 01, 2017
  Yes. Forgot the '*' in arg_max() sorry. It is best practice to project at the end after summarize
  - Morten Lerudjordet
    Copper Contributor
    Dec 01, 2017
    Seems I can also do this, so I dont need to do project at the end:
    
    AzureActivity | where OperationName == 'Delete website' and ActivityStatus == 'Succeeded' and ResourceProvider == 'Azure Web Sites' | summarize arg_max(TimeGenerated, ResourceId, Resource, ResourceGroup, OperationName) by CorrelationId
    
    Thanks for steering me in the right direction.
Stanislav_Zhelyazkov
MVP
Dec 01, 2017
Hmm the query you've posted last is different from the one I've posted. It does not contain arg_max(). arg_max() retains all columns. If you do not want to use arg_max() when you get two results what is the difference between them? Which one of the two you want to be displayed only?