Archive

Count table value pair

New Member

I have a table like this derive from search
alt text
I need to have it formatted like this. Like counting the username-device pair occurance
alt text

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

Tags (1)
0 Karma
1 Solution

Super Champion

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

View solution in original post

0 Karma

New Member

alt text

0 Karma

New Member

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
0 Karma

Super Champion

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

View solution in original post

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

New Member

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.

0 Karma