SOLVED
Home

updating my OMS queries

%3CLINGO-SUB%20id%3D%22lingo-sub-542781%22%20slang%3D%22en-US%22%3Eupdating%20my%20OMS%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-542781%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20after%20Jan%202019%20%22search%22%20and%20%22union%22%20is%26nbsp%3Bnot%20supported%20in%20log%20queries.%20I%20have%20tried%20updating%20my%20all%20queries%20but%20could%26nbsp%3Bnot%20find%20any%20other%20workarounds%20for%20resolving%20these%20errors.%20here%20is%20my%20query%20for%20checking%26nbsp%3B%3CSPAN%3EDevices%20with%20Signatures%20Out%20of%20Date%20I%26nbsp%3Bneed%20help%20for%20tweaking%20this%20query%20so%20that%20it%20runs%20just%20as%20it%20use%20to%20do.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3Esearch%20in%20(ProtectionStatus)%20*%20%7C%20summarize%20Rank%20%3D%20max(ProtectionStatusRank)%20by%20Computer%20%7C%20limit%20500000%20%7C%20where%20Rank%20%3D%3D%20%22250%22%20%2F%2F%20Oql%3A%20Type%3DProtectionStatus%20%7C%20measure%20max(ProtectionStatusRank)%20as%20Rank%20by%20Computer%20%7C%20top%20500000%20%7C%20where%20Rank%3A250%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-542781%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnalytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESecurity%20%26amp%3B%20Compliance%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-551677%22%20slang%3D%22en-US%22%3ERe%3A%20updating%20my%20OMS%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-551677%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337292%22%20target%3D%22_blank%22%3E%40vicky2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20ran%20your%20query%20and%20it%20seems%20to%20work%20fine%20for%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3Esearch%20in%20(ProtectionStatus)%20*%20%7C%20summarize%20Rank%20%3D%20max(ProtectionStatusRank)%20by%20Computer%20%7C%20limit%20500000%20%7C%20where%20Rank%20%3D%3D%20%22250%22%20%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EThe%20first%20bit%20above.%20did%20return%20no%20results%20as%20I%20had%20no%20250.%20So%20I%20removed%20the%20whare%20and%20got%20a%20list%20of%20results.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EThe%20second%20part%20of%20your%20query%20below%20is%20commented%20out.%20so%20it%20does%20not%20get%20used.%20the%20%2F%2F%20comments%20it%20out.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%2F%2F%20Oql%3A%20Type%3DProtectionStatus%20%7C%20measure%20max(ProtectionStatusRank)%20as%20Rank%20by%20Computer%20%7C%20top%20500000%20%7C%20where%20Rank%3A250%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EWhat%20are%20you%20expecting%20to%20see%3F%20Can%20you%20try%20running%20the%20first%20bit%20without%20the%20%7C%20where%26nbsp%3BRank%20%3D%3D%20%22250%22%20%3F%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ELet%20me%20know%20how%20you%20get%20on.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-557910%22%20slang%3D%22en-US%22%3ERe%3A%20updating%20my%20OMS%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-557910%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F50205%22%20target%3D%22_blank%22%3E%40Richard%20Hooper%3C%2FA%3E%20and%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F337292%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40vicky2019%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EProtectionStatus%20%0A%7C%20summarize%20Rank%20%3D%20max(ProtectionStatusRank)%20by%20Computer%20%0A%7C%20where%20Rank%20%3D%3D%20%22250%22%3C%2FPRE%3E%0A%3CP%3EYou'd%20actually%20write%20it%20like%20the%20above%20example%2C%20a%20%3CSTRONG%3Esearch%3C%2FSTRONG%3E%20is%20unnecessary%20as%20you%20know%20the%20table%20you%20are%20looking%20at.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20can%20run%20the%20above%20in%20the%20free%20demo%20portal%20%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAAwsoyi9JTS7JzM8LLkksKS1W4OWqUSguzc1NLMqsSlVIzi%252FNK9HQ1FEISszLVrBVyE2s0AhA0wKS0lRIqlRwzs8tKC1JLQKbUZ6RWpQK1WaroGRkaqDEBQD%252Fa%252B4LbQAAAA%253D%253D%26amp%3Btimespan%3DP1D%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EThere%20are%20Rank%3D%3D250%20entries%20available%20there%20for%20you%20to%20test%20your%20code%20on.%26nbsp%3B%20%26nbsp%3BI'd%20also%20probably%20do%20a%20count%20of%20the%20records%20and%20a%20top%205%20or%2010%20like%20this%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EProtectionStatus%20%0A%7C%20summarize%20count()%2C%20Rank%20%3D%20max(ProtectionStatusRank)%20by%20Computer%20%0A%7C%20where%20Rank%20%3D%3D%20%22250%22%0A%7C%20top%205%20by%20count_%20desc%20%3C%2FPRE%3E%0A%3CP%3E%3CSTRONG%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20622px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112924i4B293D5A6C8C3CB5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-05-10%20124241.png%22%20title%3D%22Annotation%202019-05-10%20124241.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUsing%20%3CSTRONG%3Elimit%3C%2FSTRONG%3E%20or%20%3CSTRONG%3Etop%3C%2FSTRONG%3E%20of%20500%2C000%20isn't%20necessary%20(10k%20records%20is%20default%20max%20returned%20anyway).%26nbsp%3B%20The%20fact%20you%20are%20using%20a%20%3CSTRONG%3Esummarize%3C%2FSTRONG%3E%20massively%20reduces%20the%20return%20record%20count%20(usually)%20as%20well.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20is%20also%20a%20dedicated%20Log%20Analytics%20page%20on%20tech%20Community%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Log-Analytics%2Fbd-p%2FAzureLogAnalytics%22%20target%3D%22_self%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Log-Analytics%2Fbd-p%2FAzureLogAnalytics%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-558159%22%20slang%3D%22en-US%22%3ERe%3A%20updating%20my%20OMS%20queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-558159%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20Clive%20that%20worked%20!!%3C%2FP%3E%3CP%3EI'll%20join%20the%20Log%20Analytics%20community%20as%20well%20I%20have%20few%20other%20ones%20that%20needs%20correction.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 

 

 

3 Replies
Highlighted

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

Highlighted
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 

Annotation 2019-05-10 124241.png

 

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

 

Highlighted

Thank you so much Clive that worked !!

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