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

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

wilcoxj — Fri, 13 Jan 2017 15:03:32 GMT

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.

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

somesoni2 — Fri, 13 Jan 2017 17:17:15 GMT

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

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

DalJeanis — Fri, 13 Jan 2017 23:39:35 GMT

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

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

DalJeanis — Fri, 13 Jan 2017 23:43:49 GMT

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