Filtering non-compliant Machines from forescout logs

[DSIre]

We have events feeding into splunk from ForeScout logs (no forescout APP) and i would like to filter it so that only machines currently non-compliant are shown in a search at the end of each month.

They was a similar question asked
regex-help-in-filtering-machines-that-were-not-com

but their field set up is different, we have a Compliant field which is either Yes or No and a Policy field which states which Policy the host is non compliant with, there are 4 different Policies (Anti-Virus Compliance - SCCM Compliance - DLP Compliance - Safety Compliance) Over the course of a month a host may show as non compliant to multiple Policies (Compliant = no for multiple Policies) and also have some of these remediated over the month (Compliant = Yes for any one or more of the Policies). So in the final report we will have to see only the non-compliant machines listed as below including all the Policies it is still non-compliant too.
eg

IP Machine Non Compliant Policy User
10.10.10.10 HostA Anti-Virus Compliance DSMITH
DLP Compliance

The solution of the previous question applied to our situation

index=forescout (Group = "Windows Machine" OR Group = "Windows Workstations" OR Group = "Windows Laptops") (Policy = "Anti-Virus Compliance" OR Policy = "SCCM Compliance" OR Policy = "DLP Compliance" OR Policy = "Safety Compliance") earliest=-30d@d
| fields Machine Compliant
| eval statusValue = if(match(Compliant, "No"), 0, 1)
| stats sum(statusValue) as statusValue by Machine
| search statusValue = 0
| table Machine

This Gives us the Machine name output if it was last seen as Compliant = No, but it could be non-compliant for multiple Policies so it does not give the list or detials we need.

Also 🙂
We are trying to use a lookup to pull in city, host_serial_nbr, os_name going by the Machine field (nt_host field in the csv).

I had used this in a previous search and it worked fine.

|eval nt_host = Machine | lookup assets.csv nt_host OUTPUT city host_serial_nbr os_name | table IP, nt_host, User, Status, city, host_serial_nbr, os_name

Any help would be great!