topic Re: HELP ON EVAL FOR CALCULATING A NUMBER OF DAYS in Splunk Search

HELP ON EVAL FOR CALCULATING A NUMBER OF DAYS

jip31 — Tue, 04 Jun 2019 11:15:12 GMT

hello
I use the search below in order to calculate a last logon date and a last reboot date by host
now I need to add 2 fields : the number of days since the last logon has occured and the number of days since the last reboot has occured

index="test" source="test" (EventCode=6005 OR EventCode=6006) 
| fields host SystemTime EventCode 
| eval SystemTime=strftime(strptime(SystemTime, "'%Y-%m-%dT%H:%M:%S.%9Q%Z'"), "%y-%m-%d %H:%M") 
| stats latest(SystemTime) as SystemTime by host EventCode 
| xyseries host EventCode SystemTime 
| rename "6005" as LastLogon "6006" as LastReboot 
| sort -LastLogon -LastReboot limit=10

I tried to do something like this but I dont succeed

 | eval NbDaysLogon= (now() - SystemTime) 
| eval NbDaysReboot= (now() - SystemTime)

could you help me please?

Re: HELP ON EVAL FOR CALCULATING A NUMBER OF DAYS

VatsalJagani — Tue, 04 Jun 2019 12:50:24 GMT

Hi @jip31,

Reason your query is not working is:
SystemTime is string and now() is integer. Earliert SystemTime was string then with strptime you converted it to integer. And with strftime you again converted to string. Remove outer strftime function, something like.
| eval SystemTime=strptime(SystemTime, "'%Y-%m-%dT%H:%M:%S.%9Q%Z'")

To find NoOfDay you can use:
| eval NoDaysLogon = round((now() - SystemTime)/(3600*24), 2)

Hope this helps!!!

Re: HELP ON EVAL FOR CALCULATING A NUMBER OF DAYS

FrankVl — Tue, 04 Jun 2019 13:15:40 GMT

And to add to that:

Doing stats latest() after stripping off the _time field is also a bit tricky. Either do as below (incl. _time in the fields command).

 index="test" source="test" (EventCode=6005 OR EventCode=6006) 
 | fields _time host SystemTime EventCode 
 | eval SystemTime=strptime(SystemTime, "'%Y-%m-%dT%H:%M:%S.%9Q%Z'")
 | stats latest(SystemTime) as SystemTime by host EventCode 
 | xyseries host EventCode SystemTime 
 | rename "6005" as LastLogon "6006" as LastReboot 
 | sort -LastLogon -LastReboot limit=10
 | eval NbDaysLogon=round((now() - LastLogon)/(3600*24), 2)
 | eval NbDaysReboot=round((now() - LastReboot )/(3600*24), 2)
 | convert ctime(LastLogon) | convert ctime(LastReboot)

Or do like this (taking the max value of SystemTime, rather than the latest):

 index="test" source="test" (EventCode=6005 OR EventCode=6006) 
 | fields host SystemTime EventCode 
 | eval SystemTime=strptime(SystemTime, "'%Y-%m-%dT%H:%M:%S.%9Q%Z'")
 | stats max(SystemTime) as SystemTime by host EventCode 
 | xyseries host EventCode SystemTime 
 | rename "6005" as LastLogon "6006" as LastReboot 
 | sort -LastLogon -LastReboot limit=10
 | eval NbDaysLogon=round((now() - LastLogon)/(3600*24), 2)
 | eval NbDaysReboot=round((now() - LastReboot )/(3600*24), 2)
 | convert ctime(LastLogon) | convert ctime(LastReboot)

In any case, as @VatsalJagani suggested, you need to keep SystemTime as a number during the calculations and only change it to a string later (I use the convert command for that at the very end in above examples).

Re: HELP ON EVAL FOR CALCULATING A NUMBER OF DAYS

jip31 — Wed, 05 Jun 2019 03:24:17 GMT

thanks franck
I dont succeed to display the data like this :

| table host LastLogon LastReboot NbDaysReboot NbDaysReboot
what I have to do please??

Re: HELP ON EVAL FOR CALCULATING A NUMBER OF DAYS

FrankVl — Wed, 05 Jun 2019 07:09:33 GMT

What does work and what exactly does not work for you? Please be more specific, clearly describe what you have tried, what output you get, how that is different from what you expect.

Re: HELP ON EVAL FOR CALCULATING A NUMBER OF DAYS

jip31 — Wed, 05 Jun 2019 10:40:09 GMT

franck
after other checking the search globally works 😉
just, I would like to change the LastLogon and LastReboot format
I tried to change | eval SystemTime but I have issues
Actually the format is 06/04/2019 19:04:05.964162
06/04/2019 19:04 format will be enough
and last thing, I would like to have the fields in this order :
host LastLogon NbDaysLogon Lastreboot NbDaysReboot
Thanks in advance

Re: HELP ON EVAL FOR CALCULATING A NUMBER OF DAYS

FrankVl — Wed, 05 Jun 2019 11:34:35 GMT

Changing the order of the fields should be possible by adding a table command at the very end of the search.

Changing the time format can be done by either using eval LastLogon=strftime(LastLogon, "%y-%m-%d %H:%M") instead of the convert command I used, or by changing the timeformat applied by the convert command: `convert timeformat= "%y-%m-%d %H:%M" ctime(LastLogon)

Re: HELP ON EVAL FOR CALCULATING A NUMBER OF DAYS

jip31 — Wed, 05 Jun 2019 11:54:56 GMT

perfect thanks a lot