count list host count by sourcetype, sourcetype by index

[proletariat99]

Hi,
This seems like it would be simple, but I can't figure it out for the life of me. I really like the stats list layout for dashboard panels where you can have a list of results as a subset of parent results. The most useful use case for this, IMO, is to create a list of all splunk indexes, and the sourcetypes associated with each index (as a list). This is pretty easy:

index=* earliest=-30m@m | dedup index sourcetype | stats list(sourcetype) by index

Beautiful layout, relatively quick search, and it's almost perfect. But I want to add a count of hosts per sourcetype to the list so that the count of hosts is on the same line item as sourcetype. I thought this would work:

index=* earliest=-30m@m | dedup index sourcetype host | stats count(host) as HostCount by sourcetype |stats list(HostCount) by index sourcetype

but alas... it doesn't. I'm pretty sure it's Splunk's fault, because clearly my logic is flawless. 🙂 However, could someone please help?
Thanks.

[wrangler2x]

Building off of @somesoni2's answer, here is that search taken into the new 6.2+ feature tstats, which is incredibly faster! Just enter this whole string on one line in search:

| tstats values(host) AS Host dc(host) as count by sourcetype,index | eval sourcetype=sourcetype." - ".count | stats list(sourcetype) as "sourcetype-hostCount" by index, Host

[somesoni2 · Accepted solution]

Will this work for you...

|metasearch index=* sourcetype=* host=* | stats dc(host) as count by sourcetype,index | eval sourcetype=sourcetype."-".count | stats list(sourcetype) as "sourcetype-hostCount" by index

Output format

**index   sourcetype-hostCount**
--------------------------------------
main      access_combined_wcookie-1
          buffer-1
          data-1
          email-1
          empData-1
          generic-1
          urldata-1
myindex   javalog-1

[proletariat99]

Wow, that's lightning fast. I wasn't aware of | metasearch. Thanks!

[jtrucks]

Try this:

index=* earliest=-30m@m | dedup index sourcetype host| stats dc(host) AS hostcount,values(sourcetype) AS stlist by index

Enjoy.

[proletariat99]

That's really helpful in variety of ways, but I'm actually looking for the count of hosts per sourcetype. I think this does it properly:

index=*_na |eventstats dc(host) as device by sourcetype| dedup sourcetype|stats values(sourcetype) as "Source Type" list(device) as "Device Count" by index |sort + index, +"Source Type"

Unfortunately, it's ridiculously costly.

[jtrucks]

oooh right. nice catch. I edited the search. Thanks!

[lukejadamec]

This search give the count for host sourcetype combinations by index.

Try switching count with dc.
`index=* earliest=-30m@m | dedup index sourcetype host| stats dc(host) AS hostcount,values(sourcetype) AS stlist by index'