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

     

Richard_Hooper's avatar
Richard_Hooper
MVP
May 09, 2019

vicky2019 

 

I ran your query and it seems to work fine for me.

 

search in (ProtectionStatus) * | summarize Rank = max(ProtectionStatusRank) by Computer | limit 500000 | where Rank == "250"

 

The first bit above. did return no results as I had no 250. So I removed the whare and got a list of results.

 

The second part of your query below is commented out. so it does not get used. the // comments it out.

 

// Oql: Type=ProtectionStatus | measure max(ProtectionStatusRank) as Rank by Computer | top 500000 | where Rank:250 

 

What are you expecting to see? Can you try running the first bit without the | where Rank == "250" ?

 

Let me know how you get on.

  • 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