Group hosts by Sourcetype by Index

[king2jd]

Hello,

I am trying to perform a search that groups all hosts by sourcetype and groups those sourcetypes by index. So far I have this:

| tstats values(host) AS Host, values(sourcetype) AS Sourcetype WHERE index=* by index

But this search does map each host to the sourcetype. Instead it shows all the hosts that have at least one of the resulting sourcetypes as a sourcetype.

[rjthibod · Accepted solution]

How about this?

| tstats count where index=* by index sourcetype host 
| stats list(host) as Hosts by index sourcetype

[king2jd]

Does exactly what I needed. Thanks for your help!

[gcusello]

have you tried
stats count by host, sourcetype, index OR tstats count by host, sourcetype, index ?

Bye.
Giuseppe

[rjthibod]

Can you give an example of what the end data should look like in table format?

[king2jd]

Index1----sourcetype1-----host1
------host2
------sourcetype2---host 3
Index2-----sourcetype3----host1
----host5

Does this help you?

[king2jd]

That came out worse than I thought but essentially
index1-sourcetype1-host1,host2
index2-sourcertype2-host1,host4