Scoreboard query not reflecting the true value

Products

• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

Sometimes, especially when custom fields are involved in the Where clause, the scoreboard query doesn't give the expected result in terms of records extracted from the database. Tickets where the queried field is NULL may be excluded from the results.

Why don't scoreboard query nodes sometimes extract the expected records?

This could be more frequent when custom fields are involved in a condition in the Where clause of the stored query 

Environment

CA Service Desk Manager 12.9, 14.1, 17.0, 17.1, 17.2

Resolution

The problem could be due to the value the (custom) field has stored in the database.

That because sometimes a value <empty> is stored in the db as a NULL value, hence, if the Where section has a condition on that specific field, to compare values (with a string, an integer etc, on the base of the type of the field), the stored query doesn't consider and so doesn't extract the records having that field set to NULL.

To extract them, an explicit condition to get records having that field set to NULL is needed.

A sample: suppose to have the Requests 1, 2, 3, 4, 5 and to have:

Request 1 assigned to group Grp01

Request 2 assigned to group Grp02

Request 3 assigned to group Grp03

Request 4 and 5 with no group associated

To extract the requests with group assigned NOT equal to Grp01 and to Grp02, the first attempt could be to use the stored query, for Type = Request, with Where Clause:

NOT (group.last_name in (\'Grp01\', \'Grp02\')) AND type = \'R\' 

as the requests not having group = Grp01 and group = Grp02 are Request 3, Request 4 and Request 5, the expected result is to get all of them but, instead, this stored query extracts only the Request 3 while Request 4 and Request 5 are not displayed.

That because the Request 4 and Request 5 have the group set to NULL.

To get the expected result (Request 3, 4, 5 all extracted by the stored query), the stored query has to be modified to include the requests having the group set to NULL:

(NOT (group.last_name in (\'Grp01\', \'Grp02\')) OR (group IS NULL)) AND type = \'R\'