Nagios Support Forum

Posted: **Fri Feb 14, 2014 4:54 am**

Hi Team,

Do we have any document regarding mysql database table information.

I want to know what are the things keeping in every tables which is in mysql databases with decsription.

Posted: **Fri Feb 14, 2014 10:59 am**

We generally don't provide that sort of information for a few reasons:

1.) It is very easy to break things in XI if you make a mistake in MySQL
2.) When things do break, it is very difficult for us to fix it
3.) Our database table names and columns are fairly self-documenting

For the nagiosql MySQL db you can issue the following commands to get a good layout:

Code: Select all

use nagiosql;
show tables;
describe tbl_host;

This should output something like the following:

Code: Select all

+------------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field                        | Type                | Null | Key | Default           | Extra                       |
+------------------------------+---------------------+------+-----+-------------------+-----------------------------+
| id                           | int(10) unsigned    | NO   | PRI | NULL              | auto_increment              |
| host_name                    | varchar(255)        | NO   | MUL | NULL              |                             |
| alias                        | varchar(255)        | NO   |     | NULL              |                             |
| display_name                 | varchar(255)        | YES  |     |                   |                             |
| address                      | varchar(255)        | NO   |     | NULL              |                             |
| parents                      | tinyint(3) unsigned | NO   |     | 0                 |                             |
| parents_tploptions           | tinyint(3) unsigned | NO   |     | 2                 |                             |
| hostgroups                   | tinyint(3) unsigned | NO   |     | 0                 |                             |
| hostgroups_tploptions        | tinyint(3) unsigned | NO   |     | 2                 |                             |
| check_command                | text                | YES  |     | NULL              |                             |
| use_template                 | tinyint(3) unsigned | NO   |     | 0                 |                             |
| use_template_tploptions      | tinyint(3) unsigned | NO   |     | 2                 |                             |
| initial_state                | varchar(20)         | YES  |     |                   |                             |
| max_check_attempts           | int(11)             | YES  |     | NULL              |                             |
| check_interval               | int(11)             | YES  |     | NULL              |                             |
| retry_interval               | int(11)             | YES  |     | NULL              |                             |
| active_checks_enabled        | tinyint(3) unsigned | NO   |     | 2                 |                             |
| passive_checks_enabled       | tinyint(3) unsigned | NO   |     | 2                 |                             |
| check_period                 | int(11)             | NO   |     | 0                 |                             |
| obsess_over_host             | tinyint(3) unsigned | NO   |     | 2                 |                             |
| check_freshness              | tinyint(3) unsigned | NO   |     | 2                 |                             |
| freshness_threshold          | int(11)             | YES  |     | NULL              |                             |
| event_handler                | int(11)             | NO   |     | 0                 |                             |
| event_handler_enabled        | tinyint(3) unsigned | NO   |     | 2                 |                             |
| low_flap_threshold           | int(11)             | YES  |     | NULL              |                             |
| high_flap_threshold          | int(11)             | YES  |     | NULL              |                             |
| flap_detection_enabled       | tinyint(3) unsigned | NO   |     | 2                 |                             |
| flap_detection_options       | varchar(20)         | YES  |     |                   |                             |
| process_perf_data            | tinyint(3) unsigned | NO   |     | 2                 |                             |
| retain_status_information    | tinyint(3) unsigned | NO   |     | 2                 |                             |
| retain_nonstatus_information | tinyint(3) unsigned | NO   |     | 2                 |                             |
| contacts                     | tinyint(3) unsigned | NO   |     | 0                 |                             |
| contacts_tploptions          | tinyint(3) unsigned | NO   |     | 2                 |                             |
| contact_groups               | tinyint(3) unsigned | NO   |     | 0                 |                             |
| contact_groups_tploptions    | tinyint(3) unsigned | NO   |     | 2                 |                             |
| notification_interval        | int(11)             | YES  |     | NULL              |                             |
| notification_period          | int(11)             | NO   |     | 0                 |                             |
| first_notification_delay     | int(11)             | YES  |     | NULL              |                             |
| notification_options         | varchar(20)         | YES  |     |                   |                             |
| notifications_enabled        | tinyint(3) unsigned | NO   |     | 2                 |                             |
| stalking_options             | varchar(20)         | YES  |     |                   |                             |
| notes                        | varchar(255)        | YES  |     |                   |                             |
| notes_url                    | varchar(255)        | YES  |     |                   |                             |
| action_url                   | varchar(255)        | YES  |     |                   |                             |
| icon_image                   | varchar(255)        | YES  |     |                   |                             |
| icon_image_alt               | varchar(255)        | YES  |     |                   |                             |
| vrml_image                   | varchar(255)        | YES  |     |                   |                             |
| statusmap_image              | varchar(255)        | YES  |     |                   |                             |
| 2d_coords                    | varchar(255)        | YES  |     |                   |                             |
| 3d_coords                    | varchar(255)        | YES  |     |                   |                             |
| use_variables                | tinyint(3) unsigned | NO   |     | 0                 |                             |
| name                         | varchar(255)        | NO   |     | NULL              |                             |
| active                       | enum('0','1')       | NO   |     | 1                 |                             |
| last_modified                | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| access_rights                | varchar(8)          | YES  |     | NULL              |                             |
| config_id                    | tinyint(3) unsigned | NO   |     | 0                 |                             |
+------------------------------+---------------------+------+-----+-------------------+-----------------------------+

This is just for one table in one database, but it should give you a good idea of how things are organized.

Posted: **Fri Feb 14, 2014 11:15 am**

We do not have public documentation concerning the tables. I can tell you the following:

There are 2 mysql databases:
1) nagiosql - object configuration database
2) nagios - historical object status database

There is also 1 postgres database:
1) nagiosxi - XI specific settings including xi user configuration (views, dashboards, notification settings, etc)

The mysql databases are highly relational, so queries tend to include multiple inner joins. You are welcome to peruse the tables, but I would suggest you exert caution when creating queries.

Posted: **Fri Feb 14, 2014 1:20 pm**

Hi Team,

Thank you very much:

Actually we are looking for this table information for specific reason.
We have three types of customers
1. Enterprise customers
2. Standard customers
3. Normal customers

We want to store database 1 Year, 6 months and 3 month for above list of customers.

we have 1000 systems for Enterprise customers and want to store database for report graphs and logs 1 year minimum, and apply same for others.

Is there any way to store database different-2 period of time.

apart from other or more than 1 year data should not be available in mysql database for Enterprise customers
more than 6 months of data should not be available for standard customers in Mysql database
and more than 3 months of data also should not be available in MYSQL database

Is there any possibility to trim data like mentioned above......and if yes, HOW?

Posted: **Fri Feb 14, 2014 2:50 pm**

Hi Team,
every time database tables are corrupting notification and logentries mostly.
What are the reason behind this. Every time we need to start ndo2db service manualy, due to this issue monitoring engine stopped and checks are not showing in monitoring engine status.

Why every time ndo2db needs to be restart manually.

Posted: **Fri Feb 14, 2014 2:56 pm**

In regards to your first question, are you looking to provide backups or historical information? We don't really apply timestamps the the database entries for object configs so you would need to make a backup of the database. Backing up historical data can be managed the same way.

As for your second question, we will need you to be a bit more specific with what you mean by "every time".

Posted: **Fri Feb 14, 2014 3:09 pm**

Sorry ....

First of all i want to complete my first query...

I want to reduce my database (tables) size, and i dont want to put unnecessary data in to mysql database.

As of now we are using 3500 hosts with 22000 services and its generating huge data entries. its OK for now but for future purpose i need to trim database entries but device wise ..
like 1000 devices data retaintion period should be 1 year for enterprise customers.
850 devices data retaintion period should be 6 months only and some hosts for 3 months.

please let me know if you want more clarification......

My worries about database size , its size increased 1 GB in every 10-12 days....
so we are planing to trim data, customer specific,

Posted: **Mon Feb 17, 2014 11:15 am**

This is something that should be handled via our ticketing system once your other issues with the database are resolved, you are currently working with abrist over email on this issue and we need to get this resolved before any large changes are done to your database.

Nagios Support Forum

MySQL database table detail

MySQL database table detail

Re: MySQL database table detail

Re: MySQL database table detail

Re: MySQL database table detail

Re: MySQL database table detail

Re: MySQL database table detail

Re: MySQL database table detail

Re: MySQL database table detail