sqlserver: custom checkpoint query pulls back field variables as incorrect data type

The customer has defined a custom checkpoint, and can run the query successfully via the "test query" button.

 However, when checking the message variables that are being pulled back, these show that some of the fields of type INT are brought back as character type  and then these cannot be used for the row identification.

This can happen if the first row in the table you are querying contains NULL values in the respective field, and then the probe cannot properly identify the data type.

To resolve this, you would need to adjust the sql select you are using to pull back the values.  In the example, the customer used a wildcard select to get all fields, such as this:

select * from CERTIFICATE_DATA

To force the probe to recognize the correct data type, you need to adjust the select as per the example below:

select 
CertificateName, 
ExpiryDate, 
CurrentDate, 
ISNULL(DAYS_45, 0 ) as DAYS45 , 
ISNULL(DAYS_30, 0 ) DAYS30, 
ISNULL(DAYS_15, 0 ) DAYS15, 
FILE_NAME 
from CERTIFICATE_DATA

This then allowed the probe to pull back the correct data type (numeric) for the INT columns of the table.

For more details on the sqlserver probe, please check the documentation found here:

https://docops.ca.com/ca-unified-infrastructure-management-probes/ga/en/alphabetical-probe-articles/sqlserver-sql-server-monitoring