Splunk Search

How to edit my search using a start time and an end time to list the duration in my results?

abhi04
Path Finder

The below query gave me Start time, end time grouped by Job name. I want to also list the duration by subtracting end time and start time.

index=auto_prod_iw* "/afiw/batch/scripts/gc01*.ksh" "gc01*" "started - time=" OR ("ended - time=" OR "ENDED - time") 
|dedup _raw
|rex field=_raw "Job gc01\w+ - started - time=\((?\d+\-\d+\-\d+\-\d+\.\d+\.\d+)"
|rex field=_raw "Job gc01\w+ - ended - time=\((?\d+\-\d+\-\d+\-\d+\.\d+\.\d+)"
|eval duration=strptime(EndTime ,"%Y-%m-%d-%H.%M.%S")-strptime(StartTime, "%Y-%m-%d-%H.%M.%S")
|stats values(Start_Time) values(End_Time) values(duration)  by Job_Name
0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

index=auto_prod_iw* "/afiw/batch/scripts/gc01*.ksh" "gc01*" "started - time=" OR ("ended - time=" OR "ENDED - time") 
  |dedup _raw |rex field=_raw "Job gc01\w+ - (?<action>\w+) - time=\((?<timestamp>\d+\-\d+\-\d+\-\d+\.\d+\.\d+)"
  | chart list(timestamp) over Job_Name by action | rename started as StartTime ended as EndTime
  | eval temp=mvzip(StartTime, EndTime,"##") | mvexpand temp | rex field=temp "(?<StartTime>.+)##(?<EndTime>.+)"
  |eval duration=strptime(EndTime,"%Y-%m-%d-%H.%M.%S")-strptime(StartTime, "%Y-%m-%d-%H.%M.%S")
| stats list(StartTime) list(EndTime) list(duration) by Job_Name

View solution in original post

0 Karma

somesoni2
Revered Legend

Give this a try

index=auto_prod_iw* "/afiw/batch/scripts/gc01*.ksh" "gc01*" "started - time=" OR ("ended - time=" OR "ENDED - time") 
  |dedup _raw |rex field=_raw "Job gc01\w+ - (?<action>\w+) - time=\((?<timestamp>\d+\-\d+\-\d+\-\d+\.\d+\.\d+)"
  | chart list(timestamp) over Job_Name by action | rename started as StartTime ended as EndTime
  | eval temp=mvzip(StartTime, EndTime,"##") | mvexpand temp | rex field=temp "(?<StartTime>.+)##(?<EndTime>.+)"
  |eval duration=strptime(EndTime,"%Y-%m-%d-%H.%M.%S")-strptime(StartTime, "%Y-%m-%d-%H.%M.%S")
| stats list(StartTime) list(EndTime) list(duration) by Job_Name
0 Karma

abhi04
Path Finder

Hi somesoni2,

Thanks, This is working.
Can you please explain the query?

0 Karma

abhi04
Path Finder

Hi somesoni2,

Please explain the below part in your above query and why this is required?

| eval temp=mvzip(StartTime, EndTime,"##") | mvexpand temp | rex field=temp "(?.+)##(?.+)"

0 Karma

abhi04
Path Finder

@somesoni2,

Can u explain the above.

0 Karma

somesoni2
Revered Legend

If there are multiple job executions for a Job_Name, after chart list(..., you'll get a list of all start and end times for the job in the multivalued field StartTime and EndTime. (you can see it better by just running your search till rename command.

e.g. (below is value in mv field just for single row, there will be many rows like this)

job_name   StartTime     EndTime
Job1         stime1     etime1
                stime2      etime2
                stime3      etime3

Since the duration should be calculated with subtraction of StartTime from corresponding EndTime, so we are merging both multivalued field StartTime and EndTime in field temp using mvzip.

job_name   StartTime     EndTime     temp
Job1         stime1     etime1       stime1##etime1
                stime2      etime2       stime2##etime2
                stime3      etime3       stime3##etime3

We then expand temp multivalued field to get each pair of StartTime/EndTime in single row using mvexpand command. We then use rex command to extract the StartTime and EndTime value from field temp.

0 Karma

Anam
Community Manager
Community Manager

Hi @abhi04

My name is Anam and I am the Community Content Specialist for Splunk Answers.
Please refrain from commenting consecutively on the same issue and tagging the user. We appreciate how much our community members contribute and help other users so give them time to reply to your question.
For guidelines on the Community, please read the Community Manual:

http://docs.splunk.com/Documentation/Community/1.0/community/CommunityGuidelines

Thanks

0 Karma

somesoni2
Revered Legend

You're not getting result for duration as, in any event, you'll either have StartTime or EndTime, not both. You'd need to run some statistics command to bring them both in same event/row and then do the calculation.

Assuming your date format is correct and there is only one execution of a job recorded in the selected time range, try something like this

index=auto_prod_iw* "/afiw/batch/scripts/gc01*.ksh" "gc01*" "started - time=" OR ("ended - time=" OR "ENDED - time") 
 |dedup _raw |rex field=_raw "Job gc01\w+ - (?<action>\w+) - time=\((?<timestamp>\d+\-\d+\-\d+\-\d+\.\d+\.\d+)"
 | chart values(timestamp) over Job_Name by action | rename started as StartTime ended as EndTime
 |eval duration=strptime(EndTime,"%Y-%m-%d-%H.%M.%S")-strptime(StartTime, "%Y-%m-%d-%H.%M.%S")

The query will be little different if there can be multiple execution of a job in the given time range.

0 Karma

abhi04
Path Finder

Yes,

There are multiple execution of job in a time range.and so with help of field extraction I am taking the start and end time in the field in which you have mentioned as field "action".can you please tell me in that scenario

0 Karma

abhi04
Path Finder

Hi,

The query which I am using is.
index=auto_prod_iw* "/afiw/batch/scripts/gc01*.ksh" "gc01*" "started - time=" OR ("ended - time=" OR "ENDED - time")

|dedup _raw

|rex field=_raw "Job gc01\w+ - started - time=((?\d+-\d+-\d+-\d+.\d+.\d+)"

|rex field=_raw "Job gc01\w+ - ended - time=((?\d+-\d+-\d+-\d+.\d+.\d+)"

|eval duration=strptime(EndTime ,"%Y-%m-%d-%H.%M.%S")-strptime(StartTime, "%Y-%m-%d-%H.%M.%S")

|stats values(Start_Time) values(End_Time) values(duration) by Job_Name

0 Karma

adonio
Ultra Champion

can you elaborate?
seems like you are subtracting already:
|eval duration=strptime(EndTime ,"%Y-%m-%d-%H.%M.%S")-strptime(StartTime, "%Y-%m-%d-%H.%M.%S")

0 Karma

abhi04
Path Finder

The above one is not giving the result.

0 Karma

adonio
Ultra Champion

can you share some masked sample data?
also take a look at this answer:
https://answers.splunk.com/answers/663124/how-to-subtract-the-below.html

0 Karma
Get Updates on the Splunk Community!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...