Forum Discussion

vicky2019's avatar
vicky2019
Copper Contributor
May 07, 2019
Solved

updating my OMS queries

So after Jan 2019 "search" and "union" is not supported in log queries. I have tried updating my all queries but could not find any other workarounds for resolving these errors. here is my query for ...
analytics
azure
Security & Compliance
  • CliveWatson's avatar
    CliveWatson
    May 10, 2019

    Hi Richard_Hooper and @vicky2019  

     

    ProtectionStatus 
| summarize Rank = max(ProtectionStatusRank) by Computer 
| where Rank == "250"

    You'd actually write it like the above example, a search is unnecessary as you know the table you are looking at.

    You can run the above in the free demo portal https://portal.loganalytics.io/Demo?q=H4sIAAAAAAAAAwsoyi9JTS7JzM8LLkksKS1W4OWqUSguzc1NLMqsSlVIzi%2FNK9HQ1FEISszLVrBVyE2s0AhA0wKS0lRIqlRwzs8tKC1JLQKbUZ6RWpQK1WaroGRkaqDEBQD%2Fa%2B4LbQAAAA%3D%3D&timespan=P1D

    There are Rank==250 entries available there for you to test your code on.   I'd also probably do a count of the records and a top 5 or 10 like this: 

    ProtectionStatus 
| summarize count(), Rank = max(ProtectionStatusRank) by Computer 
| where Rank == "250"
| top 5 by count_ desc

     

    Using limit or top of 500,000 isn't necessary (10k records is default max returned anyway).  The fact you are using a summarize massively reduces the return record count (usually) as well.

     

    There is also a dedicated Log Analytics page on tech Community here https://techcommunity.microsoft.com/t5/Azure-Log-Analytics/bd-p/AzureLogAnalytics

     

CliveWatson's avatar
CliveWatson
Former Employee
May 10, 2019

Hi Richard_Hooper and @vicky2019  

 

ProtectionStatus 
| summarize Rank = max(ProtectionStatusRank) by Computer 
| where Rank == "250"

You'd actually write it like the above example, a search is unnecessary as you know the table you are looking at.

You can run the above in the free demo portal https://portal.loganalytics.io/Demo?q=H4sIAAAAAAAAAwsoyi9JTS7JzM8LLkksKS1W4OWqUSguzc1NLMqsSlVIzi%2FNK9HQ1FEISszLVrBVyE2s0AhA0wKS0lRIqlRwzs8tKC1JLQKbUZ6RWpQK1WaroGRkaqDEBQD%2Fa%2B4LbQAAAA%3D%3D&timespan=P1D

There are Rank==250 entries available there for you to test your code on.   I'd also probably do a count of the records and a top 5 or 10 like this: 

ProtectionStatus 
| summarize count(), Rank = max(ProtectionStatusRank) by Computer 
| where Rank == "250"
| top 5 by count_ desc

 

Using limit or top of 500,000 isn't necessary (10k records is default max returned anyway).  The fact you are using a summarize massively reduces the return record count (usually) as well.

 

There is also a dedicated Log Analytics page on tech Community here https://techcommunity.microsoft.com/t5/Azure-Log-Analytics/bd-p/AzureLogAnalytics

 

vicky2019's avatar
vicky2019
Copper Contributor
May 10, 2019

Thank you so much Clive that worked !!

I'll join the Log Analytics community as well I have few other ones that needs correction.

 

Resources