CA Service Management

Expand all | Collapse all

CI Relationships directly from SQL DB

Jump to Best Answer
  • 1.  CI Relationships directly from SQL DB

    Posted 10-25-2016 05:19 PM

    I need to find CIs with a relationship to the output of the following:

    select resource_name from ca_owned_resource where resource name like '%someCI_name%'

    I'm pulling this information from a custom field in the chg table, with the desired end result to have the ability to automatically attach CIs to a CO based on this custom field, which has the application name as the desired CI name, and any dependent CIs.

     

    I appreciate the help in advance.

     

    Cheers!

    -Izz



  • 2.  Re: CI Relationships directly from SQL DB
    Best Answer

    Posted 10-25-2016 05:57 PM

    The linkage is provided by the busmgt table using attributes hier_parent and hier_child which relate to the ca_owned_resource table via the attribute own_resource_uuid.



  • 3.  Re: CI Relationships directly from SQL DB

    Posted 10-26-2016 09:32 AM

    Thanks Lindsay_Estabrooks  This is exactly what I was looking for.  Chi_Chen, I appreciate your input.  That is essentially the SQL I was using to pull the information I needed, but was looking for relationships on the string (CI) within resource_name.

     

    Thanks all!



  • 4.  Re: CI Relationships directly from SQL DB

    Broadcom Employee
    Posted 10-25-2016 05:57 PM

    you could run

    pdm_extract -f "select id,resource_name from ca_owned_resource where resource_name like '%someci_name%'"

    and then you would need to populate your change order custom field with the id if that custom field is a SREL or

    resource_name if that is a string.



  • 5.  Re: CI Relationships directly from SQL DB

    Posted 10-26-2016 09:54 AM

    izznoland, if you want to get the complete hierarchy from a CI you need to recursively read the busmgt table. You can do it from client side but it's way faster DB side

     

    Here's how to do it :

    USE [mdb]
    GO
    /****** Object:  StoredProcedure [dbo].[getUpperHierarchyFromRoot]    Script Date: 2016-10-26 09:40:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    -- =============================================
    -- Author : Pier-Olivier Tremblay
    -- Create date: 2016-06-02
    -- Description:     gets the hierarchy of the CI
    -- Parameter @@rootCIUUID root CI
    -- =============================================
    ALTER PROCEDURE [dbo].[getUpperHierarchyFromRoot]
         @rootCIUUID binary(16)
    AS
    BEGIN
         -- SET NOCOUNT ON added to prevent extra result sets from
         -- interfering with SELECT statements.
         SET NOCOUNT ON;

         --used to test
         --set @rootCIUUID= 0x90ADA554EB25E94F913676618BE8DBB8



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

     

    If you want both side relations, you can create two CTE, and looking for relation having @rootCIUUID as the parent, and joining on the child instead of the parent, and union them both together with a select.

     

    You may want to join on "mdb.dbo.ca_owned_resource" to get informations for every CI, and on "mdb.dbo.ci_rel_type" to get relation "description".

     

    Hope this helps.



  • 6.  Re: CI Relationships directly from SQL DB

    Posted 10-26-2016 09:57 AM

    Awesome!  Thank you pier-olivier.tremblay!  I am actually wrapping a SQL script with powershell and this is perfect.

    Again I appreciate everyone's assistance!