Splunk Search

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

snipedown21
Path Finder

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.

Tags (3)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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"
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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"
---
If this reply helps you, Karma would be appreciated.
0 Karma

snipedown21
Path Finder

Makes so much sense. Thank you.!!

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...