CI Parent Child Relationship Info

[Arunava Saha]

hello Experts,

 

I need an information here on CI relationship. We have a CI "xyz" and its having 5 child CI as 'a','b','c','d','e'. I can see this information on Admin Web UI. However if I want to query this information in Database what are my target tables? It will be great if someone helps me with the SQL query to display the Parent CI and associated Child CIs in a generic format.

 

Thanks

ArunavaS

[Chi Chen]

In where you see child CI a, b, c, d, e? 

[mkucera]

Hi

It's quite simple:

The basic table for relationships is busmgt

where - hier_parent is own_resource_uuid (ca_owned_resource) of parent CI, hier_child is own_resource_uuid of child CI, ci_rel_type is id of relationship type (ci_rel_type table), del is inactive flag of relationship.

SQL:

select bmg.del as inactive_flag, par.host_name as parent_ci, crt.parenttochild as relation_type, cld.host_name as child_ci, dateadd(s,bmg.last_mod_dt,'19700101') as last_update_date

from busmgt bmg

join ca_owned resource par on bmg.hier_parent=par.own_resource_uuid

join ca_owned resource cld on bmg.hier_child=cld.own_resource_uuid

join ci_rel_type crt on crt.id=bmg.ci_rel_type

 

+ You can add filters to child, parent class, family, last update contact etc..

[Arunava Saha]

With my traditional ServiceDesk CMDB knowledge when I queried busmgt (for relationship) it shows only 26 records. But I know the answer should be in thousand. This is where I confused!!

 

Thanks

ArunavaS

[Chi Chen]

try busmgt mdb table /Business_Management schema table

[Arunava Saha]

Hello All,

 

I am able to find the exact query for my need. This query will provide parent child relationship in CMDB.

 

select nrChild.resource_name as Child_CI_Name, nrChild.[host_name] as Child_Host_Name, nrParent.resource_name as Parent_Name
from ca_owned_resource nrParent with (nolock)
inner join  hier with (nolock)
    on nrParent.own_resource_uuid = hier.hier_parent
inner join ca_owned_resource nrChild with (nolock)
    on hier.hier_child = nrChild.own_resource_uuid
where nrParent.resource_name =<Parent CI Name>

 

Thanks

ArunavaS

[Kyle_R]

Thanks for documenting the solution!

 

This helps the Community long term.

Thanks, Kyle_R.

[Pier Olivier Tremblay]

This will give you only the first level of hierarchy,

 

 

If you want to go down or up in the herarchy, you need to use a recursive query with a common table expression. Use it in a table-valued function.

 

Going up : (root uuid represent the uuid of the "lowest" CI)

CREATE FUNCTION [dbo].[fn_getCIHierarchyUp]
(
@rootCIUUID binary(16)
)
RETURNS TABLE
AS
RETURN
(
--create a CTE to use with the recursivity

With ciHierarchy(childUUID,relType,parentUUID)
as
(
--select in the relationship table
Select BMHIER.hier_child, BMHIER.ci_rel_type, BMHIER.hier_parent
from mdb.dbo.busmgt BMHIER
where BMHIER.hier_child = @rootCIUUID
UNION ALL
--union with another select in the relationship table but with a join on the CTE
Select BMHIER.hier_child, BMHIER.ci_rel_type, BMHIER.hier_parent
from mdb.dbo.busmgt BMHIER
inner join ciHierarchy BMHIERTEMP -- join on the CTE. Unleash the recursivity!
on BMHIERTEMP.parentUUID=BMHIER.hier_child
)

select * from ciHierarchy
)

 

Going down (root uuid represent the uuid of the highest CI)

CREATE FUNCTION [dbo].[fn_getCIHierarchyDown]
(
@rootCIUUID binary(16)
)
RETURNS TABLE
AS
RETURN
(
--create a CTE to use with the recursivity

With ciHierarchy(childUUID,relType,parentUUID)
as
(
--select in the relationship table
Select BMHIER.hier_child, BMHIER.ci_rel_type, BMHIER.hier_parent
from mdb.dbo.busmgt BMHIER
where BMHIER.hier_parent = @rootCIUUID
UNION ALL
--union with another select in the relationship table but with a join on the CTE
Select BMHIER.hier_child, BMHIER.ci_rel_type, BMHIER.hier_parent
from mdb.dbo.busmgt BMHIER
inner join ciHierarchy BMHIERTEMP -- join on the CTE. Unleash the recursivity!
on BMHIERTEMP.childUUID=BMHIER.hier_parent
)

select * from ciHierarchy
)

 

 

You can add a condition on the "rel_type" column to walk throught a specific type of relation!

 

 

Usage is as follow :

 

 

SELECT * FROM [dbo].[fn_getCIHierarchyXXXX] (0xAABBCCEEDDFF00112233445566778899)

 

 

Since it returns parent and child uuid, you can add two inner join on ca_owned_resource to get resource_name !

 

 

Hope this helps,