Splunk Search

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

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

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

Influencer

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.*

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

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

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.

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

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)

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

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
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!