Title
#windows
William Guilherme

William Guilherme

05/20/2020, 8:30 PM
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';
zwass

zwass

05/20/2020, 8:32 PM
You probably want
WHERE common_name IN (...)
f

fritz

05/20/2020, 8:40 PM
s

seph

05/20/2020, 8:40 PM
agree
IN
seems cleaner. But
OR
is the full predicate. So
name = foo OR name = bar or name = qux
William Guilherme

William Guilherme

05/20/2020, 9:02 PM
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');
s

seph

05/20/2020, 9:38 PM
No. You want
WHERE common_name IN ('SGIO Test Root CA - G2', 'SGIO Root CA G2')
(consult general sql docs)