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

ERFFFFF — Thu, 28 Jul 2022 15:20:47 GMT

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,

Re: Count a field by another one and removes duplicates

somesoni2 — Thu, 28 Jul 2022 15:00:36 GMT

Just add this to your current search

| eventstats dc(Username_column) as countUsername by clientip_column

Re: Count a field by another one and removes duplicates

richgalloway — Thu, 28 Jul 2022 15:02:20 GMT

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

Re: Count a field by another one and removes duplicates

ERFFFFF — Fri, 29 Jul 2022 11:01:50 GMT

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

topic Re: Count a field by another one and removes duplicates in Splunk Search

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

Re: Count a field by another one and removes duplicates

Re: Count a field by another one and removes duplicates

Re: Count a field by another one and removes duplicates