Splunk Search

How to edit my search into a timechart?

Path Finder

In a past post someone helped me create the following search

source=duo extracted_eventtype=authentication result="SUCCESS" earliest=-90d | eval Period=if(_time>=relative_time(now(),"-7d"),1,2)  | stats max(Period) as periods by username | where periods=1 | chart count

I'm looking for help with turning this same count into a timechart looking at things a certain amount of time back (in this -7) and then always -90 back and seeing which ones only exist in the -7 timeframe.

1 Solution

SplunkTrust
SplunkTrust

Give this a try. Will give you timechart of count of users in last 7 days which have never logged in before (for 7 to 90 day period)

source=duo extracted_eventtype=authentication result="SUCCESS" earliest=-7d NOT [search source=duo extracted_eventtype=authentication result="SUCCESS" earliest=-90d latest=-7d | stats count by username | table username] | timechart dc(username) as count

View solution in original post

0 Karma

Contributor

Since your original query simply has a chart count at the end, and you want it to be a timechart, why not change the end terms to timechart count? You can change the span of 7 days (7d) into something bigger or smaller depending on how you want the view to look. Given that you're going 90 days back, I figured a week span was appropriate.

source=duo extracted_eventtype=authentication result="SUCCESS" earliest=-90d | eval Period=if(_time>=relative_time(now(),"-7d"),1,2)  | stats max(Period) as periods by username | where periods=1 | timechart span=7d count
0 Karma

Path Finder

This actually just returns no results at all which it shouldn't be doing.

0 Karma

SplunkTrust
SplunkTrust

Give this a try. Will give you timechart of count of users in last 7 days which have never logged in before (for 7 to 90 day period)

source=duo extracted_eventtype=authentication result="SUCCESS" earliest=-7d NOT [search source=duo extracted_eventtype=authentication result="SUCCESS" earliest=-90d latest=-7d | stats count by username | table username] | timechart dc(username) as count

View solution in original post

0 Karma

Path Finder

Hey! This did definitely give me some data but it appears to be returning multiple events for the same users within the -7 day period. Is there a way to restrict it to unique count of the term username?

0 Karma

SplunkTrust
SplunkTrust

Well you requested a timechart and it is doing unique count. Since the time range is 7 days for timechart there could be duplicate.

0 Karma

Path Finder

Perhaps my request wasn't specific enough or what I'm looking for isn't possible. Still within those 7 days I want it to be unique usernames total that its counting so if "Bob" has never authenticated between -7d and -90 but does authenticate multiple times in -7d it should only count him as one.

0 Karma

SplunkTrust
SplunkTrust

Lets give this a try then. Takes the first login time of a users, within last 7 days, so that they will be counted once, all other logic still there.

source=duo extracted_eventtype=authentication result="SUCCESS" earliest=-7d NOT [search source=duo extracted_eventtype=authentication result="SUCCESS" earliest=-90d latest=-7d | stats count by username | table username]  | stats min(_time) as _time by username | timechart dc(username) as count
0 Karma

Path Finder

That did the trick! Thanks so much!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!