Built off the open source project Osquery
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 );
Copyright © 2005-2023 Broadcom. All Rights Reserved. The term “Broadcom” refers to Broadcom Inc. and/or its subsidiaries.