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

[johnward4]

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

[manjunathmeti]

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 · Accepted solution]

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]

@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

[493669]

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]

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

[493669]

|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]

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)

[493669]

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