Expanding Alert Log Search Query

[tkeller]

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.

[#2]

@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

[#3]

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

[#4]

@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

[#5]

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

[#6]

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

[#7 · Accepted 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

[#8]

@hspintoyou da man!