Show stats for a fixed set of hosts

[prashantjois]

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?

[somesoni2 · Accepted solution]

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]

[prashantjois]

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.

[somesoni2]

What about second search? Does that also not producing result when there are no Outage?

[prashantjois]

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?

[somesoni2]

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.