vigiboard / bdd.sql @ 805cc54a
History | View | Annotate | Download (4.62 KB)
| 1 |
REATE TABLE IF NOT EXISTS `graph` ( |
|---|---|
| 2 |
`name` varchar(100) NOT NULL, |
| 3 |
`template` varchar(2500) NOT NULL, |
| 4 |
`vlabel` varchar(2500) NOT NULL, |
| 5 |
PRIMARY KEY (`name`) |
| 6 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 7 |
|
| 8 |
|
| 9 |
|
| 10 |
|
| 11 |
|
| 12 |
CREATE TABLE IF NOT EXISTS `graphgroups` ( |
| 13 |
`graphname` varchar(100) NOT NULL, |
| 14 |
`idgraphgroup` int(10) unsigned NOT NULL, |
| 15 |
`parent` int(10) unsigned NOT NULL, |
| 16 |
PRIMARY KEY (`graphname`,`idgraphgroup`), |
| 17 |
FOREIGN KEY (graphname) REFERENCES graph(name) |
| 18 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 19 |
|
| 20 |
|
| 21 |
|
| 22 |
|
| 23 |
|
| 24 |
|
| 25 |
CREATE TABLE IF NOT EXISTS `host` ( |
| 26 |
`name` varchar(255) NOT NULL, |
| 27 |
`checkhostcmd` varchar(255) NOT NULL, |
| 28 |
`community` varchar(255) NOT NULL, |
| 29 |
`fqhn` varchar(255) NOT NULL, |
| 30 |
`hosttpl` varchar(255) NOT NULL, |
| 31 |
`mainip` varchar(255) NOT NULL, |
| 32 |
`port` int(10) unsigned NOT NULL, |
| 33 |
`snmpoidsperpdu` int(10) unsigned DEFAULT NULL, |
| 34 |
`snmpversion` varchar(255) DEFAULT NULL, |
| 35 |
PRIMARY KEY (`name`) |
| 36 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
| 37 |
|
| 38 |
|
| 39 |
|
| 40 |
|
| 41 |
|
| 42 |
CREATE TABLE IF NOT EXISTS `service` ( |
| 43 |
`name` varchar(255) NOT NULL, |
| 44 |
`type` varchar(255) NOT NULL, |
| 45 |
`command` varchar(255) NOT NULL, |
| 46 |
PRIMARY KEY (`name`) |
| 47 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; |
| 48 |
|
| 49 |
|
| 50 |
|
| 51 |
|
| 52 |
|
| 53 |
CREATE TABLE IF NOT EXISTS `events` ( |
| 54 |
`idevent` int(10) unsigned NOT NULL AUTO_INCREMENT, |
| 55 |
`hostname` varchar(100) NOT NULL, |
| 56 |
`servicename` varchar(100) DEFAULT NULL, |
| 57 |
`service_source` varchar(100) NOT NULL, |
| 58 |
`severity` int(10) unsigned NOT NULL, |
| 59 |
`status` enum( 'None', 'Acknowledged', 'Closed' ) NOT NULL DEFAULT 'None', |
| 60 |
`active` bool DEFAULT TRUE, |
| 61 |
`timestamp` datetime DEFAULT NULL, |
| 62 |
`output` text NOT NULL, |
| 63 |
`event_timestamp` datetime DEFAULT NULL, |
| 64 |
`last_check` datetime DEFAULT NULL, |
| 65 |
`recover_output` text, |
| 66 |
`timestamp_active` datetime DEFAULT NULL, |
| 67 |
`timestamp_cleared` datetime DEFAULT NULL, |
| 68 |
`trouble_ticket` varchar(20) DEFAULT NULL, |
| 69 |
`occurence` int(10) unsigned DEFAULT NULL, |
| 70 |
PRIMARY KEY (`idevent`), |
| 71 |
FOREIGN KEY (`servicename`) REFERENCES service(name), |
| 72 |
FOREIGN KEY (`hostname`) REFERENCES host(name), |
| 73 |
INDEX (`hostname`,`servicename`) |
| 74 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; |
| 75 |
|
| 76 |
|
| 77 |
|
| 78 |
|
| 79 |
CREATE TABLE IF NOT EXISTS `event_history` ( |
| 80 |
`idhistory` int(10) unsigned NOT NULL AUTO_INCREMENT, |
| 81 |
`type_action` varchar(50) NOT NULL, |
| 82 |
`idevent` int(10) unsigned NOT NULL, |
| 83 |
`key` varchar(255) DEFAULT NULL, |
| 84 |
`value` text, |
| 85 |
`timestamp` datetime DEFAULT NULL, |
| 86 |
`username` varchar(255) DEFAULT NULL, |
| 87 |
PRIMARY KEY (`idhistory`), |
| 88 |
INDEX (`idevent`), |
| 89 |
FOREIGN KEY ( idevent) REFERENCES events(idevent) |
| 90 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; |
| 91 |
|
| 92 |
|
| 93 |
|
| 94 |
|
| 95 |
|
| 96 |
|
| 97 |
CREATE TABLE IF NOT EXISTS `groups` ( |
| 98 |
`name` varchar(100) NOT NULL, |
| 99 |
`parent` varchar(100) DEFAULT NULL, |
| 100 |
PRIMARY KEY (`name`), |
| 101 |
FOREIGN KEY (parent) REFERENCES groups(name) |
| 102 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 103 |
CREATE TABLE IF NOT EXISTS `grouppermissions` ( |
| 104 |
`groupname` varchar(100) NOT NULL, |
| 105 |
`idpermission` int(10) unsigned NOT NULL, |
| 106 |
FOREIGN KEY (groupname) REFERENCES groups(name), |
| 107 |
PRIMARY KEY (groupname,idpermission) |
| 108 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 109 |
|
| 110 |
|
| 111 |
|
| 112 |
|
| 113 |
|
| 114 |
|
| 115 |
CREATE TABLE IF NOT EXISTS `hostgroups` ( |
| 116 |
`hostname` varchar(100) NOT NULL, |
| 117 |
`groupname` varchar(100) NOT NULL, |
| 118 |
PRIMARY KEY (`hostname`,`groupname`), |
| 119 |
FOREIGN KEY (hostname) REFERENCES host(name), |
| 120 |
FOREIGN KEY (groupname) REFERENCES groups(name) |
| 121 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 122 |
|
| 123 |
|
| 124 |
|
| 125 |
|
| 126 |
|
| 127 |
CREATE TABLE IF NOT EXISTS `servicegroups` ( |
| 128 |
`servicename` varchar(100) NOT NULL, |
| 129 |
`groupname` varchar(100) NOT NULL, |
| 130 |
PRIMARY KEY (`servicename`,`groupname`), |
| 131 |
FOREIGN KEY (servicename) REFERENCES service(name), |
| 132 |
FOREIGN KEY (groupname) REFERENCES groups(name) |
| 133 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 134 |
|
| 135 |
|
| 136 |
|
| 137 |
|
| 138 |
|
| 139 |
|
| 140 |
|
| 141 |
CREATE TABLE IF NOT EXISTS `perfdatasource` ( |
| 142 |
`hostname` varchar(100) NOT NULL, |
| 143 |
`servicename` varchar(100) NOT NULL, |
| 144 |
`graphname` varchar(100) NOT NULL, |
| 145 |
`type` varchar(100) NOT NULL, |
| 146 |
`label` varchar(255) DEFAULT NULL, |
| 147 |
`factor` float NOT NULL, |
| 148 |
PRIMARY KEY (`hostname`,`servicename`), |
| 149 |
FOREIGN KEY (hostname) REFERENCES host(name), |
| 150 |
FOREIGN KEY (servicename) REFERENCES service(name), |
| 151 |
FOREIGN KEY (graphname) REFERENCES graph(name) |
| 152 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 153 |
|
| 154 |
|
| 155 |
|
| 156 |
|
| 157 |
CREATE TABLE IF NOT EXISTS `servicehautniveau` ( |
| 158 |
`servicename` varchar(100) NOT NULL, |
| 159 |
`servicename_dep` varchar(100) NOT NULL, |
| 160 |
PRIMARY KEY (`servicename`,`servicename_dep`), |
| 161 |
FOREIGN KEY (servicename) REFERENCES service(name), |
| 162 |
FOREIGN KEY (servicename_dep) REFERENCES service(name) |
| 163 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 164 |
|
| 165 |
|
| 166 |
|
| 167 |
|
| 168 |
|
| 169 |
|
| 170 |
CREATE TABLE IF NOT EXISTS `servicetopo` ( |
| 171 |
`servicename` varchar(100) NOT NULL, |
| 172 |
`function` varchar(50) NOT NULL, |
| 173 |
PRIMARY KEY (`servicename`), |
| 174 |
FOREIGN KEY (servicename) REFERENCES servicehautniveau(servicename) |
| 175 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
| 176 |
|