Splunk Search

Can you help me with relative time conditions?

jip31
Motivator

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
Tags (1)
0 Karma
1 Solution

andreacorvini
Path Finder

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) ...

View solution in original post

0 Karma

jip31
Motivator

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

andreacorvini
Path Finder

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) ...

0 Karma

jip31
Motivator

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

0 Karma

andreacorvini
Path Finder

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

jip31
Motivator

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

0 Karma

andreacorvini
Path Finder

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

0 Karma

jip31
Motivator

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

andreacorvini
Path Finder

... "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
0 Karma

jip31
Motivator

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

0 Karma

andreacorvini
Path Finder

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.

0 Karma

jip31
Motivator

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

0 Karma

andreacorvini
Path Finder

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.

0 Karma

andreacorvini
Path Finder

...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.

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...