Splunk Search

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

aamirs291
Path Finder

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.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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"

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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"
0 Karma

aamirs291
Path Finder

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.

0 Karma

sundareshr
Legend

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" 
0 Karma

aamirs291
Path Finder

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.

0 Karma

aamirs291
Path Finder

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.

0 Karma

sundareshr
Legend

Try the updated answer

0 Karma

aamirs291
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...