# general

Mystery Incorporated

08/05/2021, 1:12 AM
Hey all I need some help with a query: Currently if I run
SELECT * FROM windows_security_products
and I have a third party anti-virus installed, it reports as the picture below. What is happening is that it reports as the third-party anti-virus & firewall is on, and microsoft defender is off. Given that I have mixed endpoints that are using defender and some with a third-party AV, how can I make a query that reports if ALL av/firewall are off not if only 1 is off. Kibana seems really limited and I don't think I can do any kind of aggregation so I think I'll have to do it with osquery.


08/05/2021, 2:46 AM
I think you’d want to play with aggregates.
Perhaps MAX it down to a row.

Mystery Incorporated

08/07/2021, 2:22 PM
@seph @zwass thanks for pointing me on the right path. I managed to create:
Copy code
SELECT * FROM windows_security_products WHERE NOT EXISTS (SELECT name FROM windows_security_products WHERE name LIKE "%Bitdefender%") UNION SELECT * FROM windows_security_products WHERE name LIKE "%Bitdefender%"
And this is showing me either Microsoft Defender on machines with no Bitdefender, and only Bitdefender on machines with Bitdefender. Bloody tops, thanks :)