topic How to get all indexes and sourcetypes? in Splunk Search

How to get all indexes and sourcetypes?

jagadeeshm — Fri, 27 Jan 2017 01:41:29 GMT

After browsing through Splunk Answers, the closest I could get is the following SPL to list all Indexes and Sourcetypes in a single table -

| eventcount summarize=false index=* index!=_* | dedup index | fields index 
      | map maxsearches=100 search="| metadata type=sourcetypes index=\"$index$\" | eval retention=tostring(abs(lastTime-firstTime), \"duration\") | convert ctime(firstTime) ctime(lastTime) | sort lastTime | rename totalCount AS \"TotalEvents\" firstTime AS \"FirstEvent\" lastTime AS \"LastEvent\" | eval index=\"$index$\"" | rename index as "Index" "sourcetype" as "SourceType" | fields Index  SourceType TotalEvents FirstEvent LastEvent

I want to provide the users with the ability to filter by indexes and sourcetypes. Here is what I have so far -

<form>
  <label>Splunk Indexes and SourceTypes</label>
  <fieldset submitButton="false">
    <input type="multiselect" token="index" searchWhenChanged="true">
      <label>Select Index</label>
      <search>
        <query>| rest /servicesNS/-/-/data/indexes|rename "title" as index | eval dy = (frozenTimePeriodInSecs/86400) % 365 | eval retention = dy . " days" | dedup index | stats count by index</query>
      </search>
      <fieldForLabel>index</fieldForLabel>
      <fieldForValue>index</fieldForValue>
      <choice value="\&quot;$index$\&quot;">ALL</choice>
      <default>"\""$index$\"""</default>
      <initialValue>\"$index$\"</initialValue>
    </input>
    <input type="multiselect" token="source_type" searchWhenChanged="true">
      <label>Select SourceType(s)</label>
      <search>
        <query>| metadata type=sourcetypes index=* | stats count by sourcetype</query>
      </search>
      <fieldForLabel>sourcetype</fieldForLabel>
      <fieldForValue>sourcetype</fieldForValue>
      <prefix>(</prefix>
      <suffix>)</suffix>
      <valuePrefix>SourceType=</valuePrefix>
      <delimiter> OR </delimiter>
      <choice value="*">ALL</choice>
      <default>*</default>
      <initialValue>*</initialValue>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>| eventcount summarize=false index=* index!=_* | dedup index | fields index         | map maxsearches=100 search="| metadata type=sourcetypes index=\"$index$\" | eval retention=tostring(abs(lastTime-firstTime), \"duration\") | convert ctime(firstTime) ctime(lastTime) | sort lastTime | rename totalCount AS \"TotalEvents\" firstTime AS \"FirstEvent\" lastTime AS \"LastEvent\" | eval index=\"$index$\"" | rename index as "Index" "sourcetype" as "SourceType" | fields Index  SourceType TotalEvents FirstEvent LastEvent | search $source_type$</query>
          <earliest>-3d@d</earliest>
          <latest>now</latest>
        </search>
        <option name="count">10</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</form>

I am unable to achieve 2 things here -

When I filter indexes, I want the respective sourcetypes to be filtered in the sourctypes dropdown
Display the table with selected indexes and sourcetypes only (should be able to select multiple in both case)

The query seems to be slow, but it gives the expected output.

Any advice? Thanks!

Re: How to get all indexes and sourcetypes?

jagadeeshm — Fri, 27 Jan 2017 04:50:06 GMT

Here is my final version with the following -

Default option is show "All" indexes and sourcetypes
Selecting specific indexes will filter sourcetypes
Submit button to filter the table based on the selected indexes and sourcetypes.

Note # Tips are welcome to improve the performance of the SPL.

Hope that saves a few hours for someone.

<form>
  <label>Splunk Indexes and SourceTypes</label>
  <fieldset submitButton="true">
    <input type="multiselect" token="index_selected">
      <label>Select Index</label>
      <search>
        <query>| rest /servicesNS/-/-/data/indexes|rename "title" as index | search (index!=_* AND index!="cim_*") | stats count by index</query>
      </search>
      <fieldForLabel>index</fieldForLabel>
      <fieldForValue>index</fieldForValue>
      <choice value="*">All</choice>
      <default>*</default>
      <initialValue>*</initialValue>
      <valuePrefix>index=</valuePrefix>
      <delimiter> OR </delimiter>
    </input>
    <input type="multiselect" token="sourcetype_selected">
      <label>Select SouceType(s)</label>
      <choice value="*">All</choice>
      <search>
        <query>| metadata type=sourcetypes $index_selected$ | rename sourcetype as SourceType | stats count by SourceType</query>
        <earliest>0</earliest>
      </search>
      <fieldForLabel>SourceType</fieldForLabel>
      <fieldForValue>SourceType</fieldForValue>
      <default>*</default>
      <prefix>(</prefix>
      <suffix>)</suffix>
      <initialValue>*</initialValue>
      <valuePrefix>SourceType=</valuePrefix>
      <delimiter> OR </delimiter>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>| eventcount summarize=false index=* index!=_* | dedup index | fields index         | map maxsearches=100 search="| metadata type=sourcetypes index=\"$$index$$\" | eval retention=tostring(abs(lastTime-firstTime), \"duration\") | convert ctime(firstTime) ctime(lastTime) | sort lastTime | rename totalCount AS \"TotalEvents\" firstTime AS \"FirstEvent\" lastTime AS \"LastEvent\" | eval index=\"$$index$$\"" | rename  "sourcetype" as "SourceType" | fields index  SourceType TotalEvents FirstEvent LastEvent |search $index_selected$ | search $sourcetype_selected$</query>
          <earliest>-3d@d</earliest>
          <latest>now</latest>
        </search>
        <option name="count">100</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</form>

Re: How to get all indexes and sourcetypes?

aljohnson_splun — Fri, 27 Jan 2017 17:33:17 GMT

Hey @Jagadeeshm,

You could also use a rest end point to get all the indexes, then map over them to get metadata for each:

 | rest services/data/indexes | fields title
 | map maxsearches=50 search="| metadata type=sourcetypes index=$title$ | eval index=\"$title$\""

Re: How to get all indexes and sourcetypes?

gjanders — Tue, 29 Sep 2020 12:34:20 GMT

You could also use tstats for this purpose, for example:
| tstats count where index=* groupby index
(I think you could even leave the where clause out if you wish)

| tstats count where index=* groupby index, sourcetype

Or similar...I've also done searches such as:
| tstats count, min(_time), max(_time) where index=* groupby index, sourcetype, source

Just an alternative...