Database Model#

Most of the settings and contents are saved in a Database Management System (DBMS) by i-doit. MySQL or MariaDB are applied as DBMS. But how is the structure of the i-doit database model?

Basics#

Some of the over 400 tables of the tenant database are structured with help of a defined names scheme. For example, this is the reason why all i-doit tables have the prefix "isys_".

Other identifiers for CMDB categories are "cats" for specific categories and "catg" for global categories. They end with the English name of the relevant category and the suffix _list (for example, isys_catg_model_list, isys_catg_memory_list). The tables category accounts for 50% of the complete database. Another major part of the tables are dialog lists with which combo boxes are filled. Many of these special lists contain values about type and manufacturer and can be recognized because of their names, such as "type" or "manufacturer".

References (Foreign Keys)#

To avoid redundancies within the data scheme the i-doit databases are structured in a relational way and normalized according to the scheme of the third normal form. Foreign keys have a uniform designation (see also the paragraph for "Name Scheme") so that you can recognize them immediately.

Name Scheme#

Besides the designation of the single tables, there is a consistent concept for names which runs through all field designations of the i-doit tables. It starts with the name of the table, followed by the actual name of the column. For example, the id field of the table isys_catg_cpu_list is called:

isys_catg_cpu_list__id
    /\              /\
 Table CPU            Column (ID)
isys_catg_cpu_list__title
    /\               /\
 Table CPU            Column(title)

Correspondingly, references to other fields (foreign keys) follow a similar principle. A reference always contains the complete name of the referenced column, so you can always see to which table column the reference points by means of the name.

Example object reference of a category:

isys_catg_cpu_list__isys_obj__id
/\                 /\      /\
Table CPU      Table Obj.    Column

Objects#

Each object is a data record in the isys_obj table. Characteristics, such as the object type (servers, clients, routers etc.), creation date, status (normal, archived, deleted) and some additional attributes, are recorded in this table. The determination of the object type is carried out through referencing of the table isys_obj_type in which the respective characteristics and location (infrastructure, software etc.) are saved.

Global and Specific Categories#

Each category is reflected in the database as individual table and is in a 1:n relation to the object. As a basic principle there is a distinction between single values and lists. Single values are simple entries (for example, accounting or form factor) and only one entry per object exists in the table. On the other hand, lists enable multiple entries (CPU, memory, ports ...). In each data record of the global category there is a reference to a single object.

Example for the structure of the global category for a graphic card and global category for IP addresses:

isys_catg_graphic_list is above the foreign key isys_catg_graphic_list__isys_obj__id in relation to the object. Through the same scheme the IP address is in relation to the object:
isys_catg_ip_list contains the foreign key isys_catg_ip_list__isys_obj__id.

This results in the following SQL statement for the determination of all objects, their graphic cards and IP details:

SELECT
  isys_obj__title,
  isys_catg_graphic_list__title,
  isys_catg_graphic_list__memory,
  isys_memory_unit__title,
  isys_catg_ip_list__hostname,
  isys_catg_ip_list__address
FROM isys_obj
INNER JOIN isys_catg_graphic_list
ON isys_catg_graphic_list__isys_obj__id = isys_obj__id
INNER JOIN isys_memory_unit
ON isys_catg_graphic_list__isys_memory_unit__id = isys_memory_unit__id
INNER JOIN isys_catg_ip_list
ON isys_catg_ip_list__isys_obj__id = isys_obj__id

The same also applies to specific categories. A query for all net details of all net objects looks as follows:

SELECT
  isys_obj__title,
  isys_cats_net_list__address,
  isys_cats_net_list__dhcp_range_from,
  isys_cats_net_list__dhcp_range_to,
  isys_cats_net_list__dhcp,
  isys_cats_net_list__def_gw
FROM
isys_cats_net_list
INNER JOIN isys_obj
ON isys_cats_net_list__isys_obj__id = isys_obj__id

Diagram: Relation between categories and objects

**

In the diagram you can see how an object is linked to the global category CPU. This category again accesses Dialog+ entries, such as manufacturer and type. The same happens with the global category Form factor which accesses the form factor type.

This diagram illustrates the relations of an object to the category "Host address" (IP). The object (for example, Server1) is in the the table isys_obj, the allocated IP addresses are in the table isys_catg_ip_list. Because of the detailed level of characteristics of the host address three additional tables are referenced beside the basic details (hostname, address ...):

isys_connection, containing the connection to the net object
isys_ip_assignment, determining the form of assignment of the IP (DHCP, static)
isys_net_type, providing the net type

Users, Contacts, Persons, Person Groups#

Contacts, which may consist both of internal users and external persons in i-doit, are recorded in the table isys_obj with the respective object types. Internal users differ from external "contacts" only by the filling of content with i-doit authentication parameters (such as user name or password) in the table isys_cats_person_list. Groups (and right groups) are saved in isys_obj by the system_._

Logbook#

The logbook consists of entries in the table isys_logbook. This table again has some references to other tables. Important are the following tables:

isys_logbook_archive (archived logbook entries)
isys_logbook_event (events, such as created, changed or deleted)
isys_logbook_level (alarm level for coloured presentation in the logbook and priority)
isys_logbook_source (where was the event triggered, for example, internal or external source, Nagios etc.)

Overview of the Table#

CMDB categories

isysgui_catg
isysgui_cats
isysgui_custom

Dialog table for accounting (isys_catg_account_list)

isys_account

Tables for cable connections

isys_cable_connection
isys_cable_type

Global categories

isys_catg_access_list
isys_catg_accounting_list
isys_catg_application_list
isys_catg_backup_list
isys_catg_connector_list
isys_catg_contact_list
isys_catg_controller_list
isys_catg_cpu_frequency
isys_catg_cpu_list
isys_catg_distributor
isys_catg_drive_list
isys_catg_emergency_plan_list
isys_catg_fc_port_list
isys_catg_file_list
isys_catg_formfactor_list
isys_catg_global_category
isys_catg_global_list
isys_catg_graphic_list
isys_catg_idic_list
isys_catg_image_list
isys_catg_ip_list
isys_catg_ip_list_2_isys_catg_port_list
isys_catg_ip_list_2_isys_netp_ifacel
isys_catg_lic_list
isys_catg_location_list
isys_catg_logb_list
isys_catg_maintenance_list
isys_catg_manual_list
isys_catg_memory_list
isys_catg_model_list
isys_catg_nagios
isys_catg_nagios_list
isys_catg_netp_list
isys_catg_netv
isys_catg_netv_list
isys_catg_net_type_list
isys_catg_odep_list
isys_catg_overview_list
isys_catg_port_list
isys_catg_port_list_2_isys_netp_con
isys_catg_port_list_2_isys_netp_ifacel
isys_catg_power_consumer_list
isys_catg_sanpool_list
isys_catg_sound_list
isys_catg_stor_list
isys_catg_ui_list
isys_catg_version_list
isys_catg_virtual_list
isys_catg_virtual_machine_list
isys_catg_workflow_list

Specific categories

isys_cats_access_point_list
isys_cats_ac_list
isys_cats_application_list
isys_cats_building_list
isys_cats_client_list
isys_cats_cp_contract_list
isys_cats_distributor
isys_cats_emergency_plan_list
isys_cats_enclosure_list
isys_cats_file_list
isys_cats_group_list
isys_cats_lic_group
isys_cats_lic_item
isys_cats_lic_list
isys_cats_location_generic_list
isys_cats_maintenance_list
isys_cats_monitor_list
isys_cats_net_list
isys_cats_pobj_list
isys_cats_prt_emulation
isys_cats_prt_list
isys_cats_prt_paper
isys_cats_room_list
isys_cats_router_list
isys_cats_san_list
isys_cats_service_list
isys_cats_switch_fc_list
isys_cats_switch_net_list
isys_cats_tapelib_list
isys_cats_wan_list
isys_cats_ws_net_type_list

Connections between objects and categories

isys_connection

Contact references

isys_contact
isys_contact_data_item
isys_contact_type

Object combinations

isys_container

Dialog: Type of the mobile contract

isys_cp_contract_type

Information about version and revision of the i-doit database

isys_db_init

Dependencies, type of dependency

isys_dependency
isys_dep_type

Connection table for drive to storage

isys_drive_list_2_stor_list

Export templates

isys_export

Dialog: FC port medium

isys_fc_port_medium

Files

isys_file_category (category)
isys_file_group (group)
isys_file_physical (path to physical file)
isys_file_type (file types)

Group administration

isys_group

Connection table of the rights groups to modules, objects, contacts or user sessions

isys_group_2_isys_module
isys_group_2_isys_obj
isys_group_2_isys_person_intern
isys_group_2_isys_user_session

Dialog: IP assignment

isys_ip_assignment

LDAP configuration

isys_ldap (connection to LDAP server)
isys_ldap_directory (directory types)

T

isys_location_image

Registration of locked objects (objects in process)

isys_lock

Logbook

isys_logbook
isys_logbook_archive
isys_logbook_event
isys_logbook_event_class
isys_logbook_lc_parameter
isys_logbook_level
isys_logbook_source

Dialog: Reaction time for maintenance contracts

isys_maintenance_reaction_rate

Dialog: Title of the working memory

isys_memory_title

Dialog: Title of the model

isys_model_title

Module administration

isys_module

Monitor

isys_monitor_resolution
isys_monitor_unit

Tables for the Nagios configuration

isys_nagios_commands
isys_nagios_config
isys_nagios_contacts
isys_nagios_contact_groups
isys_nagios_dependency
isys_nagios_host_escalations
isys_nagios_nagioshosts
isys_nagios_ndo
isys_nagios_service
isys_nagios_service_escalations
isys_nagios_timeperiods

Dialogs for specific category Net (Layer 3-Net)

isys_net_dns_domain
isys_net_dns_server
isys_net_type
isys_net_type_title

Table for CMDB objects (configuration items)

isys_obj

Table for CMDB object types (CI types)

isys_obj_type

Object type group and connection table of object types to global categories

isys_obj_type_2_isysgui_catg
isys_obj_type_2_isysgui_catg_overview
isys_obj_type_group

Table for organisations

isys_organisation_intern_iop

Table for contacts

isys_person_extern
isys_person_intern
isys_person_intern_iop

Port dialogs

isys_port_duplex
isys_port_negotiation
isys_port_speed
isys_port_standard

Power connection

isys_power_con
isys_power_female_socket
isys_power_fuse_ampere
isys_power_male_plug

Category General: Intended purpose

isys_purpose

Role and rights administration

isys_right
isys_right_2_isys_role
isys_role

SAN capacity unit

isys_san_capacity_unit

Search

isys_search

Service

isys_service_manufacturer

Raid level

isys_stor_raid_level

Tape library

isys_tapelib_type

Object tree groups (infrastructure, software, others)

isys_tree_group

Time unit

isys_unit_of_time

User locales, sessions and settings

isys_user_locale
isys_user_mydoit
isys_user_session
isys_user_setting
isys_user_ui

WAN

isys_wan_capacity_unit
isys_wan_role
isys_wan_type

Wifi specifications

isys_wf_type_2_wf_tp
isys_wlan_auth
isys_wlan_channel
isys_wlan_encryption
isys_wlan_function
isys_wlan_standard

Tables for workflow management

isys_workflow
isys_workflow_2_isys_workflow_action
isys_workflow_action
isys_workflow_action_parameter
isys_workflow_action_type
isys_workflow_category
isys_workflow_status
isys_workflow_template_parameter
isys_workflow_type

Temporary tables

temp_obj_data

Dialog-Plus Tables#

isys_ac_air_quantity_unit => Air-conditioning system: unit for air quantity
isys_ac_refrigerating_capacity_unit => Air-conditioning system: unit for cooling capacity
isys_guarantee_period_unit => Guarantee period: unit
isys_memory_unit => Memory: unit
isys_stor_unit => Storage: unit
isys_temp_unit => Temperature: unit
isys_ac_type => Air-conditioning system: type
isys_catd_drive_type => Drive: type
isys_catg_cpu_type => CPU: type
isys_catg_formfactor_type => Form factor
isys_cats_prt_type => Printer: type
isys_client_type => Client: type
isys_controller_type => Controller: type
isys_filesystem_type => File system
isys_memory_type => Memory: type
isys_monitor_type => Monitor: type
isys_port_type => Port: type
isys_power_fuse_type => Power object: fuse type
isys_power_connection_type => Power object: type of the connector
isys_room_type => Room: type
isys_stor_con_type => Storage: connection
isys_stor_type => Storage: device type
isys_ui_con_type => Interface: connection type
isys_ui_plugtype => Interface: plug type
isys_application_manufacturer => Applications: manufacturer
isys_catg_cpu_manufacturer => CPU: manufacturer
isys_controller_manufacturer => Controller: manufacturer
isys_graphic_manufacturer => Graphic cards: manufacturer
isys_memory_manufacturer => Memory: manufacturer
isys_model_manufacturer => Model: manufacturer
isys_sound_manufacturer => Sound cards: manufacturer
isys_stor_manufacturer => Storage: manufacturer
isys_controller_model => Controller: model
isys_stor_model => Storage: model
isys_depth_unit => Form factor: unit
isys_iface_manufacturer => Interface: manufacturer
isys_iface_model => Interface: model
isys_pc_manufacturer => Consumer: manufacturer
isys_pc_model => Consumer: model
isys_plug_type => Port: plug
isys_netx_ifacel_type => log. interface: type
isys_fc_port_type => FC port: type
isys_access_type => Access: access type