Link between Node and business application in HP UCMDB Database

Hi All,

 

I am trying to extract all the computers that are having the business applications from the UCMDB Database. I am not able to identify any common link between these two tables. Can any one guide me how to join these two tables in HP UCMDB?. Thanks for your help and assistance on this one. Really appericiated.

Regards,

Krithik

  • Relationships are stored in the Link table, using the CMDB ID for the "ends" (end1 = parent, end2 = child).

    Basic overview query:

    SELECT 
    	 l.[A_ROOT_CLASS]
    	,l.[END1_ID]
          	,l.[END2_ID]
    	,r.A_ROOT_CLASS
    	,rc.A_ROOT_CLASS
      FROM [ucmdb].[dbo].[LINK_1] l
      join [ucmdb].[dbo].[ROOT_1] r
      on l.END1_ID = r.CMDB_ID
      join [ucmdb].[dbo].[ROOT_1] rc
      on l.END2_ID = rc.CMDB_ID

     

     

  • Thank you very much for your response. Could you please let me know how to join between computer and VMWARE ESX Server in sql query?.  This would be really helpful for my needs.

  • Hi,

    I think it will be helpful -

        inner join dbo.EXECUTION_ENVIRONMENT_1 ex1
            inner join dbo.HYPERVISOR_1 h1
                inner JOIN dbo.VMWARE_ESX_SERVER_1 vmware1
                on h1.A_ROOT_CONTAINER = CAST('' AS XML).value('xs:hexBinary(sql:column("vmware1.CMDB_ID"))', 'VARCHAR(MAX)')
            on h1.CMDB_ID = ex1.END1_ID
        on n3.CMDB_ID  = ex1.END2_ID

  • Hi ,

    Thanks in adavance.

    I tried to run this in oracle db

     inner join dbo.EXECUTION_ENVIRONMENT_1 ex1
            inner join dbo.HYPERVISOR_1 h1
                inner JOIN dbo.VMWARE_ESX_SERVER_1 vmware1
                on h1.A_ROOT_CONTAINER = CAST('' AS XML).value('xs:hexBinary(sql:column("vmware1.CMDB_ID"))', 'VARCHAR(MAX)')
            on h1.CMDB_ID = ex1.END1_ID
        on n3.CMDB_ID  = ex1.END2_ID

    I  was not able to run due to errors.

     

    could you please share me the quey  suppoted by oracle.

     

    Thanks

    Sunil