Scoreboard Queries

This article contains the following topics:
casm171
This article contains the following topics:
One of the tables in the database, Cr_Stored_Queries, defines stored queries. These stored queries, which are similar to SQL queries, can be used to customize the counter fields on nodes in the scoreboard area of the administrative and web interfaces. The counter fields tell how many records match the query. For example, they can tell how many of various types of requests have been assigned to the logged-in user.
Each user can customize the counter fields that appear on his or her scoreboard (this is explained in the online help.) However, the system administrator must first define the various types of requests that can be counted in these counter fields as stored queries.
 Scoreboard counts will be incorrect if database query values are equal to NULL. For example, if your Scoreboard query specifies that assignee.organization = xyz, and an assignee field is blank (NULL) for a record, then that record will not be part of the Scoreboard count.
Stored Queries for Logged in User
Two of the fields that must be defined on the Stored Query Detail window are Where Clause and Label. Both of these fields can contain expressions that are customized to the logged-in user. Stored queries refer to objects and attributes, rather than to table names and columns. A stored query that is customized to the logged-in user consists of two parts, as follows:
  • The object (such as cr for a request)
    This is usually specified on the left of the equal (=) sign. The syntax for this part of the stored query is:
    att_name[.att_name...].SREL_att_name
A stored query always has a Type, which is an object name that the query is executed against and provides context for the query. In the syntax above, the first att_name must be an attribute name of the context object.
  • The logged-in user (the instance of the cnt object for this user)
    This must be specified on the right of the equal (=) sign if the tickets are to be selected based on an attribute of the logged-in user. The syntax for this part of the stored query is:
    @
    att_name
    [.
    att_name
    ...].
    SREL_att_name
For more information about objects and attributes, see the CA Service Desk Manager Reference Commands section.
Syntax for cr Object
Use this syntax if the reference is to the request (cr) object:
att_name[.att_name...].SREL_att_name
This example identifies the location of the person assigned to handle a ticket. In this example, the object name is omitted, as the type of the Stored Query implies the cr object:
assignee.location=@cnt.location AND active=1
  • assignee
    The attribute in the request object that maps to the assignee field in the corresponding table. For example, the assignee attribute is defined in the cr object with SREL agt, which means it refers to the agt factory. The agt factory is part of the cnt object definition.
  • location
    The attribute in the cnt object that maps to the c_l_id field in the Contact table. The location attribute is defined in the cnt object with SREL loc, which means it refers to the loc object.
WHERE Clause
The following example demonstrates a value you can code in a WHERE clause:
assignee.location=@cnt.location AND active=1
Given the Stored Queries type is a Request, this query selects all active requests where the assignee’s location is the same as the location of the logged-in user.
Label
Attributes in the cnt object can be included in labels the same way they are included in WHERE clauses. Here is an example of the use of an attribute in the cnt object in a label:
@cnt.location.name Calls
This label will include the name of a location, for example, Phoenix, where Phoenix is substituted for @cnt.location.name when the label is displayed on a window. The label will be displayed as Phoenix Calls.
The IN Keyword
The IN keyword allows a stored query to reference two (or more) tables without creating a join. This can result in significant efficiency in executing the query. It is coded as follows:
SREL_att_name IN ( value1 [, value2 [,...]] )
For example, a request query could be coded as:
category.sym IN (\'Soft%\', \'Email\')
This results in the following SQL WHERE clause:
category IN (SELECT persid FROM prob_ctg WHERE sym LIKE 'Soft%' OR sym = 'Email')
One use of IN is to avoid Cartesian products. For example, the following query results in a Cartesian product and is very inefficient:
assignee.last_name LIKE 'MIS%' OR group.last_name LIKE 'MIS%'
By using IN, the query does not create a Cartesian product; in fact, it creates no joins at all, as illustrated by the following example:
assignee.last_name IN 'MIS%' OR group.last_name IN 'MIS%'
The parentheses that normally enclose the list of values on the right side of IN can be omitted if there is only one value in the list. Similarly, you should avoid joins in data partitions by converting a data partition, illustrated as follows:
assignee.last_name LIKE 'Smith' to: assignee = U'374683AA82ACE34AB999A042F3A0BA2E'
where:
  • U
    indicates that the value is a uuid.
  • '374683AA82ACE34AB999A042F3A0BA2E'
    The 32 characters in single quotes indicates the string representation of an actual uuid.
This avoids the join with some loss in clarity. Using IN, the same partition can be written as illustrated in the next example, with the clarity of the first version and almost the same efficiency as the second version:
assignee.last_name IN 'Smith'
CA SDM supports the IN clause applied to QREL or BREL lists. For example, if you want to find all the Requests with Assets that are parents of another specific Asset (with id 374683AA82ACE34AB999A042F3A0BA2E), the appropriate where clause is as follows:
affected_resource.[parent]child_hier.child IN (U’374683AA82ACE34AB999A042F3A0BA2E’)
The first part of the clause,
affected_resource
, is an SREL (foreign key) of the cr (Request) object, pointing to the Network_Resource table. The
child_hier
portion is a list of hier objects pointing to the hierarchical relationships. The last part,
child
, forms the first part of the where clause for the IN sub query. The
374683AA82ACE34AB999A042F3A0BA2E
portion is the foreign key value to match on
child
.
[parent]
specifies thesub query return. Since the id value is a string representation of a UUID it must be indicated as such and written as U’374683AA82ACE34AB999A042F3A0BA2E’
The following is an example of the actual SQL generated, which provides all the Requests where the Asset is a parent of a specific Asset:
SELECT Call_Req.id FROM Call_Req WHERE Call_Req.affected_rc IN (SELECT hier_parent FROM Asset_Assignment WHERE hier_child = U'374683AA82ACE34AB999A042F3A0BA2E')
To query on multiple parents, you can provide a comma-separated list in the () portion of the SQL, as shown by the following example:
affected_resource.[parent]child_hier.child IN (U'374683AA82ACE34AB999A042F3A0BA2E', U'374683AA82ACE34AB999A042F3A0BA2E')
The attribute name in brackets ([]) is used to form the SELECT portion of the sub-clause. Bracket notation is not used for the group Stored Queries shipped with CA Service Desk Manager Version 6.0, as illustrated in this example:
(assignee = @cnt.id OR group.group_list.member IN (@cnt.id)) AND active = 1
If bracket notation is not used, the SQL subsystem assumes that it is the attribute name of the first symbol in the dot-notation portion. It works in this case, more out of luck, that the group_list object has an attribute named ‘group’ in it. If it were named anything else, the where clause would fail to parse! The equivalent clause with brackets illustrated as follows:
(assignee = @cnt.id OR group.[group]group_list.member IN (@cnt.id)) AND active = 1
You cannot extend the dot notation. For example, the following does not work:
affected_resource.[parent]child_hier.child.name IN ('chicago1')
Query Based on Priority
In the database, the Priority table has two columns named sym and enum. The value the users see are the sym values. But the application sees the sym based on the enum values. At present, the default sym values 1 to 5 are reversed in their enum value.
Example
Sym
Enum
1
5
2
4
3
3
4
2
5
1
Therefore, when writing the stored query, when you reference a value of 5, you are actually looking for priority 1 unless you use a .sym to specify which attribute to look at.
Do not change the default enum values the product assigns. Instead, when adding new sym values, just continue from the highest enum value and so on.
Time-Based Queries
Time spans can be used to create time-based stored queries. A time span specifies a period of time, which can be relative to the current date. For example, a time span could refer to today, yesterday, last week, or last month. A time span has a name, such as TODAY or YESTERDAY. You refer to a time span in a stored query by using either of two built-in functions, as follows:
  • StartAtTime (
    timespan-name
    )
    This refers to the beginning of the period described by the time span.
  • EndAtTime (timespan-name)
    This refers to the end of the period described by the time span.
The syntax rules for stored queries require that the time span name be enclosed in single quotes, with each single quote preceded by a backslash. For example, to refer to the beginning of last week, you would specify:
StartAtTime(\'PAST_WEEK\')
The passage of time makes it necessary to periodically refresh a stored query containing a reference to a time span. For example, the interval described by “yesterday” changes at midnight. You specify the Start Time, End Time, and Trigger Time for refreshes in the Timespan Detail window.
Start Time
Start Time specifies the beginning of the time span in absolute or relative terms. The following table describes the fields within the Start Time section of the Timespan Detail window:
  • Year
    An explicit year, such as 2000, or a relative year, such as +1 (next year) or - 1 (last year)
  • Month
    An explicit month from 1 (January) to 12 (December), or a relative month, such as +1 (next month) or - 1 (last month)
  • Day
    An explicit day from 1 to 31, or a relative day, such as +1 (tomorrow) or - 1 (yesterday)
  • Hour
    An explicit hour from 0 to 24, or a relative hour, such as +1 (next hour) or - 1 (last hour)
  • Minute
    An explicit minute from 0 to 59, or a relative minute, such as +1 or - 1
End Time
End Time specifies the end of the time span in absolute or relative terms. The End Time fields of the Timespan Detail window are the same as the Start Time fields of the Timespan Detail window.
Trigger Time
The Trigger Time field specifies when the WHERE clause of a stored query containing a reference to the time span is recreated and the stored query refreshed. Trigger Time must be relative to the current time as described in the following table:
  • Year
    Must be a relative year from - 1 (last year) to +36 (36 years from now).
  • Month
    Must be a relative month from - 1 (last month) to +11 (11 months from now).
  • Day
    Must be a relative day from - 1 (yesterday) to +31 (31 days from now).
  • Hour
    Must be a relative hour from - 1 (last hour) to +23 (23 hours from now).
  • Minute
    Must be a relative minutes from +9 (9 minutes from now) to +59 (59 minutes from now).