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:
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|
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
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
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!