Splunk Search
Highlighted

Splunk table get values smaller than 4 months

Explorer

Hello,

I have the below query which works fine:

{My search}
| rename user_id as User
| stats max(asctime) as "Last login time (UTC)" by User
| table User "Last login time (UTC)"

Now from the table result, I want to get only the raws where "Last login time (UTC)" is 4 months ago or older.

Many thanks for your help!

Tags (2)
0 Karma
Highlighted

Re: Splunk table get values smaller than 4 months

SplunkTrust
SplunkTrust

Try this. If the 'asctime' field is text rather than epoch time then it won't work, but then again, your current query won't work, either.

{My search}
| rename user_id as User
| stats max(asctime) as maxasctime by User
| where maxasctime < relative_time(now(), "-4mon")
| rename maxasctime as "Last login time (UTC)"
| table User "Last login time (UTC)"
---
If this reply helps you, an upvote would be appreciated.
Highlighted

Re: Splunk table get values smaller than 4 months

Explorer

Hello,

asctime has the following format:

asctime:     2020-03-04 13:33:29,020    

Could you please advise what should I change in this case?

Thanks again and regards,

0 Karma
Highlighted

Re: Splunk table get values smaller than 4 months

Explorer

Aha! I found it. It worked this way:

 | where strptime(maxasctime,"%Y-%m-%d") < relative_time(now(), "-4mon")

Many thanks for your help!

View solution in original post

0 Karma
Highlighted

Re: Splunk table get values smaller than 4 months

Ultra Champion

Good job @anouar_jben
I have a few question.
your timezone is UTC?
now() is local time.
but it will be okay because it is on a monthly basis.

0 Karma
Highlighted

Re: Splunk table get values smaller than 4 months

Explorer

Well seen @to4kawa ! you are right, I should convert both variables to the same timezone to have more accurate results. I will work on it.
Thanks and regards

0 Karma