How do write a search to list all indexes associated with a sourcetype?

[hkj2332]

I have no trouble listing all the sourcetypes associated with an index, but I need to go the other way - What are all the indexes for a given sourcetype. The search I started with for this is:

index=* OR index=_* sourcetype= SourceTypeName | dedup index | table index

However, this is very slow (not a surprise), and, more a concern, not returning all the indexes that use that sourcetype.

Is there a faster search I can use to do this and not miss associated indices?

Thanks!

[somesoni2 · Accepted solution]

Another faster method availabe 6.1 onward Splunk versions is tstats. Try something like this

| tstats count WHERE index=* sourcetype=* by index, sourcetype | fields - count

I've seen it run much faster then metasearch.

[hkj2332]

Just tried tstats - way faster. Thanks!

[MuS]

Hi hkl2332,

much faster to use metasearch as it search only in the metadata of the specified indexes:

| metasearch index=* sourcetype=* | stats count by index, sourcetype | fields - count

See the docs http://docs.splunk.com/Documentation/Splunk/6.3.0/SearchReference/Metasearch for more details.

cheers, MuS

[hkj2332]

That'll get me a list of all the sourcetypes - any idea how to also get the indices that use that sourcetype in the resulting table?

[MuS]

Oh sorry my bad 🙂 mess up metadata and metasearch so see my updated answer 😉

[hkj2332]

Oh, of course... Thanks!!! I've run this search before, but for some reason I was totally forgetting I can order by EITHER column. Thank you!

[jagadeeshm]

@MuS - Is there anyway to get this working in dependent of time?

I want to list ALL index and the sourcetype.

[MuS]

@jagadeeshm you can run a tstats search

 | tstats count where index=* by sourcetype, index, _time | timechart sum(count) AS count by index