Using custom SQL queries

Number of records

Set a threshold for this attribute to get a notification if the query returns a high number of records (rows) that exceed the threshold. By default, this attribute does not generate an alarm or warning event. For more information about this attribute, see the Number of records attribute page.

For example, you can use this attribute of the PostgreSQL Custom Query monitor type to monitor how many transactions are being executed by a specific user.

In the TrueSight console, You add a custom query to the environment configuration through the Custom SQL queries panel.

Custom SQL Query panel sample

The complete query in this example is:

SELECT usename, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' AND usename ILIKE 'postgres'

To monitor other users than postgres, update the WHERE clause.

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

Number of records graph

Suggested action

A high number of return rows can indicate an over-utilized resource. A generated event indicates that you might want to update the number of rows returned by the query to avoid alarms or warning events.

Query status

Set a threshold for this attribute to get a notification if the query was successful or unsuccessful. By default, this attribute will not generate an alarm or warning event. For more information about this attribute, see the Query status attribute page.

Set a threshold for this attribute to get a notification if the query execution was successful or unsuccessful. By default, this attribute will not generate an Alarm or a Warning events. For more information about this attribute,

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

Query status graph

Suggested action

If an SQL query fails because of a bad SQL query definition, revise your query and correct the configuration in the CMA screen.

Response time

Set a threshold for this attribute to monitor the response time, in milliseconds, required by a query to retrieve data. By default, this attribute will not generate an alarm or warning event. For more information about this attribute, see the Response time attribute page.

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

Response time graph

Suggested action

A high response time value can indicate delays occurring in the PostgreSQL database, check your database performance.

Value

Use this attribute to retrieve data from a selected cell. The data must be a numeric value that is stored in first cell of the result and the type of the cell must be a numeric, or text, data type. By default, this attribute will not generate an alarm or warning event. For more information about this attribute, see the Value attribute page.

For example, you can use this attribute of the PostgreSQL Custom Query monitor type to sample if a specific user is causing an excessive number of disk reads during a cache sequence.

In the TrueSight console, you add a custom query to the environment configuration through the Custom SQL queries panel, see the Number of records section.

The complete query to sample if a specific user is causing an excessive number of disk reads is:

SELECT sum(idx_blks_read) as idx_read FROM pg_statio_user_indexes WHERE relname ILIKE 'postgres';

For more information about setting an alarm or warning event for this attribute, or changing the default attribute range, see Defining a monitoring policy.

Value graph

Event generated by the KM

You can see the event in the TrueSight console in Monitoring > Events.

Suggested action

If the retrieved value does not meet the single cell requirement, this parameter will not return a value. You need to update the selection to a single, specific cell that holds a number value.