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
Can you provide a sample event time for the 2019-05-19 data?
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
@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")
Thanks to you!
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.