topic Re: Issue with time format calculation in Splunk Search

Issue with time format calculation

jip31 — Thu, 13 Jun 2019 12:27:53 GMT

Hi
The request below is working but I have an issue on the NbDaysLogon and NbDaysReboot calculation.
As you can see, the fields are the difference between now() and the systemtime of the event
But there is a mistake somewhere because for example;
If the systemtime is 19/05/2019 and assuming that now is today 13/062019 I have a result of 29 days instead 25 days.
What is the problem?
Thank you.

index="X" sourcetype=XmlWinEventLog source="XmlWinEventLog:System" (EventCode=6005 OR EventCode=6006) 
    | 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 
    | eval NbDaysLogon=round((now() - LastLogon)/(3600*24), 2) 
    | eval NbDaysReboot=round((now() - LastReboot )/(3600*24), 2) 
    | eval LastLogon=strftime(LastLogon, "%y-%m-%d %H:%M") 
    | eval LastReboot=strftime(LastReboot, "%y-%m-%d %H:%M") 
    | search NbDaysLogon$tok_logon$ 
    | search NbDaysReboot$tok_reboot$ 
    | lookup lookup_cmdb_fo_all.csv HOSTNAME as host output SITE COUNTRY TOWN ROOM 
    | where SITE=$SITE$ 
    | stats values(LastReboot) as LastReboot values(NbDaysReboot) as NbDaysReboot values(LastLogon) as LastLogon values(NbDaysLogon) as NbDaysLogon  values(COUNTRY) as COUNTRY values(TOWN) as TOWN values(SITE) as SITE values(ROOM) as ROOM by host 
    | sort -NbDaysLogon -NbDaysReboot limit=10

Re: Issue with time format calculation

somesoni2 — Thu, 13 Jun 2019 15:40:30 GMT

The expressions looks good. Do all the NbDaysLogon values are off? Try to run half the search and verify if the values as you're expect.

Re: Issue with time format calculation

jnudell_2 — Thu, 13 Jun 2019 18:09:42 GMT

Can you provide a sample event time for the 2019-05-19 data?

Re: Issue with time format calculation

jip31 — Fri, 14 Jun 2019 05:34:30 GMT

Hummm sorry I wonder if it's not me who cheated on me because I have inverted the date format (english to french) I continue my investigations and i keep you aware
sorry

Re: Issue with time format calculation

niketn — Sun, 16 Jun 2019 03:51:33 GMT

@jip31 the NbDaysLogon and NbDaysReboot logic seems to work fine. Have you verified that SystemTime is converting to correct Epoch time? What is the SystemTime value when you pull it from index and display in a table (without any formatting)?

Following is run anywhere example which generates Random Logon and Reboot days. Commands till | table Today LastLogon LastReboot generate dummy data.

| makeresults count=5 
| fields - _time 
| eval randomDiffLogon=random(),
       randomDiffReboot=random() 
| eval randomDiffLogon="-".substr(randomDiffLogon,1,2)."d@d",
       randomDiffReboot="-".substr(randomDiffReboot,1,2)."d@d"
       | map search="| makeresults
| fields - _time
| eval LastLogon=relative_time(now(),\"$randomDiffLogon$\"),
       LastReboot=relative_time(now(),\"$randomDiffReboot$\")"
| eval Today=now()
| fieldformat Today=strftime(Today,"%y-%m-%d %H:%M")
| table Today LastLogon LastReboot

| eval NbDaysLogon=round((now() - LastLogon)/(3600*24), 2) 
| eval NbDaysReboot=round((now() - LastReboot )/(3600*24), 2) 
| eval LastLogon=strftime(LastLogon, "%y-%m-%d %H:%M") 
| eval LastReboot=strftime(LastReboot, "%y-%m-%d %H:%M")

Re: Issue with time format calculation

jip31 — Thu, 20 Jun 2019 07:00:57 GMT

Thanks to you!