# Discovery Azure SQL

# Introduction

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.

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**

<table id="bkmrk-parameter-descriptio"><thead><tr><th>Parameter</th><th>Description</th></tr></thead><tbody><tr><td>`--conf`</td><td>Path to the configuration file generated by the Discovery task.</td></tr></tbody></table>

**Configuration file (`--conf`)**

```ini
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**

```ini
[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:

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

```

The command returns values similar to these:

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

```

Use them in the Discovery task as follows:

```text
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:

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

```

Example:

```bash
./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:

```text
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:

```text
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**

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

**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.