SOLVED

Expanding Alert Log Search Query

Copper 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

@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
 

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

@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

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

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

best response confirmed by tkeller (Copper Contributor)
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
 

 

@hspintoyou da man! 

1 best response

Accepted Solutions
best response confirmed by tkeller (Copper Contributor)
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
 

 

View solution in original post