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.

Maybe with

EXISTS

(https://www.sqlitetutorial.net/sqlite-exists/) and

CASE

(https://www.sqlitetutorial.net/sqlite-case/)?

I think you’d want to play with aggregates.

@seph @zwass thanks for pointing me on the right path. I managed to create:

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