SQLServer

This document describes the SQL Server functionality of PandoraFMS discovery.

Introduction

This plugin is designed to monitor SQL Server databases by executing queries that extract information crucial for assessing the performance and status of the databases. This information includes metrics such as the number of connections, query counts, and restart status. These data points will be reflected in PandoraFMS through modules that provide statistical value within an agent representing each database.

It's important to note that this plugin is specifically developed for use with Pandora FMS Discovery. Unlike other plugins, it doesn't generate agents through XML; instead, all the discovered information is returned in the JSON output of the plugin.

Prerrequisites

This plugin establishes remote connections to the databases it monitors, so it's essential to ensure connectivity between the Pandora FMS server and these databases.

Parameters and configuration

Parameters

--conf Path to the configuration file.
--target_databases Path to the configuration file containing database targets.
--target_agents Path to the configuration file containing agent targets.
--custom_queries Path to the configuration file containing custom queries.

The configuration file (--conf)

agents_group_id: The ID of the group where agents will be created.
interval: The monitoring interval for agents in seconds.
user: The connection user.
password: The password for the user.
threads: The number of threads used for agent creation.
modules_prefix: The prefix for module names.
execute_custom_queries: Activate with 1 to enable the use of custom queries.
analyze_connections: Activate with 1 to enable connection monitoring.
engine_uptime: Activate with 1 to enable runtime monitoring.
query_stats: Activate with 1 to enable query statistics monitoring.

 

Example

agents_group_id = 10
interval = 300
user = sa 
password = HHgD85V@
threads = 1
modules_prefix = 
execute_custom_queries = 1
analyze_connections = 1
engine_uptime = 1
query_stats = 1

The file content for "--target_databases" should consist of a list of target databases, with each database separated by commas or lines. The format for each database can be any of the following:

ip
ip:puerto
ip\instancia

Example

172.17.0.4:1433\DEVENV
172.17.0.2:1433\PRODENV

The file content for "--target_agents"

should consist of a list of agent names, with each agent separated by commas or lines. These agent names will be used to associate the information from each target database with the corresponding specified agent name, instead of letting the plugin generate agent names automatically.

The position of each agent name in the list should match the position of the target database in its own list. In other words, the name for the first target database should be the first name in this list, considering that blank lines are ignored. This ensures that the information is correctly associated with the desired agents.

Example

agente1,,agente3
agente4
agente5,agente6,agente7,,agente9

Custom queries (--custom_queries)

 should be specified as modules that you want to monitor. These modules should follow a specific structure, which is as follows:

check_begin: Module opening tag.
name: Module name.
description: Module description.
operation: Type of operation.
datatype: Module data type.
min_warning: Minimum warning threshold configuration.
max_warning: Maximum warning threshold configuration.
str_warning: Warning string configuration.
warning_inverse: Activate inverse interval with 1 for warning threshold.
min_critical: Minimum critical threshold configuration.
max_critical: Maximum critical threshold configuration.
str_critical: Critical string configuration.
critical_inverse: Activate inverse interval with 1 for critical threshold.
module_interval: This interval is calculated as a multiplier of the agent's interval.
target: Custom query.
target_databases: Database agents for which the module will be created.
check_end: Module closing tag.

Example

check_begin
name Select 1 
description Number of invalid objects
operation value
datatype generic_data
min_warning 5
target SELECT 1;
target_databases all
check_end

check_begin
name NumeroConexiones
description Number of connections
operation value
datatype generic_data
min_warning 10
target SELECT COUNT(*) AS NumeroConexiones FROM sys.dm_exec_sessions WHERE is_user_process = 1;
target_databases pandora
check_end

check_begin
name lista_table_size.MB
description table size in MB
operation full
datatype generic_data
target SELECT SUM(reserved_page_count) * 8 / 1024.0 AS TamañoMB FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('lista');
target_databases pandora
check_end

 

 

 

Manual execution

The plugin execution format is as follows:

./pandora_mssql \
--conf < ruta al fichero de configuración > \
--target_databases < ruta al fichero de configuración que contiene las bases de datos objetivo > \
[ --target_agents < ruta al fichero de configuración de agentes > ] \
[ --custom_queries < ruta al fichero de configuración que contiene las consultas personalizas > ]

For example:

./pandora_mssql \
--conf /usr/share/pandora_server/util/plugin/mssql.conf \
--target_databases /usr/share/pandora_server/util/plugin/targets.conf \
--target_agents /usr/share/pandora_server/util/plugin/target_agents.conf \
--custom_queries /usr/share/pandora_server/util/plugin/custom_queries.conf

 

Discovery

This plugin can be integrated with Pandora FMS Discovery. To do this, you should load the ".disco" package, which you can download from the Pandora FMS library.

https://pandorafms.com/library/ 

image-1687944678469.png

Once loaded, you can monitor Microsoft SQL Server environments by creating Discovery tasks from the Management > Discovery > Applications section.

image-1687944760619.png

For each task, the following minimum information will be requested:

image-1684830969832.png

You can also adjust the task configuration to customize the desired monitoring:

image-1684831036543.png

Successfully completed tasks will have an execution summary with the following information:

image-1684831071407.png

Tasks that do not complete successfully will have an execution summary that records the errors encountered.

Agents y modules generated by the plugin

The plugin will create one agent for each target database.

 Each agent will contain the following modules if "engine_uptime" is activated:


restart_detection

It will be 0 if an unexpected restart is detected, and 1 if this is not the case. When a server restarts unexpectedly, there can

be a disruption in database access, and potentially, transactions or data may not be saved correctly, leading to data integrity issues.

If "query_stats" is activated, each agent will contain the following modules:

queries The total number of queries. Monitoring queries is essential for understanding the workload running on the server and assessing the overall system performance. By monitoring the total number of queries, you can identify activity spikes, optimize performance, and detect potential issues such as inefficient or excessive queries.
update The number of UPDATE queries. UPDATE queries are used to modify existing data in the database. Monitoring UPDATE queries is important to assess the frequency and efficiency of data updates. You can identify UPDATE queries that affect a large number of rows or have a significant impact on server performance. This allows you to optimize queries, review table structures, or take measures to reduce the load generated by updates.
delete The number of DELETE queries. DELETE queries are used to remove data from the database. Monitoring DELETE queries is useful to assess the frequency and efficiency of data deletions. You can identify DELETE queries that affect a large number of rows or have a significant impact on server performance. This allows you to optimize queries, review table structures, or take measures to reduce the load generated by deletions.
insert The number of INSERT queries. INSERT queries are used to add new data to the database. Monitoring INSERT queries allows you to assess the frequency and efficiency of data insertions. You can identify INSERT queries that are generating a high load on the server or may be causing performance issues. This enables you to optimize queries, review table structures, or consider strategies like deferred insertion to enhance performance in high-concurrency environments.

If "analyze_connections" is activated, each agent will contain the following modules:



session usage The number of current connections relative to the total maximum connections. Monitoring session usage in SQL Server is important for optimizing performance, identifying locking issues, enhancing security and auditing, and efficiently planning server resources.

The plugin will also create one module for each custom query defined in the configuration file.