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
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.
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.
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
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
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.
amazing! thanks for the fast answer.