Nagios Support Forum

Code: Select all

# Create "nagiosxi" default database schema

CREATE DATABASE `nagiosxi`;
CREATE USER 'nagiosxi'@'localhost' IDENTIFIED BY 'n@gweb';
GRANT ALL ON nagiosxi.* TO 'nagiosxi'@'localhost';
USE `nagiosxi`;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_auditlog` (
    `auditlog_id` int auto_increment,
    `log_time` timestamp,
    `source` text,
    `user` varchar(200),
    `type` int,
    `message` text,
    `ip_address` varchar(45),
    `details` text,
    primary key (`auditlog_id`),
    index using btree (`log_time`),
    index using btree (`user`),
    index using btree (`type`),
    index using btree (`ip_address`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_commands` (
    `command_id` int auto_increment,
    `group_id` int default 0,
    `submitter_id` int default 0,
    `beneficiary_id` int default 0,
    `command` int NOT NULL,
    `submission_time` timestamp not null,
    `event_time` timestamp not null,
    `frequency_type` int default 0,
    `frequency_units` int default 0,
    `frequency_interval` int default 0,
    `processing_time` timestamp,
    `status_code` int default 0,
    `result_code` int default 0,
    `command_data` text,
    `result` text,
    primary key (`command_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_events` (
    `event_id` int auto_increment,
    `event_time` timestamp,
    `event_source` smallint,
    `event_type` smallint default 0 not null,
    `status_code` smallint default 0 not null,
    `processing_time` timestamp,
    primary key (`event_id`),
    index using btree (`event_source`),
    index using btree (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_meta` (
    `meta_id` int auto_increment,
    `metatype_id` int default 0,
    `metaobj_id` int default 0,
    `keyname` varchar(128) not null,
    `keyvalue` text,
    primary key (`meta_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_options` (
    `option_id` int auto_increment,
    `name` varchar(128) not null,
    `value` text,
    primary key (`option_id`),
    index using btree (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_sysstat` (
    `sysstat_id` int auto_increment,
    `metric` varchar(128) not null,
    `value` text,
    `update_time` timestamp not null,
    primary key (`sysstat_id`),
    index using btree (`metric`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_usermeta` (
    `usermeta_id` int auto_increment,
    `user_id` int not null,
    `keyname` varchar(255) not null,
    `keyvalue` longtext,
    `autoload` smallint default 0,
    primary key (`usermeta_id`),
    index using btree (`autoload`),
    constraint `user_unique_key` unique (`user_id`, `keyname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_users` (
    `user_id` int auto_increment,
    `username` varchar(64) not null,
    `password` varchar(128) not null,
    `name` varchar(100),
    `email` varchar(128) not null,
    `backend_ticket` varchar(128),
    `enabled` smallint default 1,
    primary key (`user_id`),
    unique(`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_eventqueue` (
    `eventqueue_id` int auto_increment,
    `event_time` timestamp,
    `event_source` smallint,
    `event_type` smallint default 0 not null,
    `event_meta` text,
    primary key (`eventqueue_id`),
    unique(`eventqueue_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;USE `nagiosxi`;

ALTER TABLE xi_usermeta MODIFY keyvalue LONGTEXT;

ALTER TABLE xi_users ADD COLUMN api_key varchar(128) NULL;
ALTER TABLE xi_users ADD COLUMN api_enabled smallint DEFAULT 0 NOT NULL;

UPDATE xi_users SET api_enabled = 1, api_key = backend_ticket;

# Account security features
ALTER TABLE xi_users ADD COLUMN login_attempts smallint(6) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_attempt int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_password_change int(10) DEFAULT 0 NOT NULL;

# Security information
ALTER TABLE xi_users ADD COLUMN last_login int(11) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_edited int(11) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_edited_by int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN created_by int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN created_time int(11) DEFAULT 0 NOT NULL;

# Event Queue table for more efficient Global Event Handlers
CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_eventqueue` (
    `eventqueue_id` int auto_increment,
    `event_time` int,
    `event_source` smallint,
    `event_type` smallint default 0 not null,
    `event_meta` text,
    primary key (`eventqueue_id`),
    unique(`eventqueue_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Code: Select all

php pg2mysql_cli.php nagiosxi.psql nagiosxi.mysql myisam
Filesize: 38.5M
PHP Notice:  Undefined offset: 24 in /usr/local/src/Testing/PG-2-MySQL/pg2mysql-master/pg2mysql.inc.php on line 336
Reading    progress:  70%   position:   27.0M   line:     90000   sql chunk:        17  mem usage:   43M

Completed!     99389 lines          17 sql chunks

Notes:
 - No its not perfect
 - Yes it discards ALL stored procedures
 - Yes it discards ALL queries except for CREATE TABLE and INSERT INTO
 - Yes you can email us suggestsions: info[AT]lightbox.org
    - In emails, please include the Postgres code, and the expected MySQL code
 - If you're having problems creating your postgres dump, make sure you use "--format p --inserts"
 - Default output engine if not specified is MyISAM
(Linux 2.6.32-642.6.2.el6.x86_64)nagios@crulnxicore01:/usr/local/src/Testing/PG-2-MySQL/pg2mysql-master
!> ll
total 39432
drwxr-xr-x 2 nagios users     4096 Dec  7 11:45 .
drwxr-xr-x 3 nagios users     4096 Dec  7 11:44 ..
-rw-r--r-- 1 nagios users    15145 Feb 21  2015 gpl.txt
-rw-r--r-- 1 nagios users     2751 Dec  7 11:45 nagiosxi.mysql
-rw-r--r-- 1 nagios users 40318590 Dec  7 11:44 nagiosxi.psql
-rw-r--r-- 1 nagios users     1534 Feb 21  2015 pg2mysql_cli.php
-rw-r--r-- 1 nagios users    13189 Feb 21  2015 pg2mysql.inc.php
-rw-r--r-- 1 nagios users     2370 Feb 21  2015 pg2mysql.php
-rw-r--r-- 1 nagios users     2985 Feb 21  2015 README