steampipe plugin install azure

Table: azure_mssql_managed_instance - Query Azure SQL Managed Instances using SQL

Azure SQL Managed Instance is a fully managed SQL Server Database Engine hosted in Azure cloud. It provides most of the SQL Server's capabilities, allowing you to migrate SQL server workloads to Azure with minimal changes. With built-in intelligence that learns your unique database patterns and adaptive performance tuning based on AI, SQL Managed Instance is a best-in-class database service.

Table Usage Guide

The azure_mssql_managed_instance table provides insights into Azure SQL Managed Instances. As a DBA or a cloud architect, you can explore specific details about these instances, including their settings, configurations, and health status. Use this table to monitor and manage your SQL instances effectively, ensuring optimal performance and resource utilization.

Examples

Basic info

Explore the status and security settings of your managed instances in Azure's SQL service. This query is useful for understanding the licensing and encryption standards used across your instances, helping you maintain compliance and security in your database management.

select
name,
id,
state,
license_type,
minimal_tls_version
from
azure_mssql_managed_instance;
select
name,
id,
state,
license_type,
minimal_tls_version
from
azure_mssql_managed_instance;

List managed instances with public endpoint enabled

Identify instances where Azure's managed SQL servers have their public data endpoint enabled. This helps in assessing the elements within your setup that might be exposed to potential security risks.

select
name,
id,
state,
license_type,
minimal_tls_version
from
azure_mssql_managed_instance
where
public_data_endpoint_enabled;
select
name,
id,
state,
license_type,
minimal_tls_version
from
azure_mssql_managed_instance
where
public_data_endpoint_enabled = 1;

List security alert policies of the managed instances

Explore the security alert policies of managed instances to understand their configurations, such as creation time, disabled alerts, and retention days. This can help in assessing the security measures in place and identifying areas for improvement.

select
name,
id,
policy -> 'creationTime' as policy_creation_time,
jsonb_pretty(policy -> 'disabledAlerts') as policy_disabled_alerts,
policy -> 'emailAccountAdmins' as policy_email_account_admins,
jsonb_pretty(policy -> 'emailAddresses') as policy_email_addresses,
policy ->> 'id' as policy_id,
policy ->> 'name' as policy_name,
policy -> 'retentionDays' as policy_retention_days,
policy ->> 'state' as policy_state,
policy ->> 'storageAccountAccessKey' as policy_storage_account_access_key,
policy ->> 'storageEndpoint' as policy_storage_endpoint,
policy ->> 'type' as policy_type
from
azure_mssql_managed_instance,
jsonb_array_elements(security_alert_policies) as policy;
select
name,
i.id,
json_extract(policy.value, '$.creationTime') as policy_creation_time,
json_extract(policy.value, '$.disabledAlerts') as policy_disabled_alerts,
json_extract(policy.value, '$.emailAccountAdmins') as policy_email_account_admins,
json_extract(policy.value, '$.emailAddresses') as policy_email_addresses,
json_extract(policy.value, '$.id') as policy_id,
json_extract(policy.value, '$.name') as policy_name,
json_extract(policy.value, '$.retentionDays') as policy_retention_days,
json_extract(policy.value, '$.state') as policy_state,
json_extract(policy.value, '$.storageAccountAccessKey') as policy_storage_account_access_key,
json_extract(policy.value, '$.storageEndpoint') as policy_storage_endpoint,
json_extract(policy.value, '$.type') as policy_type
from
azure_mssql_managed_instance as i,
json_each(security_alert_policies) as policy;

Schema for azure_mssql_managed_instance

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
administrator_logintextAdministrator username for the managed instance.
administrator_login_passwordtextAdministrator password for the managed instance.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
cloud_environmenttextThe Azure Cloud Environment.
collationtextCollation of the managed instance.
dns_zonetextThe Dns zone that the managed instance is in.
dns_zone_partnertextThe resource id of another managed instance whose DNS zone this managed instance will share after creation.
encryption_protectorsjsonbThe managed instance encryption protectors.
fully_qualified_domain_nametextThe fully qualified domain name of the managed instance.
idtextContains ID to identify a managed instance uniquely.
identityjsonbThe azure active directory identity of the managed instance.
instance_pool_idtextThe Id of the instance pool this managed server belongs to.
license_typetextThe license type of the managed instance.
maintenance_configuration_idtextSpecifies maintenance configuration id to apply to this managed instance.
managed_instance_create_modetextSpecifies the mode of database creation.
minimal_tls_versiontextMinimal TLS version of the managed instance.
nametext=The friendly name that identifies the managed instance.
proxy_overridetextConnection type used for connecting to the instance.
public_data_endpoint_enabledbooleanWhether or not the public data endpoint is enabled.
regiontextThe Azure region/location in which the resource is located.
resource_grouptext=The resource group which holds this resource.
restore_point_in_timetimestamp with time zoneSpecifies the point in time of the source database that will be restored to create the new database.
security_alert_policiesjsonbThe security alert policies of the managed instance.
skujsonbManaged instance SKU.
source_managed_instance_idtextThe resource identifier of the source managed instance associated with create operation of this instance.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statetextThe state of the managed instance.
storage_size_in_gbbigintThe managed instance storage size in GB.
subnet_idtextSubnet resource ID for the managed instance.
subscription_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Subscription ID in which the resource is located.
tagsjsonbA map of tags for the resource.
timezone_idtextId of the timezone.
titletextTitle of the resource.
typetextThe resource type of the managed instance.
v_coresbigintThe number of vcores of the managed instance.
vulnerability_assessmentsjsonbThe managed instance vulnerability assessments.

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_mssql_managed_instance