George Menegakis (все сообщения)

Hello,

During the good old days of OVSD I have writen some queries about OVSD structure that made my life easier. Maybe you fild is useful too.

Queries are writen on T/SQL so if you are using Oracle as DB, you may have to made some changes (eg adding schema name before the tables).

Fields Report (*): This query will return information about fields. You must replace 1033 with your language locale

Цитата

sel ect ent.ent_name, lal.lal_text as 'Label', (lower (tab.tab_acronym) + '.' + col.col_name) as 'column', tab.tab_name, atr.atr_name, atr.atr_computed as 'computed?', atr.atr_customfield as 'custom?', atr.atr_custfieldact as 'activated?' ,att.att_name as 'type', ent.ent_name as parent_ent, (lower (tab2.tab_acronym) + '.' + col2.col_name)as 'column', tab2.tab_name fr om ifc_columns col inner join ifc_tables tab on col.col_tab_oid=tab.tab_oid inner join ifc_attributes atr on col.col_atr_oid=atr.atr_oid inner join ifc_attrib_types att on att.att_oid=atr.atr_att_oid inner join rep_labels_locale lal on atr.atr_lbl_oid=lal.lal_lbl_oid inner join ifc_entities ent on col.col_ent_ref=ent.ent_oid inner join ifc_entities ent2 on atr.atr_ent_oid=ent2.ent_oid left outer join ifc_columns col2 on col.rel_col_to=col2.col_oid left outer join ifc_tables tab2 on col2.col_tab_oid=tab2.tab_oid where lal.lal_lng_oid=1033 order by ent.ent_name asc

Fields report for Service Call (by replacing table names you can get information about other entities). Please have in mind that this query won't list 4K fields, unless you include the table names in the where clause. Replace 1033 with your language locale. Modify the where clause if you want to list the fields of another entity (Eg incident) with the appropriate table names.

Цитата

sel ect ent.ENT_NAME as 'Entity Parent Type', attr.ATR_OID as 'OID', lal.LAL_TEXT as 'Label', col.COL_NAME as 'Column Name', tab.TAB_NAME as 'Table Name', col2.COL_NAME as 'Referenced to', tab2.TAB_NAME as 'To Table', att.ATT_NAME as 'Field Type', attr.ATR_CUSTOMFIELD as 'Is Custom?', attr.ATR_CUSTFIELDACT as 'Is Activated?', uit.UIT_NAME as 'Presented As' fr om IFC_ENTITIES ent inner join IFC_ATTRIBUTES attr on ent.ENT_OID=attr.ATR_ENT_OID inner join IFC_COLUMNS col on attr.ATR_OID=col.COL_ATR_OID inner join IFC_TABLES tab on col.COL_TAB_OID=tab.TAB_OID inner join rep_labels_locale lal on attr.ATR_LBL_OID=lal.LAL_LBL_OID left outer join IFC_COLUMNS col2 on col2.COL_OID=col.REL_COL_TO left outer join IFC_TABLES tab2 on col2.COL_TAB_OID=tab2.TAB_OID left outer join IFC_ATTRIB_TYPES att on att.ATT_OID=attr.ATR_ATT_OID left outer join IFC_ATTRIBUTE_UI_TYPES uit on uit.UIT_OID=att.ATT_OID wh ere tab.TAB_NAME in ('itsm_servicecalls', 'itsm_ser_custom_fields', 'itsm_ser_cft001') and lal.LAL_LNG_OID=1033

Report on mandatory fields per entity. Replace 1033 with your language locale

Цитата

sel ect ent.ent_name as 'Entity', atr.atr_name as 'Attribute', rct.rct_name as 'Status' from rep_attr_per_status aps inner join ifc_attributes atr on aps.aps_atr_oid=atr.atr_oid inner join ifc_entities ent on atr.atr_ent_oid=ent.ent_oid inner join rep_codes rcd on aps.aps_cod_oid=rcd.rcd_oid inner join rep_codes_text rct on rcd.rcd_oid=rct.rct_rcd_oid wh ere rct.rct_lng_oid=1033 order by ent.ent_oid,rcd.rcd_ordering, aps.aps_atr_oid asc

OVSD 5.x - Report on Service Event Relations. If you are using oracle you must definately change the report. Replace + with || and str() with the PL/SQL equivalent.

Цитата

select ent_from.ent_name as 'Fr om 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 'Fr om 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' fr om 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

I will try to dig my archive in order to find more of these report in hope that you may find them useful.

LONG LIVE OV Service Desk.