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

[ERFFFFF]

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,

[richgalloway · Accepted solution]

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

[ERFFFFF]

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

[somesoni2]

Just add this to your current search

| eventstats dc(Username_column) as countUsername by clientip_column