Hi guys. I am trying to run an osquery that is able to inspect for multiple certificate common names; however, I am using the OR conditional statement, as the machines can have one of the other certificate; however, in one machine I am running this, it does not have the first certificate CN=SGIO Test Root CA - G2, and the query fails entirely. What is the best way to build this query, so the conditional statement pass correct by matching either one of the CNs?

SELECT common_name FROM certificates WHERE common_name = 'SGIO Test Root CA - G2' OR 'SGIO Root CA G2' OR 'SGIO ROOT CA' OR 'SGIO Basic Assurance CA2' OR 'SGIO Basic Assurance CA2 G2' OR 'SGIO Basic Assurance CA G2';

You probably want

WHERE common_name IN (...)

☝️

agree

IN

seems cleaner. But

OR

is the full predicate. So

name = foo OR name = bar or name = qux

Got it... I think this will do it. I have to test first, but I'll try in my lab

SELECT common_name FROM certificates WHERE common_name IN (common_name = 'SGIO Test Root CA - G2' OR common_name = 'SGIO Root CA G2' OR name = 'SGIO Root CA' OR common_name = 'SGIO Basic Assurance CA2' OR common_name = 'SGIO Basic Assurance CA2 G2' OR common_name = 'SGIO Basic Assurance CA G2');

No. You want

WHERE common_name IN ('SGIO Test Root CA - G2', 'SGIO Root CA G2')

(consult general sql docs)