Description: As devices should only have trusted certificates, this query looks for self signed and expired certs on devices. Quite useful in helping with certificate management and determining if there are malicious certs on machines.
What The Data Shows: Provides visibility into Certificates installed on machines.
SQL:
SELECT case self_signed when "0"
THEN "FALSE" when "1"
THEN "TRUE"
END "Self Signed",datetime(not_valid_after,"unixepoch","localtime")
AS "Cert Expired" FROM certificates WHERE self_signed = 1
OR not_valid_after < (SELECT unix_time FROM time );
