steampipe plugin install azuresteampipe plugin install azure

Table: azure_mysql_flexible_server - Query Azure MySQL Flexible Servers using SQL

Azure MySQL Flexible Server is a fully managed database service with built-in high availability and flexible scaling. It allows you to run your MySQL server workloads on Azure and includes features like stop/start, burstable compute, and adjustable storage. This service makes it easy to build cloud-native applications or modernize existing applications using a managed platform.

Table Usage Guide

The azure_mysql_flexible_server table provides insights into Azure MySQL Flexible Servers within Azure Database for MySQL. As a database administrator, you can explore server-specific details through this table, including server configurations, geographical location, and other server-related aspects. Utilize it to uncover information about servers, such as their current state, performance tier, and the associated resource group.

Examples

Basic info

Explore the key details of your Azure MySQL flexible servers such as location, backup retention days, storage IOPS, and public network access. This can help in understanding the configuration and performance of your servers.

select
  name,
  id,
  location,
  backup_retention_days,
  storage_iops,
  public_network_access
from
  azure_mysql_flexible_server;

select
  name,
  id,
  location,
  backup_retention_days,
  storage_iops,
  public_network_access
from
  azure_mysql_flexible_server;

List servers with public network access disabled

Determine the areas in which servers have public network access turned off, enabling you to assess potential security risks and ensure compliance with your organization's policies.

select
  name,
  id,
  public_network_access
from
  azure_mysql_flexible_server
where
  public_network_access = 'Disabled';

select
  name,
  id,
  public_network_access
from
  azure_mysql_flexible_server
where
  public_network_access = 'Disabled';

List servers with storage auto grow disabled

Determine the areas in which servers have the automatic storage growth feature disabled. This can be useful to identify potential risks of running out of storage space unexpectedly.

select
  name,
  id,
  storage_auto_grow
from
  azure_mysql_flexible_server
where
  storage_auto_grow = 'Disabled';

select
  name,
  id,
  storage_auto_grow
from
  azure_mysql_flexible_server
where
  storage_auto_grow = 'Disabled';

List servers with backup retention days greater than 90 days

Determine the areas in which server backup retention exceeds a 90-day period, which could assist in identifying potential resource optimization and cost-saving opportunities.

select
  name,
  id,
  backup_retention_days
from
  azure_mysql_flexible_server
where
  backup_retention_days > 90;

select
  name,
  id,
  backup_retention_days
from
  azure_mysql_flexible_server
where
  backup_retention_days > 90;

List server configuration details

Assess the elements within your Azure MySQL flexible server by understanding the specific server configurations in use. This allows you to identify potential areas for optimization and ensure your server is set up according to your organization's requirements. Note: Flexible Server configurations is the same as Server parameters as shown in Azure MySQL Flexible Server console

select
  name as server_name,
  id as server_id,
  configurations ->> 'Name' as configuration_name,
  configurations -> 'ConfigurationProperties' ->> 'value' as value
from
  azure_mysql_flexible_server,
  jsonb_array_elements(flexible_server_configurations) as configurations;

select
  name as server_name,
  s.id as server_id,
  json_extract(configurations.value, '$.Name') as configuration_name,
  json_extract(
    configurations.value,
    '$.ConfigurationProperties.value'
  ) as value
from
  azure_mysql_flexible_server as s,
  json_each(flexible_server_configurations) as configurations;

Current state of audit_log_enabled parameter for the servers

This query is used to assess the status of the audit log feature on your Azure MySQL flexible servers. It helps in maintaining security and compliance by identifying servers where this feature is not enabled.

select
  name as server_name,
  id as server_id,
  configurations ->> 'Name' as configuration_name,
  configurations -> 'ConfigurationProperties' ->> 'value' as value
from
  azure_mysql_flexible_server,
  jsonb_array_elements(flexible_server_configurations) as configurations
where
  configurations ->> 'Name' = 'audit_log_enabled';

select
  name as server_name,
  s.id as server_id,
  json_extract(configurations.value, '$.Name') as configuration_name,
  json_extract(
    json_extract(configurations.value, '$.ConfigurationProperties'),
    '$.value'
  ) as value
from
  azure_mysql_flexible_server as s,
  json_each(flexible_server_configurations) as configurations
where
  json_extract(configurations.value, '$.Name') = 'audit_log_enabled';

List servers with slow_query_log parameter enabled

Explore which servers have the slow_query_log parameter enabled, allowing you to identify potential performance issues and optimize your database operations. This is particularly useful for monitoring and improving the efficiency of your Azure MySQL flexible servers.

select
  name as server_name,
  id as server_id,
  configurations ->> 'Name' as configuration_name,
  configurations -> 'ConfigurationProperties' ->> 'value' as value
from
  azure_mysql_flexible_server,
  jsonb_array_elements(flexible_server_configurations) as configurations
where
  configurations -> 'ConfigurationProperties' ->> 'value' = 'ON'
  and configurations ->> 'Name' = 'slow_query_log';

select
  name as server_name,
  s.id as server_id,
  json_extract(configurations.value, '$.Name') as configuration_name,
  json_extract(
    json_extract(configurations.value, '$.ConfigurationProperties'),
    '$.value'
  ) as value
from
  azure_mysql_flexible_server as s,
  json_each(flexible_server_configurations) as configurations
where
  json_extract(
    json_extract(configurations.value, '$.ConfigurationProperties'),
    '$.value'
  ) = 'ON'
  and json_extract(configurations.value, '$.Name') = 'slow_query_log';

List servers with log_output parameter set to file

Determine the areas in which servers have their log output parameter set to a file. This is useful for identifying servers that are configured to log activity directly to a file, which could be a requirement for certain security or auditing purposes.

select
  name as server_name,
  id as server_id,
  configurations ->> 'Name' as configuration_name,
  configurations -> 'ConfigurationProperties' ->> 'value' as value
from
  azure_mysql_flexible_server,
  jsonb_array_elements(flexible_server_configurations) as configurations
where
  configurations -> 'ConfigurationProperties' ->> 'value' = 'FILE'
  and configurations ->> 'Name' = 'log_output';

select
  name as server_name,
  s.id as server_id,
  json_extract(configurations.value, '$.Name') as configuration_name,
  json_extract(
    json_extract(configurations.value, '$.ConfigurationProperties'),
    '$.value'
  ) as value
from
  azure_mysql_flexible_server as s,
  json_each(flexible_server_configurations) as configurations
where
  json_extract(
    json_extract(configurations.value, '$.ConfigurationProperties'),
    '$.value'
  ) = 'FILE'
  and json_extract(configurations.value, '$.Name') = 'log_output';

Schema for azure_mysql_flexible_server

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
administrator_login	text		The administrator's login name of a server.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
availability_zone	text		Availability Zone information of the server.
backup_retention_days	bigint		Backup retention days for the server.
cloud_environment	text		The Azure Cloud Environment.
create_mode	text		The mode to create a new server.
earliest_restore_date	timestamp with time zone		Specifies the earliest restore point creation time.
flexible_server_configurations	jsonb		The server configurations(parameters) details of the server.
fully_qualified_domain_name	text		The fully qualified domain name of the server.
geo_redundant_backup	text		Indicates whether Geo-redundant is enabled, or not for server backup.
high_availability	jsonb		High availability related properties of a server.
id	text		Contains ID to identify a server uniquely.
location	text		The server location.
maintenance_window	jsonb		Maintenance window of a server.
name	text	=	The friendly name that identifies the server.
network	jsonb		Network related properties of a server.
public_network_access	text		Whether or not public network access is allowed for this server.
region	text		The Azure region/location in which the resource is located.
replica_capacity	bigint		The maximum number of replicas that a primary server can have.
replication_role	text		The replication role of the server.
resource_group	text	=	The resource group which holds this resource.
restore_point_in_time	timestamp with time zone		Restore point creation time (ISO8601 format), specifying the time to restore from.
sku_name	text		The name of the sku.
sku_tier	text		The tier of the particular SKU.
source_server_resource_id	text		The source MySQL server id.
state	text		The state of the server.
storage_auto_grow	text		Indicates whether storage auto grow is enabled, or not.
storage_iops	bigint		Storage IOPS for a server.
storage_size_gb	bigint		Indicates max storage allowed for a server.
storage_sku	text		The sku name of the server storage.
subscription_id	text		The Azure Subscription ID in which the resource is located.
system_data	jsonb		The system metadata relating to this server.
tags	jsonb		A map of tags for the resource.
title	text		Title of the resource.
type	text		The resource type of the server.
version	text		Specifies the version of the server.

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_mysql_flexible_server