Forum Discussion

Copper Contributor

Dec 03, 2019

Solved

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

Azure Alert Logs Query

azure monitor

hspinto
Dec 05, 2019
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

hspinto

Microsoft

Dec 04, 2019

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!

tkeller

Copper Contributor

Dec 05, 2019

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!

hspinto
Microsoft
Dec 05, 2019
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
- tkeller
  Copper Contributor
  Dec 05, 2019
  hspintoyou da man!