Clarity Service Management

Expand all | Collapse all

CI Parent Child Relationship Info

Jump to Best Answer
  • 1.  CI Parent Child Relationship Info

    Posted 02-01-2019 07:57 AM

    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



  • 2.  Re: CI Parent Child Relationship Info

    Posted 02-01-2019 10:47 AM

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



  • 3.  Re: CI Parent Child Relationship Info

    Posted 02-01-2019 10:49 AM

    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..



  • 4.  Re: CI Parent Child Relationship Info

    Posted 02-01-2019 11:33 AM

    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



  • 5.  Re: CI Parent Child Relationship Info

    Posted 02-01-2019 10:49 AM

    try busmgt mdb table /Business_Management schema table



  • 6.  Re: CI Parent Child Relationship Info
    Best Answer

    Posted 02-04-2019 05:20 AM

    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



  • 7.  Re: CI Parent Child Relationship Info

    Posted 02-07-2019 07:27 PM

    Thanks for documenting the solution!

     

    This helps the Community long term.


    Thanks, Kyle_R.



  • 8.  Re: CI Parent Child Relationship Info

    Posted 02-11-2019 01:27 PM

    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,