This article applies as of PRTG 22

Can an SQL sensor set a specific status based on string values retrieved from a database?

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:

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:

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.

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:

This workaround addresses the error encountered when trying to display text within the message field of your MySQL v2 sensor (version 16.3.24.5303+). 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:

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 Column 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

More

• How to set up the SQL v2 sensors in PRTG? Is there a guide?
• How can I monitor error tables in SQL databases?