Splunk Search

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

Path Finder

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.

0 Karma
1 Solution

Splunk Employee
Splunk Employee

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:
alt text

View solution in original post

Splunk Employee
Splunk Employee

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:
alt text

View solution in original post

Path Finder

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

Path Finder

alt text

0 Karma

Splunk Employee
Splunk Employee

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.

0 Karma

Path Finder

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

0 Karma

Path Finder

Hello Morris, Can you please confirm on below

0 Karma

Path Finder

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!