topic Splunk table get values smaller than 4 months in Splunk Search

Splunk table get values smaller than 4 months

anouar_jben — Wed, 04 Mar 2020 10:56:53 GMT

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!

Re: Splunk table get values smaller than 4 months

richgalloway — Wed, 04 Mar 2020 13:46:13 GMT

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

Re: Splunk table get values smaller than 4 months

anouar_jben — Wed, 04 Mar 2020 14:03:01 GMT

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,

Re: Splunk table get values smaller than 4 months

anouar_jben — Wed, 04 Mar 2020 14:07:33 GMT

Aha! I found it. It worked this way:

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

Many thanks for your help!

Re: Splunk table get values smaller than 4 months

to4kawa — Thu, 05 Mar 2020 07:57:39 GMT

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.

Re: Splunk table get values smaller than 4 months

anouar_jben — Thu, 05 Mar 2020 08:30:34 GMT

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