Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

CI missing relation report

Highlighted
Jan Gunnar Helg
Senior Member

CI missing relation report

Hi

We want to verify the integrity of our CI's. We want to start with making a report of all CI's that have no relations, how can I do this? Its not part of any view I can setup within HPOVSD. Is there an SQL select I can run for this?
16 REPLIES
Carol Hibbard
Honored Contributor

Re: CI missing relation report

From Configuration Mgmt, go to advanced find. Go to advanced tab. On field *TYPE*, enter one of these search strings and operator "is empty". Press add to list.

"Related Cis;CI to;" gives all CI To relations...
"Related CIs;CI From" gives all CI From relations...
"Parent CIs;CI Parent" gives Parent CI

The trick is to go to form definition, rightmouseclick on any field, find localized name.
Jan Gunnar Helg
Senior Member

Re: CI missing relation report

Sadly it does not work :(

If I create a view using the filter:

Related CIs;CI to EQUALS empty

I get 0 hits.

I know there are alot of CI's that have no relations, so something is not working here :(
Eng.NM
Member

Re: CI missing relation report

hi

try to refresh the views and delete your cash, if it didnt work also so it could be related to bug and you may need SP upgrade, what the system version and SP you are using?
thanks
Carol Hibbard
Honored Contributor

Re: CI missing relation report

You should try all three queries as the from/to find parent/child relations and the related to are peer relations.
Jan Gunnar Helg
Senior Member

Re: CI missing relation report

I've tried them all and indivudally, still 0 hits. Cache is cleared when running.
We are using HPOVSD 4.5 SP21

I've attached the filter I've created.

Is there any way to query this directly in the DB instead with a select statement?
Eng.NM
Member

Re: CI missing relation report

the filter seems fine, but it will only show CIs that hasnt any relation, can you please tell me is it a tree view or explorer or table view ?

it seems that tree and explorer views has some problems on this service pack

let me just know the type of this view
Jan Gunnar Helg
Senior Member

Re: CI missing relation report

Hi

Its a table view, and I am looking for all CI's that has no relations so it should be correct.
Carol Hibbard
Honored Contributor

Re: CI missing relation report

Are you sure that one of your CI's has none of the three relation types? You might try each one individually just to make sure.
Jan Gunnar Helg
Senior Member

Re: CI missing relation report

Yep, tried each indivudally. 0 hits on all 3 :(
Carol Hibbard
Honored Contributor

Re: CI missing relation report

Show us a CI with no relations on the form.
Jan Gunnar Helg
Senior Member

Re: CI missing relation report

Attached :)
Venkatesha_1
Esteemed Contributor

Re: CI missing relation report

Hi,

You can use the below query to get the list of CI's that has the Related CI's.

select CIRelation.CI_FROM_SEARCH_CODE, CIRelation.CI_TO_SEARCH_CODE from servicedesk.v1033_CI_Relation CIRelation, servicedesk.v1033_configuration_item CI where CI.search_code= CIRelation.CI_FROM_SEARCH_CODE

From your complete CI list subtract these CI's to get the Empty Related CI's.

HTH
Venky
Carol Hibbard
Honored Contributor

Re: CI missing relation report

Your CI form has been modified from the original. It looks like you are showing only peer relations and not parent/child relations. Do you have the original form still and can you check on this?

Or if you do the query that Venkat provided, does this CI show as having parent or child relations?
Carol Hibbard
Honored Contributor

Re: CI missing relation report

Attached is a screenshot of the OOB form.
Jan Gunnar Helg
Senior Member

Re: CI missing relation report

The select does not work:

Invalid object name 'servicedesk.v1033_CI_Relation'.

We do not have any child/parent relations. Only "Related CIs" relations.
LexTNS
Regular Collector

Re: CI missing relation report

Hi Jan,
The 1033 part looks like a country code. Try replacing that part with your country code. Hope this helps, Lex.
//Add this to "OnDomLoad" event