topic Re: How to group by host, then severity, and include a count for each severity? in Splunk Search

How to group by host, then severity, and include a count for each severity?

_smp_ — Tue, 14 Jun 2016 03:22:48 GMT

I know this is probably very trivial to most, but I am a pretty new user. I am struggling quite a bit with a simple task: to group events by host, then severity, and include the count of each severity. I have gotten the closest with this:

| stats values(severity) as Severity, count(severity) by severity, host

This comes close, but there are two things I need to change:
1) The output includes an duplicate column of the severities
2) The output contains the host in each row of output.

Is it possible to group by host, then severity, and include a count for each severity? Like this?

serverA    Low          20
           Medium       15
           High         11
serverB    Medium       6
           High         15

Re: How to group by host, then severity, and include a count for each severity?

sundareshr — Tue, 14 Jun 2016 04:12:29 GMT

Try this

... | chart count over host by severity | untable host severity count | stats values(severity) as Severity  values(count) as Count by host

Re: How to group by host, then severity, and include a count for each severity?

_smp_ — Tue, 14 Jun 2016 12:57:32 GMT

Thanks for the reply. This is close, but not quite. There are two problems with this output:
1) There is a "NULL" value for every group of severities, and the count is 0.
2) Aside from the Count of Null values (0), there is only one other Count, instead of counting each Severity.

The output looks like this:

XXX.XX.XXX.XXX      NULL                 0
                      critical             901
                      high
                      informational
                      low
                      medium

Re: How to group by host, then severity, and include a count for each severity?

_smp_ — Tue, 14 Jun 2016 13:18:17 GMT

As I continue to scan Answers, I came across this which is pretty much what I'm after:

index=main | stats count by host severity

Pretty simple. However, is there a way to group them by host, instead of separate lines for each unique combination of values?

Re: How to group by host, then severity, and include a count for each severity?

woodcock — Tue, 14 Jun 2016 19:18:33 GMT

Try this:

 ... severity="*" | chart count over host BY severity

Re: How to group by host, then severity, and include a count for each severity?

somesoni2 — Tue, 14 Jun 2016 19:51:08 GMT

This should do it

index=main | stats count by host severity | stats list(severity) as severity list(count) as count by host

Re: How to group by host, then severity, and include a count for each severity?

_smp_ — Tue, 14 Jun 2016 19:53:00 GMT

Thank you for the response. That seems to calculate the values correctly, but is there a way to display the results in the format I noted?

Re: How to group by host, then severity, and include a count for each severity?

_smp_ — Tue, 14 Jun 2016 19:58:37 GMT

Yep, that's the answer, thank you very much. This shows me how much I have to learn - that query is more complex than I expected it to be. Thanks again!