Forum Discussion
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 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
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 hereThere 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
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
Microsoft
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 hereThere 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
Thank you so much Clive that worked !!
I'll join the Log Analytics community as well I have few other ones that needs correction.
