Splunk Search

How do you calculate the difference between two different dates?

wagnerlucena
Explorer

HI all,

I've read many articles in Splunk community to find out how to calculate different dates. I get the correct result putting the date into the eval syntax but no success informing my fields into variables in eval syntax. Can anyone help me solve this problem?

My log is a JSON, I converted the date with strftime and strptime to get the format as "%d-%B-%y".

Follow below for my syntaxes, the first one is the one I'm working to solve and second one is the syntax that i got the correct result informing the date instead of field:

| rename fields.created as createdtime, key as Ticket, fields.updated as updatedtime
| eval created=strftime(strptime(createdtime,"%Y-%m-%dT%H:%M:%S.%3N"),"%d-%B-%y")
| eval last_time=strftime(strptime(updatedtime,"%Y-%m-%dT%H:%M:%S.%3N"),"%d-%B-%y")
| eval diff=(last_time-created) | eval diff = round(diff/60/60/24) 
| search Ticket=ACSD-12754 
| eventstats values(Ticket) as Ticket 
| table Ticket,fields.status.name,diff,created, last_time 

alt text

| eval created="26-October-18" 
| eval triaged="29-October-18" 
| eval dt_created=strptime(created, "%d-%B-%y") |  eval last_date=strptime(triaged, "%d-%B-%y") 
| eval diff=(last_date-dt_created) 
| eval diff = round(diff/60/60/24)  
| table created triaged diff | dedup created, triaged 

alt text

0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@wagnerlucena,

In your first search , calculate the difference in epoch (strptime) format before you convert it to string using strftime,

Try

| rename fields.created as createdtime, key as Ticket, fields.updated as updatedtime
| eval created=strptime(createdtime,"%Y-%m-%dT%H:%M:%S.%3N")
| eval last_time=strptime(updatedtime,"%Y-%m-%dT%H:%M:%S.%3N")
| eval diff=(last_time-created) | eval diff = round(diff/60/60/24) 
| search Ticket=ACSD-12754 
| eventstats values(Ticket) as Ticket 
| table Ticket,fields.status.name,diff,created, last_time *
Happy Splunking!

View solution in original post

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@wagnerlucena,

In your first search , calculate the difference in epoch (strptime) format before you convert it to string using strftime,

Try

| rename fields.created as createdtime, key as Ticket, fields.updated as updatedtime
| eval created=strptime(createdtime,"%Y-%m-%dT%H:%M:%S.%3N")
| eval last_time=strptime(updatedtime,"%Y-%m-%dT%H:%M:%S.%3N")
| eval diff=(last_time-created) | eval diff = round(diff/60/60/24) 
| search Ticket=ACSD-12754 
| eventstats values(Ticket) as Ticket 
| table Ticket,fields.status.name,diff,created, last_time *
Happy Splunking!
0 Karma

wagnerlucena
Explorer

Hi @renjith.nair thank you for your answer.

I converted it into epoch time, but got the same result. I mean, the diff is not being calculating. Take a look the picture.

alt text

renjith_nair
SplunkTrust
SplunkTrust

@wagnerlucena, that's because your created and last_time are multivalue field.

Try replacing the variables by

 | eval created=strptime(mvindex(createdtime,0),"%Y-%m-%dT%H:%M:%S.%3N")
 | eval last_time=strptime(mvindex(updatedtime,0),"%Y-%m-%dT%H:%M:%S.%3N")
Happy Splunking!

wagnerlucena
Explorer

Hi @renjith.nair you rock! Thanks for support, now i'm able get the difference. I'll adjust my query to retrieve the results that i'm looking for. Thanks again.

renjith_nair
SplunkTrust
SplunkTrust

@wagnerlucena,
You are welcome. Glad that it worked. You may upvote the answer/comment if it's useful for others 🙂

Happy Splunking!

abhijitsaoji
Explorer

can you please mention the final formula that you have used, I am looking for the same but not able to get the difference between two dates

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

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

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...