updating my OMS queries

[vicky2019]

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 checking Devices with Signatures Out of Date I need help for tweaking this query so that it runs just as it use to do.

search in (ProtectionStatus) * | summarize Rank = max(ProtectionStatusRank) by Computer | limit 500000 | where Rank == "250" // Oql: Type=ProtectionStatus | measure max(ProtectionStatusRank) as Rank by Computer | top 500000 | where Rank:250 

[#2]

@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.

[#3 · Accepted solution]

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 here

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

[#4]

Thank you so much Clive that worked !!

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