Splunk Search

How do I subtract two fields containing %m%d%y format time values to get the age of an event?

johnward4
Communicator

I'm trying to create a new field that is the result of the Current Date minus the time stamp when my events were created. My overall goal is the show duration=the # of days between my current date and when the events were created.

Current query :

| index=test sourcetype=testsourcetype username, Subject
| eval currentdate= now() 
| convert timeformat="%d %m %Y %H:%M:%S %Z" ctime(currentdate) AS c_time 
| eval Event=strftime (_time, "%d %m %Y %H:%M:%S %Z")
| eval duration=('c_time'-'Event')/(60*60)
| table username, Subject, ID, _time, c_time, Event, duration
0 Karma
1 Solution

493669
Super Champion

Try this:

index=test sourcetype=testsourcetype
 | eval currentdate= now() 
 | convert timeformat="%d %m %Y %H:%M:%S %Z" ctime(currentdate) AS c_time 
 | eval Event=strftime (_time, "%d %m %Y %H:%M:%S %Z"),c_time1=c_time
 |eval c_time1=strptime(c_time1,"%d %m %Y %H:%M:%S %Z") ,Event1=strptime(Event,"%d %m %Y %H:%M:%S %Z")
 | eval duration=('c_time1'-'_time')/(24*60*60)
 | table username, Subject, ID, _time, c_time, Event1, duration

View solution in original post

manjunathmeti
SplunkTrust
SplunkTrust

You can directly find the difference between now() and _time and divide it by 86400 to get duration in number of days, for example:

index=test sourcetype=testsourcetype username, Subject 
| eval duration=floor((now()-_time) / 86400)
| table username, Subject, ID, Event, duration

Note: *floor** function rounds a number down to the nearest whole integer, use ceil function to round a number up to the next highest integer.*

493669
Super Champion

Try this:

index=test sourcetype=testsourcetype
 | eval currentdate= now() 
 | convert timeformat="%d %m %Y %H:%M:%S %Z" ctime(currentdate) AS c_time 
 | eval Event=strftime (_time, "%d %m %Y %H:%M:%S %Z"),c_time1=c_time
 |eval c_time1=strptime(c_time1,"%d %m %Y %H:%M:%S %Z") ,Event1=strptime(Event,"%d %m %Y %H:%M:%S %Z")
 | eval duration=('c_time1'-'_time')/(24*60*60)
 | table username, Subject, ID, _time, c_time, Event1, duration

johnward4
Communicator

@493669 can you explain what's happening in the evals so I can better grasp it?

I also would like to compute the duration between another to fields, e.g. CreationDate - ClosureDate. Then I would like get the average duration in days for both "open events" and events that have been closed.

Open events would be any events that are not marked as Closed=1
Closed event would be any events that are marked as Closed=0

0 Karma

493669
Super Champion

to calculate difference in days you need to convert CreationDate and ClosureDate into epoch (in seconds) using strptime ..If you could provide format of CreationDate and ClosureDate then I can help you with exact query...
after calculating epoch then subtract these two and then divide by 86400 to get difference in days.

johnward4
Communicator

CreationDate

2018-03-07 22:59:30.1
2018-03-07 22:27:45.343

ClosureDate

2018-03-07 22:05:10.843
2018-03-07 21:07:57.17

0 Karma

493669
Super Champion
|eval CreationDate=strptime(CreationDate,"%Y-%m-%d %H:%M:%S.%3N"), ClosureDate=strptime(ClosureDate,"%Y-%m-%d %H:%M:%S.%3N")
|eval days=round((CreationDate-ClosureDate)/86400)

johnward4
Communicator

Sweet! thanks @493669 Do you have any advice on how to take an average of both fields by GroupName then calculate the combinedaverage?

e.g. stats avg(duration), avg(closedduration) by GroupName

index=test sourcetype="test" GroupName=* TestID=* 
| dedup TestID
| eval currentdate= now() 
| convert timeformat="%d %m %Y %H:%M:%S %Z" ctime(currentdate) AS c_time
| eval Event=strftime ("CreationDate", "%d %m %Y %H:%M:%S %Z"),c_time1=c_time
| eval c_time1=strptime(c_time1,"%d %m %Y %H:%M:%S %Z") ,Event1=strptime(Event,"%d %m %Y %H:%M:%S %Z")
| eval duration=('c_time1'-'_time')/(60*60)
| eval CreationDate1=strptime(CreationDate,"%Y-%m-%d %H:%M:%S.%3N"), ClosureDate1=strptime(ClosureDate,"%Y-%m-%d %H:%M:%S.%3N")
| eval closedduration=round((ClosureDate1-CreationDate1)/86400)
| lookup testcategory.csv HD_Nome OUTPUT Difficulty AS Difficulty
| stats avg(duration), avg(closedduration) by GroupName
| rename avg(duration) as avgdur, avg(closedduration) as avgclosed
| eval combinedavg = round(avgdur + avgclosed)
| stats avg(combinedavg) by GroupName
| table avg(duration), GroupName, avg(closedduration), combinedavg, avg(combinedavg)
| sort - avg(combinedavg)
0 Karma

493669
Super Champion

Try this simplified query ...in below query duration and closedduration to be in days so divided by 86400...

 index=test sourcetype="test" GroupName=* TestID=* 
 | dedup TestID
| eval duration=round((now()-_time) / 86400)
| eval CreationDate1=strptime(CreationDate,"%Y-%m-%d %H:%M:%S.%3N"), ClosureDate1=strptime(ClosureDate,"%Y-%m-%d %H:%M:%S.%3N")
 | eval closedduration=round((ClosureDate1-CreationDate1)/86400)
 | lookup testcategory.csv HD_Nome OUTPUT Difficulty AS Difficulty
| stats avg(duration) as avgdur , avg(closedduration) as avgclosed by GroupName
| eval combinedavg = round((avgdur + avgclosed)/2)
| sort - combinedavg
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 ...