How to List Certificates Across All Mac and Windows Devices
Using Kolide, you can easily view and query Certificates across your fleet.
Introduction
A certificate is a digitally signed document which attests the identity of an individual or organization. Using asymmetrical encryption, the validity of a certification can be verified mathematically to ensure that its claims are coming from a legitimate source.
Certificates are not valid forever and expire after a defined period of time. Once a certificate expires it must be replaced, certificates can also be revoked prior to scheduled expiration by the certificate authority.
For more information about the purpose and utilization of certificates by a device, refer to the following helpful links:
What Certificate Data Can Kolide Collect?
Kolide's endpoint agent bundles in osquery to efficiently collect Certificates from Mac and Windows devices in your fleet. Once collected, Kolide will parse, clean up, and centrally store this data in Inventory for your team to view, query, or export via API.
Kolide meticulously documents every piece of data returned so you can understand the results.
Certificates Schema
| Column | Type | Description | |
|---|---|---|---|
| id | Primary Key |
Unique identifier for the object |
|
| device_id | Foreign Key |
Device associated with the entry |
|
| device_name | Text |
Display name of the device associated with the entry |
|
| authority_key_id | Text |
Commonly referred to as the AKID. Used to distinguish one public key from another when a given Certification Authority (CA) has multiple signing keys |
|
| common_name | Text |
Certificate CommonName |
|
| is_authority | Boolean |
|
|
| issuer | Text |
Certificate issuer distinguished name |
|
| key_algorithm | Text |
The algorithm used to generate the certificate's key |
|
| key_strength | Text |
The strength of the certificate's key in bits or elliptical curve name ex: |
|
| key_usage | Text |
The certificate's key usage and extended key usage |
|
| not_valid_after | Timestamp |
The certificates expiration date/time |
|
| not_valid_before | Timestamp |
The earliest date/time the certificate is valid |
|
| path | Text |
Path to Keychain or PEM bundle |
|
| self_signed | Boolean |
|
|
| serial | Text |
Certificate serial number |
|
| sha1 | Text |
SHA1 hash of the raw certificate contents |
|
| sid | Text |
The Security Identifer (SID) of the Windows user associated with the certificate Data only available for: |
|
| signing_algorithm | Text |
The algorithm used to sign the certificate |
|
| store | Text |
Certificate system store Data only available for: |
|
| store_id | Text |
Exists for service/user stores. Contains raw store id provided by WinAPI Data only available for: |
|
| store_location | Enum::Text |
The Security Identifer (SID) of the Windows user associated with the certificate Data only available for: Can be one of the following:
|
|
| subject | Text |
Certificate distinguished name |
|
| subject_key_id | Text |
Commonly referred to as the SKID. Provides a means of identifying certificates that contain a particular Public Key |
|
| username | Text |
Windows username derived from the SID column Data only available for: |
|
| collected_at | Timestamp |
Time the row of data was first collected in the database |
|
| updated_at | Timestamp |
Time the row of data was last changed in the database |
|
What Can You Do With This Information?
Kolide enables you to write your own queries against the data the agent collects. This allows you to build your own reports and API endpoints. For example, you can:
SELECT
not_valid_before,
not_valid_after,
SUBSTRING(common_name,1,64) common_name_truncated,
device_name,
serial,
ROUND(EXTRACT(epoch FROM not_valid_after - CURRENT_TIMESTAMP(0)::TIMESTAMP WITHOUT TIME ZONE)/86400) AS expires_in_days
FROM device_certificates
WHERE not_valid_after > NOW()
AND not_valid_after < NOW() + interval '90 days';| serial | device_name | expires_in_days | not_valid_after | not_valid_before | common_name_truncated |
|---|---|---|---|---|---|
| 0326C4D57B566FD628B8 | georges-macbook | 6 | 2022-06-01T11:05:57.000Z | 2021-06-01T11:00:57.000Z | 4C5FCA7E-03E5-47C0-9FD8-59C3BCE32922 |
| 13B2B493281AF1934BCA9E48405E6195 | razer.local | 49 | 2022-07-14T16:05:35.000Z | 2022-01-12T16:05:35.000Z | razer |
| 762A92893312249B46FA839D7B097BD8 | lenovo-thinkpad | 32 | 2022-06-28T00:00:00.000Z | 2019-06-28T00:00:00.000Z | Microsoft Intune MDM Device CA |
| 1970D3431C0E9BB7426EC9EAC51B778D | dell-xps-090 | 7 | 2022-06-03T00:00:00.000Z | 2021-06-03T00:00:00.000Z | MS-Organization-P2P-Access [2021] |
| 56EB088834C470A74BD07EA1F2037266 | nau-bmp | 46 | 2022-07-12T00:00:00.000Z | 2017-07-12T00:00:00.000Z | Microsoft Intune Root Certification Authority |
Why Should I Collect Certificates?
An IT or Security team may wish to review or audit the installation of certificates on a device for a variety of purposes including but not limited to:
Verifying the desired configuration of a particular software (some VPN's for example rely on the installation of a certificate to connect)
Detecting the presence of certificates which may indicate the presence of malware or potential compromise
End-User Privacy Consideration
Kolide practices Honest Security. We believe that data should be collected from end-user devices transparently and with privacy in mind.
No private key material associated with the certificates is sent to Kolide.
When you use Kolide to list Certificate data from end-user devices, Kolide gives the people using those devices insight into exactly what data is collected, the privacy implications, and who on the IT team can see the data. This all happens in our end-user privacy center which can be accessed directly by employees.
