I'm sure this is easy to do, but I'm a bit stumped. Say I have a search like this:
http_status="500" | stats count by client_address, url, server_name, http_status_description, http_method, http_version, user_agent, referrer
I want to generate an alert if the aggregate count is greater than a specified threshold, like 100, but cannot figure out how to do this...
Any help is appreciated.
i would say like:
httpstatus="500" | stats count(httpstatus) as ErrorCount by url|table servername,clientaddress,url,httpstatusdescription, httpmethod, httpversion, useragent, referrer,ErrorCount|where ErrorCount > 100
You can also get rid of some fields which are not really required, to keep the result table clean.
For the below requirement i would do stats count only, But will not be able to show other details. I would do a join to just show the count.
httpstatus="500" | stats count by url|join host[httpstatus="500" | stats count(httpstatus) as ErrorCount]|table servername,clientaddress,url,httpstatusdescription, httpmethod, httpversion, useragent, referrer,count,TotalErrorCount|where TotalError_Count > 100
this will show you TotalErrorcount same for each of the urls/ips.
Hope this gives you a hint, but it's not the correct condition for alert to see.
Actually, neither of these will work. I don't want to know where a single aggregate sum exceeds 100. I want to know if the sum total of all of the aggregate sums exceeds 100. For example, I may have something like this:
client_address url server count
10.0.0.1 /stuff /myserver.com 50
10.0.0.2 /stuff2 /myserver.com 51
I want the above result set to generate an alert because 50+51 > 100. If I simply check where count > 100, then any one result would need to have a count of 100 or more for the alert to be generated.
Hope this makes sense.
Based on the comment under jtrucks answer:
http_status="500" |eventstats count as TOTAL_COUNT| stats latest(TOTAL_COUNT) as TC count by client_address, url, server_name, http_status_description, http_method, http_version, user_agent, referrer | where TC > 100