Splunk Search

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

gagandeep_arora
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

kmorris_splunk
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

kmorris_splunk
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

gagandeep_arora
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

gagandeep_arora
Path Finder

alt text

0 Karma

kmorris_splunk
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

gagandeep_arora
Path Finder

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

0 Karma

gagandeep_arora
Path Finder

Hello Morris, Can you please confirm on below

0 Karma

gagandeep_arora
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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!