Summary stats for a search

[lehrfeld]

Hi All - I'm working on creating a summary report and I am having difficulty discerning the various addtotals or addcoltotals commands to get Splunk to yield to my bidding. Here is what I have so far ...

location campID Clickers
cityA 1 20
cityA 2 10
cityA 3 5
cityB 1 15
cityB 2 25
cityC 4 7
... ..

sourcetype=phishing_clickers
| lookup lookup.csv identity as userID OUTPUT bunit as location
| stats count(userID) as Clickers by location, campID
| addColtotals

I would like to be able to sum each location's total Clicker amount. So something like this ...

location campID Clickers Total_Clicks
cityA 1 20
cityA 2 10
cityA 3 5
35
cityB 1 15
cityB 2 25
40
cityC 4 7
7
... ..

Thank you!!

Mike

[lguinn2 · Accepted solution]

First, I am not sure that you are getting the data you expect, As you show it "Clickers" should probably be labelled "Clicks" - your name implies that this is the number of people clicking, not the number of clicks. If you want the unique count for userID, then use distinct_count(userID) not count(userID). Very different.

But try this, changing back to count if that is really what you want,..

sourcetype=phishing_clickers
| lookup lookup.csv identity as userID OUTPUT bunit as location
| stats distinct_count(userID) as Clickers by location, campID
| appendpipe [stats sum(Clickers) as TotalClickers by location | eval campID = "Total"]
| sort location

[lehrfeld]

Thank you! You are correct in your evaluation of the count Vs. distinct_count. However,in our shop the context is correct (these are not web logs, they have already been parsed partially before I get them in Splunk). My error in not defining the problem's scope properly.

I love that appendpipe command. Thank you! Mike