Calculate Difference between two time fields in single event

[kpavan]

Hi All,

Am trying to calculate difference between starttime and endtime for tasksession, both start and end time are in single event like
TASKNAME CREATED_TIME LAST_ACCESS_TIME, but using two different query unable to get the expected result 1st query difference is null and second query difference is all 00:00. Not sure where is missing.

Could you please help me on getting correct difference values.

1st Query : Result is NULL

search 
|  eval StartTime=strftime(strptime(CREATED_TIME, "%Y-%m-%d %H:%M:%S"),"%Y/%m/%d %H:%M:%S") 
| eval EndTime=strftime(strptime(LAST_ACCESS_TIME, "%Y-%m-%d %H:%M:%S"),"%Y/%m/%d %H:%M:%S") 
| eval Diff=tostring(EndTime-StartTime,"duration") | table TASKNAME StartTime EndTime Diff

Output:
TASKNAME NAME StartTime EndTime Diff
Registration 2016/11/24 16:08:55 2016/11/24 16:08:58 (NULL)

2nd Query : Result is 00:00

index=cfa_prod_ca_idm_db source=cfa_idm_all_tasks sourcetype=cfa_idm_tasks STATE=128
| dedup TASKSESSIONID 
| eval StartTime=strftime(strptime(CREATED_TIME, "%Y-%m-%d %H:%M:%S"),"%Y/%m/%d %H:%M:%S") 
| eval EndTime=strftime(strptime(LAST_ACCESS_TIME, "%Y-%m-%d %H:%M:%S"),"%Y/%m/%d %H:%M:%S") 
| transaction TASKNAME maxevents=1
| eval Duration=strftime(duration,"%M:%S") | table TASKNAME StartTime EndTime Duration

Output:
TASKNAME StartTime EndTime Duration
Registration 2016/11/24 16:08:55 2016/11/24 16:08:58 00:00

Expected Duration : 00:03 but showing 00:00

Thanks!
Pavan

[lguinn2]

Since you aren't showing the original events, I can't be sure, but I think that you are parsing the time incorrectly.
If CREATED_TIME and LAST_ACCESS_TIME are formatted in epoch time, then you don't need the strftime/strptime at all. If these fields are formatted as text ("Nov 24 2016 11:39"), then you only need the strptime to convert them into epoch time. The transaction command with maxevents=1 is useless; you need to remove it.

I would do this, assuming that the time fields appear as text in the event:

 search 
 | eval StartTime=strptime(CREATED_TIME, "%Y-%m-%d %H:%M:%S")
 | eval EndTime=strptime(LAST_ACCESS_TIME, "%Y-%m-%d %H:%M:%S")
 | eval Diff=tostring(EndTime-StartTime,"duration") 
 | table TASKNAME StartTime EndTime Diff CREATED_TIME LAST_ACCESS_TIME

Once you are happy with the results, you can rename the fields or whatever. For the second search:

index=cfa_prod_ca_idm_db source=cfa_idm_all_tasks sourcetype=cfa_idm_tasks STATE=128  
| dedup TASKSESSIONID   
| eval StartTime=strptime(CREATED_TIME,"%Y-%m-%d %H:%M:%S")  
| eval EndTime=strptime(LAST_ACCESS_TIME,"%Y-%m-%d %H:%M:%S") 
| eval Duration=strftime(EndTime-StartTime,"%M:%S")
| table TASKNAME StartTime EndTime Duration CREATED_TIME LAST_ACCESS_TIME

If this doesn't work, please show an example event containing CREATED_TIME and LAST_ACCESS_TIME

[kpavan]

Hi lguinn,

Thanks for replying!

the provided query didn't work, its through an error invalid number. Below are my created and last access time format from event.

Could you please let me how convert these times and get the difference?

CREATED_TIME LAST_ACCESS_TIME
2016-11-24 16:08:55.0 2016-11-24 16:08:58.0
2016-11-24 16:07:38.0 2016-11-24 16:07:38.0
2016-11-24 16:07:05.0 2016-11-24 16:07:06.0
2016-11-24 16:06:54.0 2016-11-24 16:06:57.0
2016-11-24 16:05:05.0 2016-11-24 16:05:06.0

Thanks!

[gcusello]

hi kpavan,
try

 | eval StartTime=strptime(CREATED_TIME,"%Y-%m-%d %H:%M:%S.%N")  
 | eval EndTime=strptime(LAST_ACCESS_TIME,"%Y-%m-%d %H:%M:%S.%N") 

if it continues to give error, try your search deleting a pipe at a time from the end

index=cfa_prod_ca_idm_db source=cfa_idm_all_tasks sourcetype=cfa_idm_tasks STATE=128  
 | dedup TASKSESSIONID   
 | eval StartTime=strptime(CREATED_TIME,"%Y-%m-%d %H:%M:%S.%N")  
 | eval EndTime=strptime(LAST_ACCESS_TIME,"%Y-%m-%d %H:%M:%S.%N")

to understand what is the command in error.
Bye.
Giuseppe