topic Re: How to group and add the count for each value of a field? in Splunk Search

How to group and add the count for each value of a field?

emamedov — Thu, 07 Jan 2016 21:56:38 GMT

I am currently trying to group together unique products, and have the username listed under each product, however, I want to also add a count and that is where I hit a roadblock. I have looked at multiple posts regarding this topic and can't quite get over the finish line.

Example:

Instead of:
Product A:
User1
User2
User2
User2
User3

Product B:
User 1
User 1
User 2

I would like to have:

Product A:
User 1 - 1
User 2 - 3
User 3 - 1

Product B:
User 1 - 2
User 2 - 1

Below is the search string I'm currently using to generate just the grouped product/users:

eventtype=product-view|stats values(username) by productname

Re: How to group and add the count for each value of a field?

frobinson_splun — Fri, 08 Jan 2016 00:05:02 GMT

Hi @emamedov,
Have you tried using "stats count by" with the fields you are trying to aggregate?

For example:
eventtype=product-view|stats count by productname username

As a more general example, if I run a search that includes this :
...| stats count by (fieldA) (fieldB)
then the results would show a count of how many (fieldB) items there are, per (fieldA) item. It seems like a search like this would give you view counts per user for each product.

Here are some examples in the documentation that might help:
http://docs.splunk.com/Documentation/Splunk/6.3.1511/SearchReference/Stats#Use_Case_Examples

Re: How to group and add the count for each value of a field?

sundareshr — Fri, 08 Jan 2016 00:33:13 GMT

Try this

eventtype=product-view | chart count over username by productname

And then if you want totals, you can do

eventtype=product-view | chart count over username by productname | addtotals | addcoltotals labelfield=fieldA label=Totals

Re: How to group and add the count for each value of a field?

dark_15 — Tue, 12 Jan 2016 17:52:25 GMT

Hello @emamedov,
I ran into a similar problem and found a solution hidden in the splunk archives here. For your use case, try this:

 eventtype=product-view 
 | stats count by username, productname
 | stats list(username) as "User Name" list(count) as count by productname
 | rename productname as "Product Name"

Good luck and happy hunting!