Solved: Columns when linking a Ticket to a CI

[RBehr]

When linking a Ticket to a Config Item, the columns are:

SELECT | INCIDENT STATE | CONFIGITEM# | NAME | DEPLOYMENT STATE | CREATED | LINKED AS

(a) Can I change these (especially Name, Deployment State and Created)? How?
(b) Can I specify different columns for different CI classes? How?

Thanks

[RBehr]

Any response to this would be gratefully appreciated.

[RBehr]

I have worked my way around this one. Thought I'd record my solutions here in case it assists anybody.

Obviously, please proceed with caution and ensure you have a solid knowledge of the Perl, HTML and SQL necessary for these changes. Use at own risk - you could easily break OTRS!

Because we use OTRS to monitor audiovisual tickets and assets, our Config Item records required fairly extensive re-definition. CI data is stored as XML within MySQL tables (I assume to handle the versioning of CI data), so getting at it isn't straightforward. Further, OTRS have written a module specifically for us so that CIs can be split by customer - a ticket for a customer can only link to CIs we manage on behalf of that customer, which filters the full list of CIs for search results.

I wanted to expose an additional six fields (columns) to the search results in the list of CIs offered on the list screen. Defining the CI class was carried out as normal. However, in order to expose these items on the list, I needed to amend the SQL in Version.pm. In our instance, this file is stored in opt/otrs/Custom/Kernel/System/ITSMConfigItem, however this is because this is a bespoke module. Because of the way CI data is stored in XML (in table xml_storage), querying this data direct from xml_storage and linking it to the primary CI data in configitem_version resulted in a query that took too long to execute.

I therefore run the following SQL to create a table which holds the CI id and the six fields I need to expose for the list at regular intervals (in our case, daily is sufficient as the CI database is not that regularly updated):

Code: Select all

select id, location, manufacturer, model, slaresponse, slaonsite, slarestoration
into im_configitem_extended
from configitem
left join
(
select loc.xml_key as drvTblid, loc.xml_content_value as location, man.xml_content_value as manufacturer, mdl.xml_content_value as model,
slaresponse.xml_content_value as slaresponse, slaonsite.xml_content_value as slaonsite, slarestoration.xml_content_value as slarestoration
from xml_storage as loc
join xml_storage as man on loc.xml_key = man.xml_key 
join xml_storage as mdl on loc.xml_key = mdl.xml_key 
join xml_storage as slaresponse on loc.xml_key = slaresponse.xml_key 
join xml_storage as slaonsite on loc.xml_key = slaonsite.xml_key 
join xml_storage as slarestoration on loc.xml_key = slarestoration.xml_key 
where loc.xml_content_key like ''%Location%'' and loc.xml_content_key like ''%Content%'' 
and man.xml_content_key like ''%{''''Manufacturer%'' and man.xml_content_key like ''%Content%'' 
and mdl.xml_content_key like ''%Model%'' and mdl.xml_content_key like ''%Content%'' 
and slaresponse.xml_content_key like ''%SLAResponse%'' and slaresponse.xml_content_key like ''%Content%'' 
and slaonsite.xml_content_key like ''%SLAAttendance%'' and slaonsite.xml_content_key like ''%Content%'' 
and slarestoration.xml_content_key like ''%SLARestoration%'' and slarestoration.xml_content_key like ''%Content%''
) as drvTbl 
on drvTbl.drvTblid = configitem.last_version_id

This goes into a table with the following definition:

Code: Select all

create table im_configitem_extended (
configitem_id bigint,
location varchar(250),
manufacturer varchar(250),
model varchar(250),
slaresponse varchar(250),
slaonsite varchar(250),
slarestoration varchar(250))

What this does is store the latest values I need against the CIs in a holding table which can be easily joined to via the configitem_id.

Now, in Version.pm, in sub VersionGet, change the get version SQL to the following:

Code: Select all

SQL => 'SELECT id, configitem_version.configitem_id, name, definition_id, '
                . 'depl_state_id, inci_state_id, create_time, create_by, '
                . 'location, manufacturer, model, slaresponse, slaonsite, slarestoration '
                . 'FROM configitem_version join im_configitem_extended on configitem_version.configitem_id = im_configitem_extended.configitem_id '
                . 'WHERE configitem_version.configitem_id = ? ORDER BY id DESC',

This is done for cached and non-cached data, the difference being the presence or absence of the WHERE clause.

Below, under "# fetch the result", add the references to the additional rows:

Code: Select all

        $Version{Manufacturer} = $Row[9];
        $Version{Model}        = $Row[10];
        $Version{Location}     = $Row[8];
        $Version{SLAResponse}  = $Row[11];
        $Version{SLAOnSite}    = $Row[12];
        $Version{SLARestoration}= $Row[13];

(There are other files which you may wish to change in the same way, such as sub CongifItemGet in opt/otrs/Kernel/System/ITMSConfigItem.pm, depending on where you may need to access your additional fields.)

Now, to expose these fields in the Link Item CI search result list, change sub TableCreateComplex in /opt/otrs/Kernel/Output/HTML/LinkObjectITSMConfigItem.pm by changing @ItemColumns:

Code: Select all

            my @ItemColumns = (
                {
                    Type             => 'CurInciSignal',
                    Key              => $ConfigItemID,
                    Content          => $Version->{CurInciState},
                    CurInciStateType => $Version->{CurInciStateType},
                },
                {
                    Type    => 'Link',
                    Content => $Version->{Name},
                    Link    => '$Env{"Baselink"}Action=AgentITSMConfigItemZoom;ConfigItemID='
                        . $ConfigItemID,
                },
                {
                    Type      => 'Text',
                    Content   => $Version->{Location},
                    MaxLength => 50,
                },
                {
                    Type      => 'Text',
                    Content   => $Version->{Manufacturer},
                    Translate => 1,
                },
                {
                    Type    => 'Text',
                    Content => $Version->{Model},
                },
                {
                    Type    => 'Text',
                    Content => $Version->{SLAResponse},
                },
                {
                    Type    => 'Text',
                    Content => $Version->{SLAOnSite},
                },
                {
                    Type    => 'Text',
                    Content => $Version->{SLARestoration},
                },
            );

(Note I have removed the column widths, because of the large number of columns my users insisted they wanted).

And change the columns headings immediately below:

Code: Select all

        # define the block data
        my %Block = (
            Object    => $Self->{ObjectData}->{Object},
            Blockname => $Self->{ObjectData}->{Realname} . ' (' . $Class . ')',
            Headline  => [
                {
                    Content => 'State',
                },
                {
                    Content => 'ConfigItem#',
                },
                {
                    Content => 'Location',
                },
                {
                    Content => 'Manufacturer',
                },
                {
                    Content => 'Model',
                },
                {
                    Content => 'Response SLA',
                },
                {
                    Content => 'On Site SLA',
                },
                {
                    Content => 'Restoration SLA',
                },
            ],

Done!

[RBehr]

Very important update!!!

Use LEFT JOIN in SQL statements (not JOIN as I indicate above)! Otherwise import and other routines cannot find new configuration items and will fail.