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

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

Votes:

0

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 15, 2024 11:29:03 AM by  Yasodhara Das [Paessler Support]



35 Replies

Accepted Answer

Votes:

1

This article applies as of PRTG 22

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 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 EmotionalState 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
CharlieAngry
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
Charlie3
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 at least one employee 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="OK" value="1">Good</SingleInt>
		<SingleInt state="Warning" value="0">Unknown</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 the 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 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 example: name of the 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 (in example: oid.paessler.employee.emotionalstate) from the list.
  6. Click Create to add the sensor.


Data settings
Click to enlarge.


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:

Sensor overview
Click to enlarge.

More

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

Last change on Sep 18, 2023 9:18:10 AM by  Jacqueline Conforti [Paessler Support]



Votes:

0

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

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



Votes:

0

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

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

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



Votes:

0

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

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

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



Votes:

0

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



Votes:

0

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

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



Votes:

0

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,360) 2 2



Votes:

0

OK, I will wait.

Thanks a lot for your attention.

Created on Aug 31, 2015 7:07:14 PM



Votes:

0

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



Votes:

0

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]



Votes:

0

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

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



Votes:

1

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]

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



Votes:

0

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

Created on Aug 5, 2016 7:53:04 AM



Votes:

0

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

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



Votes:

0

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]

Last change on May 11, 2022 11:19:32 AM by  Florian Weik (1,927) 2 2



Votes:

0

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

Last change on Aug 19, 2016 8:06:35 AM



Votes:

0

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

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



Votes:

0

Exactly :)

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



Votes:

0

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

Created on Jun 14, 2019 8:12:04 AM



Votes:

0

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

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



Votes:

0

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

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

Created on Jun 17, 2019 12:05:12 PM



Votes:

0

Created on Jun 17, 2019 12:08:24 PM



Votes:

0

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

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

Created on Jun 18, 2019 6:09:26 AM



Votes:

0

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



Votes:

0

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

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

Created on Jun 26, 2019 1:49:45 PM



Votes:

0

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]



Votes:

0

Hello.
I would like to create a sensor that make one channel for each value in column 0 (Job) and takes its corresponding value in column 1 (IsRunning).
I cant figure out the right options to choose to have one channel for each row created automatically.
I don't want to create the channel manually as more job are added frequently.
Does anyone know a way to do it?

JobIsRunning
jn11
jn20
jn31
jn40
......

Thank for the help

Created on Feb 9, 2021 5:09:07 PM




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.