steampipe plugin install azure

Table: azure_synapse_workspace - Query Azure Synapse Workspaces using SQL

Azure Synapse Workspace is a feature within Microsoft Azure that integrates with big data and data warehouse technology for immediate insights. It offers a unified experience to ingest, prepare, manage, and serve data for immediate business intelligence and machine learning needs. Azure Synapse Workspace is designed to enable collaboration between data professionals and business decision-makers in a secure and compliant manner.

Table Usage Guide

The azure_synapse_workspace table provides insights into Azure Synapse Workspaces within Microsoft Azure. As a data analyst or data scientist, explore workspace-specific details through this table, including managed private endpoints, firewall settings, and associated metadata. Utilize it to uncover information about workspaces, such as those with private endpoint connections, the status of managed private endpoints, and the verification of firewall rules.

Examples

Basic info

Explore the status and type of your Synapse workspaces in Azure to understand their current operation and provisioning state. This can help in managing and optimizing your resources effectively.

select
id,
name,
type,
provisioning_state
from
azure_synapse_workspace;
select
id,
name,
type,
provisioning_state
from
azure_synapse_workspace;

List synapse workspaces with public network access enabled

Identify instances where Synapse workspaces in Azure have public network access enabled. This can be useful for security audits to ensure that sensitive data is not exposed to the public internet.

select
id,
name,
type,
provisioning_state,
public_network_access
from
azure_synapse_workspace
where
public_network_access = 'Enabled';
select
id,
name,
type,
provisioning_state,
public_network_access
from
azure_synapse_workspace
where
public_network_access = 'Enabled';

List synapse workspaces with user assigned identities

Discover the segments that utilize user-assigned identities within Synapse workspaces. This is beneficial for those wanting to understand which workspaces are configured with specific identity types, aiding in security and access management.

select
id,
name,
identity -> 'type' as identity_type
from
azure_synapse_workspace
where
exists (
select
from
unnest(regexp_split_to_array(identity ->> 'type', ',')) elem
where
trim(elem) = 'UserAssigned'
);
select
id,
name,
json_extract(identity, '$.type') as identity_type
from
azure_synapse_workspace
where
instr(json_extract(identity, '$.type'), 'UserAssigned') > 0;

List private endpoint connection details for synapse workspaces

Explore the details of private endpoint connections for Synapse workspaces. This is beneficial for understanding the status, actions required, and provisioning state of these connections, which can aid in managing and troubleshooting your Azure Synapse Workspaces.

select
name as workspace_name,
id as workspace_id,
connections ->> 'id' as connection_id,
connections ->> 'privateEndpointPropertyId' as connection_private_endpoint_property_id,
connections ->> 'privateLinkServiceConnectionStateActionsRequired' as connection_actions_required,
connections ->> 'privateLinkServiceConnectionStateDescription' as connection_description,
connections ->> 'privateLinkServiceConnectionStateStatus' as connection_status,
connections ->> 'provisioningState' as connection_provisioning_state
from
azure_synapse_workspace,
jsonb_array_elements(private_endpoint_connections) as connections;
select
name as workspace_name,
w.id as workspace_id,
json_extract(connections.value, '$.id') as connection_id,
json_extract(connections.value, '$.privateEndpointPropertyId') as connection_private_endpoint_property_id,
json_extract(
connections.value,
'$.privateLinkServiceConnectionStateActionsRequired'
) as connection_actions_required,
json_extract(
connections.value,
'$.privateLinkServiceConnectionStateDescription'
) as connection_description,
json_extract(
connections.value,
'$.privateLinkServiceConnectionStateStatus'
) as connection_status,
json_extract(connections.value, '$.provisioningState') as connection_provisioning_state
from
azure_synapse_workspace as w,
json_each(private_endpoint_connections) as connections;

List encryption details for synapse workspaces

Explore encryption details for Synapse workspaces to understand the status and level of security measures in place. This can be particularly useful for security audits or for ensuring compliance with data protection regulations.

select
name as workspace_name,
id as workspace_id,
encryption -> 'CmkKey' ->> 'keyVaultUrl' as cmk_key_vault_url,
encryption -> 'CmkKey' ->> 'name' as cmk_key_name,
encryption ->> 'CmkStatus' as cmk_status,
encryption -> 'DoubleEncryptionEnabled' as double_encryption_enabled
from
azure_synapse_workspace;
select
name as workspace_name,
id as workspace_id,
json_extract(encryption, '$.CmkKey.keyVaultUrl') as cmk_key_vault_url,
json_extract(encryption, '$.CmkKey.name') as cmk_key_name,
json_extract(encryption, '$.CmkStatus') as cmk_status,
json_extract(encryption, '$.DoubleEncryptionEnabled') as double_encryption_enabled
from
azure_synapse_workspace;

Schema for azure_synapse_workspace

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
adla_resource_idtextThe ADLA resource ID.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
cloud_environmenttextThe Azure Cloud Environment.
connectivity_endpointsjsonbConnectivity endpoints of the resource.
default_data_lake_storagejsonbWorkspace default data lake storage account details.
diagnostic_settingsjsonbA list of active diagnostic settings for the resource.
encryptionjsonbThe encryption details of the workspace.
extra_propertiesjsonbWorkspace level configs and feature flags.
idtextFully qualified resource ID for the resource.
identityjsonbThe identity of the workspace.
managed_resource_group_nametextThe managed resource group of the resource.
managed_virtual_networktextA managed virtual network for the workspace.
managed_virtual_network_settingsjsonbManaged virtual network settings of the workspace.
nametext=The name of the resource.
private_endpoint_connectionsjsonbPrivate endpoint connections to the workspace.
provisioning_statetextThe provisioning state of the resource.
public_network_accesstextPubic network access to workspace.
purview_configurationjsonbPurview configuration of the workspace.
regiontextThe Azure region/location in which the resource is located.
resource_grouptext=The resource group which holds this resource.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
sql_administrator_logintextLogin for workspace SQL active directory administrator.
sql_administrator_login_passwordtextThe SQL administrator login password of the resource.
subscription_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Subscription ID in which the resource is located.
tagsjsonbA map of tags for the resource.
titletextTitle of the resource.
typetextThe type of the resource.
virtual_network_profilejsonbVirtual network profile of the resource.
workspace_managed_sql_server_vulnerability_assessmentsjsonbThe vulnerability assessments details of the workspace.
workspace_repository_configurationjsonbGit integration settings of the workspace.
workspace_uidjsonbThe unique identifier of the workspace.

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_synapse_workspace