topic Re: Problem with count in Splunk Search

Problem with count

MBerikcurtis — Fri, 09 Sep 2011 22:40:07 GMT

I'm using the search to get a count of Windows Event Codes. If I remove stats count by EventCode, I get the Event Description colum to populate. As soon as I include count, the colum goes blank.

sourcetype="*Security" | stats count by EventCode  | table EventCode EventCodeDescription count | sort EventCode

Re: Problem with count

sideview — Fri, 09 Sep 2011 23:30:19 GMT

Well when you do stats count by EventCode, that says that you're only interested in seeing the number of events for each value of EventCode. If there's always one EventCodeDescription value for each EventCode value (as indeed there is), then here's how you would bring the other field along for the ride as well:

`stats count first(EventCodeDescription) by EventCode`

Stats has all kinds of amazing power, but the best thing about it is how transparent it is. Once you learn that the by foo, bar, baz clauses are just saying to analyze unique combinations of foo,bar and baz, and once you learn the core set of statistical operations, you'll know what the result rows will look like just by reading the search.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Stats

Note that these other two pages are essential references as well. If you use Splunk a lot they will quickly become your main reference pages in the search language:

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonStatsFunctions

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions

Re: Problem with count

MBerikcurtis — Sat, 10 Sep 2011 01:59:59 GMT

amazing! thanks for the fast answer.

Re: Problem with count

MBerikcurtis — Mon, 12 Sep 2011 18:11:46 GMT

I have implemented what you sudgested and am now using:

sourcetype="*Security"
| stats count first(EventCodeDescription) by EventCode
| table EventCode EventCodeDescription count
| sort EventCode

but still nothing is shown in the EventCodeDescription column.

Re: Problem with count

acdevlin — Mon, 12 Sep 2011 18:26:47 GMT

I agree with Nick's assessment, but would recommend a slightly different solution.

Rather than using Stats, if you are only interested in CALCULATING a count rather than DISPLAYING it, Eventstats would be a better command to use. It keep the fields not specified in that particular pipe, yet will still calculate the count.

I think you'll then want your query to look something like this:

sourcetype="*Security" | eventstats count as "EventCount" by EventCode  | table EventCode EventCodeDescription EventCount | sort EventCode

Hope this helps.

Re: Problem with count

MBerikcurtis — Mon, 12 Sep 2011 19:00:09 GMT

I have implemented the solution posted by acdevlin and it works! I have added dedup so that the search reads

sourcetype="*Security" | eventstats count as "EventCount" by EventCode | table EventCode EventCodeDescription EventCount | sort EventCode | dedup EventCode