steampipe plugin install grendel-consulting/kolidesteampipe plugin install grendel-consulting/kolide

kolide_admin_user kolide_audit_log kolide_check kolide_deprovisioned_person kolide_device kolide_device_group kolide_device_group_device kolide_device_open_issue kolide_issue kolide_package kolide_person kolide_person_group kolide_person_open_issue kolide_person_registered_device

Table: kolide_device

Lists the devices that have been enrolled in Kolide.

Examples

Basic info

select
  name,
  hardware_model,
  serial
from
  kolide_device;

List all devices that are in a failed or failing auth state

select
  name,
  registered_owner_identifier,
  will_block_at
from
  kolide_device
where
  auth_state != 'Good'
order by
  will_block_at asc;

List all Apple devices not on the latest major version of macOS

select
  name,
  registered_owner_identifier,
  serial
from
  kolide_device
where
  device_type = 'Mac'
  and operating_system not like '%Sonoma%';

List all Apple devices not on the latest minor version of macOS Sonoma

select
  name,
  registered_owner_identifier,
  serial
from
  kolide_device
where
  device_type = 'Mac'
  and operating_system not like '%14.4%'

List all Apple devices considered vintage

Provided as an example; we recommend reviewing the official list of discontinued, vintage and obsolete products.

select
  name,
  registered_owner_identifier,
  serial,
  hardware_model
from
  kolide_device
where
  device_type = 'Mac'
  and (
    hardware_model like '%2015%'
    or hardware_model like '%2016%'
    or hardware_model like '%2017%'
  );

Schema for kolide_device

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
auth_configuration_authentication_mode	text		Who can be authenticated with this device, one of 'only_registered_owner', 'only_registered_owner_or_group_members' or 'anyone'.
auth_configuration_device_id	text		Canonical identifier for this device, empty if it is not registered
auth_configuration_person_groups	jsonb		Description of the groups allowed to authenticate with this device.
auth_state	text		Authorisation status of the device, one of Good, Notified, Will Block or Blocked.
device_type	text	=, ~~	Platform type of the device, one of Mac, Windows, Linux, iOS or Android.
form_factor	text		Form factor of the device, one of Computer, Tablet or Phone.
hardware_model	text		Specific hardware model of the device.
hardware_uuid	text	=, ~~	Hardware UUID for the device.
id	text	=	Canonical identifier for the device.
last_authenticated_at	timestamp with time zone	=, >, <	When the device was last authenticated with Kolide.
name	text	=, ~~	Canonical human name for the device.
note	text	=, ~~	Notes provided by a Kolide administrator (in Markdown format).
operating_system	text		Operating system installed on the device.
product_image_url	text		URL of the device's product image.
registered_at	timestamp with time zone	=, >, <	When the device was registered to its current owner.
registered_owner_identifier	text		Canonical identifier for the registered owner of this device.
serial	text	=, ~~	Hardware serial for the device.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
title	text		Display name for this resource.
will_block_at	timestamp with time zone	=, >, <	If the auth status is 'Will Block', this timestamp describes when the device will be blocked by a failing check.