Splunk Search

Splunk table get values smaller than 4 months

anouar_jben
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
1 Solution

anouar_jben
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

anouar_jben
Explorer

Aha! I found it. It worked this way:

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

Many thanks for your help!

0 Karma

to4kawa
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

anouar_jben
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

richgalloway
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, Karma would be appreciated.

anouar_jben
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...