How to filter if a user has events in the last 60 days?

[snipedown21]

I have a table which consists of user names, events triggered by the user and the timestamps when the events were triggered.
I want to create a report which shows me the top 10 users who haven't triggered an event in the past 60 or greater days.
User event Timestamp
ABC OC 22-01-2017
ABC OC 23-05-2017
XYZ EZ 24-04-2017
PQR OC 01-01-2017

According to the table above, ABC and PQR haven't had an event since more than 60 days. ABC has not triggered an event in 122 days and PQR in 143 days. Note that ABC has triggered an event just two days ago.
Hence, He doesn't qualify in the report.

PQR has not triggered an event in the last 143 days, hence he qualifies in the report.
The final report must show top 10 users.

Here's what I have but It takes really long to complete execution on the dataset I have.

index=sometable | stats count by user_name, event_name, _time | eval d=split(_time, " ") | eval now=now() | eval diff= now - d | eval days=diff/(3600*24) | sort -days | fields - count, - d, - diff, - _time | fields user_name, days | sort -days |rename days as lastUsed | where min(lastUsed) > 60 | stats min(lastUsed) by user_name | sort -min(lastUsed) | rename min(lastUsed) as _last | eval "Last User Activity"=ceil(_last) | rename user_name as "User Name" | head 10
Thank you.

[richgalloway · Accepted solution]

Long execution times are to be expected when searching a large data set over 60 days time. There are still some efficiencies that can be made. The first is to filter your data as much and as soon as possible. That reduces the amount of data that is transferred from indexers to search head. Your query specifies only index, but adding sourcetype and other fields will help cut down the number of events read. Second, eliminate unneeded operations like sorting 3 times and calculating a stat that is not used (count).

Try this untested query

index=sometable | where _time<relative_time(now(), "-60d@d") | eval days = (now() - _time) / 86400 | sort - days | head 10 | eval "Last User Activity"=ceil(_last) | rename user_name as "User Name" | table "User Name" event "Last User Activity"

[snipedown21]

Makes so much sense. Thank you.!!

[kamlesh_vaghela]

Hi @snipedown21,
Can you please try below search?

index=sometable | dedup user_name| eval dt= strptime(_time,"%d-%m-%Y"), now=now(), diff= now - dt | where diff>(3600*24*60) | sort -diff| head 10 | eval "Last User Activity"=ceil(diff/(3600*24)) | rename user_name as "User Name" 

Thanks