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

How do you report all relations between Service Calls and Incidents

Highlighted
-=R=-
Frequent Visitor

How do you report all relations between Service Calls and Incidents

Ok... so I've had this ticket open with support for 4 damn weeks and I can't get any movement.

How do you report the # of Requests "related" to Incidents. When I look at an Incident in my system (eg 675), it shows that there are 24 related Service Call. Meanwhile our Helpdesk agents say they've related about 100. Sure enough, if I do an advanced find from Service Call to find "TO" Service Events of #675, I get 70.

Tired of waiting, I decided to build a report to compensate for OVSD 5.1's design. Reporting off of Service_Event_Relations view I've just discovered that "to_id" and "from_id" holds at least BOTH Incidents and Service Request ID's at the exact same time.. and most likely Change and Problem ID's as well. There appears to be NOTHING in Service_Event_Relation that tells you if the given TO/FROM_ID is an SC, INC, PROB, or CHANGE.

Any ideas you guys and gals have would be greatly appreciated.
8 REPLIES
-=R=-
Frequent Visitor

Re: How do you report all relations between Service Calls and Incidents

Thought I'd give this one more bump before giving up.
George M. Meneg
Honored Contributor

Re: How do you report all relations between Service Calls and Incidents

Hello,

If it was OVSD 4.5 I would gladly helped you. But I'm not sure about the Service Event relations of OVSD 5.x!
menes fhtagn
-=R=-
Frequent Visitor

Re: How do you report all relations between Service Calls and Incidents

If you wouldn't mind... would you tell me the answer as if it were 4.5? If its even remotely similar it might help me track the answer down on my own...

... since HP Support just finished telling me reporting on Request/Incident linkages is NOT SUPPORTED.

George M. Meneg
Honored Contributor

Re: How do you report all relations between Service Calls and Incidents

OVSD 4.5 is completely different. In OVSD 5 the cdm_serv_evt_relations has eight columns:

sre_oid : The object id (oid) of the service event relation
sre_from: The oid of the from object
sre_from_ent_oid: The oid of the entity type of the from object
sre_lockseq: Tracking sequence.
sre_rty_oid: The oid of the type of the relation
sre_tem_oid: The oid of the template of the service event relation
sre_to: The oid of the to object
sre_to_ent_oid: The oid of the entity type of the to object.

So, to answer your question the sre_ent_from_oid and sre_ent_to_oid tells you the entity of the object.

However creating a query to get a full report would be a nightmare because it will need a conditional join depending on sre_ent_from_oid and sre_ent_to_oid

However if you don't mind nulls here is a query:

select ent_from.ent_name as 'From Entity',
str (xinc_from.xinc_id) + ' - ' + inc_from.inc_description as 'From Incident',
str (ser_from.ser_id) + ' - ' + ser_from.ser_description as 'From Service call',
str (prob_from.pro_id) + ' - ' + prob_from.pro_description as 'From Problem',
str (cha_from.cha_id) + ' - ' + cha_from.cha_description as 'From Change',
ent_to.ent_name as 'To Entity',
str (xinc_to.xinc_id) + ' - ' + inc_to.inc_description as 'To Incident',
str (ser_to.ser_id) + ' - ' + ser_to.ser_description as 'To Service call',
str (prob_to.pro_id) + ' - ' + prob_to.pro_description as 'To Problem',
str (cha_to.cha_id) + ' - ' + cha_to.cha_description as 'To Change'
from cdm_serv_evt_relations sre inner join ifc_entities ent_from on sre.sre_from_ent_oid=ent_from.ent_oid
inner join ifc_entities ent_to on sre.sre_to_ent_oid=ent_to.ent_oid
left outer join cdm_incidents inc_from on sre.sre_from = inc_from.inc_oid
left outer join cdm_incidents_x xinc_from on inc_from.inc_oid=xinc_from.xinc_inc_oid
left outer join sd_servicecalls ser_from on sre.sre_from=ser_from.ser_oid
left outer join sd_problems prob_from on sre.sre_from=prob_from.pro_oid
left outer join sd_changes cha_from on sre.sre_from=cha_from.cha_oid
left outer join cdm_incidents inc_to on sre.sre_to = inc_to.inc_oid
left outer join cdm_incidents_x xinc_to on inc_to.inc_oid=xinc_to.xinc_inc_oid
left outer join sd_servicecalls ser_to on sre.sre_to=ser_to.ser_oid
left outer join sd_problems prob_to on sre.sre_to=prob_to.pro_oid
left outer join sd_changes cha_to on sre.sre_to=cha_to.cha_oid

Please, don't forget to assign points if the answers to your question where helpful.
menes fhtagn
-=R=-
Frequent Visitor

Re: How do you report all relations between Service Calls and Incidents

Hey George,

I kicked you over some points for the info, but you're right... its completely different in 5.1 than 4.5. Right now I'm trying to get Crystal to pull from the IFC_entities table where the ent_oid = from_oid (in the service_event_relation) table.

Want to know the sad part? I'm only back engineering this piece of crap in Crystal because it won't show all relations in the OVSD tool. Go to the Incident and it ONLY shows the Service Requests related FROM the Incident form. When you go to Advanced Find for Service Calls and query how many SC's are related TO the same Incident, you get a completely different number.

"Yeah? So?" says support.

I'm at the end of my rope with these people.
George M. Meneg
Honored Contributor

Re: How do you report all relations between Service Calls and Incidents

Hello,

The query I posted above is for OVSD 5 and it works (i've tested it on a v5 system). It's not perfect but will fetch all the necessary information you need.
menes fhtagn
George M. Meneg
Honored Contributor

Re: How do you report all relations between Service Calls and Incidents

select ent_from.ent_name as 'From Entity',
case ent_from.ent_name
when 'Incident' then str (xinc_from.xinc_id) + ' - ' + inc_from.inc_description
when 'Service call' then str (ser_from.ser_id) + ' - ' + ser_from.ser_description
when 'Problem' then str (prob_from.pro_id) + ' - ' + prob_from.pro_description
when 'Change' then str (cha_from.cha_id) + ' - ' + cha_from.cha_description
else 'Not Recognized'
END as 'From Object',
ent_to.ent_name as 'To Entity',
case ent_to.ent_name
when 'Incident' then str (xinc_to.xinc_id) + ' - ' + inc_to.inc_description
when 'Service call' then str (ser_to.ser_id) + ' - ' + ser_to.ser_description
when 'Problem' then str (prob_to.pro_id) + ' - ' + prob_to.pro_description
when 'Change' then str (cha_to.cha_id) + ' - ' + cha_to.cha_description
else 'Not Recognized'
END as 'To Object'
from cdm_serv_evt_relations sre inner join ifc_entities ent_from on sre.sre_from_ent_oid=ent_from.ent_oid
inner join ifc_entities ent_to on sre.sre_to_ent_oid=ent_to.ent_oid
left outer join cdm_incidents inc_from on sre.sre_from = inc_from.inc_oid
left outer join cdm_incidents_x xinc_from on inc_from.inc_oid=xinc_from.xinc_inc_oid
left outer join sd_servicecalls ser_from on sre.sre_from=ser_from.ser_oid
left outer join sd_problems prob_from on sre.sre_from=prob_from.pro_oid
left outer join sd_changes cha_from on sre.sre_from=cha_from.cha_oid
left outer join cdm_incidents inc_to on sre.sre_to = inc_to.inc_oid
left outer join cdm_incidents_x xinc_to on inc_to.inc_oid=xinc_to.xinc_inc_oid
left outer join sd_servicecalls ser_to on sre.sre_to=ser_to.ser_oid
left outer join sd_problems prob_to on sre.sre_to=prob_to.pro_oid
left outer join sd_changes cha_to on sre.sre_to=cha_to.cha_oid

Hello,

this is a better query. It works on OVSD 5, and it returns 4 rows

Entity From, From Object, Entity To, To Object

the from object and to object contains the concatenation of item's ID with item's description.
menes fhtagn
Oscar U Diaz
Regular Collector

Re: How do you report all relations between Service Calls and Incidents

Hello R

Try sd_dataform.bat, I do not know if this command in SD5 there but SD 4.5 saw the service Calls relating to an incident, creates an intelligent action and uses these options:

sd_dataform.bat "Service call" Relation.Incident.ID [ID] = "-ListAlways

I hope this will help you.
//Add this to "OnDomLoad" event