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

PRTG Sensor Reporting: No Valid Datatable found

Votes:

0

Hi PRTG Support,

We have a SQL sensor setup for monitoring one of our scheduled jobs. The sql script returns either '0' or ''1' based on the result of the job and then checks against the lookup setup for these boolean values (0 or 1).

0 = Failure 1 = Success

The script runs fine when I run it on the sql database and returns '1'. However, when I try to run it on SQLv2 (PRTG) it returns "No Valid Datatable found".

I have already read few KBs on the website and it suggested to remove the semicolons (;) from the sql script. I have already done it but no luck with it. The script is as follows:

---
DECLARE @date date=getdate(),@type varchar(50)='Upload',@Typename varchar(50)='Account',@status INT
set @status =0
 IF (SELECT COUNT(*)
 FROM
 (SELECT TS as RunTime FROM [adminlog]
    WHERE
        [Type] = @type
        AND Typename = @Typename
        AND ts > @date
        AND ( Action like 'Job started'
        OR Action  like 'Finished: Uploads finished:%'
        OR Action like 'job Completed')
        ) rows) = 3
        SET @status=1
SELECT @status
---

The sensor id is: 265648 and the result is as follows:
---------------
<prtg>
<error>1</error>
<text>No valid datatable was returned</text>
</prtg>
---------------

Could you please advise as what has to be done to fix it? Thanks.

prtg sensor sql

Created on Aug 9, 2017 3:58:44 AM

Last change on Aug 9, 2017 8:42:05 AM by Torsten Lindner [Paessler Support]



5 Replies

Votes:

0

Hi Leon,

Could you provide the actual query result from a SQL client?

Kind regards,
Stephan Linke, Tech Support Team

Created on Aug 9, 2017 10:27:24 AM by Stephan Linke [Paessler Support]



Votes:

0

Hi Stephan,

The actual query from SQL client returns "1". Please let me know what additional information is required to fix it. Thanks.

Regards, Apurva

Created on Aug 10, 2017 4:46:11 AM



Votes:

0

Hi Apurva,

Could you make the last line look like this: SELECT @status as "Status" Then it should at least work in the sensor. In order to evaluate the result, configure it like this:

Data ProcessingProcess Data Table
Select Channel Value byColumn Number
Sensor Channel #1 NameStatus
Sensor Channel #1 Column Number1
Sensor Channel #1 UnitCount

..and then create it. This cannot be changed for existing sensors, so you may need to recreate it.
You may also need a lookup. This one should do the trick: 

<?xml version="1.0" encoding="UTF-8"?>
  <ValueLookup id="oid.database.scheduled.jobs" desiredValue="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd">
    <Lookups>
      <SingleInt state="Error" value="0">
        Unknown
      </SingleInt>
      <SingleInt state="Ok" value="1">
        Good
      </SingleInt>
    </Lookups>
  </ValueLookup>

Just save it under C:\Program Files (x86)\PRTG Network Monitor\Lookups\custom as oid.db.scheduled.jobs.ovl. Navigate to Setup | System Administration | Administrative Tools and click "Go" where it says "Load Lookups". It will then be available as a lookup within the sensor.

Kind regards,
Stephan Linke, Tech Support Team

Created on Aug 10, 2017 6:04:32 AM by Stephan Linke [Paessler Support]

Last change on Aug 10, 2017 6:04:59 AM by Stephan Linke [Paessler Support]



Votes:

0

Hi Stephan,

The mentioned trick does not work.

It still returns "No valid datatable was returned" in PRTG sensor. I just amended the last line to read as: SELECT @status as "Status" in my sql script. The lookup was already existing.

Just one quick question, if the senor unit is set to: Sensor Channel #1 Unit: Count how will the lookup be used in this case?

Could you please advise quickly and help me fix this issue. This needs to be fixed urgently. Thanks for your support.

Regards, Apurva

Created on Aug 10, 2017 6:31:25 AM



Votes:

0

What actual PRTG version are you using? When you open up C:\Program Files (x86)\PRTG Network Monitor\Sensor System\SQLv2\ and enter the credentials and query there, what results do you get?

Kind regards,
Stephan Linke, Tech Support Team

Created on Aug 10, 2017 8:31:31 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.