hi
i use the request below but i have an issue with the relative_time:
secondlastday=I dont want to have events after 2 days before the actual date but I want to have events only between the actual date nexttendays= same thing. I just want to have events betweem the actual date and the 10 days after
other problem, when I do a sort on Lastlogon, it doesnt works
Could you help me please?
index="ai-wkst-windows-wmi-fr" (sourcetype="WMI:LastLogon" OR sourcetype="WMI:LastReboot")
| eval LastLogon = strptime(LastLogon,"%Y%m%d%H%M%S")
| eval LastBootUpTime = strptime(LastBootUpTime,"%Y%m%d%H%M%S")
| eval secondlastday=relative_time(now(), "-2d@d")
| eval nexttendays=relative_time(now(), "+10d@d")
| where (LastLogon < secondlastday) AND (LastBootUpTime > nexttendays)
| eval LastLogon = strftime(LastLogon,"%d-%m-%Y %H:%M:%S")
| eval LastBootUpTime = strftime(LastLogon,"%d-%m-%Y %H:%M:%S")
| stats latest(LastLogon) as LastLogon, latest(LastBootUpTime) as LastReboot by host
If you are trying to find hosts that have not been logged onto in 2 days and that last booted up more than ten days ago, then try this...
index="ai-wkst-windows-wmi-fr" (sourcetype="WMI:LastLogon" OR sourcetype="WMI:LastReboot")
| eval LastLogon = strptime(LastLogon,"%Y%m%d%H%M%S")
| eval LastBootUpTime = strptime(LastBootUpTime,"%Y%m%d%H%M%S")
| stats max(LastLogon) as LastLogon, max(LastBootUpTime) as LastReboot by host
| eval twodaysago=relative_time(now(), "-2d@d")
| eval tendaysago=relative_time(now(), "-10d@d")
| where (LastLogon < twodaysago) AND (LastBootUpTime < tendaysago)
| eval LastLogon = strftime(LastLogon,"%Y-%m-%d %H:%M:%S")
| eval LastBootUpTime = strftime(LastLogon,"%Y-%m-%d %H:%M:%S")
If that was not your intent, then please describe in plain language what you are trying to achieve.
Note - for your own sake and those of your clients and customers, please get in the habit of presenting dates in ISO date format (year month day then time). "2019-01-07" is unambiguous so no one can mistake January seventh (01-07-2019 in US, or 07-01-2019 in UK) for July first.
Also, when presented in ISO format, dates can be directly compared for greater than, less than or equality, without converting formats.
Followup questions if the above did not solve your issue:
1) Are these two fields ever on the same event? If not, then no events will ever survive the "AND". Thus, you have to do the stats command before the compare.
2) Why would "LastBootUpTime" be a future date-time? Either the name is wrong, or the logic is wrong. We assumed the logic was wrong in the code presented above.
If you are trying to find hosts that have not been logged onto in 2 days and that last booted up more than ten days ago, then try this...
index="ai-wkst-windows-wmi-fr" (sourcetype="WMI:LastLogon" OR sourcetype="WMI:LastReboot")
| eval LastLogon = strptime(LastLogon,"%Y%m%d%H%M%S")
| eval LastBootUpTime = strptime(LastBootUpTime,"%Y%m%d%H%M%S")
| stats max(LastLogon) as LastLogon, max(LastBootUpTime) as LastReboot by host
| eval twodaysago=relative_time(now(), "-2d@d")
| eval tendaysago=relative_time(now(), "-10d@d")
| where (LastLogon < twodaysago) AND (LastBootUpTime < tendaysago)
| eval LastLogon = strftime(LastLogon,"%Y-%m-%d %H:%M:%S")
| eval LastBootUpTime = strftime(LastLogon,"%Y-%m-%d %H:%M:%S")
If that was not your intent, then please describe in plain language what you are trying to achieve.
Note - for your own sake and those of your clients and customers, please get in the habit of presenting dates in ISO date format (year month day then time). "2019-01-07" is unambiguous so no one can mistake January seventh (01-07-2019 in US, or 07-01-2019 in UK) for July first.
Also, when presented in ISO format, dates can be directly compared for greater than, less than or equality, without converting formats.
Followup questions if the above did not solve your issue:
1) Are these two fields ever on the same event? If not, then no events will ever survive the "AND". Thus, you have to do the stats command before the compare.
2) Why would "LastBootUpTime" be a future date-time? Either the name is wrong, or the logic is wrong. We assumed the logic was wrong in the code presented above.
hi
sorry my explanations was not explicit
what I want :
for example for secondlastday I want events that are included between now() and minus two days before
for example today we are 2019-01-08 so I just want events between 2019-01-08 and 2019-01-06 but i dont want events previous to the 2019-01-06
same thing for nexttendays
I want only events included between now() and ten days after max the secondlastday
so in my example I would like to have events from the 2019-01-16 and after
nobody cant help me on this topic please??
You accepted an answer; was that a mistake? If you need more help, I would probably just start a new question.
@jip31 - Could you tell me how it is that you expect future events to be on your system? Are you loading some kind of future projections of activity? Are you sure that the date/time you are looking for is in the _time
field of the events?
Add more expressions to the where
clause to restrict secondlastday and nexttendays as desired.
What do you mean that sorting on LastLogon doesn't work? What do you get? What do you want?
for example for secondlastday I want events that are included between now() and minus two days before
for example today we are 2019-01-08 so I just want events between 2019-01-08 and 2019-01-06 but i dont want events previous to the 2019-01-06
same thing for nexttendays
I want only events included between now() and ten days after max the secondlastday
so in my example I would like to have events from the 2019-01-16 and after