Splunk table get values smaller than 4 months

[anouar_jben]

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!

[anouar_jben · Accepted solution]

Aha! I found it. It worked this way:

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

Many thanks for your help!

[to4kawa]

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.

[anouar_jben]

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

[richgalloway]

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

[anouar_jben]

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,