steampipe plugin install azuresteampipe plugin install azure

Table: azure_security_center_sub_assessment - Query Azure Security Center Sub-Assessments using SQL

Azure Security Center is a unified infrastructure security management system that strengthens the security posture of your data centers, and provides advanced threat protection across your hybrid workloads in the cloud - whether they're in Azure or not. It provides security management and threat protection across your hybrid cloud workloads. It allows you to prevent, detect, and respond to threats with increased visibility.

Table Usage Guide

The azure_security_center_sub_assessment table provides insights into the detailed security findings for each resource within Azure Security Center. As a security engineer, you can explore specific security assessment details through this table, including severity, status, and associated metadata. Utilize it to uncover information about security vulnerabilities and the remediation steps for each resource.

Examples

Basic info

Explore the various sub-assessments within Azure's Security Center. This allows you to understand and categorize different security elements by their unique identifiers, names, display names, types, and categories, providing a comprehensive overview of your security landscape.

select
  id,
  name,
  display_name,
  type,
  category
from
  azure_security_center_sub_assessment;

select
  id,
  name,
  display_name,
  type,
  category
from
  azure_security_center_sub_assessment;

List unhealthy sub assessment details

Determine the areas in which security aspects are marked as unhealthy in Azure Security Center. This provides a way to identify instances where security measures may need to be improved or updated.

select
  name,
  type,
  category,
  status
from
  azure_security_center_sub_assessment
where
  status ->> 'Code' = 'Unhealthy';

select
  name,
  type,
  category,
  status
from
  azure_security_center_sub_assessment
where
  json_extract(status, '$.Code') = 'Unhealthy';

List all container registry vulnerabilities with corresponding remedies

Identify potential security vulnerabilities in your container registry and uncover the specific remediation steps to mitigate them. This is crucial for maintaining robust security practices and ensuring system integrity.

select
  container_registry_vulnerability_properties,
  remediation,
  resource_details
from
  azure_security_center_sub_assessment
where
  container_registry_vulnerability_properties ->> 'AssessedResourceType' = 'ContainerRegistryVulnerability';

select
  container_registry_vulnerability_properties,
  remediation,
  resource_details
from
  azure_security_center_sub_assessment
where
  json_extract(
    container_registry_vulnerability_properties,
    '$.AssessedResourceType'
  ) = 'ContainerRegistryVulnerability';

List all server vulnerabilities with corresponding remedies

Determine the areas in which server vulnerabilities exist and discover the corresponding remedies. This is beneficial for maintaining server security and ensuring prompt remediation of any identified vulnerabilities.

select
  server_vulnerability_properties,
  remediation,
  resource_details
from
  azure_security_center_sub_assessment
where
  server_vulnerability_properties ->> 'AssessedResourceType' = 'ServerVulnerabilityAssessment';

select
  server_vulnerability_properties,
  remediation,
  resource_details
from
  azure_security_center_sub_assessment
where
  json_extract(
    server_vulnerability_properties,
    '$.AssessedResourceType'
  ) = 'ServerVulnerabilityAssessment';

List all sql server vulnerabilities with corresponding remedies

Explore vulnerabilities in your SQL server and ascertain appropriate remedies. This query is useful for maintaining security and addressing potential risks in your SQL server environment.

select
  sql_server_vulnerability_properties,
  remediation,
  resource_details
from
  azure_security_center_sub_assessment
where
  sql_server_vulnerability_properties ->> 'AssessedResourceType' = 'SqlServerVulnerability';

select
  sql_server_vulnerability_properties,
  remediation,
  resource_details
from
  azure_security_center_sub_assessment
where
  json_extract(
    sql_server_vulnerability_properties,
    '$.AssessedResourceType'
  ) = 'SqlServerVulnerability';

Control examples

Security Center container image scan should be enabled

Schema for azure_security_center_sub_assessment

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
akas	jsonb	Array of globally unique identifier strings (also known as) for the resource.
assessed_resource_type	text	Details of the sub-assessment.
assessment_name	text	The assessment name.
category	text	Category of the sub-assessment.
cloud_environment	text	The Azure Cloud Environment.
container_registry_vulnerability_properties	jsonb	ContainerRegistryVulnerabilityProperties details of the resource that was assessed.
description	text	Human readable description of the assessment status.
display_name	text	User friendly display name of the sub-assessment.
id	text	The resource id.
impact	text	Description of the impact of this sub-assessment.
name	text	The resource name.
remediation	text	Information on how to remediate this sub-assessment.
resource_details	jsonb	Details of the resource that was assessed.
resource_group	text	The resource group which holds this resource.
server_vulnerability_properties	jsonb	ServerVulnerabilityProperties details of the resource that was assessed.
sql_server_vulnerability_properties	jsonb	SQLServerVulnerabilityProperties details of the resource that was assessed.
status	jsonb	The status of the sub-assessment.
subscription_id	text	The Azure Subscription ID in which the resource is located.
time_generated	text	The date and time the sub-assessment was generated.
title	text	Title of the resource.
type	text	The resource type.

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)" -- azure

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

steampipe_export_azure --config '<your_config>' azure_security_center_sub_assessment