topic Re: dedup results in a table and count them in Splunk Search

dedup results in a table and count them

ndcl — Tue, 20 Aug 2013 12:23:01 GMT

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

Re: dedup results in a table and count them

linu1988 — Tue, 20 Aug 2013 12:31:46 GMT

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

Re: dedup results in a table and count them

amit_saxena — Tue, 20 Aug 2013 12:34:51 GMT

Hi,

How about using "dedup ComputerName, User" ?

Regards,
Amit Saxena

Re: dedup results in a table and count them

ndcl — Tue, 20 Aug 2013 12:45:58 GMT

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.

Re: dedup results in a table and count them

ndcl — Tue, 20 Aug 2013 12:49:58 GMT

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.

Re: dedup results in a table and count them

linu1988 — Tue, 20 Aug 2013 14:17:23 GMT

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

Re: dedup results in a table and count them

ndcl — Wed, 04 Sep 2013 13:03:33 GMT

Hey,
this works and looks pritty nice!!!

Thanks!!!

Re: dedup results in a table and count them

fbl_itcs — Mon, 18 Nov 2013 09:02:40 GMT

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