1alter table actions add esc_period number(10) DEFAULT '0' NOT NULL; 2alter table actions add def_shortdata varchar2(255) DEFAULT ''; 3alter table actions add def_longdata varchar2(2048) DEFAULT ''; 4alter table actions add recovery_msg number(10) DEFAULT '0' NOT NULL; 5alter table actions add r_shortdata varchar2(255) DEFAULT ''; 6alter table actions add r_longdata varchar2(2048) DEFAULT ''; 7 8CREATE INDEX actions_1 on actions (eventsource,status); 9CREATE TABLE alerts_tmp ( 10 alertid number(20) DEFAULT '0' NOT NULL, 11 actionid number(20) DEFAULT '0' NOT NULL, 12 eventid number(20) DEFAULT '0' NOT NULL, 13 userid number(20) DEFAULT '0' NOT NULL, 14 clock number(10) DEFAULT '0' NOT NULL, 15 mediatypeid number(20) DEFAULT '0' NOT NULL, 16 sendto varchar2(100) DEFAULT '' , 17 subject varchar2(255) DEFAULT '' , 18 message varchar2(2048) DEFAULT '' , 19 status number(10) DEFAULT '0' NOT NULL, 20 retries number(10) DEFAULT '0' NOT NULL, 21 error varchar2(128) DEFAULT '' , 22 nextcheck number(10) DEFAULT '0' NOT NULL, 23 esc_step number(10) DEFAULT '0' NOT NULL, 24 alerttype number(10) DEFAULT '0' NOT NULL, 25 PRIMARY KEY (alertid) 26); 27 28alter table alerts add eventid bigint DEFAULT '0' NOT NULL; 29 30update alerts a set eventid = (select min(e.eventid) from events e where e.objectid = a.triggerid and e.object = 0 and e.clock = a.clock) where a.eventid = 0; 31update alerts a set eventid = (select min(e.eventid) from events e where e.objectid = a.triggerid and e.object = 0 and e.clock = a.clock + 1) where a.eventid = 0; 32 33insert into alerts_tmp (alertid,actionid,eventid,userid,clock,mediatypeid,sendto,subject,message,status,retries,error,nextcheck) select alertid,actionid,eventid,userid,clock,mediatypeid,sendto,subject,message,status,retries,error,nextcheck from alerts; 34 35drop table alerts; 36alter table alerts_tmp rename to alerts; 37update alerts set status=3 where retries>=2; 38 39CREATE INDEX alerts_1 on alerts (actionid); 40CREATE INDEX alerts_2 on alerts (clock); 41CREATE INDEX alerts_3 on alerts (eventid); 42CREATE INDEX alerts_4 on alerts (status,retries); 43CREATE INDEX alerts_5 on alerts (mediatypeid); 44CREATE INDEX alerts_6 on alerts (userid); 45alter table config add event_ack_enable number(10) DEFAULT '1' NOT NULL; 46alter table config add event_expire number(10) DEFAULT '7' NOT NULL; 47alter table config add event_show_max number(10) DEFAULT '100' NOT NULL; 48alter table config add default_theme varchar2(128) DEFAULT 'default.css'; 49alter table config add authentication_type number(10) DEFAULT 0 NOT NULL; 50alter table config add ldap_host varchar2(255) DEFAULT ''; 51alter table config add ldap_port number(10) DEFAULT 389 NOT NULL; 52alter table config add ldap_base_dn varchar2(255) DEFAULT ''; 53alter table config add ldap_bind_dn varchar2(255) DEFAULT ''; 54alter table config add ldap_bind_password varchar2(128) DEFAULT ''; 55alter table config add ldap_search_attribute varchar2(128) DEFAULT ''; 56alter table dhosts modify ip varchar2(39) default ''; 57CREATE INDEX dhosts_1 on dhosts (druleid,ip); 58CREATE TABLE drules_tmp ( 59 druleid number(20) DEFAULT '0' NOT NULL, 60 proxy_hostid number(20) DEFAULT '0' NOT NULL, 61 name varchar2(255) DEFAULT '' , 62 iprange varchar2(255) DEFAULT '' , 63 delay number(10) DEFAULT '0' NOT NULL, 64 nextcheck number(10) DEFAULT '0' NOT NULL, 65 status number(10) DEFAULT '0' NOT NULL, 66 PRIMARY KEY (druleid) 67); 68insert into drules_tmp select druleid,0,name,iprange,delay,nextcheck,status from drules; 69drop table drules; 70alter table drules_tmp rename to drules; 71CREATE INDEX dservices_1 on dservices (dhostid,type,key_,port); 72begin execute immediate 'drop table escalations'; exception when others then null; end; 73CREATE TABLE escalations ( 74 escalationid number(20) DEFAULT '0' NOT NULL, 75 actionid number(20) DEFAULT '0' NOT NULL, 76 triggerid number(20) DEFAULT '0' NOT NULL, 77 eventid number(20) DEFAULT '0' NOT NULL, 78 r_eventid number(20) DEFAULT '0' NOT NULL, 79 nextcheck number(10) DEFAULT '0' NOT NULL, 80 esc_step number(10) DEFAULT '0' NOT NULL, 81 status number(10) DEFAULT '0' NOT NULL, 82 PRIMARY KEY (escalationid) 83); 84CREATE INDEX escalations_1 on escalations (actionid,triggerid); 85CREATE INDEX escalations_2 on escalations (status,nextcheck); 86CREATE TABLE events_tmp ( 87 eventid number(20) DEFAULT '0' NOT NULL, 88 source number(10) DEFAULT '0' NOT NULL, 89 object number(10) DEFAULT '0' NOT NULL, 90 objectid number(20) DEFAULT '0' NOT NULL, 91 clock number(10) DEFAULT '0' NOT NULL, 92 value number(10) DEFAULT '0' NOT NULL, 93 acknowledged number(10) DEFAULT '0' NOT NULL, 94 PRIMARY KEY (eventid) 95); 96insert into events_tmp select eventid,source,object,objectid,clock,value,acknowledged from events; 97drop table events; 98alter table events_tmp rename to events; 99CREATE INDEX events_1 on events (object,objectid,eventid); 100CREATE INDEX events_2 on events (clock); 101update graphs_items set color='FF0000' where color='Red'; 102update graphs_items set color='960000' where color='Dark Red'; 103update graphs_items set color='00FF00' where color='Green'; 104update graphs_items set color='009600' where color='Dark Green'; 105update graphs_items set color='0000FF' where color='Blue'; 106update graphs_items set color='000096' where color='Dark Blue'; 107update graphs_items set color='FFFF00' where color='Yellow'; 108update graphs_items set color='969600' where color='Dark Yellow'; 109update graphs_items set color='00FFFF' where color='Cyan'; 110update graphs_items set color='000000' where color='Black'; 111update graphs_items set color='969696' where color='Gray'; 112update graphs_items set color='FFFFFF' where color='White'; 113alter table graphs_items modify color varchar2(6) DEFAULT '009600'; 114 115CREATE INDEX graphs_items_1 on graphs_items (itemid); 116CREATE INDEX graphs_items_2 on graphs_items (graphid); 117alter table graphs add show_legend number(10) DEFAULT '0' NOT NULL; 118alter table graphs add show_3d number(10) DEFAULT '0' NOT NULL; 119alter table graphs add percent_left number(5,2) DEFAULT '0' NOT NULL; 120alter table graphs add percent_right number(5,2) DEFAULT '0' NOT NULL; 121CREATE UNIQUE INDEX history_log_2 on history_log (itemid,id); 122CREATE UNIQUE INDEX history_text_2 on history_text (itemid,id); 123CREATE TABLE hosts_profiles_ext ( 124 hostid number(20) DEFAULT '0' NOT NULL, 125 device_alias varchar2(64) DEFAULT '', 126 device_type varchar2(64) DEFAULT '', 127 device_chassis varchar2(64) DEFAULT '', 128 device_os varchar2(64) DEFAULT '', 129 device_os_short varchar2(64) DEFAULT '', 130 device_hw_arch varchar2(32) DEFAULT '', 131 device_serial varchar2(64) DEFAULT '', 132 device_model varchar2(64) DEFAULT '', 133 device_tag varchar2(64) DEFAULT '', 134 device_vendor varchar2(64) DEFAULT '', 135 device_contract varchar2(64) DEFAULT '', 136 device_who varchar2(64) DEFAULT '', 137 device_status varchar2(64) DEFAULT '', 138 device_app_01 varchar2(64) DEFAULT '', 139 device_app_02 varchar2(64) DEFAULT '', 140 device_app_03 varchar2(64) DEFAULT '', 141 device_app_04 varchar2(64) DEFAULT '', 142 device_app_05 varchar2(64) DEFAULT '', 143 device_url_1 varchar2(255) DEFAULT '', 144 device_url_2 varchar2(255) DEFAULT '', 145 device_url_3 varchar2(255) DEFAULT '', 146 device_networks varchar2(2048) DEFAULT '', 147 device_notes varchar2(2048) DEFAULT '', 148 device_hardware varchar2(2048) DEFAULT '', 149 device_software varchar2(2048) DEFAULT '', 150 ip_subnet_mask varchar2(39) DEFAULT '', 151 ip_router varchar2(39) DEFAULT '', 152 ip_macaddress varchar2(64) DEFAULT '', 153 oob_ip varchar2(39) DEFAULT '', 154 oob_subnet_mask varchar2(39) DEFAULT '', 155 oob_router varchar2(39) DEFAULT '', 156 date_hw_buy varchar2(64) DEFAULT '', 157 date_hw_install varchar2(64) DEFAULT '', 158 date_hw_expiry varchar2(64) DEFAULT '', 159 date_hw_decomm varchar2(64) DEFAULT '', 160 site_street_1 varchar2(128) DEFAULT '', 161 site_street_2 varchar2(128) DEFAULT '', 162 site_street_3 varchar2(128) DEFAULT '', 163 site_city varchar2(128) DEFAULT '', 164 site_state varchar2(64) DEFAULT '', 165 site_country varchar2(64) DEFAULT '', 166 site_zip varchar2(64) DEFAULT '', 167 site_rack varchar2(128) DEFAULT '', 168 site_notes varchar2(2048) DEFAULT '', 169 poc_1_name varchar2(128) DEFAULT '', 170 poc_1_email varchar2(128) DEFAULT '', 171 poc_1_phone_1 varchar2(64) DEFAULT '', 172 poc_1_phone_2 varchar2(64) DEFAULT '', 173 poc_1_cell varchar2(64) DEFAULT '', 174 poc_1_screen varchar2(64) DEFAULT '', 175 poc_1_notes varchar2(2048) DEFAULT '', 176 poc_2_name varchar2(128) DEFAULT '', 177 poc_2_email varchar2(128) DEFAULT '', 178 poc_2_phone_1 varchar2(64) DEFAULT '', 179 poc_2_phone_2 varchar2(64) DEFAULT '', 180 poc_2_cell varchar2(64) DEFAULT '', 181 poc_2_screen varchar2(64) DEFAULT '', 182 poc_2_notes varchar2(2048) DEFAULT '', 183 PRIMARY KEY (hostid) 184); 185CREATE TABLE hosts_tmp ( 186 hostid number(20) DEFAULT '0' NOT NULL, 187 proxy_hostid number(20) DEFAULT '0' NOT NULL, 188 host varchar2(64) DEFAULT '' , 189 dns varchar2(64) DEFAULT '' , 190 useip number(10) DEFAULT '1' NOT NULL, 191 ip varchar2(39) DEFAULT '127.0.0.1' , 192 port number(10) DEFAULT '10050' NOT NULL, 193 status number(10) DEFAULT '0' NOT NULL, 194 disable_until number(10) DEFAULT '0' NOT NULL, 195 error varchar2(128) DEFAULT '' , 196 available number(10) DEFAULT '0' NOT NULL, 197 errors_from number(10) DEFAULT '0' NOT NULL, 198 lastaccess number(10) DEFAULT '0' NOT NULL, 199 inbytes number(20) DEFAULT '0' NOT NULL, 200 outbytes number(20) DEFAULT '0' NOT NULL, 201 useipmi number(10) DEFAULT '0' NOT NULL, 202 ipmi_port number(10) DEFAULT '623' NOT NULL, 203 ipmi_authtype number(10) DEFAULT '0' NOT NULL, 204 ipmi_privilege number(10) DEFAULT '2' NOT NULL, 205 ipmi_username varchar2(16) DEFAULT '' , 206 ipmi_password varchar2(20) DEFAULT '' , 207 PRIMARY KEY (hostid) 208); 209insert into hosts_tmp select hostid,0,host,dns,useip,ip,port,status,disable_until,error,available,errors_from,0,0,0,0,623,0,2,'','' from hosts; 210drop table hosts; 211alter table hosts_tmp rename to hosts; 212CREATE INDEX hosts_1 on hosts (host); 213CREATE INDEX hosts_2 on hosts (status); 214CREATE INDEX hosts_3 on hosts (proxy_hostid); 215alter table httpstep modify url varchar2(255) DEFAULT ''; 216CREATE TABLE httptest_tmp ( 217 httptestid number(20) DEFAULT '0' NOT NULL, 218 name varchar2(64) DEFAULT '' , 219 applicationid number(20) DEFAULT '0' NOT NULL, 220 lastcheck number(10) DEFAULT '0' NOT NULL, 221 nextcheck number(10) DEFAULT '0' NOT NULL, 222 curstate number(10) DEFAULT '0' NOT NULL, 223 curstep number(10) DEFAULT '0' NOT NULL, 224 lastfailedstep number(10) DEFAULT '0' NOT NULL, 225 delay number(10) DEFAULT '60' NOT NULL, 226 status number(10) DEFAULT '0' NOT NULL, 227 macros varchar2(2048) DEFAULT '' , 228 agent varchar2(255) DEFAULT '' , 229 time number(20,4) DEFAULT '0' NOT NULL, 230 error varchar2(255) DEFAULT '' , 231 PRIMARY KEY (httptestid) 232); 233 234insert into httptest_tmp select * from httptest; 235drop table httptest; 236alter table httptest_tmp rename to httptest; 237 238CREATE INDEX httptest_httptest_1 on httptest (applicationid); 239CREATE INDEX httptest_2 on httptest (name); 240CREATE INDEX httptest_3 on httptest (status); 241delete from ids; 242alter table items add ipmi_sensor varchar2(128) DEFAULT ''; 243CREATE INDEX items_4 on items (templateid); 244drop table node_cksum; 245 246CREATE TABLE node_cksum ( 247 nodeid number(10) DEFAULT '0' NOT NULL, 248 tablename varchar2(64) DEFAULT '', 249 recordid number(20) DEFAULT '0' NOT NULL, 250 cksumtype number(10) DEFAULT '0' NOT NULL, 251 cksum clob DEFAULT '' NOT NULL, 252 sync varchar2(128) DEFAULT '' 253); 254CREATE INDEX node_cksum_cksum_1 on node_cksum (nodeid,tablename,recordid,cksumtype); 255drop table node_configlog; 256alter table nodes modify ip varchar2(39) DEFAULT ''; 257alter table nodes drop column event_lastid; 258alter table nodes drop column history_lastid; 259alter table nodes drop column history_str_lastid; 260alter table nodes drop column history_uint_lastid; 261CREATE TABLE opconditions ( 262 opconditionid number(20) DEFAULT '0' NOT NULL, 263 operationid number(20) DEFAULT '0' NOT NULL, 264 conditiontype number(10) DEFAULT '0' NOT NULL, 265 operator number(10) DEFAULT '0' NOT NULL, 266 value varchar2(255) DEFAULT '', 267 PRIMARY KEY (opconditionid) 268); 269CREATE INDEX opconditions_1 on opconditions (operationid); 270alter table operations add esc_period number(10) DEFAULT '0' NOT NULL; 271alter table operations add esc_step_from number(10) DEFAULT '0' NOT NULL; 272alter table operations add esc_step_to number(10) DEFAULT '0' NOT NULL; 273alter table operations add default_msg number(10) DEFAULT '0' NOT NULL; 274alter table operations add evaltype number(10) DEFAULT '0' NOT NULL; 275drop table profiles; 276CREATE TABLE profiles ( 277 profileid number(20) DEFAULT '0' NOT NULL, 278 userid number(20) DEFAULT '0' NOT NULL, 279 idx varchar2(96) DEFAULT '', 280 idx2 number(20) DEFAULT '0' NOT NULL, 281 value_id number(20) DEFAULT '0' NOT NULL, 282 value_int number(10) DEFAULT '0' NOT NULL, 283 value_str varchar2(255) DEFAULT '', 284 source varchar2(96) DEFAULT '', 285 type number(10) DEFAULT '0' NOT NULL, 286 PRIMARY KEY (profileid) 287); 288CREATE INDEX profiles_1 on profiles (userid,idx,idx2); 289CREATE TABLE proxy_dhistory ( 290 id number(20) NOT NULL, 291 clock number(10) DEFAULT '0' NOT NULL, 292 druleid number(20) DEFAULT '0' NOT NULL, 293 type number(10) DEFAULT '0' NOT NULL, 294 ip varchar2(39) DEFAULT '', 295 port number(10) DEFAULT '0' NOT NULL, 296 key_ varchar2(255) DEFAULT '0', 297 value varchar2(255) DEFAULT '0', 298 status number(10) DEFAULT '0' NOT NULL, 299 PRIMARY KEY (id) 300); 301CREATE INDEX proxy_dhistory_1 on proxy_dhistory (clock); 302CREATE TABLE proxy_history ( 303 id number(20) NOT NULL, 304 itemid number(20) DEFAULT '0' NOT NULL, 305 clock number(10) DEFAULT '0' NOT NULL, 306 timestamp number(10) DEFAULT '0' NOT NULL, 307 source varchar2(64) DEFAULT '', 308 severity number(10) DEFAULT '0' NOT NULL, 309 value varchar2(2048) DEFAULT '', 310 PRIMARY KEY (id) 311); 312CREATE INDEX proxy_history_1 on proxy_history (clock); 313alter table rights drop column type; 314alter table screens_items add dynamic number(10) DEFAULT '0' NOT NULL; 315CREATE TABLE scripts ( 316 scriptid number(20) DEFAULT '0' NOT NULL, 317 name varchar2(255) DEFAULT '', 318 command varchar2(255) DEFAULT '', 319 host_access number(10) DEFAULT '0' NOT NULL, 320 usrgrpid number(20) DEFAULT '0' NOT NULL, 321 groupid number(20) DEFAULT '0' NOT NULL, 322 PRIMARY KEY (scriptid) 323); 324CREATE INDEX services_1 on services (triggerid); 325alter table sessions add status number(10) DEFAULT '0' NOT NULL; 326alter table sysmaps_elements add iconid_disabled number(20) DEFAULT '0' NOT NULL; 327update sysmaps_elements set iconid_disabled=iconid_off; 328CREATE TABLE sysmaps_link_triggers ( 329 linktriggerid number(20) DEFAULT '0' NOT NULL, 330 linkid number(20) DEFAULT '0' NOT NULL, 331 triggerid number(20) DEFAULT '0' NOT NULL, 332 drawtype number(10) DEFAULT '0' NOT NULL, 333 color varchar2(6) DEFAULT '000000', 334 PRIMARY KEY (linktriggerid) 335); 336CREATE UNIQUE INDEX sysmaps_link_triggers_1 on sysmaps_link_triggers (linkid,triggerid); 337update sysmaps_links set color_on='FF0000' where color_on='Red'; 338update sysmaps_links set color_on='960000' where color_on='Dark Red'; 339update sysmaps_links set color_on='00FF00' where color_on='Green'; 340update sysmaps_links set color_on='009600' where color_on='Dark Green'; 341update sysmaps_links set color_on='0000FF' where color_on='Blue'; 342update sysmaps_links set color_on='000096' where color_on='Dark Blue'; 343update sysmaps_links set color_on='FFFF00' where color_on='Yellow'; 344update sysmaps_links set color_on='969600' where color_on='Dark Yellow'; 345update sysmaps_links set color_on='00FFFF' where color_on='Cyan'; 346update sysmaps_links set color_on='000000' where color_on='Black'; 347update sysmaps_links set color_on='969696' where color_on='Gray'; 348update sysmaps_links set color_on='FFFFFF' where color_on='White'; 349update sysmaps_links set color_off='FF0000' where color_off='Red'; 350update sysmaps_links set color_off='960000' where color_off='Dark Red'; 351update sysmaps_links set color_off='00FF00' where color_off='Green'; 352update sysmaps_links set color_off='009600' where color_off='Dark Green'; 353update sysmaps_links set color_off='0000FF' where color_off='Blue'; 354update sysmaps_links set color_off='000096' where color_off='Dark Blue'; 355update sysmaps_links set color_off='FFFF00' where color_off='Yellow'; 356update sysmaps_links set color_off='969600' where color_off='Dark Yellow'; 357update sysmaps_links set color_off='00FFFF' where color_off='Cyan'; 358update sysmaps_links set color_off='000000' where color_off='Black'; 359update sysmaps_links set color_off='969696' where color_off='Gray'; 360update sysmaps_links set color_off='FFFFFF' where color_off='White'; 361insert into sysmaps_link_triggers select linkid,linkid,triggerid,drawtype_on,color_on from sysmaps_links; 362alter table sysmaps_links drop column triggerid; 363alter table sysmaps_links rename column drawtype_off to drawtype; 364alter table sysmaps_links rename column color_off to color; 365alter table sysmaps_links modify color varchar2(6) DEFAULT '000000'; 366alter table sysmaps_links drop column drawtype_on; 367alter table sysmaps_links drop column color_on; 368-- See sysmaps_links.sql 369CREATE TABLE trends_uint ( 370 itemid number(20) DEFAULT '0' NOT NULL, 371 clock number(10) DEFAULT '0' NOT NULL, 372 num number(10) DEFAULT '0' NOT NULL, 373 value_min number(20) DEFAULT '0' NOT NULL, 374 value_avg number(20) DEFAULT '0' NOT NULL, 375 value_max number(20) DEFAULT '0' NOT NULL, 376 PRIMARY KEY (itemid,clock) 377); 378alter table triggers modify comments varchar2(2048) DEFAULT ''; 379alter table triggers add type number(10) DEFAULT '0' NOT NULL; 380CREATE TABLE users_tmp ( 381 userid number(20) DEFAULT '0' NOT NULL, 382 alias varchar2(100) DEFAULT '', 383 name varchar2(100) DEFAULT '', 384 surname varchar2(100) DEFAULT '', 385 passwd varchar2(32) DEFAULT '', 386 url varchar2(255) DEFAULT '', 387 autologin number(10) DEFAULT '0' NOT NULL, 388 autologout number(10) DEFAULT '900' NOT NULL, 389 lang varchar2(5) DEFAULT 'en_gb', 390 refresh number(10) DEFAULT '30' NOT NULL, 391 type number(10) DEFAULT '0' NOT NULL, 392 theme varchar2(128) DEFAULT 'default.css', 393 attempt_failed number(10) DEFAULT 0 NOT NULL, 394 attempt_ip varchar2(39) DEFAULT '', 395 attempt_clock number(10) DEFAULT 0 NOT NULL, 396 PRIMARY KEY (userid) 397); 398insert into users_tmp select userid,alias,name,surname,passwd,url,0,autologout,lang,refresh,type,'default.css',0,'',0 from users; 399drop table users; 400alter table users_tmp rename to users; 401update users set passwd='5fce1b3e34b520afeffb37ce08c7cd66' where alias<>'guest' and passwd='d41d8cd98f00b204e9800998ecf8427e'; 402CREATE INDEX users_1 on users (alias); 403alter table usrgrp add gui_access number(10) DEFAULT '0' NOT NULL; 404alter table usrgrp add users_status number(10) DEFAULT '0' NOT NULL; 405