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="\"$index$\"">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 -
The query seems to be slow, but it gives the expected output.
Any advice? Thanks!
Here is my final version with the following -
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>
Here is my final version with the following -
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>
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...
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$\""