How to subtract dates from two events to find the duration?

[aamirs291]

Hello Everyone,

I have two events which I have uploaded in CSV format and the events will be consistent as below:

Ticket_Number,Created_Date,Ticket_Status,End_Time
INABCDEF,07/14/2016 06:36:47 AM,INPROG,07/14/2016 06:47:14 AM
INABCDEF,07/14/2016 06:36:47 AM,RESOLVED,07/14/2016 08:58:25 AM

I was able to find that the duration for the ticket INABCDEF was in INPROG by subtracting End_Time and Created_Date by using eval with strptime.

To find the duration for the ticket with the status RESOLVE, I will need to subtract the End_Time when the Ticket status was RESOLVED from the End_Time when the ticket was INPROG.
i.e Subtract the End_Time of event 2 where status is RESOLVED from the End_Time of event1 where status is INPROG.

I am new to Splunk and I don't know how to subtract field values from two different events. Let me know if anyone knows the answer to this.

[somesoni2 · Accepted solution]

Give this a try

your base search giving fields Ticket_Number,Created_Date,Ticket_Status,End_Time
| convert mktime(Created_Date) mktime(End_Time) timeformat="%m/%d/%Y %H:%M:%S %p"
| eval InProgressTime=if(Ticket_Status="INPROG",End_Time,null())
| eval ResolvedTime=if(Ticket_Status="RESOLVED",End_Time,null())
| stats values(Created_Date) as Created_Date values(InProgressTime) as InProgressTime values(ResolvedTime) as ResolvedTime by Ticket_Number
| eval Duration_INPROG=InProgressTime-Created_Date | eval Duration_RESOLVED=ResolvedTime-Created_Date
| convert ctime(Created_Date) ctime(*Time) timeformat="%m/%d/%Y %H:%M:%S %p"

[aamirs291]

Somesoni2,

Duration_RESOLVED is supposed to be ResolvedTime-InProgressTime as I had mentioned in my question. I did this and got the value I required. Thank you for the code.

[sundareshr]

Try this *UPDATED*

 base search | stats latest(eval(if(Ticket_Status="RESOLVED"), End_Time, null())) as resolved latest(eval(if(Ticket_Status="INPROG"), End_Time, null())) as inprog by Ticket_Number | eval resolved=strptime(resolved, "%m/%d/%Y %H:%M:%S %p") | eval inprog=strptime(inprog, "%m/%d/%Y %H:%M:%S %p") | eval duration=resolved-inprog | eval duration=tostring(duration, "duration") | convert ctime(resolved) ctime(inprog) timeformat="%m/%d/%Y %H:%M:%S %p" 

[aamirs291]

Hi Sundareshr,

When I ran the above code from |stats latest ... I am receiving the following error
Error in 'SearchProcessor': Mismatched quotes and/or parenthesis.

[aamirs291]

I would also like to add to this that I would not want to use the value for _time but instead as mentioned in the question I want ;
RESOLUTION TIME = End_Time when the ticket is RESOLVED minus End_Time when the ticket is INPROG. I want the values from the table I mentioned instead of the _time which splunk generates automatically. In Summary, Subtracting two user defined dates from two events. Thank you.

[sundareshr]

Try the updated answer

[aamirs291]

I am getting the following error when I run the updated code
Error in 'stats' command: The eval expression for dynamic field 'eval(if(Ticket_Status="RESOLVED"), End_Time, null())' is invalid. Error='The operator at ', End_Time, null()' is invalid.'

Note: somesoni2's code worked with some tweaks. Thank you.