combining distinct count and count in one table

[JWBailey]

I have two indexers that are being load balanced. I am interested to see the distribution of events from each host onto the indexers.

To get a list of hosts and the number of events on each indexer, I can run:

host=* | stats count by host splunk_server | sort host

I am also interested in hosts that are only writing to a single indexer. To get this I can run:

host=* | stats dc(splunk_server) as Splunk_Server by host | where Splunk_Server==1

How can I combine the two to show me the number of events from a host when it is only on a single indexer?

I am still running splunk 5 if that is relevant.

Thanks.

[somesoni2 · Accepted solution]

Try following (faster as well)

| metasearch host=* | stats first(splunk_server) as Indexer dc(splunk_server) as CountIndexers, count by host | where CountIndexers=1 | fields - CountIndexers

[somesoni2]

yw. Also, always use "|metasearch" if you're dealing with just any of these fields - host,source, sourcetype, index, splunk_server.

[JWBailey]

OK... i see.. you can perform multiple different operations within the same stats command.

Thank you.