ITSM fields in database

[fondofotrs]

hi,
i tried looking for category, impact, priority, state and criticality fields of Change requests in database; however, i am not able to find them. for instance, i can see change_item table has changes; but how do i know what values are represented by category_id, impact_id etc? any help on this is appreciated.

[StefanoBoccanera]

hi
you have to use the integre values found into change_item fields (change_state_id, impact_id,priority_id, category_id) like a key to identify the requested value.
For example to find the change state, use the value in change_state_id (saying for example 48) and you'll find the row with id=48 in general_catalog table.
The value in name field is the change state showed into the OTRS ITSM views.

Following an exatrct from a simple select statement (select id, general_catalog_class,name from general_catalog where id=48)

id general_catalog_class name
48 | ITSM::ChangeManagement::Change::State | pending approval

regards

Stefano

[chetannagaonkar]

thank you Stefano. i can find the state id but cannot find impact, criticality or priority id. i have checked in all tables but i don't see them in any tables. do you see the tables for priority id, category id etc? there are ones for ticket, but what for change?

[fondofotrs]

nobody? these fields should be stored somewhere in the database, don't they?

[crythias]

According to http://source.otrs.org/viewvc.cgi/ITSM/ ... iew=markup, the database table "change_item" has this:

Code: Select all

 <TableCreate Type="post" Name="change_item">
710	            <Column AutoIncrement="true" Name="id" PrimaryKey="true" Required="true" Type="BIGINT"></Column>
711	            <Column Name="change_number" Required="true" Size="100" Type="VARCHAR"></Column>
712	            <Column Name="title" Required="false" Size="250" Type="VARCHAR"></Column>
713	            <Column Name="description" Required="false" Size="3800" Type="VARCHAR"></Column>
714	            <Column Name="description_plain" Required="false" Size="3800" Type="VARCHAR"></Column>
715	            <Column Name="justification" Required="false" Size="3800" Type="VARCHAR"></Column>
716	            <Column Name="justification_plain" Required="false" Size="3800" Type="VARCHAR"></Column>
717	            <Column Name="change_state_id" Required="true" Type="INTEGER"></Column>
718	            <Column Name="change_manager_id" Required="false" Type="INTEGER"></Column>
719	            <Column Name="change_builder_id" Required="true" Type="INTEGER"></Column>
720	            <Column Name="category_id" Required="true" Type="INTEGER"></Column>
721	            <Column Name="impact_id" Required="true" Type="INTEGER"></Column>
722	            <Column Name="priority_id" Required="true" Type="INTEGER"></Column>
723	            <Column Name="requested_time" Required="false" Type="DATE"></Column>
724	            <Column Name="create_time" Required="true" Type="DATE"></Column>
725	            <Column Name="create_by" Required="true" Type="INTEGER"></Column>
726	            <Column Name="change_time" Required="true" Type="DATE"></Column>
727	            <Column Name="change_by" Required="true" Type="INTEGER"></Column>
728	            <Unique Name="change_item_change_number">
729	                                <UniqueColumn Name="change_number"></UniqueColumn>

[fondofotrs]

thank you, crythias. although the category id, priority id etc are listed there, to find the relevant categories, priorities, they would be defined somewhere, right? i don't see the relevant tables that have mappings of category id and category, priority id and priority etc.

[StefanoBoccanera]

The relevant info about category, impact, criticality can be found into the general_catalog table and modified from the admin panel.
The 'Change' fiedl will contain only the 'id' from the row og general_catalog table.

Attached you'll find an output from general_catalog table with the 'sql' command used to generate it.

Hope it can help you.

best regards

stefano

[fondofotrs]

great. thank you for the detailed answer, Stefano.