CI Relationship
MSSQL
SELECT ci.ciname AS 'Name',
(SELECT rel.relationship + Char(10) + ' ' + ci2.ciname
+ Char(10) FROM cirelationships AS cirel
LEFT JOIN ci AS ci2
ON ci2.ciid = cirel.ciid2
LEFT JOIN relationshiptype AS rel
ON rel.relationshiptypeid = cirel.relationshiptypeid
WHERE cirel.ciid = ci.ciid
FOR xml path('')) AS 'Relationship Details' FROM ci
WHERE ciid IN (SELECT ciid
FROM cirelationships)
PGSQL
SELECT ci.ciname AS "Name",
(SELECT Array_to_string(Array_agg(Concat(rel.relationship, ' ',
ci2.ciname)),
', ')
FROM cirelationships AS cirel
LEFT JOIN ci AS ci2
ON ci2.ciid = cirel.ciid2
LEFT JOIN relationshiptype AS rel
ON rel.relationshiptypeid = cirel.relationshiptypeid
WHERE cirel.ciid = ci.ciid) "Relationship Details" FROM ci
WHERE ciid IN (SELECT ciid
FROM cirelationships)
New to ADSelfService Plus?
Related Articles
OnBoarding and OffBoarding Users
Requirement: User Onboarding Process: During user onboarding, we gather user information by requesting additional fields. A multiselect field displays all the available assets. Subsequently, we create a new user account and link the selected assets ...Debug Prints in FastText Algorithm
Issue: The zia category/template prediction model loading thread got stuck for a long time. Instructions: -> Download the attached file (zlabs-ml-fasttext.jar.txt) and rename it to zlabs-ml-fasttext.jar. -> Stop the Application service -> Please take ...How to auto-create change request(s) for unplanned changes on Assets
This post describes the use of a python script to auto-create change requests for unplanned modifications on Assets using Custom Schedules. This script is applicable only for builds prior to 11138. UseCase: Multiple assets are inventoried in ...Request deleted by technician
This report is used to find the deleted request in the application. We can get the data from the Trash and system log viewer. To make any changes to a query, refer to the KB article below. ...Support Groups
This Report is used to export the list of Support groups configured in the application with the complete details. MSSQL SELECT qd.QUEUENAME "Support group", ci.DESCRIPTION "Description", su.first_name "Owned By", STUFF( (SELECT ',' + au.first_name ...