How to edit my search into a timechart?

[jpringle03]

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.

[adayton20]

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

[jpringle03]

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

[somesoni2 · Accepted solution]

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

[jpringle03]

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?

[somesoni2]

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

[jpringle03]

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.

[somesoni2]

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

[jpringle03]

That did the trick! Thanks so much!