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]

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?

[renjith_nair · Accepted solution]

@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

[willadams]

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?

[willadams]

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

[renjith_nair]

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

[willadams]

Appreciated. Thanks