You are here

SQL Server Agentless Monitoring

Requires Opsview 6
check_circle
Opsview Supported

404: Not Found

Microsoft SQL System Agentless Opspack

Microsoft SQL is a relational database management system (RDBMS) which allows you to process transactions, collect business intelligence and analyse data. Microsoft SQL incoporates various data management and analytics tools, and supports technologies such as cloud computing and mobile devices.

What You Can Monitor

The Microsoft SQL System Agentless Opspack allows you to monitor the state of a given database, without having to install the Opsview agent on the target host.

The service checks provided in this Opspack will report metrics and health information about your Microsoft SQL Database system, including physical and virtual memory utilization, and address space statistics.

Service Checks

Service Check Description
Microsoft SQL System Agentless - Kernel Pool Statistics Total amount of the paged and nonpaged kernel pool
Microsoft SQL System Agentless - Memory Utilization Specifies the percentage of committed memory that is in the working set
Microsoft SQL System Agentless - Paging Statistics Total Page File Size, Page Fault Count, Locked Page Allocations Size and Available Page File Size
Microsoft SQL System Agentless - Physical Memory Statistics Total Page File Size, Page Fault Count, Locked Page Allocations Size and Available Page File Size
Microsoft SQL System Agentless - Process Physical Memory Status Low Physical Memory Notification
Microsoft SQL System Agentless - Process Virtual Memory Status Low Virtual Memory Notification
Microsoft SQL System Agentless - Server Listener Checks Microsoft SQL Server listening on TCP port
Microsoft SQL System Agentless - System Cache Size Total amount of system cache memory
Microsoft SQL System Agentless - System Memory State Description Memory state defined from High Memory/Low Memory Resource Notifications
Microsoft SQL System Agentless - Virtual Address Space Statistics Memory state defined from High Memory/Low Memory Resource Notifications

Prerequisites

  • Ensure your Opsview Monitor version is at least 6.0. Check Opsview Release Notes for the latest version of Opsview Monitor.
  • To use this Opspack, your Windows host must first be configured for Powershell Agentless Monitoring.

Setup Windows Host for Monitoring

By default, Windows hosts will not allow remote PowerShell scripts to run, which is required for Opsview Agentless Monitoring plugins to work.

This can be configured manually by the Windows Host administrator, or automatically using our recommended approach by running the ConfigureRemoting.ps1 Powershell script on the Windows Host.

Powershell Agentless Monitoring requires at least version 5.0 of Powershell. Check the Powershell version on your Windows Host by running:

$PSVersionTable.PSVersion

Run the ConfigureRemoting.ps1 script with Administrator privileges using a Powershell terminal. This will configure firewall rules, self-signed SSL certificates and authentication for PowerShell remoting.

Check this has been configured properly by running:

winrm quickconfig

You should get the following output:

WinRM service is already running on this machine.
WinRM is already set up for remote management on this computer.

By default port 5985 must be opened from the Opsview monitoring server to the Windows host you wish to check. WinRM utilises the HTTP/HTTPS protocol and can be configured to use certificates to secure the data in transit.

Ensure the service is listening by running:

For HTTP: netstat -an | findstr 5985

For HTTPS: netstat -an | findstr 5986

When using basic authentication with WinRM, the following commands must also be run on the windows host:

winrm set winrm/config/service/auth '@{Basic="true"}'
winrm set winrm/config/service '@{AllowUnencrypted="true"}'
winrm set winrm/config/client/auth '@{Basic="true"}'

If you receive a 500 error, which is a known issue on Windows Server 2016, you may need to install WinRM-IIS-Ext. You can do so by running the following command:

Add-WindowsFeature winrm-IIS-Ext

Setup and Configuration

To use this Opspack, you need to add the 'Database - Microsoft SQL System Agentless' Opspack to your Opsview Monitor system.

Step 1: Import the Opspack

Download the database-microsoft-system-agentless.opspack file from the Releases section of this repository.

Navigate to Host Template Settings inside Opsview Monitor and select Import Opspack in the top right corner.

Then click Browse and select the database-microsoft-system-agentless.opspack file. Click Upload and then click Import when the file is uploaded.

For more information, refer to Opsview Knowledge Center - Importing Opspacks

Step 2: Add the Host Template

Add the Database - Microsoft SQL System Agentless Host Template to your Opsview Monitor host.

For more information, refer to Opsview Knowledge Center - Adding Host Templates to Hosts.

Step 3: Add and configure variables required for this host

Variable Description
WINRM_TRANSPORT Used for authenticating with the remote host. The Value is the Authentication Transport Type - must be one of: Basic, Certificate, Kerberos or NTLM. NOTE: CredSSP is not supported. Override the Username and Password with the credentials to be used for authentication with the remote host. Scheme can be overridden to make the check use HTTP (defaults to https). Extra arguments can be used to pass extra arguments to check_by_winrm.py
KERBEROS_REALM Only required if authenticating using Kerberos. The Value is the Kerberos realm.

For more information, refer to Opsview Knowledge Center - Adding Variables to Hosts.

Optional:

If you want to monitor an MSSQL server instance other than the default instance, you can add the instance name using the MSSQL_INSTANCE_NAME variable. Add the instance name in the Value field for this variable. Only one instance can be monitored per host - add additional hosts if you want to monitor multiple MSSQL server instances.

Step 4: Apply changes and the system will now be monitored

View Service Checks

v2.2-opsview-6

ODI-3568 v2.2-opsview-6

Set status to stable

v2.1-opsview-6

ODI-2793 v2.1-opsview-6

Update to include changes to Monitoring module

v2.0-opsview-6

ODI-1452 v2.0-opsview-6

Initial release of agentless monitoring Opspacks for Opsview Monitor 6.0