I'm trying to build a table of outages. For example:
Host Num. Outages
A 1
B 2
C 0
Servers will log an outage with the words "outage detected" in a field called errorMsg
My search would normally be as follows:
index=serverdata errorMsg="outage detected" | stats count by host
However, if no outages are detected, then no events are logged. So the above command would only show results for hosts A and B. I would also like it to show 0 for C.
How can I achieve this?
Try this
index=serverdata errorMsg="outage detected" | stats count by host | append [| metadata type=hosts index=serverdata | table host | eval count=0] | stats max(count) as count by host
OR (slower)
| metadata type=hosts index=serverdata | table host | eval count=0 | join type=left host [search index=serverdata errorMsg="outage detected" | stats count by host]
Try this
index=serverdata errorMsg="outage detected" | stats count by host | append [| metadata type=hosts index=serverdata | table host | eval count=0] | stats max(count) as count by host
OR (slower)
| metadata type=hosts index=serverdata | table host | eval count=0 | join type=left host [search index=serverdata errorMsg="outage detected" | stats count by host]
Thanks for the suggestion. This appears to work if there is at least 1 event but will return no results if there are no outages at all.
What about second search? Does that also not producing result when there are no Outage?
oh you're right, the second search does work! Thanks!
Follow-up question: Is it possible to do this with anything other than the default fields (host/source/sourcetype)? Reading the docs for metadata it seems like it only returns the values for those three. My events also return a field called "app" which has the app which caused the outage and I would like to get a similar count by app.
Would I just write those values to a summary index myself?
You're right, the metadata command will return summaries by hosts/sourcetypes/sources and we can't add custom fields.
You can either do a summary indexing to get list of host-app combination or you can use a lookup table file to store the same (save on space). A sample daily search would be like this.
| inputlookup host_app_lookup.csv | append [search earliest=-1d@d latest=@d index=serverdata | stats count by host,app | table host,app] | stats count by host,app | table host,app | outputlookup append=false host_app_lookup.csv
You can run this every night at 1 AM and it will update the lookup table with new combinations of app-host found yesterday.