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

[_smp_]

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

[somesoni2 · Accepted solution]

This should do it

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

[_smp_]

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!

[woodcock]

Try this:

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

[_smp_]

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?

[_smp_]

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?

[sundareshr]

Try this

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

[_smp_]

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