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_open_issue

Lists the unresolved and unexempted issues created when a specific device fails a check; some checks, when they fail, will produce multiple issues, each with a unique primary_key_value.

You will need to provide a valid device_id for all queries to this table.

Examples

Basic info

select
  title,
  detected_at,
  blocks_device_at,
  resolved_at,
  exempted
from
  kolide_device_open_issue
where
  device_id = '1553';

List all device-blocking issues

select
  title,
  detected_at,
  value
from
  kolide_device_open_issue
where
  device_id = '1553'
  and blocks_device_at is not null;

Diagnose specific issues with battery health

Batteries in most modern laptops have a recharging "cycle count", after which the battery is considered to be fully consumed

select
  d.name,
  d.hardware_model,
  d.serial,
  o.value -> 'cycle_count' as battery_cycles,
  o.value -> 'health' as battery_health,
  o.value -> 'max_capacity' as battery_max
from
  kolide_device_open_issue o,
  kolide_device d
where
  o.check_id = '15804'
  and o.device_id = d.id
  and o.device_id = '1553';

Schema for kolide_device_open_issue

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
blocks_device_at	timestamp with time zone	=, >, <	When the device will be blocked from authenticating by this failing issue, or null if the check is not configured to block authentication.
check_id	text	=, ~~	Canonical identifier for the check this issue relates to.
detected_at	timestamp with time zone	=, >, <	When this issue was initially detected.
device_id	text	=	Canonical identifier for the device this issue relates to.
exempted	boolean	=	Whether this issue has been granted an exemption.
id	text		Canonical identifier for this issue.
issue_key	text	=, ~~	Primary key that distinguishes one issue from another in the context of a single check; only applicable for checks that can produce multiple issues.
issue_value	text	=, ~~	Primary identifying value that distinguishes one issue from another in the context of a single check; only applicable for checks that can produce multiple issues.
last_rechecked_at	timestamp with time zone	=, ~~	When this issue was last rechecked.
resolved_at	timestamp with time zone	=, >, <	When this issue was resolved, or null if still open.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
title	text	=, ~~	Descriptive title for this issue.
value	jsonb		Relevant data that describes why the device failed the check.