Table: crowdstrike_spotlight_vulnerability - Query CrowdStrike Spotlight Vulnerabilities using SQL

CrowdStrike Spotlight is a vulnerability management module within the CrowdStrike Falcon platform. It provides a comprehensive view of vulnerabilities in your environment and prioritizes them based on risk. It helps organizations to identify, assess, and remediate vulnerabilities in their infrastructure.

Table Usage Guide

The crowdstrike_spotlight_vulnerability table provides insights into vulnerabilities within the CrowdStrike Spotlight module. As a security analyst, you can explore vulnerability-specific details through this table, including severity, status, and associated metadata. Use it to uncover information about vulnerabilities, such as those with high severity scores, their current status, and the resources they are associated with.

Examples

Basic info

Assess the elements within your system to understand the status of potential vulnerabilities and when they were first detected. This can help you prioritize your security efforts by focusing on the most recent or severe vulnerabilities.

select
  created_timestamp,
  host_info -> 'hostname' AS hostname,
  status
from
  crowdstrike_spotlight_vulnerability;

select
  created_timestamp,
  json_extract(host_info, '$.hostname') AS hostname,
  status
from
  crowdstrike_spotlight_vulnerability;

List all known vulnerabilities

Gain insights into the known vulnerabilities within your system, including their status, associated product, and expert ratings, to better understand potential security risks. This query is beneficial for system administrators and security teams to proactively manage and mitigate potential threats.

select
  created_timestamp,
  host_info -> 'hostname' AS hostname,
  status,
  app -> 'product_name_version' AS product_name_version,
  cve -> 'exprt_rating' AS exprt_rating,
  cve -> 'id' AS cve_id,
  cve -> 'description' AS cve_description
from
  crowdstrike_spotlight_vulnerability;

select
  created_timestamp,
  json_extract(host_info, '$.hostname') AS hostname,
  status,
  json_extract(app, '$.product_name_version') AS product_name_version,
  json_extract(cve, '$.exprt_rating') AS exprt_rating,
  json_extract(cve, '$.id') AS cve_id,
  json_extract(cve, '$.description') AS cve_description
from
  crowdstrike_spotlight_vulnerability;

List vulnerabilities created in the last 15 days

Discover the latest vulnerabilities by pinpointing those that have arisen within the last 15 days. This can help you stay updated on potential security threats and respond promptly to mitigate risks.

select
  created_timestamp,
  host_info -> 'hostname' AS hostname,
  status
from
  crowdstrike_spotlight_vulnerability
where
  created_timestamp > now() - interval '15 days';

select
  created_timestamp,
  json_extract(host_info, '$.hostname') AS hostname,
  status
from
  crowdstrike_spotlight_vulnerability
where
  created_timestamp > datetime('now', '-15 days');

List all vulnerabilities with an exprt rating of critical that were open for more than 24 hours

Identify instances where critical vulnerabilities were open for more than a day, providing insights into potential security risks and enabling necessary remediation measures.

select
  created_timestamp,
  host_info -> 'hostname' AS hostname,
  cve,
  status
from
  crowdstrike_spotlight_vulnerability
where
  cve ->> 'exprt_rating' = 'CRITICAL'
  and status = 'closed'
  and created_timestamp - closed_timestamp > interval '24 hours';

select
  created_timestamp,
  json_extract(host_info, '$.hostname') AS hostname,
  cve,
  status
from
  crowdstrike_spotlight_vulnerability
where
  json_extract(cve, '$.exprt_rating') = 'CRITICAL'
  and status = 'closed'
  and strftime('%s', created_timestamp) - strftime('%s', closed_timestamp) > 24 * 60 * 60;

List all open vulnerabilities with an exprt rating of critical

Discover the segments that contain open vulnerabilities rated as critical. This can be beneficial for prioritizing and addressing the most severe security threats in a timely manner.

select
  created_timestamp,
  host_info -> 'hostname' AS hostname,
  cve,
  status
from
  crowdstrike_spotlight_vulnerability
where
  cve ->> 'exprt_rating' = 'CRITICAL'
  and status = 'open';

select
  created_timestamp,
  json_extract(host_info, '$.hostname') AS hostname,
  cve,
  status
from
  crowdstrike_spotlight_vulnerability
where
  json_extract(cve, '$.exprt_rating') = 'CRITICAL'
  and status = 'open';

List all open vulnerabilities with an exprt rating of critical with the email of the host user

This query is used to identify critical vulnerabilities that are currently open within a system, along with the associated host user's email. This can be beneficial for prioritizing and addressing security issues, and for directly contacting the responsible parties.

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

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

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_spotlight_vulnerability