Splunk Search

How to edit my search to limit the distinct user count by set number of events per unique user?

ON34C02151009
Explorer

I was asked to provide active users since December 2016. With the logs we're working with, there's really no way to get a number of "active users"; someone at my branch suggested the best way would be to get a number for distinct users, but there would be no way to distinguish between users who logged into the system once and those who log in regularly.

My proposed solution is to provide the distinct user count but to only count users that appear in 5 or more events (I was told I can assume 5 events is enough to consider them "active"). Is there a way to do this or would anyone have some advice? What I have so far is just the distinct users part, but I haven't been able to figure out what limits the distinct user count based on number of events per distinct user.

sourcetype=access* uri_path=* NOT eventtype=Web* |fillnull value="Missing Field"|  stats dc(UID) as distinct_users
0 Karma
1 Solution

adonio
Ultra Champion

using @somesoni2 search for dc as well if needed
sourcetype=access* uri_path=* NOT eventtype=Web* |fillnull value="Missing Field"| stats count as active_users by UID | where active_users >=5

| stats dc(UID) as distinct_active_users

View solution in original post

adonio
Ultra Champion

using @somesoni2 search for dc as well if needed
sourcetype=access* uri_path=* NOT eventtype=Web* |fillnull value="Missing Field"| stats count as active_users by UID | where active_users >=5

| stats dc(UID) as distinct_active_users

ON34C02151009
Explorer

I tested this comparing the results to my basic distinct users search and then just running your query up to the end of the first stats command. It appears to work! Thank you very much.

0 Karma

adonio
Ultra Champion

maybe this can work:

 sourcetype=access* uri_path=* NOT eventtype=Web* |fillnull value="Missing Field"|  stats count as active_users by UID | where count >=5 | sort -count
0 Karma

ON34C02151009
Explorer

I'm unable to parse the uri_path= UID= part.

0 Karma

adonio
Ultra Champion

hmmm, i used the same base search you shared in question, the only change i made is after stats:

... | stats count as active_users by UID | where count >=5 | sort -count

0 Karma

somesoni2
Revered Legend

Your search in question gives you a single row-column with distinct count of users. If your desirable output is the same, just add your stats command to end of @adonio's search.

 sourcetype=access* uri_path=* NOT eventtype=Web* |fillnull value="Missing Field"|  stats count as active_users by UID | where count >=5  |  stats dc(UID) as distinct_active_users
0 Karma

ON34C02151009
Explorer

@adonio Sorry about that, I must have pasted the search incorrectly.So far I'm not getting any results with your actual search though.

somesoni2, your search gives me the "distinct_active_users" bar but the results just stay at 0. I tried some other variations of my search but it remains the same.

0 Karma

adonio
Ultra Champion

does your base search produces results?
is UID a field that already exists and has valid values?

0 Karma

ON34C02151009
Explorer

Yes to both.

Maybe I'm off the mark but I think the error might be in the initial stats search. Correct me if I'm wrong, but doesn't stats count as active_users by UID need something in front of count? Or is there a default field it uses if you don't specify anything in brackets such as count(UID) as distinct_user by _time?

0 Karma

somesoni2
Revered Legend

What results you get when you run your search?? It's strange, the only thing @adonio's search is using is field UID, which seems to be in same letter-case as yours.

 sourcetype=access* uri_path=* NOT eventtype=Web* |fillnull value="Missing Field"|  stats dc(UID) as distinct_users
0 Karma

ON34C02151009
Explorer

Actually, it's the where count >= 5 that is not working for me. sourcetype=access* uri_path=* NOT eventtype=Web* |fillnull value="Missing Field"| stats count as active_users by UID works but it gives me everything.

0 Karma

adonio
Ultra Champion

my bad, must be the time and day
thee where is to be by active_users since i stated the count to be named active users
so: ... | stats count as active_users by UID | where active_users >=5
or: ... | stats count by UID | where count >=5

ill put it in an answer.

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...