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.
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
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
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
Wow, that's lightning fast. I wasn't aware of | metasearch. Thanks!
Try this:
index=* earliest=-30m@m | dedup index sourcetype host| stats dc(host) AS hostcount,values(sourcetype) AS stlist by index
Enjoy.
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.
oooh right. nice catch. I edited the search. Thanks!
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'