Splunk Search
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.