LUTION GmbH
Alfred-Escher-Strasse 11, 8002 Zürich | +41 44 461 16 15
Within HP ServiceManager version 9.32 the cross table query functionality has been introduced.
Since ever HP ServiceManager does not provide a relational data model. With the Cross Table Query functionality at least relational queries become possible.
Author:
Alan Gross
CEO & Senior Consultant at LUTION
Since ages HP ServiceManager developers were forced to build business logic in a quite inefficient way due to the fact that queries involving multiple tables were not possible.
To achieve the same result we had to code what normally the database engine would automatically do for us. This means a lot of subselects and looping through record sets which takes time and consumes also resources on the database and application server.
Such business logic is causing actually a lot of performance bottlenecks within HP ServiceManager.
We would like to select all CIs in the CMDB not having any downstream or upstream relations.
This query involves the device and cirelationship table. The CMDB device table normally contains quite a high number of records - therefore looping through all device records and checking for each if there is any related record within the cirelationship table is definitely not an option.
In the past there were only 2 options to achieve this:
Fortunately these times are over since HPSM 9.32. Cross Table queries can be used from almost anywhere in HPSM e.g. JavaScript
var file = new SCFile("device");
var sql = "SELECT ta1.logical.name from device ta1
LEFT OUTER JOIN cirelationship ta2
ON ( ta1.logical.name=ta2.logical.name )
LEFT OUTER JOIN cirelationship ta3 ON ( ta1.logical.name=ta3.related.cis )
WHERE (ta2.logical.name=NULL and ta3.related.cis=NULL)";
var count = file.doCount(sql);
print("count: "+count);
There we go - This query returns all CIs having no downstream nor upstream relationship in one step.
The same query can also be used for instance within a link (used by so called "fill" fields on forms).
Since the system checks the syntax on the query field I suggest to pass such cross table queries whenever possible in a variable.
Even when this functionality has been introduced quite late by HPE it's a big pleasure to have it now.
This opens a lot of doors and makes the live of a HPSM developer definitely easier. As you can imagine there are also certain limitations e.g.
See also the following demo video by HPE.