Highlighted
New Member.
130 views

sql query to join node and installed software

Hi , I am trying to join the UCMDB tables to get the sofwares installed and running softwares on the machine. But i am unable to get exact results , please help me with the query. I also tied with node element but unable to join all together.

SELECT config.*

FROM

[dbo].[NODE_1] a WITH (NOLOCK)

JOIN [dbo].[CONFIGURATION_ITEM_1] CONFIG WITH (NOLOCK)

ON a.cmdb_id = CONFIG.cmdb_id

JOIN [dbo].[RUNNING_SOFTWARE_1] RUNSW WITH (NOLOCK)

ON a.a_root_class = CONFIG.a_root_class

AND A.A_PRIMARY_IP_ADDRESS=RUNSW.A_APPLICATION_IP

join dbo.CDM_INSTALLED_SOFTWARE_1 INST

on INST.INLINE_COMPOSITION_CMDB_ID>RUNSW.CMDB_ID and INST.INLINE_COMPOSITION_END1<RUNSW.CMDB_ID

 

Installed with Node element, but unable to join with node:

select * from [dbo].[INFRASTRUCTURE_ELEMENT_1] I

join [dbo].[NODE_ELEMENT_1] N

on I.CMDB_ID=N.CMDB_ID

JOIN [dbo].[CDM_INSTALLED_SOFTWARE_1] INST

ON INST.CMDB_ID=N.CMDB_ID

join [dbo].[CONFIGURATION_ITEM_1] c

on c.cmdb_id=i.cmdb_id

0 Likes
3 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: sql query to join node and installed software

This is why we have TQLs, Topology Query Language.
are you trying to reinvent the wheel?

 

What is your use case for such a query?

Kind regards,
Bogdan Mureșan
EMEA CMS Technical Success
0 Likes
Highlighted
New Member.

Re: sql query to join node and installed software

Hi John,

Thanks for responding.
So i was requested to pull the data from ucmdb database, Who evr mavhine id has installed softwares and running softwares. I was asked to figure the relation between node and running software, installed software tables.

It would be a great help if you help me with this.
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: sql query to join node and installed software

You can try to create your TQL, add in the TQL layour whatever attributes you need and export the TQL results using the JMX method retrieveTQLresults. 
This JMX method can export in XML format also.

Kind regards,
Bogdan Mureșan
EMEA CMS Technical Success
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.