How to search for all sourcetypes, corresponding indexes, and their latest accessed time in a table format?

[gagandeep_arora]

How to search for all the sourcetypes, corresponding indexes, and their latest accessed time in a table format?

My prime motive is to find out sourcetypes which are not recently (may be for months) searched by any user.

[kmorris_splunk · Accepted solution]

Try something like this:

index=_audit action=search info=granted search=* NOT "search_id='scheduler" NOT "search='|history" NOT "user=splunk-system-user" NOT "search='typeahead" NOT "search='| metadata type=* | search totalCount>0" 
| rex field=search "index=(?P<search_index>[^ ]+)" 
| rex field=search "sourcetype=(?P<search_sourcetype>[^ ]+)" 
| eval time=strftime(_time, "%m/%d/%y %H:%M:%S") 
| rex field=search_index "\"(?P<search_index>\w+)" 
| rex field=search_sourcetype "\"(?P<search_sourcetype>\w+)" 
| stats max(time) as last_searched by search_index search_sourcetype 
| sort -search_index -search_sourcetype

It will produce results like this:

[gagandeep_arora]

thanks Morris, There was a difference in time stamp as well, I clubbed the output of both the queries to make sure it was accessed. Thanks for your valuable inputs.: So for others below are the two search queries for their reference, They may use any as per their suitability 🙂

Cheers, Gagandeep Arora
SearchQuery1:

index=_audit action=search info=granted  | eval _raw=search | eval _raw=mvindex(split(_raw,"|"),0) 
 | table _raw _time | extract | eval last_searched=strftime(_time, "%m/%d/%y %H:%M:%S") 
 | dedup index sourcetype | table index sourcetype last_searched

SearchQuery2:

 index=_audit action=search info=granted   | rex field=search "index=(?P[^ ]+)"  | rex field=search "sourcetype=(?P[^ ]+)"  | eval time=strftime(_time, "%m/%d/%y %H:%M:%S")  | rex field=search_index "\"(?P\w+)"  | rex field=search_sourcetype "\"(?P\w+)"  | stats max(time) as last_searched by search_index search_sourcetype  | sort -search_index -search_sourcetype

[gagandeep_arora]

[kmorris_splunk]

In my base search it is only looking where search_id has a value. That could be the difference. Try taking that out of my search to see if thats it. My search extracts the sourcetype and the index from the search field, which would account for the difference in what you are seeing for the sourcetype. Either way, I think you should be close to what you were looking for.

[gagandeep_arora]

Thanks Morris, I clubbed the output from both the search queries.

[gagandeep_arora]

Hello Morris, Can you please confirm on below

[gagandeep_arora]

Thanks Morris, I also created a search for this:

index=_audit action=search info=granted  | eval _raw=search | eval _raw=mvindex(split(_raw,"|"),0) 
| table _raw _time | extract | eval time=strftime(_time, "%m/%d/%y %H:%M:%S") 
| dedup index sourcetype | table index sourcetype time

I can see difference in Search count and difference in output. Can you please check and confirm which query would suite best.

My prime motive is to compare sourcetype of this output with my excel for a time frame of 1 month.