Splunk Search

How to get all indexes and sourcetypes?

jagadeeshm
Contributor

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 -

  1. When I filter indexes, I want the respective sourcetypes to be filtered in the sourctypes dropdown
  2. 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!

Tags (1)
0 Karma
1 Solution

jagadeeshm
Contributor

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>

View solution in original post

0 Karma

jagadeeshm
Contributor

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>
0 Karma

gjanders
SplunkTrust
SplunkTrust

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...

aljohnson_splun
Splunk Employee
Splunk Employee

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$\""
0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

<P style=" text-align: center; "><span class="lia-inline-image-display-wrapper lia-image-align-center" ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

<FONT size="5"><FONT size="5" color="#FF00FF">Get the latest news and updates from the Splunk Community ...