Table: vanta_computer - Query Vanta Computer Assets using SQL
Vanta is a security and compliance automation platform. It simplifies the complex process of achieving and maintaining compliance with standards like SOC 2, HIPAA, and GDPR. Vanta's Computer Assets are individual computing devices that are part of your organization's network.
Table Usage Guide
The vanta_computer
table provides insights into computer assets within Vanta's security and compliance automation platform. As a security analyst or compliance officer, explore details about each computer, including its operating system, installed software, and other related information through this table. Utilize it to monitor the security status of each computer, track software installations, and maintain compliance with various standards.
Important Notes
- To query the table you must set
session_id
argument in the config file (~/.steampipe/config/vanta.spc
).
Examples
Basic info
Explore which computers have a specific owner, serial number, and operating system version. This can help in identifying and managing the different machines within your network.
select owner_name, serial_number, agent_version, os_version, hostnamefrom vanta_computer;
select owner_name, serial_number, agent_version, os_version, hostnamefrom vanta_computer;
List computers with unencrypted hard drive
Discover the segments that consist of computers with unencrypted hard drives, allowing you to identify potential security vulnerabilities and take necessary actions to ensure data protection.
select owner_name, serial_number, agent_version, os_version, hostnamefrom vanta_computerwhere not is_encrypted;
select owner_name, serial_number, agent_version, os_version, hostnamefrom vanta_computerwhere is_encrypted = 0;
List computers with no screen lock configured
Discover the segments that include computers lacking screen lock configuration. This could be useful for identifying potential security risks within your network and implementing necessary security measures.
select owner_name, serial_number, agent_version, os_version, hostnamefrom vanta_computerwhere not has_screen_lock;
select owner_name, serial_number, agent_version, os_version, hostnamefrom vanta_computerwhere has_screen_lock = 0;
List computers with no password manager installed
Identify computers that may be vulnerable due to the absence of a password manager. This can help in enhancing system security by pinpointing machines that need password manager installations.
select owner_name, serial_number, agent_version, os_version, hostnamefrom vanta_computerwhere installed_password_managers is null;
select owner_name, serial_number, agent_version, os_version, hostnamefrom vanta_computerwhere installed_password_managers is null;
List computers not checked over the last 90 days
Explore which computers haven't been checked in the last 90 days. This is useful to identify potential risks or issues that might have been overlooked due to lack of regular monitoring.
select owner_name, serial_number, agent_version, os_version, hostname, last_pingfrom vanta_computerwhere last_ping < (current_timestamp - interval '90 days');
select owner_name, serial_number, agent_version, os_version, hostname, last_pingfrom vanta_computerwhere last_ping < datetime('now', '-90 day');
List unmonitored computers
Discover the segments that contain computers that are not being monitored. This is useful in identifying potential gaps in your IT infrastructure, allowing you to address any unsupported operating systems or versions.
select owner_name, serial_number, agent_version, os_version, hostname, case when (unsupported_reasons -> 'unsupportedOsVersion') :: boolean then 'OS version not supported' when (unsupported_reasons -> 'unsupportedOsType') :: boolean then 'OS not supported' end as statusfrom vanta_computerwhere unsupported_reasons is not null;
select owner_name, serial_number, agent_version, os_version, hostname, case when json_extract(unsupported_reasons, '$.unsupportedOsVersion') = 'true' then 'OS version not supported' when json_extract(unsupported_reasons, '$.unsupportedOsType') = 'true' then 'OS not supported' end as statusfrom vanta_computerwhere unsupported_reasons is not null;
List computers with Tailscale app installed
Determine the areas in which computers have the Tailscale app installed. This query is useful for gaining insights into the distribution and usage of this specific application within your network.
select owner_name, serial_number, last_ping, app as applicationfrom vanta_computer, jsonb_array_elements_text(endpoint_applications) as appwhere app like 'Tailscale %';
select owner_name, serial_number, last_ping, app.value as applicationfrom vanta_computer, json_each(endpoint_applications) as appwhere app.value like 'Tailscale %';
List computers with no Slack app installed
Determine the computers that do not have the Slack app installed. This can be useful for IT administrators to identify and rectify gaps in software deployment across the organization.
with device_with_slack_installed as ( select distinct id from vanta_computer, jsonb_array_elements_text(endpoint_applications) as app where app like 'Slack %')select owner_name, serial_number, last_pingfrom vanta_computerwhere endpoint_applications is not null and id not in ( select id from device_with_slack_installed );
with device_with_slack_installed as ( select distinct id from vanta_computer, json_each(endpoint_applications) as app where app.value like 'Slack %')select owner_name, serial_number, last_pingfrom vanta_computerwhere endpoint_applications is not null and id not in ( select id from device_with_slack_installed );
List computers with an older version of Zoom app (< 5.12)
Determine the areas in which computers are running outdated versions of the Zoom app for potential software updates. This helps in maintaining system security and ensuring all devices are up-to-date with the latest software versions.
select owner_name, serial_number, last_ping, app as applicationfrom vanta_computer, jsonb_array_elements_text(endpoint_applications) as appwhere app like 'zoom.us %' and string_to_array(split_part(app, ' ', 2), '.') :: int [ ] < string_to_array('5.12', '.') :: int [ ];
Error: SQLite does not support string_to_arrayand split functions.
List computers owned by inactive users
Explore which computers are owned by users who are no longer active. This can help in asset management and ensuring resources are effectively allocated.
select u.display_name as owner, c.serial_number, u.end_date, c.last_pingfrom vanta_computer as c join vanta_user as u on c.owner_id = u.id and not u.is_active;
select u.display_name as owner, c.serial_number, u.end_date, c.last_pingfrom vanta_computer as c join vanta_user as u on c.owner_id = u.id and u.is_active = 0;
Schema for vanta_computer
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
agent_version | text | The Vanta agent version. | |
endpoint_applications | jsonb | A list of applications installed on the device. | |
has_screen_lock | boolean | If true, the workstation has a screen lock configured. | |
host_identifier | text | The host identifier of the workstation. | |
hostname | text | The hostname of the workstation. | |
id | text | A unique Vanta generated identifier of the computer. | |
installed_av_programs | jsonb | A list of anti-virus programs installed in the workstation. | |
installed_password_managers | jsonb | A list of password managers installed in the workstation. | |
is_encrypted | boolean | If true, the workstation's hard drive is encrypted. | |
is_password_manager_installed | boolean | If true, a password manager is installed in the workstation. | |
last_ping | timestamp with time zone | The time when the workstation was last scanned by the Vanta agent. | |
num_browser_extensions | bigint | The number of browser extensions installed in the workstation. | |
organization_name | text | The name of the organization. | |
os_version | text | The OS version of the workstation. | |
owner_id | text | A unique identifier of the owner of the workstation. | |
owner_name | text | The name of the workstation owner. | |
serial_number | text | The serial number of the workstation. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
unsupported_reasons | jsonb | Specifies the reason for unmonitored computers. |
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)" -- vanta
You can pass the configuration to the command with the --config
argument:
steampipe_export_vanta --config '<your_config>' vanta_computer