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!

Fun with Regular Expression - multiples of nine

Fun with Regular Expression - multiples of nineThis challenge was first posted on Slack #regex channel ...

[Live Demo] Watch SOC transformation in action with the reimagined Splunk Enterprise ...

Overwhelmed SOC? Splunk ES Has Your Back Tool sprawl, alert fatigue, and endless context switching are making ...

What’s New & Next in Splunk SOAR

Security teams today are dealing with more alerts, more tools, and more pressure than ever.  Join us on ...