SOLVED

Expanding Alert Log Search Query

%3CLINGO-SUB%20id%3D%22lingo-sub-1045249%22%20slang%3D%22en-US%22%3EExpanding%20Alert%20Log%20Search%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1045249%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20-%3C%2FP%3E%3CP%3EWe%20have%20an%20alert%20setup%20that%20does%20the%20following%3A%3C%2FP%3E%3CP%3EW3CIISLog%3CBR%20%2F%3E%7C%20where%20Computer%20%3D%3D%20%22W9%22%20or%20Computer%20%3D%3D%20%22W10%22%3CBR%20%2F%3E%7C%20summarize%20Hits%3Dcount()%20by%20cIP%3CBR%20%2F%3E%7C%20where%20Hits%20%26gt%3B%3D%20600%3CBR%20%2F%3E%7C%20where%20cIP%20!startswith%20%2210.0%22%3CBR%20%2F%3E%7C%20order%20by%20Hits%20desc%3C%2FP%3E%3CP%3EAnd%20what%20I%20would%20like%20is%20for%20those%20results%20to%20then%20be%20piped%20into%20this%20query%20and%20the%20results%20returned%3A%3C%2FP%3E%3CP%3EW3CIISLog%3CBR%20%2F%3E%7C%20where%20cIP%20%3D%3D%20%22192.34.85.134%22%3CBR%20%2F%3E%7C%20summarize%20count%20()%20by%20csUriStem%3CBR%20%2F%3E%7C%20order%20by%20count_%20desc%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1045249%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Alert%20Logs%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1045506%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20Alert%20Log%20Search%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1045506%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F476764%22%20target%3D%22_blank%22%3E%40tkeller%3C%2FA%3E%2C%20something%20like%20this%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EW3CIISLog%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20Computer%20%3D%3D%20%22W9%22%20or%20Computer%20%3D%3D%20%22W10%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20summarize%20Hits%3Dcount()%20by%20cIP%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20Hits%20%26gt%3B%3D%20600%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20cIP%20!startswith%20%2210.0%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%7C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3Ejoin%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%20kind%3Dinner%20(%3CSPAN%3EW3CIISLog%26nbsp%3B%3C%2FSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3Esummarize%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%20UriHits%20%3D%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3Ecount%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E()%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3Eby%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%20%3CSPAN%3EcIP%3C%2FSPAN%3E%2C%20%3CSPAN%3EcsUriStem%3C%2FSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3Eon%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%20%3CSPAN%3EcIP%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3EcIP%3C%2FSPAN%3E%2C%20Hits%2C%20%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3EcsUriStem%3C%2FSPAN%3E%2C%20%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3EUriHits%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eorder%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Hits%20desc%2C%20%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3EUriHits%26nbsp%3B%3C%2FSPAN%3Edesc%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1045841%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20Alert%20Log%20Search%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1045841%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3Ewill%20check%20this%20out%20tomorrow%2C%20thanks%20for%20the%20reply%20(y)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1047775%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20Alert%20Log%20Search%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1047775%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3Ethat%20does%20indeed%20return%20part%20of%20the%20desired%20results%2C%20but%20it%20truncates%20them...%20I%20assume%20because%20of%20%22Summarize%22%3F%26nbsp%3B%20Is%20there%20a%20better%20results%20collector%2C%20and%20is%20there%20a%20way%20to%20better%20format%20the%20results%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1047973%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20Alert%20Log%20Search%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1047973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F476764%22%20target%3D%22_blank%22%3E%40tkeller%3C%2FA%3E%2C%20what%20do%20you%20mean%20by%20truncating%3F%20Do%20you%20wanted%20to%20see%20more%20columns%20or%20more%20rows%3F%20Summarize%20indeed%20reduces%20the%20column%20span%20to%20what%20is%20specified%20in%20the%20aggregation%20-%20if%20you%20want%20more%20columns%2C%20you%20have%20to%20add%20them%20to%20the%20aggregation.%20Regarding%20row%20count%2C%20Log%20Analytics%20has%20a%20limit%20on%20the%20number%20of%20rows%20returned%20by%20a%20query%20(10%2C000).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20elaborate%20more%20on%20the%20results%20format%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1051555%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20Alert%20Log%20Search%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1051555%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3EIt%20is%20working%20very%20well%20it's%20just%20the%20Alert%20email%20is%20truncated%2C%20but%20clicking%20on%20the%20link%20to%20the%20results%20gives%20all%20the%20details.%26nbsp%3B%20Ok%20Last%20Question...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20set%20a%20low%20threshold%20i.e.%20I%20don't%20care%20about%20hits%20or%20URLS%20less%20than%2050%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20extremely%20helpful%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3E%20%2C%20thank%20you%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1051667%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20Alert%20Log%20Search%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1051667%22%20slang%3D%22en-US%22%3E%3CP%3EGlad%20I%20am%20helping%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F476764%22%20target%3D%22_blank%22%3E%40tkeller%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERegarding%20the%20low%20threshold%2C%20I%20am%20not%20sure%20I%20understood%20what%20you%20meant%2C%20but%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%5BI%20am%20also%20structuring%20the%20query%20better%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20If%20you%20want%20to%20exclude%20hits%2FURLs%20counts%20less%20than%2050%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EW3CIISLog%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20(Computer%20%3D%3D%20%22W9%22%20or%20Computer%20%3D%3D%20%22W10%22)%20and%26nbsp%3BcIP%20!startswith%20%2210.0%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20summarize%20Hits%3Dcount()%20by%20cIP%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20Hits%20%26gt%3B%3D%20600%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bkind%3Dinner%20(W3CIISLog%26nbsp%3B%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BUriHits%20%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BcIP%2C%26nbsp%3BcsUriStem)%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BcIP%3C%2FSPAN%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BcIP%2C%20Hits%2C%26nbsp%3BcsUriStem%2C%26nbsp%3BUriHits%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20where%20Hits%20%26gt%3B%2050%20and%20UriHits%20%26gt%3B%2050%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eorder%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BHits%20desc%2C%26nbsp%3BUriHits%26nbsp%3Bdesc%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CP%3E2)%20If%20you%20want%20just%20the%20topmost%2050%20cIP%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EW3CIISLog%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20(Computer%20%3D%3D%20%22W9%22%20or%20Computer%20%3D%3D%20%22W10%22)%20and%26nbsp%3BcIP%20!startswith%20%2210.0%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20summarize%20Hits%3Dcount()%20by%20cIP%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20where%20Hits%20%26gt%3B%3D%20600%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%7C%20order%20by%20Hits%20desc%3C%2FP%3E%0A%3CP%3E%7C%20limit%2050%3CBR%20%2F%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bkind%3Dinner%20(W3CIISLog%26nbsp%3B%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BUriHits%20%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BcIP%2C%26nbsp%3BcsUriStem)%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BcIP%3C%2FSPAN%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BcIP%2C%20Hits%2C%26nbsp%3BcsUriStem%2C%26nbsp%3BUriHits%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eorder%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BHits%20desc%2C%26nbsp%3BUriHits%26nbsp%3Bdesc%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%2F%2F%20I%20don't%20think%20we%20can%20limit%20here%20further%20for%20UriHits%20results%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1051686%22%20slang%3D%22en-US%22%3ERe%3A%20Expanding%20Alert%20Log%20Search%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1051686%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3Eyou%20da%20man!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello -

We have an alert setup that does the following:

W3CIISLog
| where Computer == "W9" or Computer == "W10"
| summarize Hits=count() by cIP
| where Hits >= 600
| where cIP !startswith "10.0"
| order by Hits desc

And what I would like is for those results to then be piped into this query and the results returned:

W3CIISLog
| where cIP == "192.34.85.134"
| summarize count () by csUriStem
| order by count_ desc

Any help is greatly appreciated.

7 Replies
Highlighted

@tkeller, something like this?

 

W3CIISLog
| where Computer == "W9" or Computer == "W10"
| summarize Hits=count() by cIP
| where Hits >= 600
| where cIP !startswith "10.0"

| join kind=inner (W3CIISLog | summarize UriHits = count() by cIP, csUriStem) on cIP

| project cIP, Hits, csUriStem, UriHits
| order by Hits desc, UriHits desc
 
Highlighted

@hspintowill check this out tomorrow, thanks for the reply (y)

Highlighted

@hspintothat does indeed return part of the desired results, but it truncates them... I assume because of "Summarize"?  Is there a better results collector, and is there a way to better format the results?

Thank you very much

Highlighted

@tkeller, what do you mean by truncating? Do you wanted to see more columns or more rows? Summarize indeed reduces the column span to what is specified in the aggregation - if you want more columns, you have to add them to the aggregation. Regarding row count, Log Analytics has a limit on the number of rows returned by a query (10,000).

 

Can you elaborate more on the results format?

 

Thank you!

Highlighted

@hspintoIt is working very well it's just the Alert email is truncated, but clicking on the link to the results gives all the details.  Ok Last Question...

 

How to set a low threshold i.e. I don't care about hits or URLS less than 50 ?

 

You are extremely helpful @hspinto , thank you very much!

Highlighted
Solution

Glad I am helping, @tkeller.

 

Regarding the low threshold, I am not sure I understood what you meant, but:

 

[I am also structuring the query better]

 

1) If you want to exclude hits/URLs counts less than 50

 

W3CIISLog
| where (Computer == "W9" or Computer == "W10") and cIP !startswith "10.0"
| summarize Hits=count() by cIP
| where Hits >= 600

join kind=inner (W3CIISLog | summarize UriHits = count() by cIP, csUriStem) on cIP

project cIP, Hits, csUriStem, UriHits
| where Hits > 50 and UriHits > 50
order by Hits desc, UriHits desc
 

2) If you want just the topmost 50 cIP:

 

W3CIISLog
| where (Computer == "W9" or Computer == "W10") and cIP !startswith "10.0"
| summarize Hits=count() by cIP
| where Hits >= 600

| order by Hits desc

| limit 50
join kind=inner (W3CIISLog | summarize UriHits = count() by cIP, csUriStem) on cIP

project cIP, Hits, csUriStem, UriHits
order by Hits desc, UriHits desc
// I don't think we can limit here further for UriHits results
 

 

Highlighted

@hspintoyou da man!