Splunk Search

How to edit my stats search to only display results older than X days?

wilcoxj
New Member

I am running this stats latest search for Microsoft Cloud Services UserLoggedIn:

index=o365 Operation=UserLoggedIn |
eval mytime=strftime(_time, "%m/%d/%Y") |
stats latest(mytime) As mytime by UserId

After it pulls the latest event, I need to search through and only pull out events that are older than X days.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try like this (checking last logged in time is 2 day older than current time. Adjust where clause per your need)

index=o365 Operation=UserLoggedIn |
stats max(_time) as mytime by UserId
| where mytime>(relative_time(now(),"-2d")
| eval mytime=strftime(mytime, "%m/%d/%Y") 

If you want to keep 1 day granularity, try this

index=o365 Operation=UserLoggedIn |
stats max(_time) as mytime by UserId
| where relative_time(mytime,"@d") >=(relative_time(now(),"-2d@d"")
| eval mytime=strftime(mytime, "%m/%d/%Y") 

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

In your original code, you are reformatting the _time of every record, rather than only reformatting the time of the latest value. Keep the _time in epoch format until after the aggregation. somesoni2 fixed that in his code, but didn't call it to your attention.

Here are some search efficiency notes for splunk -
1) Eliminate fields (or entire records) at the earliest possible moment
2) Do your calculations at the last possible moment.

The resulting code looks something like this -

 index=o365 Operation=UserLoggedIn 
| fields _time UserId
| stats latest(_time) As mytime by UserId
| where mytime <= (relative_time(now(),"-2d"))
| eval mytime=strftime(_time, "%m/%d/%Y") 
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try like this (checking last logged in time is 2 day older than current time. Adjust where clause per your need)

index=o365 Operation=UserLoggedIn |
stats max(_time) as mytime by UserId
| where mytime>(relative_time(now(),"-2d")
| eval mytime=strftime(mytime, "%m/%d/%Y") 

If you want to keep 1 day granularity, try this

index=o365 Operation=UserLoggedIn |
stats max(_time) as mytime by UserId
| where relative_time(mytime,"@d") >=(relative_time(now(),"-2d@d"")
| eval mytime=strftime(mytime, "%m/%d/%Y") 
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

somesoni2 - good answer but switch your tests - "logged in time older than 2 days ago" means "date less than 2 days ago".

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