We want to create a custom checkpoint on the sql_response probe for Microsoft SQL Server database and get the QoS value for this custom checkpoint stored on the backend Database.
This Article will show you the way you can configure the probe to collect the custom checkpoint QoS.
When you create a custom checkpoint, the checkpoint execution can be a regular SQL query or a stored procedure (sp).
How to create the custom checkpoint and QoS.
1 - Open the probe sql_response probe, click configure and Edit profile, you will see 5 tabs on the popup screen, Click on the SQL Query tab, at my sample I decided to choose a regular sql query but you also can use a stored procedure. The query I chose is "select * from CA_UIM.dbo.cm_computer_system.
<Please see attached file for image>
2 - For the query output, you will see a few columns, the first is CS_ID, second CS_KEY, third CS_TYPE, and so on.
<Please see attached file for image>
3 - I decided to choose the CS_KEY as my custom checkpoint.
4 - Click in the value tab
5 -Click on the magnifying glass and a new popup will show the QoS list
6 - Right click on the blank QoS list field and create New
7 -Look for the new popup windows on the metric field for CS_KEY or the metric that you want to publish on the DB.
8 -If you look on the QoS list on Metric field and you are not able to find the metric you desire, this mean the results for the query or stored procedure is configured on the database as DATETIME or VARCHAR.
<Please see attached file for image>
9 - Open the table that are you working with, at my sample I’m working on the table “cm_computer_system” and look for the column name CS_KEY and check the datatype what information you have there. If it is configured as DATETIME or VARCHAR you will never be able to populate the QoS in to the database.
<Please see attached file for image>
10 - Now open the Value tab and look the Row Key options you will be able to see the metric you want to populate is showing as RawKey and not as metric
<Please see attached file for image>
The only way you can choose the metric on the metric list is if the columns on the database table are INT, TYNYINT, BIGINT, e BIT
This procedure is ONLY valid for a Microsoft SQL Server database.