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?
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.
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
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
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
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")
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.
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