Table: azure_sql_server_vulnerability_assessments

WITH
  protected_servers
    AS (
      SELECT
        s.id AS server_id
      FROM
        azure_sql_servers AS s
        LEFT JOIN azure_sql_server_vulnerability_assessments AS va ON
            s._cq_id = va._cq_parent_id
      WHERE
        (va.properties->'recurringScans'->>'isEnabled')::BOOL IS true
    )
SELECT
  'Vulnerability assessment should be enabled on your SQL servers' AS title,
  i.subscription_id,
  i.id AS instance_id,
  CASE
  WHEN p.server_id IS NULL THEN 'fail'
  ELSE 'pass'
  END
FROM
  azure_sql_servers AS i LEFT JOIN protected_servers AS p ON p.server_id = i.id;

SELECT
  'Ensure that Vulnerability Assessment (VA) is enabled on a SQL server by setting a Storage Account (Automated)'
    AS title,
  s.subscription_id,
  s.id AS server_id,
  CASE
  WHEN (a.properties->>'storageContainerPath') IS NULL
  OR a.properties->>'storageContainerPath' = ''
  THEN 'fail'
  ELSE 'pass'
  END
FROM
  azure_sql_servers AS s
  LEFT JOIN azure_sql_server_vulnerability_assessments AS a ON
      s._cq_id = a._cq_parent_id;

SELECT
  'Ensure that VA setting Periodic Recurring Scans is enabled on a SQL server (Automated)'
    AS title,
  s.subscription_id,
  s.id,
  CASE
  WHEN (a.properties->'recurringScans'->>'isEnabled')::BOOL IS NOT true
  THEN 'fail'
  ELSE 'pass'
  END
FROM
  azure_sql_servers AS s
  LEFT JOIN azure_sql_server_vulnerability_assessments AS a ON
      s._cq_id = a._cq_parent_id;

SELECT
  'Ensure that VA setting "Also send email notifications to admins and subscription owners" is set for a SQL server (Automated)'
    AS title,
  s.subscription_id,
  s.id AS server_id,
  CASE
  WHEN (a.properties->'recurringScans'->>'emailSubscriptionAdmins')::BOOL
  IS NOT true
  THEN 'fail'
  ELSE 'pass'
  END
FROM
  azure_sql_servers AS s
  LEFT JOIN azure_sql_server_vulnerability_assessments AS a ON
      s._cq_id = a._cq_parent_id;

WITH
  vulnerability_emails
    AS (
      SELECT
        id,
        unnest((v.properties->'recurringScans'->>'emails')::STRING[]) AS emails
      FROM
        azure_sql_server_vulnerability_assessments AS v
    ),
  emails_count
    AS (
      SELECT
        id, count(emails) AS emails_number
      FROM
        vulnerability_emails
      GROUP BY
        id
    )
SELECT
  'Ensure that VA setting Send scan reports to is configured for a SQL server (Automated)'
    AS title,
  s.subscription_id,
  s.id AS server_id,
  CASE
  WHEN c.emails_number = 0 OR c.emails_number IS NULL THEN 'fail'
  ELSE 'pass'
  END
FROM
  azure_sql_servers AS s
  LEFT JOIN azure_sql_server_vulnerability_assessments AS sv ON
      s._cq_id = sv._cq_parent_id
  LEFT JOIN emails_count AS c ON sv.id = c.id;