Export a csv of all hosts and their sources?

[Peter]

I need to generate a splunk coverage report that shows all of the hosts and all of the sources they are sending from. What would this search look like and how can I export it? I've tried chart commands, but the "Other" section and the matrix format makes this incomplete for my purposes.

[Simeon]

The above search will work for small instances or low volume scenarios. If you need a quick way to get the hosts and sources information separately:

| metadata type=hosts

OR

| metadata type=sources

For information over the last 24 hours:

| metadata type=hosts | eval diff=now()-recentTime | where diff < 86400 | convert ctime(*Time)

OR

| metadata type=sources | eval diff=now()-recentTime | where diff < 86400 | convert ctime(*Time)

If you need to find information for a different time range, modify the 86400 value to your desired time (in seconds).

Also, the first two above searches will give you a very fast and complete summary of all hosts and sources.

[oreoshake · Accepted solution]

Hmmm, this is a terribly inefficient way of doing it...but if you only need to do it once...

* | dedup host, source | stats count by host, source | sort host

I usually run this search when verifying that I'm getting all of the inputs that I want

[oreoshake]

That's true, I just use the dedup command to show when the first instance of that source came through. I used to think it counter intuitively sped up the searches, but that's not the case.

[gkanapathy]

typo, should be limit=0 not limit=*

[gkanapathy]

this works, but the dedup clause is unnecessary here as stats does the same work. The difference is you'd always wind up with a count of 1 with the dedup there first. You could also do * | top limit=0 host,source and get percentages in addition to count, or * | top limit=0 host by source, or * | top limit=* source by host which will give you slightly different breakdowns.