Splunk Search

How to count a field by another one and removes duplicates?

ERFFFFF
Explorer

Hello everyone !

After a few hours of research i come ask your help.

Here is my data :

Username_column clientip_column
username1 xxx.xxx.xxx.xxx
username1 xxx.xxx.xxx.xxx
username1 xxx.xxx.xxx.xxx
username1 yyy.yyy.yyy.yyy
username2 xxx.xxx.xxx.xxx
username2 zzz.zzz.zzz.zzz
username3 yyy.yyy.yyy.yyy
username3 xxx.xxx.xxx.xxx

 

So, what i would like to do is to create another column called "countUsername" which contain the number of usernames by clientip without duplicates (of usernames).

Here is my dream table (what i want) :

Username_column clientip_column countUsername
username1 xxx.xxx.xxx.xxx 3
username1 xxx.xxx.xxx.xxx 3
username1 xxx.xxx.xxx.xxx 3
username1 yyy.yyy.yyy.yyy 2
username2 xxx.xxx.xxx.xxx 3
username2 zzz.zzz.zzz.zzz 1
username3 yyy.yyy.yyy.yyy 2
username3 xxx.xxx.xxx.xxx 3

 

I tried various of things like :

| eventstats values(count(Username_column)) as countUsername by clientip_column

 creating a multivalue column and trying of mvdedup().

combine my Username_column and my clientip_column like so :

| eval countUsername=Username_column. " " . clientip_column

 and doing lots of things on that, if(mach)), regex, ...

But everything that i tried didn't work. The best thing that i can get is :

| eventstats count(Username_column) as countUsername by clientip_column

 But with this line, my usernames are duplicated. (like the table bellow, i tried some things with this result but no results on my side)

Username_column clientip_column countUsername
username1 xxx.xxx.xxx.xxx 5
username1 xxx.xxx.xxx.xxx 5
username1 xxx.xxx.xxx.xxx 5
username1 yyy.yyy.yyy.yyy 2
username2 xxx.xxx.xxx.xxx 5
username2 zzz.zzz.zzz.zzz 1
username3 yyy.yyy.yyy.yyy 2
username3 xxx.xxx.xxx.xxx 5

 

Maybe you are wondering why i'm using eventstats instead of stats. The reason is that before this line, i have a large search with multiple stats commands, and if i don't use eventstats, all my others columns at the end of my large request won't show up.

Kind regards,

Labels (4)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

You were so very close.  Use the distinct_count function to count unique values of a field.

| eventstats dc(Username_column) as countUsername by clientip_column

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

You were so very close.  Use the distinct_count function to count unique values of a field.

| eventstats dc(Username_column) as countUsername by clientip_column

 

---
If this reply helps you, Karma would be appreciated.

ERFFFFF
Explorer

Damm.... indeed close...
Thank you very much for your help !

0 Karma

somesoni2
Revered Legend

Just add this to your current search

| eventstats dc(Username_column) as countUsername by clientip_column
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...