Votes:
Hi,
I successfully setup basic SQL performance using the SQL server sensors available in PRTG. But I have problem in setting up special DB monitors below? Please advise if PRTG can support this?
Databases - Days Since Last Backup
Databases - Unavailable
Databases - Jobs Failed
SQL - Long Running SQL
Clusters - Node Unavailable
Network - SQL Server Packets Error Rate
Monitored Server - Connection Failure
Thanks!
Last change on May 6, 2011 12:59:35 PM by
Votes:
CREATE PROCEDURE [dbo].[spLastBackup]
@DatabaseName nvarchar(50)
AS
BEGIN
SELECT DATEDIFF(d, MAX(b.backup_finish_date), getdate())
FROM sys.sysdatabases s
LEFT OUTER JOIN msdb.dbo.backupset b
ON b.database_name = s.name
WHERE s.Name = @DatabaseName
END
On your PRTG server, add a new MSQL sensor. Fill in the required fields and in the "SQL Expression" field enter
exec dbo.spLastBackup YOUR_DATABASE_NAME
In the channels tab select the value channel and enter Day as unit.
CREATE PROCEDURE [dbo].[spLastBackups]
AS
BEGIN
SELECT
s.name AS Channel
,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value
,1 as IsInt
,'Days' as Unit
FROM sys.sysdatabases s
LEFT OUTER JOIN msdb.dbo.backupset b
ON b.database_name = s.name
GROUP BY s.name
END
On your PRTG server, add the Custom XML sensor SQLspXML to be downloaded from http://prtgtoolsfamily.com/?page=downloads_sensorsxml
Fill in the required parameters in the parameter section of the sensor. This sensor will produce multiple channels, one for every database on your server
Last change on Oct 20, 2014 11:00:41 AM by
16 Replies
Votes:
Hello,
I'm afraid we don't have specific sensors for those. You could only try using the "normal SQL"-sensors, if you can check these items with SQL Queries.
best regards.
Created on May 6, 2011 12:59:20 PM by
Votes:
CREATE PROCEDURE [dbo].[spLastBackup]
@DatabaseName nvarchar(50)
AS
BEGIN
SELECT DATEDIFF(d, MAX(b.backup_finish_date), getdate())
FROM sys.sysdatabases s
LEFT OUTER JOIN msdb.dbo.backupset b
ON b.database_name = s.name
WHERE s.Name = @DatabaseName
END
On your PRTG server, add a new MSQL sensor. Fill in the required fields and in the "SQL Expression" field enter
exec dbo.spLastBackup YOUR_DATABASE_NAME
In the channels tab select the value channel and enter Day as unit.
CREATE PROCEDURE [dbo].[spLastBackups]
AS
BEGIN
SELECT
s.name AS Channel
,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value
,1 as IsInt
,'Days' as Unit
FROM sys.sysdatabases s
LEFT OUTER JOIN msdb.dbo.backupset b
ON b.database_name = s.name
GROUP BY s.name
END
On your PRTG server, add the Custom XML sensor SQLspXML to be downloaded from http://prtgtoolsfamily.com/?page=downloads_sensorsxml
Fill in the required parameters in the parameter section of the sensor. This sensor will produce multiple channels, one for every database on your server
Last change on Oct 20, 2014 11:00:41 AM by
Votes:
Thanks for the note. I did change it.
Created on Oct 20, 2014 9:30:05 AM by
Votes:
Hello,
this sensors is not supported by the Paessler technical support, please contact teh vendor to the sensor directly ([email protected]).
Created on Sep 28, 2016 3:31:58 PM by
Votes:
Hello Cloud_0013,
The way to go is to enable limits for each channel and set up a max warning or max error limit at 1 day. Next you will need to add a notification trigger to the sensor.
How to Set Channel Limits
PRTG Manual: Sensor Notification Triggers Settings
Setting multiple limits in one batch, the Channel Limits tool might be of help.
Sensors |
Multi Channel Sensors |
Tools |
Notifications
Kind regards,
PRTGToolsFamily
Votes:
With a little help from PRTGToolsFamily (thanks, you're a legend) I've modified the SQL stored proc as follows:
SELECT
s.name AS Channel
,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value
,1 as IsInt
,'Days' as Unit
,1 as limitMode
,0 as limitMinWarning
,0 as limitMinError
,1 as limitMaxWarning
,2 as limitMaxError
,'Warning! Backup(s) have not completed for more than 1 consective days' as LimitWarningMsg
,'ERROR! Backup(s) have not completed for more than 2 consective days' as LimitErrorMsg
FROM sys.sysdatabases s
LEFT OUTER JOIN msdb.dbo.backupset b
ON b.database_name = s.name
WHERE s.name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')
GROUP BY s.name
This enables error and warning alarm limits and messages by default on all channels that come through on the results.
The trick is the limit settings are only loaded the first time the sensor scans, so you'd have to delete and re-add the sensor if you we're altering an existing one.
Another tip is that you after you create the Stored Proc, you have to add the user to it (will be in the 'Master' Database if you use the script above) with EXECUTE permissions, even if it's the sa user.
Last change on Jan 14, 2019 5:26:29 AM by
Votes:
To add a few more tips:
Sensors |
Multi Channel Sensors |
Tools |
Notifications
Kind regards,
PRTGToolsFamily
Votes:
Hello
The stored procedure i'm using works fine with SQL studio:
############################################################
USE [master]
GO
/****** Object: StoredProcedure [dbo].[spLastBackups] Script Date: 15.09.2023 10:35:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spLastBackups]
AS
BEGIN
SELECT
s.name AS Channel
,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value
,1 as IsInt
,'Days' as Unit
,1 as limitMode
,0 as limitMinWarning
,0 as limitMinError
,1 as limitMaxWarning
,2 as limitMaxError
,'Warning! Backup(s) have not completed for more than 1 consective days' as LimitWarningMsg
,'ERROR! Backup(s) have not completed for more than 2 consective days' as LimitErrorMsg
FROM sys.sysdatabases s
LEFT OUTER JOIN msdb.dbo.backupset b
ON b.database_name = s.name
WHERE s.name NOT IN ('model', 'tempdb', 'msdb', 'Resource')
GROUP BY s.name
END
###################################################################
I am trying to geet this working, but when i run (h****a\**_prtg is the service user for PRTG)
C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML>SQLspXML.exe -s=s*****1.h***a.**** -d=master -sp=spLastBackups -u=h****a\**_prtg -p=*** 20.4.1.9 Checking .NET Framework version. Minmal required is... 4.7 Installed version is... 4.7.2 Ok Impersonating user h****a\*****_prtg
and it stays blocked here...
Any idea or suggestion?
Thanks a lot for your help =)
Last change on Sep 18, 2023 7:18:18 AM by
Add comment