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 ...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 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 ...Dynatrace Integration using Script - v1
Dynatrace to SDP Integration Tool – Configuration Guide What this Integration Does This integration automatically connects Dynatrace with ServiceDesk Plus to keep your CMDB up to date. It regularly reads information from Dynatrace about your servers, ...Query to find users under blocked state
select * from sduser sd left join CI ci on sd.ciid=ci.ciid left join AaaUserContactInfo auc on sd.userid=auc.user_id left join AaaContactInfo aci on auc.contactinfo_id=aci.contactinfo_id where sd.status='blocked'