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.
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.
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.
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.
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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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)ASBEGIN -- 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 ciHierarchyEND
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.
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!