New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


How can I monitor strings from an SQL database and show a sensor status depending on it?

Votes:

0

Your Vote:

Up

Down

I want my SQL sensor to show a certain status depending on a string value that it retrieves from a database. Is it possible to search for strings in databases and define a specific sensor status depending on the found database string?

best-practice database microsoftsql mysql oraclesql postgresql prtg sensors sql sql-queries

Created on Feb 12, 2015 4:57:47 PM by  Gerald Schoch [Paessler Support]

Last change on Feb 12, 2015 5:46:15 PM by  Gerald Schoch [Paessler Support]



22 Replies

Accepted Answer

Votes:

0

Your Vote:

Up

Down

This article applies to PRTG Network Monitor 14.4.12 or later

Best Practice for String Search in SQL Databases

PRTG’s native SQL sensors provide manifold options to monitor your SQL databases. As of PRTG version 14.4.12, you can monitor the performance of and retrieve and analyze defined data from your Microsoft SQL, MySQL, Oracle SQL, and PostgreSQL databases with new SQL sensor types.

However, these sensors cannot monitor strings from a database directly and can only show strings in the sensor message. SQL sensors throw an error message if the SQL query returns a string value to be processed. This makes it also impossible to show a sensor status based on strings that a database contains.

Nevertheless, you are still able to monitor string values with PRTG’s SQL sensors:

  • Basically, you have to put the string operation into your SQL query.
  • The SQL expression maps the found string values into integer values.
  • For this purpose, you can use the CASE statement which is applicable to Microsoft SQL, MySQL, Oracle SQL, and PostgreSQL likewise.
  • Create all the channels you need for each possible “key” (database column 0) in the database with the Key value pair option of your SQL sensor, for example. You can also choose one of the other methods, if you want to.
  • A corresponding lookup file can map the integer back to a text and define a corresponding status for the sensor.

Example

Consider the following query that requests the “emotional state” of each “employee” who is recorded with “name” in the database:

SELECT
    [Name],[EmotionalState]
FROM
    [employee];

Let’s assume that this query returns the following table where “Name” is key and “EmotionalState” is value:

NameEmotionalState
AdamGood
BenSad
CharlyAngry
DavidHungry

As we have already seen above, the SQL sensor would now throw an error if you tried to process the retrieved value because the sensor would receive a string for any key. For example, the key “Adam” would return the string value “Good”.

So, let’s rebuild the query and use the CASE statement to map the requested string values to integers:

SELECT
    [Name],
    CASE
        WHEN [EmotionalState] = 'Good' THEN 1
        WHEN [EmotionalState] = 'Sad' THEN 2
        WHEN [EmotionalState] = 'Angry' THEN 3
        ELSE 0
    END as EmotionalState
FROM
    [employee];

With this SQL query, you get the following table:

NameEmotionalState
Adam1
Ben2
Charly3
David0

Because we do not want to see the numbers in our sensor but the original text for each key, we now define a lookup file that maps these numbers back to the respective string. With lookups, we can also define according sensor states for each “emotional state”.

Let’s say, we want these sensor states:

  • Up for the emotional state “Good” (all employees are happy)
  • Warning if there is at least one employee “Sad”
  • Down if at least one is “Angry” (angry employees are as dangerous for our company as angry birds so we want to get alerted!)
  • Warning for all other emotions.

Then the lookup definition looks like this:

<?xml version="1.0" encoding="UTF-8"?>
  <ValueLookup id="oid.paessler.employee.emotionalstate" desiredValue="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd">
    <Lookups>
      <SingleInt state="Warning" value="0">
        Unknown
      </SingleInt>
      <SingleInt state="Ok" value="1">
        Good
      </SingleInt>
      <SingleInt state="Warning" value="2">
        Sad
      </SingleInt>
      <SingleInt state="Error" value="3">
        Angry
      </SingleInt>
    </Lookups>
  </ValueLookup>

Save the lookup file into the \lookups\custom subfolder of your PRTG installation directory and the SQL query file into the respective \custom sensors\sql\ subfolder.

Now you can add the desired SQL sensor to PRTG:

  1. Define the Database Specific settings according to your needs.
  2. In section Data, choose the SQL Query File (in our example, “employees.sql”).
  3. For Data Processing, choose the option Process data table.
  4. For Select Channel Value by, choose the option Key value pair.
  5. For each key (in our example: name of employee) in your database, define a corresponding channel with a Value Lookup unit.
    1. For each defined channel, select the lookup file you created before (here: “oid.paessler.employee.emotionalstate") from the list.
  6. Continue to add the sensor.



Click here to enlarge


This best practice for string search in SQL databases gives you an idea on how to process strings from databases. If you define a suitable lookup file, you will also see the desired status for a queried string in PRTG, just like in this example:

SQL Sensor Overview
Click here to enlarge


See Also

Created on Feb 12, 2015 5:15:02 PM by  Gerald Schoch [Paessler Support]

Last change on Aug 11, 2016 5:26:52 PM by  Gerald Schoch [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Hello,

i tried it without any success in wmi tester.

ERROR 80041017

Can you help me please ? I want to know if a windows firewall is activated or not : (root\Microsoft\HomeNet)

SELECT [Connection],
CASE
WHEN [IsFirewalled] = 'False' THEN 0
WHEN [IsFirewalled] = 'True' THEN 1
ELSE 2
END as IsFirewalled
FROM HNet_ConnectionProperties

Created on Aug 24, 2015 8:44:29 PM by  IBSF (0)

Last change on Aug 4, 2016 11:09:24 AM by  Luciano Lingnau [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Where did you get this query? Not sure it's syntactically valid WQL :( You might want to use the custom script sensors and a PowerShell script that reads the values.The cmdlet

Get-NetFirewallProfile | Select Name,Enabled

...will provide you with the states. Note that it doesn't work on remote hosts unless you create a CIM Session first.

Created on Aug 25, 2015 11:11:29 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Hi,

My default sensor (Oracle Sql v2 - Beta) is OK (Demo Serveruptime.sql).

I create a new sensor with a simple query to check if exist any lock session but the error is always - Erro Oracle ORA-1153*

SELECT
   'BLOCK_USER',count(*)
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL

Can you help me?

Thanks a lot.

Created on Aug 26, 2015 12:14:38 PM by  Julio Carvalho (0)

Last change on Aug 4, 2016 11:08:53 AM by  Luciano Lingnau [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Could you execute the following:

C:\Program Files (x86)\PRTG Network Monitor\Sensor System\SQLv2.exe

...and enter the data accordingly - do you get the same error there?

Created on Aug 26, 2015 12:41:30 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Hi, if SQLv2.exe, the error is ora-12505: TNS:listener does not currently know of sid, but if I execute TNSPING* the connection works.

C:\>tnsping lithetst

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 27-AGO-2
015 16:22:49

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Arquivos de parÔmetros usados:
C:\oracle\app\product\11.2.0\client_1\network\admin\sqlnet.ora


Usado o adaptador TNSNAMES para resolver o apelido
Tentativa de contatar (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = exa1-scan.*******.com.br)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =
 lithetst) (SERVER = DEDICATED)))
OK (20 ms)

Created on Aug 27, 2015 7:24:27 PM by  Julio Carvalho (0)

Last change on Aug 4, 2016 11:08:20 AM by  Luciano Lingnau [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Hi, I forget to talk that my Oracle Server is a Cluster (exadata - ORACLE RAC). Thati is a problem?

Created on Aug 27, 2015 7:50:13 PM by  Julio Carvalho (0)



Votes:

0

Your Vote:

Up

Down

I'll talk to the developer to take a look at the thread :) Bear with us!

Created on Aug 28, 2015 12:25:22 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Its Works. But I need use to different SIDs (2 sensors - LITHE1 and LITHE2). If I use a servicename (LITHE), the connection fail. Its possible use a servicename?

Thanks

Created on Aug 28, 2015 4:34:19 PM by  Julio Carvalho (0)



Votes:

0

Your Vote:

Up

Down

This is not possible now, but starting with version 15.x.19 there will be the possibility to use either the sid or the service name.

Created on Aug 30, 2015 10:38:12 AM by  Johannes Herrmann [Paessler Support] (1,320) 2 2



Votes:

0

Your Vote:

Up

Down

OK, I will wait.

Thanks a lot for your attention.

Created on Aug 31, 2015 7:07:14 PM by  Julio Carvalho (0)



Votes:

0

Your Vote:

Up

Down

We have several sql servers, with more than 100 jobs at each. I had a simple script to get last 5min job failures, returning job's names and some more info. I used to get sms with job's name and first words of failure reason... Now it's seems like imposible to do. Create over 200 lookups? And change them, if job's name changes? Srsly? Consider going back to pasing real strings from SQL to PRTG, please.

Created on Aug 4, 2016 9:09:33 AM by  qurvax (40) 2



Votes:

0

Your Vote:

Up

Down

Hello qurvax,
thank you for your post.

Please check the following:

I advise you to adjust your SQL query and deploy it the following way:

  • The SQL query should return the NUMBER of failures in the last 5 minutes (Within the deployed sensor set limits to set the sensor down or into warning based on this value).
  • The SQL query should ALSO return a single string with the name of the jobs that failed (check the link above for mysql example), match this result to the sensor's message. It should behave exactly as expected.

When the number of failures is greater than the defined limit (0.1 for instance) the sensor will go down, if there are jobs in the sensor's message this will also be included in the notification once the sensor is down.

Please let me know whenever that works out or if you have further questions.


Best Regards,
Luciano Lingnau [Paessler Support]

Created on Aug 4, 2016 11:07:00 AM by  Luciano Lingnau [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Hello. Thanks for your help. Tried to implement, but...

1 # (#) is above the error limit of 0 # in # (Job Ok failed) :)

When there is failed jobs the result set looks like this:

numberJobNameStepNamemessageFailureDate
1JobThatHasToFail(Job outcome)The job failed. The owner (#redacted#) of job JobThatHasToFail does not have server access.2016-08-04 16:29:04.000

And when there is none, it's just one row with single column containing 0. No problems getting sensor to determine when to go red. Problem is it still wont take my message.

  • Use Data Table Value in Sensor Message is enabled
  • Sensor Message Column Number set to 1
  • Sensor Message* is Job {0} failed

Created on Aug 4, 2016 1:57:32 PM by  qurvax (40) 2

Last change on Aug 5, 2016 9:10:02 AM by  Luciano Lingnau [Paessler Support]



Votes:

1

Your Vote:

Up

Down

Hello gurvax
thank you for your reply.

I assume that you're running PRTG Version 16.3.25.xxxx. The guide which I previously linked was created in version 16.3.24. I've checked our issue-tracker and just located a Bug which affect our current release (PRTG Version 16.3.25.xxxx):

BugDescriptionAffectsFixed/Tested
1826SQL v2 Sensors deliver "Ok" instead of actual text from the query as configured16.3.25.xxxx16.3.26.5123


I'm terribly sorry for not having spotted this earlier, your set-up looks absolutely correct, but due to this bug "Ok" is being displayed instead of the actual text. Consider one of the following alternatives:

  1. Wait for the release of PRTG 16.3.26, it should be released in a couple of weeks/months.
  2. Downgrade to PRTG 16.3.24, your PRTG will have kept the installer in the following directory: C:\Program Files (x86)\PRTG Network Monitor\PRTG Installer Archive

Best Regards,
Luciano Lingnau [Paessler Support]

Created on Aug 5, 2016 7:42:32 AM by  Luciano Lingnau [Paessler Support]

Last change on Aug 5, 2016 7:44:39 AM by  Luciano Lingnau [Paessler Support]



Votes:

0

Your Vote:

Up

Down

No problem, I'll wait for updates, then.

Created on Aug 5, 2016 7:53:04 AM by  qurvax (40) 2



Votes:

0

Your Vote:

Up

Down

We monitor dozens of MySQL database servers with PRTG. One of our most basic sensors is a MySQL v1 sensor that reports on the MySQL version using a very simple query: select version(); Typical output would be strings like:

  • 5.6.25-73.0-log
  • 5.6.31-77.0-log
    How can we do this with a MySQL v2 sensor that doesn't support text output? Creating a custom lookup for each possible value is not a good option.

Created on Aug 18, 2016 2:20:00 AM by  Noah (0) 1

Last change on Aug 19, 2016 10:56:28 AM by  Torsten Lindner [Paessler Support]



Votes:

0

Your Vote:

Up

Down

This can be done using the sensor message option and modifying the query slightly: SELECT VERSION() AS Version;

The sensor will then state something like: Installed MySQL version: 5.5.41-log

Please note Luciano's post, as the function is broken in the current branch (16.3.25.xxx).

Created on Aug 18, 2016 8:20:29 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

We're still running version 16.3.24.5303+ The query does display the version number, but the sensor goes to an error state and the full message is: Id 3: "5.6.25-73.0-log" [System.String] value can not be parsed. Please keep in mind that any date or time formats need to use the "EN-US" format.

I'm willing to wait for 16.3.26.x version, but I still have some concerns here.

  1. I don't want my sensor to be in an error state to display the string. Is this just a bug of the v2 sensor in our current PRTG version?
  2. I don't know why the string cannot be parsed. Is this what's sending the sensor into alarm?
  3. Upgrading past 16.3.24.x will fully deprecate our existing sensors. I've read about the migration path but this is a scary option. The part about "Some SQL queries may return other values than before or may not work at all" is not exactly confidence inspiring.
  4. Not upgrading to 16.3.26.x does not allow us to recreate & test our sensors with the fully bug-fixed MySQL v2 sensors.

This seems like a terrible catch-22, caught between a killed-off v1 sensor and a not-ready-for-primetime v2 sensor.

(Note that this is just an example; we have other, more complicated queries where we need text output but a lookup table is not practical, and we have other queries where we do want to set the sensor to an error state based on certain values.)

Created on Aug 19, 2016 8:06:35 AM by  Noah (0) 1

Last change on Aug 19, 2016 8:54:27 AM by  Noah (0) 1



Votes:

0

Your Vote:

Up

Down

Hi Noah,

This will allow you to show strings within the message field of the sensor. The number field is just there so PRTG has a number to evaluate. Please use the following query in the SQL file: SELECT VERSION() as Version, "0" AS number

Which will output:

Versionnumber
5.5.41-log0

In the sensor settings, configure the following:

  1. Data Processing
    Process Data Table
  2. Select Channel Value by
    Column Name
  3. Sensor Channel #1 Name
    number
  4. Sensor Channel #1 Colum Name
    number
  5. Use Data Table Value in Sensor Message
    Enable
  6. Sensor Message Column Name
    Version
  7. Sensor Message
    Installed SQL version: {0}
  8. If Sensor Message changes
    Trigger 'change' notification

As for your migration concerns, the best way for you would probably be to setup a PRTG trial and check if the queries work and if they require changes. Sorry that this is causing those inconveniences for you :/

Created on Aug 19, 2016 8:51:50 AM by  Stephan Linke [Paessler Support]

Last change on Jan 26, 2017 1:49:32 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Oh! Great, thank you for the help and the super-speedy reply.

If I'm understanding this correctly, the important thing here is to return some sort of numeric result and then ignore it, so the string we actually want can come through in another channel?

Created on Aug 19, 2016 9:06:10 AM by  Noah (0) 1



Votes:

0

Your Vote:

Up

Down

Exactly :)

Created on Aug 19, 2016 9:09:03 AM by  Stephan Linke [Paessler Support]



Please log in or register to enter your reply.


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.