topic Count table value pair in Splunk Search

Count table value pair

splunkmata — Wed, 11 Jan 2017 07:08:54 GMT

I have a table like this derive from search

I need to have it formatted like this. Like counting the username-device pair occurance

Also, how do call this kind of table (field value pair?).. just so next time i know what to search

Re: Count table value pair

hunters_splunk — Wed, 11 Jan 2017 14:38:24 GMT

Hi splunkmata,

Please try the following search commands following the search string that produced your table:
... | stats count by USERNAME, DEVICE | stats list(DEVICE) as DEVICE, list(count) as count

Hope this helps. Thanks!
Hunter

Re: Count table value pair

hunters_splunk — Wed, 11 Jan 2017 14:40:20 GMT

Sorry, correction - missing by DEVICE at the end:
... | stats count by USERNAME, DEVICE | stats list(DEVICE) as DEVICE, list(count) as count by DEVICE

Thanks!
Hunter

Re: Count table value pair

cmerriman — Wed, 11 Jan 2017 14:45:06 GMT

try something like this:

...| stats count by USERNAME, DEVICE|eval deviceCount=DEVICE+" - "+count | stats list(deviceCount) as deviceCount by USERNAME|rex field=deviceCount "(?<DEVICE>.*) - (?<COUNT>.*)"|fields - deviceCount

Re: Count table value pair

splunkmata — Thu, 12 Jan 2017 04:39:17 GMT

Hi hunter,
thanks for the input.

got this error when i pipe your stat to my query
Error in 'stats' command: The output field 'DEVICE' cannot have the same name as a group-by field.

Re: Count table value pair

splunkmata — Thu, 12 Jan 2017 04:40:38 GMT

This one did it

...| stats count by USERNAME, DEVICE|eval deviceCount=DEVICE+" - "+count | stats list(deviceCount) as deviceCount by USERNAME|rex field=deviceCount "(?<DEVICE>.*) - (?<COUNT>.*)"|fields - deviceCount

Re: Count table value pair

splunkmata — Thu, 12 Jan 2017 04:45:42 GMT