Discovery Azure SQL

This Azure SQL Discovery plugin for Pandora FMS automates the monitoring of Azure SQL Database resources in an Azure subscription. It discovers SQL servers and databases, retrieves their Azure Monitor metrics, and generates Pandora FMS agents and modules.

Introduction
Prerequisites
Parameters
Create a Service Principal
Requirements
Manual Execution
Discovery
Generated Agents and Modules

Introduction

The plugin can create one Pandora FMS agent for each discovered database, or it can send all generated modules to a single target agent. It also supports filtering by resource group, SQL server, or database name, adding custom agent and module prefixes, tracking previously discovered entities, skipping the master database, and enabling or disabling module groups.

The implemented checks cover availability, database status, performance, storage usage, connections, tempdb usage, XTP storage, and deadlocks.

Prerequisites

The plugin is distributed as a compiled binary inside the .disco package, so Python and extra Python libraries do not need to be installed on the Pandora FMS server.
An Azure subscription with Azure SQL Database resources is required.
Azure credentials must be provided through a Service Principal with read permissions on the subscription or on the resource group to be monitored.
The Service Principal must be able to list SQL servers, list databases, and read Azure Monitor metrics.
A valid Pandora FMS agent group must be selected in the Discovery task. The All group is not valid for this plugin because it cannot be used as the destination group for created agents or modules.

Parameters

Advanced mode

Parameter	Description
`--conf`	Path to the configuration file generated by the Discovery task.

Configuration file (--conf)

tenant_id=Azure tenant identifier.
subscription_id=Azure subscription identifier.
client_id=Service Principal application identifier.
client_secret=Service Principal secret.
api_endpoint=Azure management API endpoint. If left empty, https://management.azure.com is used.
login_endpoint=Azure login endpoint. If left empty, https://login.microsoftonline.com is used.
resource_group=optional resource group filter. If left empty, SQL servers are discovered across the subscription.
server_name=optional SQL server filter. If left empty, all accessible SQL servers are discovered.
database_name=optional database filter. If left empty, all discovered databases are monitored.
agent_per_database=create one Pandora FMS agent for each discovered database.
target_agent=target agent used when one agent per database is not created.
agent_prefix=optional prefix for database agents.
module_prefix=optional prefix for generated modules.
interval=interval for created or updated agents.
group_id=Pandora FMS agent group id where agents will be created.
timeout=HTTP timeout in seconds. Values lower than 30 are raised to 30 seconds.
scan_databases=enable automatic database discovery.
entities_list=temporary file used to store discovered databases.
enable_entities_interval=enable periodic refresh of the entities file.
entities_interval=entities file refresh interval.
skip_master_database=skip the master database.
performance_metrics_enabled=enable performance modules.
storage_metrics_enabled=enable storage modules.
connection_metrics_enabled=enable connection modules.
tempdb_xtp_metrics_enabled=enable tempdb and XTP modules.
deadlock_metrics_enabled=enable deadlock modules.

Example

[CONF]
tenant_id=11111111-1111-1111-1111-111111111111
subscription_id=00000000-0000-0000-0000-000000000000
client_id=22222222-2222-2222-2222-222222222222
client_secret=my_client_secret
api_endpoint=
login_endpoint=
resource_group=rg-production
server_name=sql-production
database_name=
agent_per_database=1
target_agent=Azure SQL
agent_prefix=
module_prefix=
interval=300
group_id=10
timeout=30
scan_databases=1
entities_list=/tmp/tmp_discovery.azure_sql.entities
enable_entities_interval=0
entities_interval=86400
skip_master_database=1
performance_metrics_enabled=1
storage_metrics_enabled=1
connection_metrics_enabled=1
tempdb_xtp_metrics_enabled=1
deadlock_metrics_enabled=1

Create a Service Principal

The following Azure CLI command creates a Service Principal with the Reader role on a subscription:

az ad sp create-for-rbac \
  --name pandora-azure-sql-discovery \
  --role Reader \
  --scopes /subscriptions/<SUBSCRIPTION_ID>

The command returns values similar to these:

{
  "appId": "<CLIENT_ID>",
  "displayName": "pandora-azure-sql-discovery",
  "password": "<CLIENT_SECRET>",
  "tenant": "<TENANT_ID>"
}

Use them in the Discovery task as follows:

tenant       -> Tenant ID
appId        -> Client ID
password     -> Client secret
subscription -> Subscription ID

Requirements

The recommended configuration is to use a Service Principal with read-only permissions.

The plugin needs permission to:

List Microsoft.Sql/servers resources.
List Microsoft.Sql/servers/databases resources.
Read basic database properties such as status, edition, SKU, and maximum size.
Query Azure Monitor metrics for each database.

In most environments, the Reader role on the subscription or on the target resource group is enough. When access is limited to a single resource group, the Resource group field should also be configured in the Discovery task.

Manual Execution

The plugin execution format is:

./pandora_azure_sql --conf <path to configuration file>

Example:

./pandora_azure_sql --conf /etc/pandora/azure_sql.conf

When the plugin runs from Pandora FMS Discovery, the console creates a temporary configuration file and executes the binary with the --conf parameter automatically.

The plugin returns JSON output with a summary and a monitoring_data field. This is the data consumed by the Discovery server to create or update agents and modules.

Discovery

This plugin can be used from Pandora FMS Discovery.

Upload the corresponding .disco package from the Pandora FMS library or from the console plugin management view. After that, Azure SQL monitoring tasks can be created from the Cloud/Application Discovery section.

The Azure credentials step asks for:

Tenant ID: Azure tenant used by the Service Principal.
Subscription ID: Azure subscription where resources will be discovered.
Client ID: Service Principal application identifier.
Client secret: Service Principal secret.
API endpoint: Azure management endpoint. Leave it empty for standard public Azure.
Login endpoint: Azure authentication endpoint. Leave it empty for standard public Azure.

The SQL discovery step controls resource discovery and agent creation:

Resource group: optional filter to limit discovery to one resource group.
SQL server name: optional filter to limit discovery to one SQL server.
Database name: optional filter to monitor one database.
Create agent per database: when enabled, each database creates its own Pandora FMS agent. When disabled, all modules are sent to Target agent.
Target agent: destination agent used only when Create agent per database is disabled.
Agent prefix: optional prefix for agents created per database.
Module prefix: optional prefix for generated module names.
Agent interval: interval assigned to created or updated agents.
Timeout: HTTP timeout for Azure calls. Values below 30 are treated as 30 seconds.
Scan databases: queries Azure to discover databases.
Enable entities file re-scan interval: enables periodic refresh of the discovered entities file.
Re-scan entities file interval: interval used to rebuild the entity cache when re-scan is enabled.
Skip master database: prevents the master database from being monitored.

The Modules step enables or disables module groups:

Performance modules: creates CPU, DTU, physical data read, log write, sessions, and workers percentage modules.
Storage modules: creates data space used, data space used percentage, and allocated data storage modules.
Connection modules: creates successful, failed, and firewall-blocked connection modules.
Tempdb and XTP modules: creates tempdb data size, tempdb log size, tempdb log used percentage, and XTP storage modules.
Deadlock modules: creates the deadlock count module.

Successful executions include a summary similar to:

resources_discovered: number of databases discovered in the current run.
resources_vanished: number of previously stored databases that no longer appear in the current discovery.
modules: total number of generated modules.
errors: number of errors detected during execution.

Generated Agents and Modules

The plugin creates an Azure SQL Connection module for each monitored database. The module value is 1 when the database is available and 0 when a database stored in the entity cache no longer appears in Azure.

When Create agent per database is enabled, the plugin creates one agent for every discovered database. The agent name is built from the optional Agent prefix, followed by Azure SQL, the SQL server name, and the database name.

Example:

Agent prefix:
SQL server name: sql-production
Database name: appdb
Agent name: Azure SQL sql-production/appdb

When Create agent per database is disabled, every module is sent to the configured Target agent. In that mode, the SQL server and database name are added to each module name to avoid name collisions.

Available modules:

Azure SQL Connection: database connection status. Created as generic_proc.
Database online: database operational status. Created as generic_proc.
CPU percent: CPU percentage used by the database.
DTU consumption percent: DTU percentage consumed by the database.
Physical data read percent: physical data read percentage.
Log write percent: log write percentage.
Sessions percent: session usage percentage.
Workers percent: worker usage percentage.
Data space used: used data space in bytes.
Data space used percent: used data space percentage.
Data space allocated: allocated data storage in bytes.
Successful connections: total successful connections.
Failed connections: total failed connections.
Blocked by firewall connections: total connections blocked by firewall.
Tempdb data size: tempdb data size.
Tempdb log size: tempdb log size.
Tempdb log used percent: tempdb log usage percentage.
XTP storage percent: XTP storage usage percentage.
Deadlocks: total number of deadlocks.

Module type mapping

Module	Pandora FMS type	Azure aggregation	Description
Azure SQL Connection	`generic_proc`		Database connection status. Value `1` means available and `0` means vanished from discovery.
Database online	`generic_proc`		Logical database status according to Azure.
CPU percent	`generic_data`	Average	CPU usage percentage.
DTU consumption percent	`generic_data`	Average	DTU consumption percentage.
Physical data read percent	`generic_data`	Average	Physical data read percentage.
Log write percent	`generic_data`	Average	Log write percentage.
Sessions percent	`generic_data`	Average	Session usage percentage.
Workers percent	`generic_data`	Average	Worker usage percentage.
Data space used	`generic_data`	Average	Used data space in bytes.
Data space used percent	`generic_data`	Average	Used data space percentage.
Data space allocated	`generic_data`	Average	Allocated data storage in bytes.
Successful connections	`generic_data`	Total	Successful connections.
Failed connections	`generic_data`	Total	Failed connections.
Blocked by firewall connections	`generic_data`	Total	Connections blocked by firewall.
Tempdb data size	`generic_data`	Average	`tempdb` data size.
Tempdb log size	`generic_data`	Average	`tempdb` log size.
Tempdb log used percent	`generic_data`	Average	`tempdb` log usage percentage.
XTP storage percent	`generic_data`	Average	XTP storage usage percentage.
Deadlocks	`generic_data`	Total	Detected deadlocks.

Discovered entity cache

The plugin uses the entities_list temporary file to store discovered databases. This allows Pandora FMS to detect databases that existed in previous executions but no longer appear in Azure.

When a database is present in the entity file but is missing from the current Azure discovery, the plugin keeps the related agent and sends Azure SQL Connection with value 0. This makes the disappearance visible in Pandora FMS instead of silently dropping the resource from monitoring.

If Enable entities file re-scan interval is enabled, the entity file is refreshed after the selected Re-scan entities file interval expires. If it is disabled, the previously discovered entity list is preserved so vanished databases can still be detected.