How do you order stats by multiple hierarchical fields

[mbintz]

There are similar questions to this, but none are quite the same so I apologize for the overlap.

Suppose I have a set of data (events) that have a type and a subtype.

type = A, subtype = A1, A2, A3
type = B, subtype = B1, B2
type = C, subtype = (empty list)

So the events might look like this in time order:

event 1, type = C, ...
event 2, type = A, subtype=A3, ...
event 3, type = A, subtype=A1, ...
event 4, type = B, subtype=B2, ...
event 5, etc...

I've done searches similar to the following:

search index=events | stats count(type),count(subtype) by type,subtype

But those results do not exhibit the desired grouping. I would like for the resulting table to look like:

type | count(type) | subtype | count(subtype)
A           2           A1            1
                        A2            1
B           1           B1            1
C           1

[lguinn2 · Accepted solution]

This should work:

search index=events 
| stats count(type) as tcount ,count(subtype) as scount by type,subtype
| stats sum(tcount) as "Type Count" list(scount) as "Subtype Count" list(subtype) as "Subtype" by type

[somesoni2]

Probably throw a fillnull for subtype before first stats, as it can be null/empty and stats will ignore the type without a subtype.