Table: vanta_policy - Query Vanta Policies using SQL

Vanta is a security and compliance automation platform that simplifies the process of obtaining and maintaining compliance certifications. It automatically collects evidence of a company's security posture, tracks it over time, and streamlines workflows for certification renewals. A Vanta Policy is a set of rules and procedures that define how a company manages and secures its information.

Table Usage Guide

The vanta_policy table provides insights into the policy configurations within Vanta. As a security analyst, explore policy-specific details through this table, including policy names, descriptions, and associated metadata. Utilize it to uncover information about policy configurations, such as policy status, the type of policy, and the verification of policy details.

Important Notes

To query the table you must set session_id argument in the config file (~/.steampipe/config/vanta.spc).

Examples

Basic info

Explore the various policies in your system by analyzing their title, type, status, and creation date. This can help you understand the range and scope of your current policies, as well as identify any gaps or inconsistencies.

select
  title,
  policy_type,
  status,
  created_at,
  standards
from
  vanta_policy;

select
  title,
  policy_type,
  status,
  created_at,
  standards
from
  vanta_policy;

List unapproved policies

Identify policies that are pending approval to ensure timely review and validation for maintaining security compliance. This query is useful in managing organizational security by highlighting areas that need immediate attention.

select
  title,
  policy_type,
  status,
  created_at
from
  vanta_policy
where
  approver is null;

select
  title,
  policy_type,
  status,
  created_at
from
  vanta_policy
where
  approver is null;

List expired policies

Discover the segments that have policies which have expired. This is useful in understanding which areas need immediate attention for policy renewal, ensuring compliance and reducing risk.

select
  title,
  policy_type,
  status,
  created_at,
  approver ->> 'displayName' as approver
from
  vanta_policy
where
  (approved_at + interval '1 year') < current_timestamp;

select
  title,
  policy_type,
  status,
  created_at,
  json_extract(approver, '$.displayName') as approver
from
  vanta_policy
where
  (julianday('now') - julianday(approved_at)) > 365;

List policies expiring in the next 30 days

Determine the policies that are due to expire in the next 30 days. This can be useful for administrators to proactively manage policy renewals and avoid any lapses in coverage.

select
  title,
  policy_type,
  status,
  created_at,
  approver ->> 'displayName' as approver,
  'expires in ' || extract(
    day
    from
      ((approved_at + interval '1 year') - current_timestamp)
  ) || ' day(s)' as status
from
  vanta_policy
where
  current_timestamp < (approved_at + interval '1 year')
  and extract(
    day
    from
      ((approved_at + interval '1 year') - current_timestamp)
  ) <= '30';

select
  title,
  policy_type,
  status,
  created_at,
  json_extract(approver, '$.displayName') as approver,
  'expires in ' || cast(
    julianday(datetime('now')) - julianday(approved_at) as integer
  ) || ' day(s)' as status
from
  vanta_policy
where
  datetime('now') < datetime(approved_at, '+1 year')
  and cast(
    julianday(datetime(approved_at, '+1 year')) - julianday(datetime('now')) as integer
  ) <= 30;

List users who have not accepted a specific policy

Determine the users who have not accepted a specific policy, such as a 'Code of Conduct'. This can be useful for ensuring all team members are in compliance with company policies.

with policy_summary as (
  select
    p.title as policy_name,
    p.status as policy_status,
    p.approved_at,
    p.approver ->> 'displayName' as approver,
    m.failing_resource_entities
  from
    vanta_policy as p
    join vanta_monitor as m on m.test_id = p.employee_acceptance_test_id
  where
    title = 'Code of Conduct'
  order by
    policy_type
)
select
  p.policy_name,
  f ->> 'displayName' as user_name,
  u.email
from
  policy_summary as p,
  jsonb_array_elements(p.failing_resource_entities) as f
  join vanta_user as u on u.display_name = f ->> 'displayName'
where
  f ->> '__typename' = 'User';

with policy_summary as (
  select
    p.title as policy_name,
    p.status as policy_status,
    p.approved_at,
    json_extract(p.approver, '$.displayName') as approver,
    m.failing_resource_entities
  from
    vanta_policy as p
    join vanta_monitor as m on m.test_id = p.employee_acceptance_test_id
  where
    title = 'Code of Conduct'
  order by
    policy_type
)
select
  p.policy_name,
  json_extract(f.value, '$.displayName') as user_name,
  u.email
from
  policy_summary as p,
  json_each(p.failing_resource_entities) as f
  join vanta_user as u on u.display_name = json_extract(f.value, '$.displayName')
where
  json_extract(f.value, '$.__typename') = 'User';

Schema for vanta_policy

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
acceptance_controls	jsonb	Specifies the acceptance controls.
approved_at	timestamp with time zone	The time when the policy was approved.
approver	jsonb	The Vanta user who approved the policy.
created_at	timestamp with time zone	The time when the policy was created.
description	text	A human-readable description of the policy.
employee_acceptance_test_id	text	The test Id of the control that runs against employees policy acceptance.
id	text	A unique identifier of the policy.
num_users	bigint	The number of users assigned with the policy.
num_users_accepted	text	The number of user accepted the policy.
organization_name	text	The name of the organization.
policy_type	text	The type of the policy.
source	text	The source of the policy.
standards	jsonb	A list of policy standards.
status	text	The current status of the policy.
title	text	The title of the policy.
updated_at	timestamp with time zone	The time when the policy was last modified.
uploaded_doc	jsonb	Specifies the docs uploaded for the policy.
uploader	jsonb	The Vanta user that uploaded the document to Vanta.

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_policy