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