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

SQL showing all related CI's for HPOVSD 4.5

SOLVED
Go to solution
Highlighted
Jan Gunnar Helg
Senior Member

SQL showing all related CI's for HPOVSD 4.5

Hi I'm trying to write an SQL that will give me the following info

Parent CI, Child CIs

I only want to show Parent CIs and Child CIs from a specific Category so I need a filter for that.

Anyone done this? (MSSQL 2005)
13 REPLIES
Radovan Skolnik
Honored Contributor
Solution

Re: SQL showing all related CI's for HPOVSD 4.5

This is familiar - I am finishing ServiceDesk object Visualizer so I use all these SQLs (though I have them generated automatically from SD's object model).

So here's few snippets that can help (I hope you will be able to add appropriate WHERE to filter on Category):

(Configuration Item-Child Configuration Items-Configuration Item): SELECT cic_citb_oid, cic_cita_oid FROM itsm_cicomponents

(Configuration Item-Parent CIs-Configuration Item): SELECT cic_cita_oid, cic_citb_oid FROM itsm_cicomponents

These will give you all parent-child or child-parent pairs. So if you filter on first attribute to be your object and do join with Category on second object and filter to required categories that should be it. If you do not know how to join on Category the easiest way would be having your SD create reporting views in database and look up their DDL to see how to get Category into view.
Jan Gunnar Helg
Senior Member

Re: SQL showing all related CI's for HPOVSD 4.5

Thanks, exactly what I needed.

I will have no problem adding the proper joins and filters now :)
Jan Gunnar Helg
Senior Member

Re: SQL showing all related CI's for HPOVSD 4.5

Here is what I ended up with if anyone else should have a need for this (unfiltered).

SELECT
CITP.CIT_SEARCHCODE PARENT_CI,
RCATP.RCT_NAME PARENT_CI_CATEGORY,
RCTP.RCT_NAME PARENT_CI_STATUS,
CITC.CIT_SEARCHCODE CHILD_CI,
RCATC.RCT_NAME CHILD_CI_CATEGORY,
RCTC.RCT_NAME CHILD_CI_STATUS

FROM ITSM_CI_RELATIONS CIR

JOIN ITSM_CONFIGURATION_ITEMS CITP
ON CIR.CIR_CIFROM_OID = CITP.CIT_OID

JOIN ITSM_CONFIGURATION_ITEMS CITC
ON CIR.CIR_CITO_OID = CITC.CIT_OID

JOIN DBO.REP_CODES_TEXT AS RCATP
ON CITP.CIT_CAT_OID = RCATP.RCT_RCD_OID

JOIN DBO.REP_CODES_TEXT AS RCATC
ON CITC.CIT_CAT_OID = RCATC.RCT_RCD_OID

JOIN DBO.REP_CODES_TEXT AS RCTP
ON CITP.CIT_STA_OID = RCTP.RCT_RCD_OID

JOIN DBO.REP_CODES AS RCDP
ON RCTP.RCT_RCD_OID = RCDP.RCD_OID

JOIN DBO.REP_CODES_TEXT AS RCTC
ON CITC.CIT_STA_OID = RCTC.RCT_RCD_OID

JOIN DBO.REP_CODES AS RCDC
ON RCTC.RCT_RCD_OID = RCDC.RCD_OID

ORDER BY RCATP.RCT_NAME,CITP.CIT_SEARCHCODE
Radovan Skolnik
Honored Contributor

Re: SQL showing all related CI's for HPOVSD 4.5

Hmm, but this selects mainly from CI Relations not from Parent/Child.
Jan Gunnar Helg
Senior Member

Re: SQL showing all related CI's for HPOVSD 4.5

For some reason we only have 255 records in the cicomponents table. But we have 90 000+ CI's.

I did quality check the source data for anything missing and the above select gave me what I needed.
Radovan Skolnik
Honored Contributor

Re: SQL showing all related CI's for HPOVSD 4.5

CI Relations have a reference to Relation Type (i.e. something like Connected to, Installed on, Backup of, ...). It is an ttribute called cir_rty_oid. As your select is right now you have no way of distinguishing what kind of relations these CIs have.
Jan Gunnar Helg
Senior Member

Re: SQL showing all related CI's for HPOVSD 4.5

Ah I see. Nevertheless in my case this select did give me what I needed, as we have no "parent parent relations" on the CI's which was a part of my filter not shown in the select above.
Radovan Skolnik
Honored Contributor

Re: SQL showing all related CI's for HPOVSD 4.5

But now you are treating possibly different kind of relations as parent/child which an be very misleading (as usually there are several kinds of relations defined and used in default SD installation). Open some kind of CI in client and you will see 3 tabs: Parent CIs, Child CIs and Related CIs. Parent/Child is usually used as a way of (de)composition - i.e. something (Child) is part of something else (Parent). Relations have a Type which is a sort of Code field...
Jan Gunnar Helg
Senior Member

Re: SQL showing all related CI's for HPOVSD 4.5

Ah explains why we only have Related CI's. The Child/Parent tab is not in our Form.

Not a very well implemented use of HPOVSD 4.5 you may say. I'll be happy the day we can get rid of it.

For me the select gave me what I needed, but anyone else reading this thread should read it twice ;)
Radovan Skolnik
Honored Contributor

Re: SQL showing all related CI's for HPOVSD 4.5

Oh well. What replacement are you thinking about that should make a better alternative?
Jan Gunnar Helg
Senior Member

Re: SQL showing all related CI's for HPOVSD 4.5

Well as we are in the middle of a candidate process selection and I have my full name in this forum I rather not give any detailed info ;)

What I can say that we have looked at and will continue looking at

Remedy
HP Service Center
SupportWorks (Hornbill)

Also had a look at Prolin Smart Client 7 to replace HPOVSD 4.5 GUI, but to be honest it's far from Production quality yet. But looking forward to seeing new versions from them :) Might be a good alternative until we move on to another product (which can take years)
Radovan Skolnik
Honored Contributor

Re: SQL showing all related CI's for HPOVSD 4.5

Regarding Remedy and Service Manager: in case you're accustomed to be able to do various customization tasks in SD I advise trying to do similar in any of these (or having the vendor/integrator show some of it on your demand). You will find that some seemingly easy tasks require a lot of programming work. Both of these platforms require skilled developers and reversers to be able to decipher existing functionality, be able to design required functionality, assess (and counteract) impact to various (seemingly unrelated) areas of the product. Also do not forget to have these guys handy in case you want to upgrade the platform - you will usually need to redo all the changes from scratch as you need to re-analyse changed stuff, re-design it, re-assess the impact, ... I hope you get the idea...
Jan Gunnar Helg
Senior Member

Re: SQL showing all related CI's for HPOVSD 4.5

Oh yes I know so very well :|

Right now as mentioned we are only in a candidate selection stage, the real challenge/headache comes when we delve into the functionality of the selected candidates products and how they fit into our integrations.

I am sure we are in for some big surprises!
My company is definitely not one that can make use of "out of the box" software.

Thankfully we have great internal expertise in almost all areas, so we seldom have to rely on costly external consultants to "hold our hand" when dealing with such solutions.

PS: The above list of candidates are not final, and may be subject to change and additions :) (trying to be unbiased here, hehe)

//Add this to "OnDomLoad" event