Splunk Search

Nested search query

Karthikeyan
Engager

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

Labels (4)
0 Karma

Karthikeyan
Engager

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

Karthikeyan
Engager

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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)?

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...