Time Difference In Days

[IRHM73]

Hi, I wonder whether someone may be able to help me please.

I'm trying to put together a query which calculates the difference between the current date and a "Created Date". If you could have a look at the query below please, I've converted the "Created Date" to epoch time e.g. 1455700768.000000 but when I list the current date it's shown as 1456130889. So then when I try to find the difference, the query is extracting results such as 13+08:48:09.000000.

index=_audit action=edit_user operation=edit OR operation=create
 | rename object as user
 | stats max(timestamp) as "created" by user
 | eval createddate=strptime(created, "%m-%d-%Y %H:%M:%S")
 | eval testtime=now() 
 | eval diff = tostring((testtime - createddate), "duration")         
 | fields createddate testtime diff

Could someone possible look at this please and let me know where I've gone wrong and how I can calculate the difference in days between the current date and the "Created Date".

Many thanks and kind regards

Chris

[gyslainlatsa · Accepted solution]

hi,

this option display the difference in days

eval days_since = (now() - last_seen) / 86400

try like this

index=_audit action=edit_user operation=edit OR operation=create
  | rename object as user
  | stats max(timestamp) as "created" by user
  | eval createddate=strptime(created, "%m-%d-%Y %H:%M:%S")
  | eval testtime=now() 
  | eval days_since = (testtime - createddate) / 86400
  | eval diff = tostring((testtime - createddate), "duration")  
  |eval u = diff / 86400 
  | fields createddate testtime diff u days_since

[IRHM73]

Hi, thank you very much for taking the time to come back to me with this.

In addition to the suggestion made by @javiergn about rounding, this works as required.

Kind regards

Chris

[javiergn]

Hi,

13+08:48:09.000000 is the difference in days (13), hours (08), minutes (48), seconds (09) and microseconds.

If you just need the days you have several options:

• use regex to extract 13 from the above
• Divide the time difference in epoch between 86400 and round it.

Hope that helps.

Thanks,
J

[IRHM73]

Hi, thank you for coming back to me with the guidance.

Kind Regards

Chris