CI Relationships directly from SQL DB

[Anon Anon]

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

[Lindsay_Estabrooks]

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.

[Anon Anon]

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!

[Chi Chen]

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.

[Pier Olivier Tremblay]

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.

[Anon Anon]

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!