Splunk Search

Timechart span=1d count by new users not seen before

jenkinsta
Path Finder

Here is my example search to start...

index=data
| timechart span=1d by user

Now, I am trying to build out so the last 30 days I can get a count of new users that has not been seen on previous days. 

Tried some bin options and something like this but no joy. 

index=data | stats min(_time) as firstTime by user | eval isNew=if(strftime(firstTime, "%Y-%m-%d") == strftime(_time, "%Y-%m-%d"), 1, 0) | where isNew=1

Any help? 

 

Labels (2)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

The question is a bit imprecise.

What do you want to do precisely?

I'd interpret it as "For each day I want to get a count of accounts not appearing in the events already in any of the previous days". Is that right? Also how do you treat the first day of such summary? Because all acccounts from the first day would get shown this way first day.

0 Karma

jenkinsta
Path Finder

I'm looking for login attempts and the question is to identify new attempts from usernames that previously didn't try. 

 

Day 1  = day count of new seen 3

bob

sam

steve

Day 2      = day count of new seen 2

sam  # because previously seen, exclude from count

tom

ralph

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Ok. So I'd approach this from a different way.

Let's do some initial search

index=data

Then for each user we find his first ever occurrence

| stats min(_time) as _time by user

After this we have a list of first logins spread across time. So now all we need is to count those logins across each day

| timechart span=1d count

And that's it.

If you also wanted to have a list of those users for each day instead of doing the timechart you should rather group the users by day manually

| bin _time span=1d

So now you can aggregate the values over time

| stats count as 'Overall number of logins' values(user) as Users

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @jenkinsta ,

please try this:

index=data earliest=-30d latest=now
| eval period=if(_time>now()-86400,"last","previous")
| stats dc(period) AS period_count values(period) AS period BY user 
| where period_count=1 AND period="last"

Ciao.

Giuseppe

0 Karma

Temuulen0303
Explorer

You need historic data of users to compare.
You would need to configure Assets&Identities or save users to simple lookup.
You can store results daily, weekly, monthly using this search:

index=your_users_index
``` Add or configure neccessary fields
| eval bunit="your_bunit", startDate=strftime(now(),"%Y-%m-%d %H:%M:%S"),
| stats count by email, identity, nick, UserId, "first", "last", JobTitle, phone, bunit, work_country, work_city, startDate
| table email, identity, nick, UserId, "first", "last", JobTitle, phone, bunit, work_country, work_city, startDate
| search NOT [| inputlookup users.csv | fields email ]
| outputlookup append=true users.csv

And later you can sort users startDate using this search:

| inputlookup users.csv
| sort - startDate

Or get last month's new users:

| inputlookup users.csv
| eval epoch=strptime(startDate, "%Y-%m-%d %H:%M:%S")
| where epoch>relative_time(now(), "-20d")

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Is this a ChatGPT answer - firstly the OP does not mention having the Splunk Enterprise Security app - A&I framework is part of ES and your example search seems to be related to a query that would populate an Identity registry in ES rather than anything to do with the OP's post.

Secondly, the technique of search NOT [| inputlookup...] technique should never be recommended without a big warning on the use of subsearches which can perform terribly - I recently fixed a search using a NOT subsearch that was taking 18 minutes to evaluate the NOT criteria and reduced it to 9 seconds.

Certainly, a lookup of users to validate against can be a valid solution, but this would depend on whether the OP wants to find a new user's first ever login vs checking if the user has not logged in for 30 days, which is not clear.

 

bowesmana
SplunkTrust
SplunkTrust

This can tell you if the user's first login is the same as his last - hopefully this will give you some pointers

index=data earliest=-30d
| bin _time span=1d 
| stats count by _time user
| eventstats min(_time) as first max(_time) as last by user
| where first = last

 

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

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

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...