Forum Discussion
tkeller
Dec 03, 2019Copper Contributor
Expanding Alert Log Search Query
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.
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 desc2) 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
- hspinto
Microsoft
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- hspinto
Microsoft
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!