SQL Server monitoring plugin

Pandora FMS plugin for SQL Server monitoring retrieves information from selected performance counters.

Changelog
Introduction
Requirements
Compatibility matrix
Software agent modules classification
Monitoring

Changelog

Date	Author	Change	Version
02/08/12	Tomas	First Version	v1r1
21/04/15	Mario	Revision	v1r2
12/05/16	Axel	Document revision	v1r3

Introduction

This document has as main objective the description of the massive monitoring of performance counters in Windows environments with an integrated SQL service.

To extract the information, it uses performance counters of the Windows system, as well as queries through the sqlcmd command (invoke-sqlcmd in powershell):

• Powershell 2.0 Console (installed by default in Windows Server 2008 R2, and Windows 7. Available from Windows XP SP2 forward)
• An “open” interface (Pandora's as extension of the administration section) to specify free SQL queries.
• The system counts with a Pandora Windows agent installed and has the capacity to distribute file collections, so it is possible to distribute the plugin and the configuration file using Policies.
• SQLPS.exe, this is, the SQL Server administration console for Powershell.
• Install invoke-sqlcmd command in Powershell
• With the agent installation is distributed some commands as grep, gawk …, if pandora_agent system path is not included in the system path, these executables will be added into any system path.

It is important to say that the plugin of Performance Counters monitoring could be used to collect information of numerical kind (to do performance management)

Requirements

The requirements in order this monitoring could work properly are the following:

• Pandora FMS agent 3.2.1 version or higher.
• Powershell 2.0 console to execute the plugin. By default is comes installed in Windows Server 2008 R2 and Windows 7 systems, but it should be downloaded for Windows previous versions. Powershell is not compatible with Windows XP SP1 or lower system
versions.
• SQLPS.exe, this is, the SQL Server administration console for Powershell.
• Install invoke-sqlcmd command in Powershell
• It is necessary that the user with which the Pandora FMS agent is executed, that is the user that will execute the plugin, has available the follwing system permissions:
◦ Local administrator.
• The Powershell scripts execution policy should be fixed as RemoteSigned or lower.

Set-ExecutionPolicy RemoteSigned

• The different plugins will get automatically the information about all the counters that we have specified to it in a list in the counters.txt file and it will generate one module for each one in Pandora (Plugin PerfCounter). They also will get information about the status of the critical elements regarding to the SQL (Plugin SQL) server.
• Adequate configuration of the connections and permissions to allow the software agent from which the plugin is executed, to establish connection and to log against the instances to monitor and extract information from their database:
◦ Public server Role with permissions to log in the instances.
◦ Databse db_datareader role in all the instance Databases.

Compatibility matrix

Systems where it has been tested	Windows Server 2008 with SQL Server 2008
Systems where it should work	Same system of higher with requirements installed.

Depending on the language of the system, the format of the counters to monitor could change, so it
would be necessary to adapt the counters.txt file according to those circumstances.

Software agent modules classification

Status verification in SQL Server
The verification of the status of the SQL servers is done through the plugin Pandora_Plugin_SQL.ps1 and could be applied in the SQL specific technology, in a generic way (in the case of local instances) or adding different instance lists.

Generated modules:

– SQL Monitoring
– CPU_Busy – InstanceName, CPU_Timetick – InstanceName
– Idle Time – InstanceName, IO_Busy – InstanceName
– Input Packets Read – InstanceName, Input Packets Sent – InstanceName
– Instance Status – InstanceName, DatabaseName Status – InstanceName
– Packet Errors – InstanceName, Total Disk R/W Errors – InstanceName
– Total Disk Reads – InstanceName, Total Disk Writes – InstanceName
– Total Login Attempts – InstanceName

Verification of Performance Counters

The verification of performance counters is done through the Pandora_Plugin_PerfCounter.ps1 plugin and could be applied in different policies for different technologies, each of them with different counter lists depending on which we want to monitor in each of those technologies.

Next we are going to show the list of counters to monitor in the case of SQL Server:
– SQL Server Monitoring
– \SQLServer:Access Methods\Full Scans/sec
– \SQLServer:Access Methods\Range Scans/sec
– \SQLServer:Access Methods\Probe Scans/sec
– \SQLServer:Access Methods\Scan Point Revalidations/sec
– \SQLServer:Access Methods\Workfiles Created/sec
– \SQLServer:Access Methods\Worktables Created/sec
– \SQLServer:Access Methods\Worktables From Cache Ratio
– \SQLServer:Access Methods\Forwarded Records/sec
– \SQLServer:Access Methods\Skipped Ghosted Records/sec
– \SQLServer:Access Methods\Index Searches/sec
– \SQLServer:Access Methods\FreeSpace Scans/sec
– \SQLServer:Access Methods\FreeSpace Page Fetches/sec
– \SQLServer:Access Methods\Pages Allocated/sec
– \SQLServer:Access Methods\Extents Allocated/sec
– \SQLServer:Access Methods\Mixed page allocations/sec
– \SQLServer:Access Methods\Extent Deallocations/sec
– \SQLServer:Access Methods\Page Deallocations/sec
– \SQLServer:Access Methods\Page Splits/sec
– \SQLServer:Access Methods\Table Lock Escalations/sec
– \SQLServer:Access Methods\Deferred Dropped rowsets
– \SQLServer:Access Methods\Dropped rowset cleanups/sec
– \SQLServer:Access Methods\Dropped rowsets skipped/sec
– \SQLServer:Access Methods\Deferred dropped AUs
– \SQLServer:Access Methods\AU cleanups/sec
– \SQLServer:Access Methods\AU cleanup batches/sec
– \SQLServer:Access Methods\Failed AU cleanup batches/sec
– \SQLServer:Access Methods\Used tree page cookie
– \SQLServer:Access Methods\Failed tree page cookie
– \SQLServer:Access Methods\Used leaf page cookie
– \SQLServer:Access Methods\Failed leaf page cookie
– \SQLServer:Access Methods\LobSS Provider Create Count
– \SQLServer:Access Methods\LobSS Provider Destroy Count
– \SQLServer:Access Methods\LobSS Provider Truncation Count
– \SQLServer:Access Methods\LobHandle Create Count
– \SQLServer:Access Methods\LobHandle Destroy Count
– \SQLServer:Access Methods\By-reference Lob Create Count

\SQLServer:Access Methods\By-reference Lob Use Count
– \SQLServer:Access Methods\Count Push Off Row
– \SQLServer:Access Methods\Count Pull In Row
– \SQLServer:Access Methods\Count Lob Readahead
– \SQLServer:Access Methods\Page compression attempts/sec
– \SQLServer:Access Methods\Pages compressed/sec
– \SQLServer:Backup Device(*)\Device Throughput Bytes/sec
– \SQLServer:Buffer Manager\Buffer cache hit ratio
– \SQLServer:Buffer Manager\Page lookups/sec
– \SQLServer:Buffer Manager\Free list stalls/sec
– \SQLServer:Buffer Manager\Free pages
– \SQLServer:Buffer Manager\Total pages
– \SQLServer:Buffer Manager\Target pages
– \SQLServer:Buffer Manager\Database pages
– \SQLServer:Buffer Manager\Reserved pages
– \SQLServer:Buffer Manager\Stolen pages
– \SQLServer:Buffer Manager\Lazy writes/sec
– \SQLServer:Buffer Manager\Readahead pages/sec
– \SQLServer:Buffer Manager\Page reads/sec
– \SQLServer:Buffer Manager\Page writes/sec
– \SQLServer:Buffer Manager\Checkpoint pages/sec
– \SQLServer:Buffer Manager\AWE lookup maps/sec
– \SQLServer:Buffer Manager\AWE stolen maps/sec
– \SQLServer:Buffer Manager\AWE write maps/sec
– \SQLServer:Buffer Manager\AWE unmap calls/sec
– \SQLServer:Buffer Manager\AWE unmap pages/sec
– \SQLServer:Buffer Manager\Page life expectancy
– \SQLServer:Buffer Partition(*)\Free pages
– \SQLServer:Buffer Partition(*)\Free list requests/sec
– \SQLServer:Buffer Partition(*)\Free list empty/sec
– \SQLServer:CLR\CLR Execution
– \SQLServer:Cursor Manager by Type(*)\Cache Hit Ratio
– \SQLServer:Cursor Manager by Type(*)\Cached Cursor Counts
– \SQLServer:Cursor Manager by Type(*)\Cursor Cache Use Counts/sec
– \SQLServer:Cursor Manager by Type(*)\Cursor Requests/sec
– \SQLServer:Cursor Manager by Type(*)\Active cursors
– \SQLServer:Cursor Manager by Type(*)\Cursor memory usage
– \SQLServer:Cursor Manager by Type(*)\Cursor worktable usage
– \SQLServer:Cursor Manager by Type(*)\Number of active cursor plans
– \SQLServer:Cursor Manager Total\Cursor conversion rate
– \SQLServer:Cursor Manager Total\Async population count
– \SQLServer:Cursor Manager Total\Cursor flushes
– \SQLServer:Database Mirroring(*)\Bytes Sent/sec
– \SQLServer:Database Mirroring(*)\Pages Sent/sec
– \SQLServer:Database Mirroring(*)\Sends/sec
– \SQLServer:Database Mirroring(*)\Transaction Delay
– \SQLServer:Database Mirroring(*)\Redo Queue KB
– \SQLServer:Database Mirroring(*)\Redo Bytes/sec
– \SQLServer:Database Mirroring(*)\Log Send Queue KB
– \SQLServer:Database Mirroring(*)\Bytes Received/sec
– \SQLServer:Database Mirroring(*)\Receives/sec

\SQLServer:Database Mirroring(*)\Log Bytes Received/sec
– \SQLServer:Database Mirroring(*)\Log Bytes Sent/sec
– \SQLServer:Database Mirroring(*)\Send/Receive Ack Time
– \SQLServer:Database Mirroring(*)\Log Compressed Bytes Rcvd/sec
– \SQLServer:Database Mirroring(*)\Log Compressed Bytes Sent/sec
– \SQLServer:Database Mirroring(*)\Mirrored Write Transactions/sec
– \SQLServer:Database Mirroring(*)\Log Scanned for Undo KB
– \SQLServer:Database Mirroring(*)\Log Remaining for Undo KB
– \SQLServer:Database Mirroring(*)\Log Bytes Sent from Cache/sec
– \SQLServer:Database Mirroring(*)\Log Bytes Redone from Cache/sec
– \SQLServer:Database Mirroring(*)\Log Send Flow Control Time (ms)
– \SQLServer:Database Mirroring(*)\Log Harden Time (ms)
– \SQLServer:Databases(*)\Data File(s) Size (KB)
– \SQLServer:Databases(*)\Log File(s) Size (KB)
– \SQLServer:Databases(*)\Log File(s) Used Size (KB)
– \SQLServer:Databases(*)\Percent Log Used
– \SQLServer:Databases(*)\Active Transactions
– \SQLServer:Databases(*)\Transactions/sec
– \SQLServer:Databases(*)\Repl. Pending Xacts
– \SQLServer:Databases(*)\Repl. Trans. Rate
– \SQLServer:Databases(*)\Log Cache Reads/sec
– \SQLServer:Databases(*)\Log Cache Hit Ratio
– \SQLServer:Databases(*)\Bulk Copy Rows/sec
– \SQLServer:Databases(*)\Bulk Copy Throughput/sec
– \SQLServer:Databases(*)\Backup/Restore Throughput/sec
– \SQLServer:Databases(*)\DBCC Logical Scan Bytes/sec
– \SQLServer:Databases(*)\Shrink Data Movement Bytes/sec
– \SQLServer:Databases(*)\Log Flushes/sec
– \SQLServer:Databases(*)\Log Bytes Flushed/sec
– \SQLServer:Databases(*)\Log Flush Waits/sec
– \SQLServer:Databases(*)\Log Flush Wait Time
– \SQLServer:Databases(*)\Log Truncations
– \SQLServer:Databases(*)\Log Growths
– \SQLServer:Databases(*)\Log Shrinks
– \SQLServer:Databases(*)\Tracked transactions/sec
– \SQLServer:Databases(*)\Write Transactions/sec
– \SQLServer:Databases(*)\Commit table entries
– \SQLServer:Exec Statistics(*)\Extended Procedures
– \SQLServer:Exec Statistics(*)\DTC calls
– \SQLServer:Exec Statistics(*)\OLEDB calls
– \SQLServer:Exec Statistics(*)\Distributed Query
– \SQLServer:General Statistics\Active Temp Tables
– \SQLServer:General Statistics\Temp Tables Creation Rate
– \SQLServer:General Statistics\Logins/sec
– \SQLServer:General Statistics\Connection Reset/sec
– \SQLServer:General Statistics\Logouts/sec
– \SQLServer:General Statistics\User Connections
– \SQLServer:General Statistics\Logical Connections
– \SQLServer:General Statistics\Transactions
– \SQLServer:General Statistics\Non-atomic yield rate
– \SQLServer:General Statistics\Mars Deadlocks

\SQLServer:General Statistics\HTTP Authenticated Requests
– \SQLServer:General Statistics\SOAP Empty Requests
– \SQLServer:General Statistics\SOAP SQL Requests
– \SQLServer:General Statistics\SOAP Method Invocations
– \SQLServer:General Statistics\SOAP WSDL Requests
– \SQLServer:General Statistics\SOAP Session Initiate Requests
– \SQLServer:General Statistics\SOAP Session Terminate Requests
– \SQLServer:General Statistics\Processes blocked
– \SQLServer:General Statistics\Temp Tables For Destruction
– \SQLServer:General Statistics\Event Notifications Delayed Drop
– \SQLServer:General Statistics\Trace Event Notification Queue
– \SQLServer:General Statistics\SQL Trace IO Provider Lock Waits
– \SQLServer:General Statistics\Tempdb recovery unit id
– \SQLServer:General Statistics\Tempdb rowset id
– \SQLServer:Latches\Latch Waits/sec
– \SQLServer:Latches\Average Latch Wait Time (ms)
– \SQLServer:Latches\Total Latch Wait Time (ms)
– \SQLServer:Latches\Number of SuperLatches
– \SQLServer:Latches\SuperLatch Promotions/sec
– \SQLServer:Latches\SuperLatch Demotions/sec
– \SQLServer:Locks(*)\Lock Requests/sec
– \SQLServer:Locks(*)\Lock Timeouts/sec
– \SQLServer:Locks(*)\Number of Deadlocks/sec
– \SQLServer:Locks(*)\Lock Waits/sec
– \SQLServer:Locks(*)\Lock Wait Time (ms)
– \SQLServer:Locks(*)\Average Wait Time (ms)
– \SQLServer:Locks(*)\Lock Timeouts (timeout > 0)/sec
– \SQLServer:Memory Manager\Connection Memory (KB)
– \SQLServer:Memory Manager\Granted Workspace Memory (KB)
– \SQLServer:Memory Manager\Lock Memory (KB)
– \SQLServer:Memory Manager\Lock Blocks Allocated
– \SQLServer:Memory Manager\Lock Owner Blocks Allocated
– \SQLServer:Memory Manager\Lock Blocks
– \SQLServer:Memory Manager\Lock Owner Blocks
– \SQLServer:Memory Manager\Maximum Workspace Memory (KB)
– \SQLServer:Memory Manager\Memory Grants Outstanding
– \SQLServer:Memory Manager\Memory Grants Pending
– \SQLServer:Memory Manager\Optimizer Memory (KB)
– \SQLServer:Memory Manager\SQL Cache Memory (KB)
– \SQLServer:Memory Manager\Target Server Memory (KB)
– \SQLServer:Memory Manager\Total Server Memory (KB)
– \SQLServer:Plan Cache(*)\Cache Hit Ratio
– \SQLServer:Plan Cache(*)\Cache Pages
– \SQLServer:Plan Cache(*)\Cache Object Counts
– \SQLServer:Plan Cache(*)\Cache Objects in use
– \SQLServer:Replication Agents(*)\Running
– \SQLServer:Replication Dist.(*)\Dist:Delivery Latency
– \SQLServer:Replication Dist.(*)\Dist:Delivered Cmds/sec
– \SQLServer:Replication Dist.(*)\Dist:Delivered Trans/sec
– \SQLServer:Replication Logreader(*)\Logreader:Delivery Latency
– \SQLServer:Replication Logreader(*)\Logreader:Delivered Cmds/sec

\SQLServer:Replication Logreader(*)\Logreader:Delivered Trans/sec
– \SQLServer:Replication Merge(*)\Uploaded Changes/sec
– \SQLServer:Replication Merge(*)\Downloaded Changes/sec
– \SQLServer:Replication Merge(*)\Conflicts/sec
– \SQLServer:Replication Snapshot(*)\Snapshot:Delivered Cmds/sec
– \SQLServer:Replication Snapshot(*)\Snapshot:Delivered Trans/sec
– \SQLServer:SQL Errors(*)\Errors/sec
– \SQLServer:SQL Statistics\Batch Requests/sec
– \SQLServer:SQL Statistics\Forced Parameterizations/sec
– \SQLServer:SQL Statistics\Auto-Param Attempts/sec
– \SQLServer:SQL Statistics\Failed Auto-Params/sec
– \SQLServer:SQL Statistics\Safe Auto-Params/sec
– \SQLServer:SQL Statistics\Unsafe Auto-Params/sec
– \SQLServer:SQL Statistics\SQL Compilations/sec
– \SQLServer:SQL Statistics\SQL Re-Compilations/sec
– \SQLServer:SQL Statistics\SQL Attention rate
– \SQLServer:SQL Statistics\Guided plan executions/sec
– \SQLServer:SQL Statistics\Misguided plan executions/sec
– \SQLServer:Transactions\Transactions
– \SQLServer:Transactions\Snapshot Transactions
– \SQLServer:Transactions\Update Snapshot Transactions
– \SQLServer:Transactions\NonSnapshot Version Transactions
– \SQLServer:Transactions\Longest Transaction Running Time
– \SQLServer:Transactions\Update conflict ratio
– \SQLServer:Transactions\Free Space in tempdb (KB)
– \SQLServer:Transactions\Version Generation rate (KB/s)
– \SQLServer:Transactions\Version Cleanup rate (KB/s)
– \SQLServer:Transactions\Version Store Size (KB)
– \SQLServer:Transactions\Version Store unit count
– \SQLServer:Transactions\Version Store unit creation
– \SQLServer:Transactions\Version Store unit truncation
– \SQLServer:User Settable(*)\Query
– \SQLServer:Wait Statistics(*)\Lock waits
– \SQLServer:Wait Statistics(*)\Memory grant queue waits
– \SQLServer:Wait Statistics(*)\Thread-safe memory objects waits
– \SQLServer:Wait Statistics(*)\Log write waits
– \SQLServer:Wait Statistics(*)\Log buffer waits
– \SQLServer:Wait Statistics(*)\Network IO waits
– \SQLServer:Wait Statistics(*)\Page IO latch waits
– \SQLServer:Wait Statistics(*)\Page latch waits
– \SQLServer:Wait Statistics(*)\Non-Page latch waits
– \SQLServer:Wait Statistics(*)\Wait for the worker
– \SQLServer:Wait Statistics(*)\Workspace synchronization waits
– \SQLServer:Wait Statistics(*)\Transaction ownership waits

Checking sql querys and executing time

It´s necessary to execute a script (ps1) to obtain information about an instance/database or calculate the query executing time.

It will be indicated the script configuration in monitoring.

Monitoring

Monitoring throught Performance Counters

Considering that we have already installed and configured both Pandora and the system to monitor, we are going to explain how to get information about the SQL server status in general, from the activity of the different services to specific Powershell counters that through different cmdlets will be in charge of checking critical elements of our systems

In this case, we will install both a Pandora Agent and the different plugins of the Powershell agent in that machine.

To do that the Pandora software agent that we have installed in our server to monitor execute that script, we should edit the agent configuration file and do the call to the plugin through the module_plugin configuration token

We are going to edit the Pandora agent configuration file from the Pandora FMS administration console or writing directly pandora_agent.conf. To do this, we have first to activate the remote_config option in the same file to 1.

We should introduce the following at the end of the configuration file, for example:

# Agent Plugins for SQL Monitoring
module_plugin "<ruta-powershell>\powershell.exe" -command C:\'<rutaplugin>\Pandora_Plugin_PerfCounter_vx.y.ps1' -list C:\'<ruta-listado>\counters.txt'
2> counter_plugin.error

** Pandora_Plugin_PerfCounter not included in .zip. Plugin PerfCounter are available in the server with its appropriate .zip

Save the file and restart the Pandora agent.

Please, consider the error re-addressing of the plugin to one error log, specially due to the cmdlets execution timeout when you have to process lot of information in little time.

If for example, we want to generate one module for each one of the machine counters, the cmdlet will have to process an average of 20000 counters by once and so until it hasn't process all the counter list it won't show the information, all the time from which it process the info until it presents it, the Powershell cmdlet will get an error message after another warning that the list
counter hasn't been found.

This is because the Powershell cmdlet understands that if x time has passed since the counter information request and this information has been shown in the output, then, the information that you were looking for couldn't been find, but what really happen is that this information was found, but it hasn't been shown.

To avoid incrementing the log without control and even whit this receive all the errors made when executing the plugin in the last interval (just in case there is a real error), do the re-addressing using the symbol “2>” as it comes specified in the line that you should introduce in the configuration file.

Once we have configured it, we should distribute the necessary files through file collections or copying in the appropriate agent folder.

One of the most powerful characteristics of the plugin in Powershell, is the possibility of specifying instead of create one by one modules for each performance counter, select all the counters that are specified in one list, as the plugin will do a single check and generate automatically one module for all these counters, optimizing at maximum the time to extract the information. Those list should be located in the same folder where the plugin is, with the name counters.txt. Lets see an example of its content:

\SQLServer:Access Methods\Full Scans/sec
\SQLServer:Access Methods\Range Scans/sec
\SQLServer:Access Methods\Probe Scans/sec
\SQLServer:Access Methods\Scan Point Revalidations/sec

As these counters are in the counters.txt file, the plugin will do one module for each one of them. If one counter has several instances, as in the case of (*), the plugin will do one module for each one of the instances of the counter.

SQL Instances Monitoring

In an active/passive cluster architecture with remote instances, accessible form all the SQL Server nodes, it is possible using the SQL Server Powershell administration console, to do checks of the availability of these instances from these nodes, and also to extract information about the performance and the status of the databases stored in those started instances, from the cluster
active nodes.

In the case of the passive nodes, the monitoring is limited to the instance availability checks, in order to show if this node remains passive or changes to active status and meanwhile it won't try to connect with the instances or returns any information about its status.

Additionally, from the Pandora server it is possible to define ICMP checks against the remote instances service Ips in order to guarantee their connectivity.

With this aim, we have developed a Powershell plugin that, showing in a list of instances to monitor, will search in the node list of services, for each instance, if the SQL Server service of that instance is up (in case of the active node) and in that case it will try to connect with the Ip\instancename when collecting information about the DDBB status that are located in that instance.

If it is specified, it will get also the status of all the service of tha node linked with SQL.

The parameters of this plugin are the following:

-select all All local instances and services are checked (not to
be used in clusters)
 -select list Instances from the list are checked
 -select services All local services are checked
 -list Provides a path for a list with instances to check
Usage example: .\Pandora_Plugin_SQL_v1.0.ps1 -select all -list instances.txt 2>
plugin_error.log

In order the plugin works right we should inform that:
– The “select all” mode will only be used in SQL servers whose instances are all locals.
– In the case of instances located in remote servers, it isn't possible to resolve the hostname of the machine where the SQL server remote instance is located, so, to connect with the instance you will have to do it with the combo IP\instancename.

Example to instances.txt:

10.93.6.81\MSSQLSERVER
 10.93.6.85\ESTGCDBP008
 10.93.6.86\ESTGCDBP008
 10.93.6.89\ESTGCDBP008

It´s necessary install and working correctly the command invoke-sqlcmd in Powershell. For the installation and the proper operation follow these steps:
1.- Install SQL Server 2008 R2 Management Objects Download the tool Microsoft Web Platform → Installer http://www.microsoft.com/web/downloads/platform.aspx. Execute and over Product section search SQL Server 2008 R2 Management Objects, select this option and install it
2.- Install Windows PowerShell Extensions for SQL Server --> https://docs.microsoft.com/es-es/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15

To check the execution of the command invoke-sqlcmd, execute these commands previously in a powershell comand line.
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

In a Windows 2012 Server only execute this command.

Import-Module SqlPs

Support: https://blog.sandro-pereira.com/2021/02/03/a-fish-out-of-water-powershell-the-term-invoke-sqlcmd-is-not-recognized-as-the-name-of-a-cmdlet-function-script-file-or-operable-program/

Monitoring SQL Querys ( Time or Query result)
With this document is added a script to monitor sql querys and the execution time of these querys.

Script for SQL query result:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
invoke-sqlcmd -ServerInstance <HOSTNAME>\<INSTANCE_NAME>
-Username Username -Password Password -Query “query” -Database
<DATABASE_NAME>

Once generated the script, called sql_query.ps1, this is the module configuration:

module_begin
module_name Query result
module_type generic_data_string
module_exec powershell.exe <PATH>/sql_query.ps1
module_end

Script SQL query time execution, called sql_time.ps1

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
$var1=echo $( (( ((get-date).Minute * 60) + ((get-date).second)
)*1000) + (get-date).Millisecond )
$command=invoke-sqlcmd -ServerInstance
<HOSTNAME>\<INSTANCE_NAME> -Username User -Password Password
-Query “query” -Database
$var2=echo $( (( ((get-date).Minute * 60) + ((get-date).second)
)*1000) + (get-date).Millisecond )
$var3 = ($var2 - $var1) / 1000
echo $var3

Module Configuration:

module_begin module_name Query result module_type generic_data module_exec powershell.exe /sql_time.ps1 module_end