Splunk Search

Count table value pair

splunkmata
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

cmerriman
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

splunkmata
New Member

alt text

0 Karma

splunkmata
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

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

hunters_splunk
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

hunters_splunk
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

splunkmata
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
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...