Nested search query

[Karthikeyan]

Hello Experts,

Requirement is to show the no. of jobs started, completed in last 4 hours.

I have injested job log files to splunk. From file name, I can derive the job start time and the first line of the job is always "Job xxx started". With this I can count the no. of jobs that started in an hour. 

I tried extracting the info by searching the last line of the job log, which is "Job xxx completed successfully" and since there is some delays in data ingestion to Splunk, previous hours data are showing in the table thereby the table shows 5 hours count instead of 4 hrs.

Now to identify the no. of jobs that got completed successfully which is started in the hour, I tried to query with AND command, append command unsuccessfully.

Criteria is to show the count of the job that got started and completed in a 4 hr time span.

I hope we can use AND or subsearch command. Kindly help with this requirement.

Regards, Karthikeyan.SV

[Karthikeyan]

Writing the search criteria with OR condition provides different results, i.e., a job might have started at 6:59 AM and completed at 7:01 AM, and using OR condition finds the job completion and shows that in the chart of 4 hrs between 7 to 11 AM. 

To avoid this, I'm looking for a search query with AND condition.

[richgalloway]

The OR operator must be used because no event can contain BOTH "started" and "completed".

If job IDs are unique then a job starting before the search window should not appear in the results.  Likewise for jobs that do not end within the search window.

[Karthikeyan]

Thanks for the reply. The current search query to fetch the job started 

sourcetype="joblog"
| search "*Job *** started*"
| rex field=source "trace_(?<sourceTime>\d\d_\d\d_\d\d\d\d_\d\d_\d\d_\d\d)"
| eval _time=strptime(sourceTime, "%m_%d_%Y_%H_%M_%S")
| timechart span=1h count

And the requirement is to show the no. of jobs that are started and completed, in one chart.

[richgalloway]

To find the jobs that started and completed you must search for both conditions the match up the events by job ID.  The transaction command is an easy way to do this, but is not very performant.

sourcetype="joblog" ("Job * started" OR "Job * completed successfully")
| rex "Job (?<jobID>\S+)"
| rex field=source "trace_(?<sourceTime>\d\d_\d\d_\d\d\d\d_\d\d_\d\d_\d\d)"
| eval _time=strptime(sourceTime, "%m_%d_%Y_%H_%M_%S")
| transaction jobID startswith="started" endswith="completed successfully"
| timechart span=1h count

Here's another way to combine the events using stats.

sourcetype="joblog" ("Job * started" OR "Job * completed successfully")
| rex "Job (?<jobID>\S+)"
| rex field=source "trace_(?<sourceTime>\d\d_\d\d_\d\d\d\d_\d\d_\d\d_\d\d)"
| eval _time=strptime(sourceTime, "%m_%d_%Y_%H_%M_%S")
| stats earliest(_time) as _time by jobID
| timechart span=1h count

[richgalloway]

Please share the current searches.

Please clarify the requirement.  Shall the search show the number of jobs started and the number of jobs completed in 4 hours (2 numbers) or the number of jobs that started and completed within 4 hours (1 number)?