Splunk Search

Time Difference In Days

IRHM73
Motivator

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

0 Karma
1 Solution

gyslainlatsa
Motivator

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

View solution in original post

gyslainlatsa
Motivator

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
Motivator

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

0 Karma

javiergn
Super Champion

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
Motivator

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

Kind Regards

Chris

0 Karma
Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...