What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

MS SQL Sensor Showing Rows That Should Not Exist

Votes:

0

I am runnng a Microsoft SQL sensor on the PRTG remote probe with a SQL script (see below for the script). When I run the script from Management Studio it works as it should (returns 0-5 affected rows). However, when I try to run the same script with PRTG it has 300+ affected rows. I am creating a temp table which generates the 300+ rows, but then I have a filter to only pick out certain rows (the 0-5 row mentioned earlier).

Here is the script:

-- Create tmp table from sp_who results
USING MASTER
CREATE TABLE #TmpWho
(spid VARCHAR(150), 
Status VARCHAR(150), 
login VARCHAR(150),
hostname VARCHAR(150), 
blk VARCHAR(150), 
dbname VARCHAR(150), 
cmd VARCHAR(150), 
CPUtime VARCHAR(150), 
DiskIO VARCHAR(150), 
LastBatch VARCHAR(150), 
ProgramName VARCHAR(150), 
spid2 VARCHAR(150), 
reqid VARCHAR(150))
INSERT INTO #TmpWho
EXEC sp_who2

-- filter temp table where blk has special data
SELECT * FROM #TmpWho
WHERE blk not like '%.%'

DROP TABLE #TmpWho

Any ideas as to why PTRG is showing the unfiltered rows?

Just in case, here is my results from sensor:

18.03.19 15:16:54: Connections String: Data Source=REDACTED;Initial Catalog=master;User ID=sa;Password=******;Connect Timeout=60
18.03.19 15:16:54: Opening Connection to MSSQL Server 'REDACTED'
18.03.19 15:16:54: Running Command 'USE MASTER
CREATE TABLE #TmpWho
(spid VARCHAR(150), 
Status VARCHAR(150), 
login VARCHAR(150),
hostname VARCHAR(150), 
blk VARCHAR(150), 
dbname VARCHAR(150), 
cmd VARCHAR(150), 
CPUtime VARCHAR(150), 
DiskIO VARCHAR(150), 
LastBatch VARCHAR(150), 
ProgramName VARCHAR(150), 
spid2 VARCHAR(150), 
reqid VARCHAR(150))
INSERT INTO #TmpWho
EXEC sp_who2


SELECT * FROM #TmpWho
WHERE blk not like '%.%'

DROP TABLE #TmpWho'
18.03.19 15:16:54: Command completed with 330 affected Rows
18.03.19 15:16:54: Closing Connection to MSSQL Server 'REDACTED'
18.03.19 15:16:54: Execution Time: return 143.0067 [Double]
18.03.19 15:16:54: Query Execution Time: return 71.0033 [Double]
18.03.19 15:16:54: Affected Rows: return 330 [Int64]

Image of the same script being run from SQL server: https://i.imgur.com/OmkWbYU.jpg

mssql prtg sqlsensor

Created on Mar 18, 2019 7:26:37 PM



1 Reply

Votes:

0

Could you put that query into a stored procedure and execute the same in PRTG instead? That's likely to work, as it puts the execution of rather complex queries like that :)

PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Mar 19, 2019 11:39:15 AM by Stephan Linke [Paessler Support]




Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.