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

[cm22486]

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|

[cmerriman · Accepted solution]

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]

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!