Splunk Search

How to edit my search to find the distinct count by each field value in a list?

cm22486
Path Finder

Trying to count "violation type" for each program (in regards to AV program, stack pivot, overwrite code, etc etc) and show counts next to each program file path. Below is an example:

http://imgur.com/a/vMG75

Under PName, it shows a total count for multiple programs, I wanted to show a distinct value for EACH program, such as flash player had 5 counts and word had 5 counts for this particular machine and particular violation type. Here is my query:

sourcetype="abc" ViolationType="Overwrite Code" | stats values(ViolationType) as "Violation Type", values(PName) as "Program Name", count(PName) by DeviceName| eval  _time = strftime(_time,"%b %d, %Y") | rename  _time as "Date" | rename  count as "Violation Types" |rename DeviceName as "Device Name"| sort  0 -Count|
Tags (4)
0 Karma
1 Solution

cmerriman
Super Champion
sourcetype="abc" ViolationType="Overwrite Code" |eventstats count(PName) as ProgramCount by ViolationType DeviceName PName|eval ProgramNameCount=PName+" - "+ProgramCount|stats values(ProgramNameCount) as "Program Name"  count(PName) as TotalProgramCount by DeviceName ViolationType|rename DeviceName as "Device Name" ViolationType as "Violation Type"| sort  0 - TotalProgramCount 

I'm not sure if I completely understand the ask, but this is what I'm thinking you might want. The eventstats will count PName for every ViolationType, DeviceName and PName. I then created a concatenated field to combine the PName with the count, so you know which count goes with which program. Then the stats command values those by DeviceName and ViolationType. Another way to do it, if you don't want the programs in a multivalue is this:

sourcetype="abc" ViolationType="Overwrite Code" |stats count(PName) as ProgramCount by ViolationType DeviceName PName|rename DeviceName as "Device Name" ViolationType as "Violation Type" PName as "Program Name"| sort  0 - ProgramCount 

View solution in original post

cmerriman
Super Champion
sourcetype="abc" ViolationType="Overwrite Code" |eventstats count(PName) as ProgramCount by ViolationType DeviceName PName|eval ProgramNameCount=PName+" - "+ProgramCount|stats values(ProgramNameCount) as "Program Name"  count(PName) as TotalProgramCount by DeviceName ViolationType|rename DeviceName as "Device Name" ViolationType as "Violation Type"| sort  0 - TotalProgramCount 

I'm not sure if I completely understand the ask, but this is what I'm thinking you might want. The eventstats will count PName for every ViolationType, DeviceName and PName. I then created a concatenated field to combine the PName with the count, so you know which count goes with which program. Then the stats command values those by DeviceName and ViolationType. Another way to do it, if you don't want the programs in a multivalue is this:

sourcetype="abc" ViolationType="Overwrite Code" |stats count(PName) as ProgramCount by ViolationType DeviceName PName|rename DeviceName as "Device Name" ViolationType as "Violation Type" PName as "Program Name"| sort  0 - ProgramCount 

cm22486
Path Finder

Knocked it right out of the park, thank you sir! Basically, I just wanted the far right column to show not the total number of violations per machine, but the total per program listed in second column, but your solution shows total per program, and per machine, which is most excellent! Thanks!

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...