How to configure a sql_response profile that monitors CDM QOS Poll Interval?

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)

Issue/Introduction

How can we configure a sql_response profile that monitors CDM QOS Poll Interval?

The goal is to generate a sql_reponse probe alarm when the Poll interval is not 5 mins/300 secs

Environment

Any SQL_RESPONSE version and UIM configured with MS SQL Server database

Resolution

1. Run the query below to validate that all CDM QOS samplerate(poll interval) is 300 seconds via OC SLM -> SQL Query Editor, SQL Server Management Studio, or similar tool.

SELECT count(*) as count FROM S_QOS_DATA AS qd JOIN S_QOS_SNAPSHOT AS snap ON snap.table_id = qd.table_id WHERE snap.[sampletime] > dateadd(hour, -1, getdate()) AND probe = 'cdm' and samplerate <> 300

or query that excludes QOS_COMPUTER_UPTIME which is polled every 60 minutes (3600 secs)

SELECT count(*) as count FROM S_QOS_DATA AS qd JOIN S_QOS_SNAPSHOT AS snap ON snap.table_id = qd.table_id WHERE snap.[sampletime] > dateadd(hour, -1, getdate()) AND probe = 'cdm' and samplerate <> 300 and qos <> 'QOS_COMPUTER_UPTIME'

2. Create a database connection to the UIM Database on the sql_response probe

3. Create a new profile and select the UIM Database connection

4. Click on the SQL Query menu and enter the query below in the Simple Query field and run Test to confirm that value returned

SELECT count(*) as count FROM S_QOS_DATA AS qd JOIN S_QOS_SNAPSHOT AS snap ON snap.table_id = qd.table_id WHERE snap.[sampletime] > dateadd(hour, -1, getdate()) AND probe = 'cdm' and samplerate <> 300 and qos <> 'QOS_COMPUTER_UPTIME'

5. Click on the Value menu, enable Alarm and QOS

6. Add a new alarm in the Alarm List; select count for Column, add appropriate threshold values and Message variables, Click OK.

7. Click on the QOS List icon and add a New QOS

8. Enter appropriate QOS values and OK to save settings

9. Wait several poll cycles and use Metric Views to confirm sql_response metrics are displayed under the robot with the sql_response probe