topic Re: How to take the time from the field I am interested in and compare that to the current time to mark as an alert? in Splunk Search

How to take the time from the field I am interested in and compare that to the current time to mark as an alert?

willadams — Mon, 23 Jul 2018 02:59:44 GMT

I am exporting data out of AD and trying to look for devices that are older than a certain time frame. From my data extract I have the following date information (amongst a bunch of others) for the field I am interested in

lastLogonDate

The field lastLogonDate has the following format in the CSV

20-Jul-18 8:36:38PM

I have written the following evaluation to try and convert this text field to a time I can use in SPLUNK to try and figure out whether the machine was last seen in say the last 60 days.

| eval logondate_epoch=strptime(lastLogonDate, "%d-%b-%y %H:%M:%S"

I am then trying

| where logondate_epoch > 60 days

But this results in the error "operator at 'days' is invalid.

How do I take the time from the field I am interested in and compare that to the current time less 60 (or however many) days to mark as an alert?

Re: How to take the time from the field I am interested in and compare that to the current time to mark as an alert?

renjith_nair — Mon, 23 Jul 2018 05:23:35 GMT

@willadams ,

First of all the strptime format is not matching with your date format. Please try below and validate with your environment.

"your searches"
|eval logondate_epoch=strptime(lastLogonDate, "%d-%b-%y %I:%M:%S%p")
|eval diff=round((now()-logondate_epoch)/86400,0)
|where diff > 60

Re: How to take the time from the field I am interested in and compare that to the current time to mark as an alert?

willadams — Mon, 23 Jul 2018 05:57:11 GMT

Adjusted my query and this has worked as expected.

I can see that my time format was wrong with respect to the %I and %p (I will look at the SPLUNK documentation around those formats).

Can you please explain why the eval is done with the diff and what is the purpose of the divide by 145440/0?
Is "where diff" by default days? Can this be modified to months or even years?

Re: How to take the time from the field I am interested in and compare that to the current time to mark as an alert?

willadams — Mon, 23 Jul 2018 06:25:59 GMT

Okay I can see where the match for strptime was wrong. The %I being for 12 hour clock vs the %H being 24 hours; and %p for AM/PM.

Still need to understand the diff though

Re: How to take the time from the field I am interested in and compare that to the current time to mark as an alert?

renjith_nair — Tue, 29 Sep 2020 20:34:26 GMT

@willadams,
That was typo with 145440,its corrected in answer.

The unix time stamp is merely the number of seconds between a particular date and the Unix Epoch and hence the diff returns is the difference in number of seconds. Since we want to have number of days, we divide the seconds
seconds/60=minutes
minutes/60=hours
hours/24=days
which is resulting in 60*60*24 = 86400

And yes, you can convert it to any time duration. Just need to convert these seconds to the time unit by dividing it appropriately.

Re: How to take the time from the field I am interested in and compare that to the current time to mark as an alert?

willadams — Mon, 23 Jul 2018 07:51:42 GMT

Appreciated. Thanks