Splunk Search
Highlighted

Issue with time format calculation

Contributor

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
0 Karma
Highlighted

Re: Issue with time format calculation

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

Re: Issue with time format calculation

Builder

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

0 Karma
Highlighted

Re: Issue with time format calculation

Contributor

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

0 Karma
Highlighted

Re: Issue with time format calculation

Legend

@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")



| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Issue with time format calculation

Contributor

Thanks to you!

0 Karma