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
How to change CI type of an Asset
- Navigate to CMDB->Servers. - Search for the asset to which you need the CI type to be changed. - Choose the asset with their corresponding checkbox from the list and click on Actions->Modify CI type. - Choose the CI type from the list and click on ...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 ...Query to show support groups and its individual custom attributes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT ci.CINAME AS "Support Group Name", su.first_name "Owned By", ci.DESCRIPTION AS "Description",ia.attributename "Additional Attributes Name",ia.attributevalue "Additional Attributes ...Query to show workstation details.(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Below query will show Only workstations and its details. SELECT Max(workstation.workstationname) "Workstation", Max(workstation.servicetag) "Service Tag", Max(workstation.model) "Model", ...Query to get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...