View Other Properties

Contents

View Other Properties

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

true if certificate is a CA (Certificate Authority) otherwise false

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: secp384r1

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

true if certificate is self-signed otherwise false

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:

  • CurrentUser - This type of certificate store is local to a user account on the computer. This certificate store is located in the registry under the HKEY_CURRENT_USER root
  • LocalMachine - This type of certificate store is local to the computer and is global to all users on the computer. This certificate store is located in the registry under the HKEY_LOCAL_MACHINE root
  • CurrentService
  • LocalMachineGroupPolicy
  • CurrentUserGroupPolicy
  • Services
  • Users
  • LocalMachineEnterprise
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:

Active Certificates which are scheduled to expire in the next 90 days.
Kolide SQL
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';
Example Results
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 by employees through Slack or Google Workspace account.

Share this story:

Related Device Properties:

New
SSH Keys
developers, identity, security, trust
New
Windows Update Settings
updates, operating-system, security
New
Windows Pending Updates
updates, operating-system, security
View full list of Kolide's Device Properties
Try Kolide Free
Try Kolide Free