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]



34 Replies

Accepted Answer

Votes:

0

Your Vote:

Up

Down

This article applies to PRTG Network Monitor 19 or later

Best Practice for String Search in SQL Databases

The native SQL sensors of PRTG provide several 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 directly monitor strings from a database and only show strings in the sensor message. SQL sensors throw an error message if the SQL query returns a string value that is to be processed. This also makes it impossible to show a sensor status based on strings that a database contains.

Nevertheless, you are still able to monitor string values with the SQL sensors of PRTG:

  • Basically, you have to put the string operation into your SQL query.
  • The SQL expression maps the found string values to integer values.
  • For this purpose, you can use the CASE statement that is likewise applicable to Microsoft SQL, MySQL, Oracle SQL, and PostgreSQL.
  • 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 us 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 us 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 will 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 us say that we want to have these sensor states:

  • Up for the emotional state “Good” (all employees are happy)
  • Warning if there is at least one employee who is “Sad”
  • Down if at least one employee is “Angry”
  • Warning for all other emotions.

With these parameters, the lookup definition looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<ValueLookup id="oid.paessler.employee.emotionalstate"  desiredValue="1" undefinedState="Warning">
	<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 program 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, select the SQL Query File (in our example, employees.sql).
  3. For Data Processing, select the option Process data table.
  4. For Select Channel Value by, select 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. Click Create to add the sensor.



This best practice for a string search in SQL databases gives you an idea of 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 to enlarge.

More

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

Last change on Jul 30, 2019 10:05:27 AM by  Brandy Greger [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]



Votes:

0

Your Vote:

Up

Down

Is there any easy way to minitor Firebird DB's?

Created on Jun 14, 2019 8:12:04 AM by  K0NCTANT1N (60) 1



Votes:

0

Your Vote:

Up

Down

Only via the ADO SQL v2 Sensor and a corresponding connection string.

Created on Jun 14, 2019 11:13:09 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Have VBS code:

Dim dbfolder As String

        db_File = My.Settings.dbpath

        If My.Settings.serverrole = 0 Then
            FBDB= "Database=" & db_File & FB_ext
        Else
            db_loc= "maindb.FDB"
            FBDB = "User=SYSDBA;" +
                "Password=masterkey;" +
                "Database= " & db_loc & ";" +
                "DataSource=" & My.Settings.servername & ";" +
                "Port=" & My.Settings.serverport & ";" +
                "Dialect=3;" +
                "Charset=NONE;" +
                "Role=;" +
                "Connection lifetime=15;" +
                "Pooling=true;" +
                "MinPoolSize=0;" +
                "MaxPoolSize=50;" +
                "Packet Size=8192;" +
                "ServerType=0"
        End If

Could you give us an example of SQL code?

Created on Jun 14, 2019 1:29:03 PM by  K0NCTANT1N (60) 1



Votes:

0

Your Vote:

Up

Down

Unfortunately not :( What exactly are you trying to monitor, e.g. what metrics do you get when you execute that?

Created on Jun 17, 2019 7:49:58 AM by  Stephan Linke [Paessler Support]

Last change on Jun 17, 2019 7:50:06 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

I will have enough of the similarity as a SQL Server sensor (SQL 2016)

Created on Jun 17, 2019 12:05:12 PM by  K0NCTANT1N (60) 1



Votes:

0

Your Vote:

Up

Down

Created on Jun 17, 2019 12:08:24 PM by  K0NCTANT1N (60) 1



Votes:

0

Your Vote:

Up

Down

So you want to have similar metrics, compared to the MSSQL 2016 Sensor(s), for the firebird database? I'm afraid that I'm not experienced with the database, you'll need to check the web for howto's (retrieve health metrics from firebird). This might be helpful: http://www.firebirdfaq.org/faq95/.

The other thread is eight years old, I doubt that the user is still active / has this solution :(

Created on Jun 17, 2019 6:15:03 PM by  Stephan Linke [Paessler Support]

Last change on Jun 17, 2019 6:15:26 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

I'm also not experienced with the database Firebird, so I ask the public :-(

Created on Jun 18, 2019 6:09:26 AM by  K0NCTANT1N (60) 1



Votes:

0

Your Vote:

Up

Down

There are good answers, but they all do not indicate the correct ready-made solution, here is an example https://developer.ibm.com/answers/questions/371686/monitoring-agent-for-firebird-db.html

Created on Jun 18, 2019 6:15:52 AM by  K0NCTANT1N (60) 1



Votes:

0

Your Vote:

Up

Down

You could also check out the firebird subreddit. It doesn't have that many subscribers, but perhaps you're lucky and someone has a query that reads the health metrics.

Created on Jun 18, 2019 10:19:48 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Nobody helped, and I also did not find an answer on the Internet :-(

Created on Jun 26, 2019 1:49:45 PM by  K0NCTANT1N (60) 1



Votes:

0

Your Vote:

Up

Down

Hi K0NCTANT1N,

Thank you for the update.

I'm sorry, we have no experience with firebird DB either. Maybe the this article will help you create the required SQL query that you can use in the ADO SQL v2 Sensor.

Best Regards,
Moritz Heller
Paessler Support Team

Created on Jun 27, 2019 8:47:20 AM by  Moritz Heller [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.