Highlighted
Super Contributor.
Super Contributor.
279 views

Query for Orphan CIs

Jump to solution

In the past I had used the following query to identify the number of Orphan CIs (CIs with no relationships) in our UCMDB:

SELECT count(R.CMDB_ID) as Cnt, R.A_ROOT_CLASS  
FROM CDM_ROOT_1 R
left join LINK_1 L1 on R.CMDB_ID = L1.END1_ID
left join LINK_1 L2 on R.CMDB_ID = L2.END2_ID
where L1.END1_ID IS NULL and L2.END2_ID IS NULL
AND R.A_ROOT_CLASS IN (select A_ROOT_CLASS from managed_object_1)
group by R.A_ROOT_CLASS
Order by Cnt

 

From there I could get more specific by CI Type using the Query (for example the type "interface") :

SELECT  cmdb_ID, A_DISPLAY_LABEL
FROM CDM_interface_1
where cmdb_ID in (select R.CMDB_ID    
from CDM_ROOT_1 R  
left join LINK_1 L1 on R.CMDB_ID = L1.END1_ID  
left join LINK_1 L2 on R.CMDB_ID = L2.END2_ID  
where L1.END1_ID IS NULL and L2.END2_ID IS NULL)

But I noticed after checking some of the reported CIs in the UI that they did relationships.  Has something changed (now using 10.33 CP24) where these queries need to be modified and if so how?

 

Regards,
0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Query for Orphan CIs

Jump to solution

There is a tool in UCMDB called 'newDbTool.bat' in the tools directory.  You would use this with the -consistency option to clean up CIs that don't have references in multiple tables (e.g. a node that doesn't exist in cdm_node_1 and cdm_root_1, or a link that doesn't exist in cdm_composition_1 and cdm_link_1).

I am sorry that you wanted to provide negative feedback, but hopefully your question is answered.

Hope this helps,
Keith Paschal
UCMDB Worldwide Support Lead
Micro Focus Support
If you find this or any post resolves your issue, please be sure to mark it as an accepted solution."

Click the KUDOS star on the left to say 'Thanks'

View solution in original post

0 Likes
7 Replies
Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

Re: Query for Orphan CIs

Jump to solution

Hello ,

In each new version we have multiple changes. I suggest to create a ticket if you noticed any inconsistency that are affecting you in your bussiness.

Best Regards,

Melissa Carranza Mejias
Customer Support Engineer

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.
If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the STAR at the bottom left of the post and show your appreciation. “
0 Likes
Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

Re: Query for Orphan CIs

Jump to solution

Hello Skazal,

Please inform us if you have another concern related to this thread.

Regards,

Melissa Carranza Mejias
Customer Support Engineer

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.
If you are satisfied with anyone’s response please remember to give them a KUDOS by clicking on the STAR at the bottom left of the post and show your appreciation. “
0 Likes
Highlighted
Super Contributor.
Super Contributor.

Re: Query for Orphan CIs

Jump to solution

I am activly trying to pursue a satisfactory resolution to my question.  Besides this thread, I've opened a support case and if nessessary will reach out to product management.

Regards,
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Query for Orphan CIs

Jump to solution

Direct queries to the database are not supported.  The schema can and will change without notice.  If you want to dig around in the dataabase, you are free to, but a support ticket will not help you.  It is unsupported to query the database directly.  IF you need further assistance, I would reach out to a DBA and supply them with a box of donuts.

We have many requests for this, but as of yet the schema is not documented for this reason.

Hope this helps,
Keith Paschal
UCMDB Worldwide Support Lead
Micro Focus Support
If you find this or any post resolves your issue, please be sure to mark it as an accepted solution."

Click the KUDOS star on the left to say 'Thanks'
0 Likes
Highlighted
Super Contributor.
Super Contributor.

Re: Query for Orphan CIs

Jump to solution

In lieu of a box of donuts and the inability to provide a negative KUDOS value, let me rephrase my question.

It is a known fact that UCMDB can build up a large number of CIs that

  • Have no relation to any other CIs
  • Never age out of the system.
  • Create bloat of useless CIs
  • Skew capacity planning

Trying to create a TQL to identify these fails due to the large number of CIs in the system.   What is the documented solution for identifying them?

Regards,
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Query for Orphan CIs

Jump to solution

There is a tool in UCMDB called 'newDbTool.bat' in the tools directory.  You would use this with the -consistency option to clean up CIs that don't have references in multiple tables (e.g. a node that doesn't exist in cdm_node_1 and cdm_root_1, or a link that doesn't exist in cdm_composition_1 and cdm_link_1).

I am sorry that you wanted to provide negative feedback, but hopefully your question is answered.

Hope this helps,
Keith Paschal
UCMDB Worldwide Support Lead
Micro Focus Support
If you find this or any post resolves your issue, please be sure to mark it as an accepted solution."

Click the KUDOS star on the left to say 'Thanks'

View solution in original post

0 Likes
Highlighted
Super Contributor.
Super Contributor.

Re: Query for Orphan CIs

Jump to solution

Received notification from Microfocus support today that R&D has created the ER for a future release. This functionality will be added in JMX or in a new DB Tool.

Thank you R&D!

Regards,
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.