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
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
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.*
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
@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
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.
2018-03-07 22:59:30.1
2018-03-07 22:27:45.343
2018-03-07 22:05:10.843
2018-03-07 21:07:57.17
|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)
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)
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