Can you help me with relative time conditions?

[jip31]

hi,

I use this request, but I am not sure it works fine.

In the query below, I want to display the LastLogon and LastReboot fields from the following date conditions:

I just want to display a result if LastLogon < 2 days from the current day and if LastReboot >10 days from the current day.

But, when I play with the relative time values, it doesn't return the corresponding event.

For example, we are the 3rd of January.

Normally | eval secondlastday=relative_time(now(), "-2d@d" should return me values between the 1st and the 3rd of January, but it also returns the oldest values:

Could you help me please??

index="windows-wmi-fr" (sourcetype="WMI:LastLogon" OR sourcetype="WMI:LastReboot") 
| eval LastLogon = strftime(strptime(LastLogon,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M") 
| rex field=LastLogon mode=sed "s/..$//" 
| eval LastBootUpTime = strftime(strptime(LastBootUpTime,"%Y%m%d%H%M%S"),"%d/%m/%Y %H:%M") 
| rex field=LastBootUpTime mode=sed "s/..$//" 
| 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)
|stats latest(LastLogon) as LastLogon, latest(LastBootUpTime) as LastReboot by host

[jip31]

hi

i use the request below but i have an issue with the where condition
if i just use this part of code I have results corresponding
| where (LastLogon < secondlastday)
but when i use the entire request I m surprised to have no results
| where (LastLogon < secondlastday) AND (LastBootUpTime > nexttendays)
so I wonder if there is no an issue in the relative time
| eval nexttendays=relative_time(now(), "+10d@d") means well that the query check all the machines wich have booted between today and ten days after??

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) 
| stats latest(LastLogon) as LastLogon, latest(LastBootUpTime) as LastReboot by host

[andreacorvini · Accepted solution]

Try this
index="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) ...

[jip31]

I have no error but also no results even if I change relative time

[andreacorvini]

I think now the issue is related to the eval.
I don't know what you want but you are not populating nexttendays with nexttendays=relative_time(now(), "10d@d").
If you try with nexttendays=relative_time(now(), "-10d@d") you will have results I think but I don't know if it's the result you required.
So try to check variables with this:

| makeresults | eval nexttendays=relative_time(now(), "-10d@d") | eval secondlastday=relative_time(now(), "-2d@d") | table nexttendays secondlastday

[jip31]

no it doesnt works
if I just do this I have results but not in the date format
just : 1543420093.000000
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) | table LastLogon

[andreacorvini]

I think it will be correct with "-10d@d"

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) | table LastLogon

anyway please run

  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")| table LastLogon LastBootUpTime secondlastday nexttendays

1543420093.000000 is correct, it's the epoch time to compare. I use epochconverter

[jip31]

I confirm that with -10d@d it doesnt works
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) | table LastLogon LastBootUpTime

and I just want to display a result if LastLogon < 2 days from the current day and if LastReboot >10 days from the current day.

when i do the code below i have results but I need to add my where conditions

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") 
| dedup host 
| table host LastLogon LastBootUpTime

So if i do this I have results
but whenever I add AND (LastBootUpTime > nexttendays) i have no results even if I modifiy the relative time!

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") 
| dedup host 
| where (LastLogon < secondlastday)
| table host LastLogon LastBootUpTime

[andreacorvini]

... "and I just want to display a result if LastLogon < 2 days from the current day and if LastReboot >10 days from the current day."

you mean the last reboot executed in the last 10 days?
Or executed more than 10 days ago?
Please run the query I wrote and share (an example) the event you want to view.

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")| table LastLogon LastBootUpTime secondlastday nexttendays

[jip31]

I want to display only the host which have been loggend since less than 2 days and which have not rebooted since more 10 days
you can see the result here
https://cjoint.com/c/IAejdxWgnFd

[andreacorvini]

Ok, as you can see you don't have both values for the same event and in this case it's not possible with the logic in use. You have to use hostname and create a complete different search.

[jip31]

No
if you do this its on the same line

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")
|stats latest(LastLogon) as LastLogon, latest(LastBootUpTime) as LastReboot by host

[andreacorvini]

Maybe, it's the result you want? Consider you are reading 2 different events in different time. If it's the result you need, it's ok.

[andreacorvini]

...and pay attention that if you want to use

| eval nexttendays=relative_time(now(), "10d@d") 

you have to use "+"

| eval nexttendays=relative_time(now(), "+10d@d") 

but in this case you'll have a result date in the future.