topic Timechart span=1d count by new users not seen before in Splunk Search

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

jenkinsta — Wed, 08 May 2024 23:41:47 GMT

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?

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

bowesmana — Thu, 09 May 2024 00:00:43 GMT

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

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

Temuulen0303 — Thu, 09 May 2024 04:32:45 GMT

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

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

bowesmana — Thu, 09 May 2024 04:26:45 GMT

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.

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

gcusello — Thu, 09 May 2024 06:14:22 GMT

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

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

PickleRick — Thu, 09 May 2024 07:47:21 GMT

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.

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

jenkinsta — Thu, 09 May 2024 11:52:20 GMT

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

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

PickleRick — Thu, 09 May 2024 20:47:13 GMT

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