SDP CI关系报表
SDP CI关系报表
如指定配置项类型，参考如下SQL，红色标记内容为可替换配置项类型：
SELECT ci.ciname AS "名称", (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) AS "关系详情", cit.typename AS "配置项类型"
FROM ci left join citype as cit on ci.citypeid=cit.typeid WHERE ciid IN (SELECT ciid FROM cirelationships) and cit.typename like '%工作站%'
运行效果:
如指定配置项类型及地点，参考如下SQL，红色标记内容为可替换配置项类型和所需地点：
SELECT ci.ciname AS "名称", cit.typename AS "配置项类型", sdorg.name AS "地点", (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) AS "关系详情"
FROM ci
left join citype as cit on ci.citypeid=cit.typeid
left join SDOrganization as sdorg on sdorg.org_id=ci.siteid
WHERE ciid IN (SELECT ciid FROM cirelationships) and cit.typename like '%工作站%' and sdorg.name like '%上海%'