Counting number of Splunk Searches

[davidaj]

I’m trying to write a query that breaks out by index all searches that look back in certain day increments. Basically, I want to determine if users are actually writing searches that are querying the 90 days of data retention we are currently setup for, or are users only looking back less than the 90 days consistently. I would like to display the results in this format or something similar. I’m not very strong with the SPL currently, so any advise or help is much appreciated. Thanks in advance.

Index 1         7 day searches      14 day searches        21 day searches          28 day searches     90 day searches

[cmerriman]

Here's a starting off point. This likely needs some adjustments but should get you going.

index=_audit search="'search *" sourcetype=audittrail|stats values(apiStartTime) as earliest_time by search_id search|rex field=search max_match=0 "\'search\s.*index=(?<searched_index>[^\s|\"]+)"|rex field=search max_match=0 "\'search\s.*\`(?<macros_used>[^\`]+)\`"|rex field=search max_match=0 "\'search\s.*eventtype=(?<searched_eventtype>[^\s|\"]+)"|join searched_eventtype splunk_server type=left [|rest /servicesNS/-/-/admin/eventtypes splunk_server=* f=search f=title|table splunk_server title search|rename title as searched_eventtype search as searched_eventtype_def]|join macros_used splunk_server type=left [| rest /servicesNS/-/-/admin/macros splunk_server=* f=definition f=title|table splunk_server title definition|rename title as macros_used definition as macro_used_def]|rex field=macro_used_def max_match=0 "index=(?<macro_index>[^\s|\"]+)"|rex field=searched_eventtype_def max_match=0 "index=(?<eventtype_index>[^\s|\"]+)"|eval all_searches_indexes=coalesce(searched_index,coalesce(macro_index,eventtype_index))|eval all_searches_indexes=if(isnull(all_searches_indexes),"not_defined or *",all_searches_indexes)|stats values(earliest_time) as earliest_time by search_id all_searches_indexes|eval earliest_time_epoch=if(earliest_time="'ZERO_TIME'",relative_time(now(),"-90d"),strptime(earliest_time,"'%a %b %d %T %Y'"))|eval earliest_time_bucket=case(earliest_time_epoch<relative_time(now(),"-7d"),"last 7d",earliest_time_epoch<relative_time(now(),"-14d"),"last 14d",earliest_time_epoch<relative_time(now(),"-21d"),"last 21d",earliest_time_epoch<relative_time(now(),"-28d"),"last 28d",1=1,"last 90d")|chart count by all_searches_indexes earliest_time_bucket

What this is doing is looking at audit logs for any search being run. Then using regex, it's extracting anything with index= to grab the indexes. It's also looking for any macros or eventtypes so that it can grab any of those that might have indexes defined in them. You might want to tweak that bit a little to search for just eventtypes with indexes and macros with indexes. Then it joins them all together into one field and grabs the earliest time for each search and buckets the time by your definition above.

[davidaj]

Wow, this is good information and doing some things I hadn't thought of yet. I will continue to try and understand it and tweak it for what I'm looking for. 

[beatus]

I'd check out some of the awesome dashboards provided here: https://github.com/dpaper-splunk/public/tree/master/dashboards

There are a few views that do what you want, plus a whole lot of other good-ness.

[davidaj]

Thank you. Looks like lots of good information there. I will take a look.