dedup results in a table and count them

[ndcl]

Hi Base,

I just want to create a table from logon events on several servers grouped by computer. So the normal approach is: … | stats list(User) by Computer. Ok, this gives me a list with all the user per computer. But if a user logged on several times in the selected time range I will also get multiple entries of this user. This do not look nice. I want to dedup the user and add a column with the count of occurrence. Like:

Comp1 user1 3

...........user2 6

...........user3 9

Comp2 user1 1

...........user4 5

and so on.

So. All keywords are in that question (dedup and count) but:
Dedup leaves only one user entry. All others on other computer are gone and count only counts all users.

A search like this: stats count(User) by ComputerName, User will count the user but list the computer every time.

I think this is not very difficult but I didn´t get it. Someone with a hint here?

Thanks

[fbl_itcs]

Hi, it's been a while but maybe the answer is still needed:

... | stats values(User) by Computer

will give you a deduped list of the user.

Another approach would be to do a dedup. You can dedup more then one field, so:

... | dedup User, Computer | ...

will give you any combination of those two fields, but only onces.

Kind regards,
Felix

[amit_saxena]

Hi,

How about using "dedup ComputerName, User" ?

Regards,
Amit Saxena

[ndcl]

yep, and this is Prob 1. Dedup will remover every more occurrence of user than 1 but the user can logon several times and on several computers and I want to keep them.

[linu1988 · Accepted solution]

Hi,
Could we try this?

...|stats count AS Logins by User,Server|stats list(User)as Users,list(Logins)as "Login Count" by Server

I think this will give you the unique combination with number of counts.

Thanks

[ndcl]

Hey,
this works and looks pritty nice!!!

Thanks!!!

[linu1988]

Made a change, Could you check and let me know if it's useful? Haven't tried it myself actually.

[ndcl]

yep, this discribes prob 2. I have all counts of user but also the computername for every user but I want to group them by computer.