How to build Stored Queries on the base of fields belonging to extension tables of Configuration Items
Article ID: 10102
Updated On:
Products
Issue/Introduction
On the base of the Family the Configuration Items belong to, there could be Extension tables to store the additional data related to that specific Family; as a consequence, there could be the need to have stored queries extracting data on the base of values stored in these Extension tables.
As a sample, a CI belonging to family ‘Hardware.Server’ will have additional data in ‘har_serx’ (ci_hardware_server) table in addition to the data stored in ca_owned_resource table and usp_owned_resource.
The association between ‘nr’ object (configuration item) and an extension table is done through a BREL existing only in Object Engine: the relation between nr and har_serx, for example, is done via ‘assoc_har_serx’ defined in nr object as BREL towards har_serx table.
Environment
CA Service Desk Manager 17.x
Resolution
To set a stored query extracting data contained in the extension table, the extension table needs to be know, on the base of the Family the CI belongs to.
Info and details about that can be found in the Families and Classes section of the online documentation.
When the table has been found, the correspondent ‘assoc_...’ is needed; this info can be obtained checking the nr.maj file (in %NX_ROOT\bopcfg\majjc folder where %NX_ROOT is the folder where Service Desk is installed) or via Web Screen Painter Schema Designer (expand nr object).
Sample: target is to create a stored query extracting CIs belonging to Hardware.Server family and having a lease_termination_date in the past week.
Extension table: har_serx (ci_hardware_server SQL table)
From nr.maj, the assoc_har_serx, BREL towards har_serx, is defined as below:
-----
OBJECT nr {
DISPLAY_GROUP "event asset tree_node stored_query macro act_log object_notify_actlog notify_rule notify_msg_tpl" ;
TENANT_OPTIONAL ;
ATTRIBUTES ca_owned_resource {
..
} ;
ATTRIBUTES usp_owned_resource SECONDARY {
..
assoc_har_serx BREL har_serx id DYNAMIC { UI_INFO "dtlTextbox"; };
..
} ;
..
-----
Picture_1 and picture_2 below are related to the same information as provided in Web Screen Painter.
The syntax used in the Where Clause of the Stored Query (Type = Configuration Item) is the following:
(assoc_har_serx.lease_termination_date < EndAtTime(\'PAST_WEEK\'))
where 'PAST_WEEK' is a timespan and the EndAtTime is a built-in function.
Details about the use of timespans and built-in functions in stored queries can be found in Time-Based Queries section.
Additional Information
Feedback
