Count table value pair

[splunkmata]

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

[splunkmata]

[splunkmata]

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

[clara-fication · Accepted solution]

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

[hunters_splunk]

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

[hunters_splunk]

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

[splunkmata]

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.