Search code examples
splunksplunk-query

Splunk search - how to reset stats by group instead of all stats for the search

I want to create a report on job execution times by job ID. The same job ID could be executed multiple times. The logs capture each time the job starts, but does not explicitly log when it finishes. For the purposes of this report, we will determine the stop time based on the last log captured for the job. To achieve this, I need loop through the results capturing the start time and the latest log time to determine the stop time.

The problem I'm running into is I need to reset any stats captured for a job ID when the search detects another instance of the job has started. I tried the reset_before/reset_after/reset_on_change to achieve the desired results, but those trigger a reset of stats for all job IDs not the one that was re-executed. Here's a visual of the raw data and an example of the report I'm trying to generate.

Input Data sample

Desired Result sample report

Here is the start of the search...I removed the reset stat attempts to avoid causing any confusion. This search pulls back the data, but I have not been successful in getting the stats to reset by job ID when a new job starts.

index=jobs message="*Started*" OR message="*processing*" 
| rex field=message "@(?<JobID>[^\(]+)"
| stats earliest(_time) as start, latest(_time) as stop by JobID
| eval starttime=strftime(start,"%Y-%m-%d %H:%M") 
| eval stoptime=strftime(stop,"%Y-%m-%d %H:%M") 
| eval runtime=round((stop-start)/60/60,2)
| table JobbID, starttime, stoptime, runtime

Any help is appreciated!

Solution

  • Have a look at the transaction command: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transaction

    Also check out this documentation page: https://docs.splunk.com/Documentation/SplunkCloud/latest/Search/Identifyandgroupeventsintotransactions

    I recreated your input data: https://i.sstatic.net/rbG67.jpg

    Now I run this transaction command:

    yourbasesearch
| sort -_time
| transaction Job_ID startswith=(Message=Started)

    This will group your events into transactions (separately for each job ID) whenever there is a new Message=Started event.

    This is the result: https://i.sstatic.net/R2RqS.jpg

    You can get the stop time like this:

    | eval stop_time=_time+duration