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.
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")
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")
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")
somesoni2 - good answer but switch your tests - "logged in time older than 2 days ago" means "date less than 2 days ago".