Table: crowdstrike_host - Query CrowdStrike Hosts using SQL

CrowdStrike is a cybersecurity technology company that offers endpoint protection, threat intelligence, and cyberattack response services. It provides a cloud-native endpoint security platform combines Next-Gen Av, EDR, and managed hunting services into a single solution. With CrowdStrike, organizations can prevent cyberattacks, detect malicious activities, respond to security incidents, and forecast future threats.

Table Usage Guide

The crowdstrike_host table offers insights into the hosts within CrowdStrike's cybersecurity technology. As a cybersecurity analyst, you can delve into host-specific details through this table, including the host's ID, hostname, and status. This table can be utilized to uncover crucial information about hosts, such as their current security posture, potential vulnerabilities, and the overall threat landscape.

Examples

Basic info

Explore which instances have recently been accessed by reviewing the last login timestamp. This can be useful for monitoring activity and identifying potential unauthorized access.

select
  instance_id,
  hostname,
  last_login_timestamp
from
  crowdstrike_host;

select
  instance_id,
  hostname,
  last_login_timestamp
from
  crowdstrike_host;

List hosts which have been inactive for the last 3 months

Uncover the details of hosts that have not been active in the past three months. This is useful for identifying potential security risks or for optimizing resource allocation.

select
  instance_id,
  hostname,
  last_login_timestamp
from
  crowdstrike_host
where
  last_login_timestamp < current_date - interval '3 months';

select
  instance_id,
  hostname,
  last_login_timestamp
from
  crowdstrike_host
where
  last_login_timestamp < date('now', '-3 months');

List hosts which have at least one `prevention` policy applied

Explore which hosts have at least one prevention policy applied to them. This is useful for identifying areas where proactive measures are being taken to prevent potential security threats.

select
  hostname,
  policies
from
  crowdstrike_host,
  jsonb_array_elements(policies) as t
where
  t ->> 'policy_type' = 'prevention';

select
  hostname,
  policies
from
  crowdstrike_host,
  json_each(policies) as t
where
  json_extract(t.value, '$.policy_type') = 'prevention';

List hosts which do not have `firewall` applied

Uncover the details of hosts that lack a firewall application, allowing for enhanced security management and potential risk mitigation.

select
  hostname,
  device_policies
from
  crowdstrike_host
where
  (device_policies -> 'firewall' -> 'applied') :: bool = false;

select
  hostname,
  device_policies
from
  crowdstrike_host
where
  json_extract(
    json_extract(device_policies, '$.firewall'),
    '$.applied'
  ) = 'false';

List hosts which are operating in reduced functionality mode

Identify instances where certain hosts are operating in a reduced functionality mode. This can be useful in assessing the overall performance and efficiency of your network.

select
  hostname,
  device_policies
from
  crowdstrike_host
where
  reduced_functionality_mode = 'yes';

select
  hostname,
  device_policies
from
  crowdstrike_host
where
  reduced_functionality_mode = 'yes';

List hosts which are known to have critical open vulnerabilities

Discover the segments that have known critical vulnerabilities to better manage and mitigate potential security risks. This query is useful in identifying and prioritizing the hosts that require immediate attention, thereby enhancing your system's overall security posture.

select
  vuln.host_info ->> 'hostname' as hostname,
  vuln.cve,
  vuln.status as vuln_status,
  hosts.email,
  hosts.status as host_status
from
  crowdstrike_host hosts
  left join crowdstrike_spotlight_vulnerability as vuln on hosts.hostname = vuln.host_info ->> 'hostname'
where
  vuln.cve ->> 'exprt_rating' = 'CRITICAL'
  and vuln.status = 'open';

select
  json_extract(vuln.host_info, '$.hostname') as hostname,
  vuln.cve,
  vuln.status as vuln_status,
  hosts.email,
  hosts.status as host_status
from
  crowdstrike_host hosts
  left join crowdstrike_spotlight_vulnerability as vuln on hosts.hostname = json_extract(vuln.host_info, '$.hostname')
where
  json_extract(vuln.cve, '$.exprt_rating') = 'CRITICAL'
  and vuln.status = 'open';

Schema for crowdstrike_host

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
agent_load_flags	bigint		Load flags of the installed agent.
agent_local_time	timestamp with time zone		Local time of the installed agent.
agent_version	text		The version of the installed agent.
bios_manufacturer	text		The BIOS manufacturer.
bios_version	text		The version of the BIOS.
build_number	text		The build number.
cid	text		The customer ID.
config_id_base	text		Config ID base.
config_id_build	text		Config ID build.
config_id_platform	text		Config ID platform.
cpu_signature	text		The CPU signature.
detection_suppression_status	text		Detection suppression status.
device_id	text	=	Host device ID.
device_policies	jsonb		The device control policies.
email	text		The email address.
external_ip	inet		The external IP of the host.
first_login_timestamp	timestamp with time zone		Time when the first login to this host was detected.
first_seen	timestamp with time zone		Time when this host was first seen.
host_hidden_status	text		Whether the host is hidden.
hostname	text		The system hostname.
instance_id	text		The instance ID.
last_login_timestamp	timestamp with time zone		Time when the last login to this host was detected.
last_seen	timestamp with time zone		Time when this host was last seen.
local_ip	inet		The local IP address of the host.
mac_address	text		The MAC address of the host.
machine_domain	text		The domain of the host.
major_version	text		Major version.
managed_apps	jsonb		Apps managed by the agent.
meta	jsonb		Metadata information.
minor_version	text		Minor version.
notes	jsonb		Notes (if any).
os_version	text		The version of the operating system.
ou	jsonb		The organizational unit.
platform_id	text		The ID of the platform in Falcon.
platform_name	text		The platform running in the host.
pod_annotations	jsonb		Pod annotations.
pod_host_ipv4	inet		IPv4 address of the pod host.
pod_host_ipv6	inet		IPv6 address of the pod host.
pod_hostname	text		Pod hostname.
pod_id	text		Pod ID.
pod_ipv4	inet		IPv4 address of the pod.
pod_ipv6	inet		IPv6 address of the pod.
pod_labels	jsonb		Pod labels.
pod_name	text		Pod name.
pod_namespace	text		Pod namespace.
pod_service_account_name	text		Pod service account name.
pointer_size	bigint		Pointer size.
policies	jsonb		Device control policies applied to this host.
product_type	text		The type of hardware.
product_type_desc	text		The description of the type of hardware.
provision_status	text		The provisioning status.
reduced_functionality_mode	text		Whether this host is operating with reduced functionality.
release_group	text		The release group of the host.
serial_number	text		The serial number.
service_pack_major	text		Service pack minor version.
service_pack_minor	text		Service pack major version.
service_provider	text		Service provider.
service_provider_account_id	text		Service provider account ID.
site_name	text		Site name.
slow_changing_modified_timestamp	timestamp with time zone		Slow changing modified timestamp.
status	text		Operating ststus.
system_manufacturer	text		The name of the manufacturer.
system_product_name	text		The name of the product.
tags	jsonb		Falcon tags.
title	text		Title of the resource.
zone_group	text		Zone group.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- crowdstrike

You can pass the configuration to the command with the --config argument:

steampipe_export_crowdstrike --config '<your_config>' crowdstrike_host