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

[abhi04]

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

[somesoni2 · Accepted solution]

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

[abhi04]

Hi somesoni2,

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

[abhi04]

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 "(?.+)##(?.+)"

[abhi04]

@somesoni2,

Can u explain the above.

[somesoni2]

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.

[Anam]

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

[somesoni2]

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.

[abhi04]

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

[abhi04]

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

[adonio]

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

[abhi04]

The above one is not giving the result.

[adonio]

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