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

All Controls > Security Center > Security Center container image scan should be enabled

Schema for azure_security_center_sub_assessment

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
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.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
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