Splunk Search

How to edit my search into a timechart?

jpringle03
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

somesoni2
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

adayton20
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

jpringle03
Path Finder

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

0 Karma

somesoni2
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
0 Karma

jpringle03
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

somesoni2
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

jpringle03
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

somesoni2
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

jpringle03
Path Finder

That did the trick! Thanks so much!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...