generateSqlLite 30.2 KB
Newer Older
Sylvestre Ho's avatar
Sylvestre Ho committed
1
2
3
4
#!/usr/bin/env php
<?php
require_once "@CENTREON_ETC@/centreon.conf.php";

Sylvestre Ho's avatar
Sylvestre Ho committed
5
6
7
define("OK", 0);
define("NOK", 1);

Sylvestre Ho's avatar
Sylvestre Ho committed
8
9
10
11
$programName = $argv[0];

if ($argc < 3) {
    echo "$programName: Missing argument\n";
Sylvestre Ho's avatar
Sylvestre Ho committed
12
    exit(NOK);
Sylvestre Ho's avatar
Sylvestre Ho committed
13
14
15
16
17
18
}

$server_id = (int)$argv[1];
$dbfilename = $argv[2];
if (!is_writable(dirname($dbfilename))) {
    echo "$programName: Cannot write into $dbfilename\n";
Sylvestre Ho's avatar
Sylvestre Ho committed
19
    exit(NOK);
Sylvestre Ho's avatar
Sylvestre Ho committed
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
}

if (is_file($dbfilename)) {
    unlink($dbfilename);
}

try {
    $mysql_host = $conf_centreon["hostCentreon"];
    $mysql_database = $conf_centreon["db"];
    $mysql_user = $conf_centreon["user"];
    $mysql_password = $conf_centreon["password"];
    $db_centreon = new PDO("mysql:dbname=pdo;host=" . $mysql_host . ";dbname=" . $mysql_database,
    $mysql_user, $mysql_password);
    $db_centreon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

35
    // Get pollers
Quentin Garnier's avatar
Quentin Garnier committed
36
37
38
39
40
41
42
    $stmt = $db_centreon->prepare("SELECT ns_ip_address, id
        FROM nagios_server
        WHERE id = :server_id");
    $stmt->bindParam(':server_id', $server_id, PDO::PARAM_INT);
    $stmt->execute();
    $result_pollers = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
43
44
45
46
47
48
49
50
    // Get engine command file
    $stmt = $db_centreon->prepare("SELECT command_file
        FROM cfg_nagios
        WHERE nagios_server_id = :server_id AND nagios_activate = '1'");
    $stmt->bindParam(':server_id', $server_id, PDO::PARAM_INT);
    $stmt->execute();
    $result_cfg_engine = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
Quentin Garnier's avatar
Quentin Garnier committed
51
52
53
54
55
56
57
58
    // get host relations with poller
    $stmt = $db_centreon->prepare("SELECT nagios_server_id, host_host_id
        FROM ns_host_relation
        WHERE nagios_server_id = :server_id");
    $stmt->bindParam(':server_id', $server_id, PDO::PARAM_INT);
    $stmt->execute();
    $result_pollers_relations = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
Sylvestre Ho's avatar
Sylvestre Ho committed
59
    // get hosts
Quentin Garnier's avatar
Quentin Garnier committed
60
    $stmt = $db_centreon->prepare("SELECT host.host_id, host.host_name, host.host_address, host.host_snmp_community, host.host_snmp_version
Sylvestre Ho's avatar
Sylvestre Ho committed
61
62
63
64
        FROM host, ns_host_relation 
        WHERE ns_host_relation.nagios_server_id = :server_id 
        AND ns_host_relation.host_host_id = host.host_id 
        AND host.host_activate = '1'");
Sylvestre Ho's avatar
Sylvestre Ho committed
65
66
67
68
    $stmt->bindParam(':server_id', $server_id, PDO::PARAM_INT);
    $stmt->execute();
    $result_hosts = $stmt->fetchAll(PDO::FETCH_ASSOC);

Quentin Garnier's avatar
Quentin Garnier committed
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
    // get hosts templates
    $stmt = $db_centreon->prepare("SELECT host.host_id, host.host_snmp_community, host.host_snmp_version
        FROM host 
        WHERE host_register = '0' AND host_activate = '1'");
    $stmt->execute();
    $result_host_templates = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // get host relations
    $stmt = $db_centreon->prepare("SELECT host_tpl_id, host_host_id, `order` FROM host_template_relation");
    $stmt->execute();
    $result_host_templates_relation = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // get host macros
    $stmt = $db_centreon->prepare("SELECT host_host_id, host_macro_name, host_macro_value FROM on_demand_macro_host");
    $stmt->execute();
    $result_host_macros = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
Sylvestre Ho's avatar
Sylvestre Ho committed
86
    // get services
87
88
    $stmt = $db_centreon->prepare("SELECT host.host_id, service.service_id, service.service_description, service.service_template_model_stm_id, extended_service_information.esi_notes
        FROM host, host_service_relation, ns_host_relation, service LEFT JOIN extended_service_information ON service.service_id = extended_service_information.service_service_id
Sylvestre Ho's avatar
Sylvestre Ho committed
89
90
91
92
93
        WHERE ns_host_relation.nagios_server_id = :server_id 
        AND ns_host_relation.host_host_id = host.host_id 
        AND host.host_id = host_service_relation.host_host_id 
        AND host_service_relation.service_service_id = service.service_id 
        AND service.service_activate = '1'");
Sylvestre Ho's avatar
Sylvestre Ho committed
94
95
96
97
98
    $stmt->bindParam(':server_id', $server_id, PDO::PARAM_INT);
    $stmt->execute();
    $result_host_services = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // get services by hostgroup
99
100
    $stmt = $db_centreon->prepare("SELECT host.host_id, service.service_id, service.service_description, service.service_template_model_stm_id, hostgroup_relation.hostgroup_hg_id, extended_service_information.esi_notes
        FROM host, host_service_relation, hostgroup_relation, ns_host_relation, service LEFT JOIN extended_service_information ON service.service_id = extended_service_information.service_service_id
Sylvestre Ho's avatar
Sylvestre Ho committed
101
102
103
104
105
106
        WHERE ns_host_relation.nagios_server_id = :server_id 
        AND ns_host_relation.host_host_id = host.host_id 
        AND host.host_id = hostgroup_relation.host_host_id 
        AND hostgroup_relation.hostgroup_hg_id = host_service_relation.hostgroup_hg_id 
        AND host_service_relation.service_service_id = service.service_id 
        AND service.service_activate = '1'");
Sylvestre Ho's avatar
Sylvestre Ho committed
107
108
109
110
111
    $stmt->bindParam(':server_id', $server_id, PDO::PARAM_INT);
    $stmt->execute();
    $result_services_from_hg = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // get service templates
Sylvestre Ho's avatar
Sylvestre Ho committed
112
113
114
115
    $stmt = $db_centreon->prepare("SELECT service.service_id, service.service_description, service.service_template_model_stm_id 
        FROM service 
        WHERE service.service_register = '0' 
        AND service.service_activate = '1'");
Sylvestre Ho's avatar
Sylvestre Ho committed
116
117
118
119
    $stmt->execute();
    $result_services_template = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // get trap info
120
    $stmt = $db_centreon->prepare("SELECT traps_id, traps_oid, traps_status, severity_id, traps_submit_result_enable, 
Sylvestre Ho's avatar
Sylvestre Ho committed
121
        traps_execution_command, traps_reschedule_svc_enable, traps_execution_command_enable, traps_args, 
Quentin Garnier's avatar
Quentin Garnier committed
122
123
        traps_routing_mode, traps_routing_value, traps_log, traps_name, traps_exec_method, traps_downtime, traps_routing_filter_services, traps_advanced_treatment, traps_advanced_treatment_default,
        traps_timeout, traps_customcode, traps_exec_interval, traps_exec_interval_type, manufacturer_id, traps_output_transform
Sylvestre Ho's avatar
Sylvestre Ho committed
124
        FROM traps");
Sylvestre Ho's avatar
Sylvestre Ho committed
125
126
127
    $stmt->execute();
    $result_traps = $stmt->fetchAll(PDO::FETCH_ASSOC);

Quentin Garnier's avatar
Quentin Garnier committed
128
    $stmt = $db_centreon->prepare("SELECT tmo_id, trap_id, tmo_order, tmo_regexp, tmo_string, tmo_status, severity_id 
Sylvestre Ho's avatar
Sylvestre Ho committed
129
        FROM traps_matching_properties");
Sylvestre Ho's avatar
Sylvestre Ho committed
130
131
132
    $stmt->execute();
    $result_traps_matching = $stmt->fetchAll(PDO::FETCH_ASSOC);

Sylvestre Ho's avatar
Sylvestre Ho committed
133
134
    $stmt = $db_centreon->prepare("SELECT traps_id, service_id 
        FROM traps_service_relation");
Sylvestre Ho's avatar
Sylvestre Ho committed
135
136
    $stmt->execute();
    $result_traps_relation = $stmt->fetchAll(PDO::FETCH_ASSOC);
Sylvestre Ho's avatar
Sylvestre Ho committed
137
138
139
140
141
    
    $stmt = $db_centreon->prepare("SELECT trap_id, tpe_string, tpe_order
        FROM traps_preexec");
    $stmt->execute();
    $result_traps_preexec = $stmt->fetchAll(PDO::FETCH_ASSOC);
Quentin Garnier's avatar
Quentin Garnier committed
142
    
143
    $stmt = $db_centreon->prepare("SELECT traps_group_id, traps_id
garnier-quentin's avatar
garnier-quentin committed
144
        FROM traps_group_relation");
145
146
147
    $stmt->execute();
    $result_traps_group = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
Quentin Garnier's avatar
Quentin Garnier committed
148
149
150
151
152
153
    // Get trap Vendor
    $stmt = $db_centreon->prepare("SELECT id, name
        FROM traps_vendor");
    $stmt->execute();
    $result_traps_vendor = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
154
155
156
157
158
159
    // Get Severities
    $stmt = $db_centreon->prepare("SELECT sc_id, sc_name, `level`
        FROM service_categories WHERE `level` IS NOT NULL");
    $stmt->execute();
    $result_severities = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
Sylvestre Ho's avatar
Sylvestre Ho committed
160
161
} catch (PDOException $e ) {
    echo "Error on poller (id:$server_id): " . $e->getMessage() . "\n";
Quentin Garnier's avatar
Quentin Garnier committed
162
    exit(NOK);
Sylvestre Ho's avatar
Sylvestre Ho committed
163
164
165
166
167
168
}

try {
    $dbh_sqlite = new PDO('sqlite:'.$dbfilename);
    $dbh_sqlite->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Quentin Garnier's avatar
Quentin Garnier committed
169
170
171
    $dbh_sqlite->exec("
            CREATE TABLE IF NOT EXISTS `nagios_server` (
                `id` int(11) UNIQUE NOT NULL,
Quentin Garnier's avatar
Quentin Garnier committed
172
                `ns_ip_address` varchar(200) DEFAULT NULL
Quentin Garnier's avatar
Quentin Garnier committed
173
174
            );
            
175
176
177
178
179
            CREATE TABLE IF NOT EXISTS `cfg_nagios` (
                `command_file` varchar(255) DEFAULT NULL,
                `nagios_activate` int(11) DEFAULT '1'
            );
            
Quentin Garnier's avatar
Quentin Garnier committed
180
181
182
            CREATE TABLE IF NOT EXISTS `ns_host_relation` (
                `host_host_id` int(11) DEFAULT NULL,
                `nagios_server_id` int(11) DEFAULT NULL
Sylvestre Ho's avatar
Sylvestre Ho committed
183
            );
Quentin Garnier's avatar
Quentin Garnier committed
184
185
186
187
188
189
190
191
192
            
            CREATE INDEX IF NOT EXISTS idx_nhr_host_host_id ON ns_host_relation (host_host_id);
            
            CREATE TABLE IF NOT EXISTS `host` (
                `host_id` int(11) UNIQUE NOT NULL,
                `host_name` varchar(200) DEFAULT NULL,
                `host_address` varchar(255) DEFAULT NULL,
                `host_snmp_community` varchar(255) DEFAULT NULL,
                `host_snmp_version` varchar(255) DEFAULT NULL,
Quentin Garnier's avatar
Quentin Garnier committed
193
                `host_activate` int(11) DEFAULT '1'
Quentin Garnier's avatar
Quentin Garnier committed
194
195
196
            );
            
            CREATE INDEX IF NOT EXISTS idx_host_host_id ON host (host_id);
Sylvestre Ho's avatar
Sylvestre Ho committed
197
198
199
200

            CREATE TABLE IF NOT EXISTS `service` (
                `service_id` int(11) UNIQUE NOT NULL,
                `service_description` varchar(200) DEFAULT NULL,
Quentin Garnier's avatar
Quentin Garnier committed
201
                `service_template_model_stm_id` int(11) DEFAULT NULL,
Quentin Garnier's avatar
Quentin Garnier committed
202
                `service_activate` int(11) DEFAULT '1'
Sylvestre Ho's avatar
Sylvestre Ho committed
203
                );
Quentin Garnier's avatar
Quentin Garnier committed
204
205
                
            CREATE INDEX IF NOT EXISTS idx_service_service_id ON service (service_id);
206
207
208
209
210
211
212
            
            CREATE TABLE IF NOT EXISTS `extended_service_information` (
                `service_service_id` int(11) UNIQUE NOT NULL,
                `esi_notes` text DEFAULT NULL
                );
            
            CREATE INDEX IF NOT EXISTS idx_esi_service_service_id ON extended_service_information (service_service_id);
Sylvestre Ho's avatar
Sylvestre Ho committed
213
214
215
216
217
218
219
220

            CREATE TABLE IF NOT EXISTS `hostgroup_relation` (
                `host_host_id` int(11) DEFAULT NULL,
                `hostgroup_hg_id` int(11) DEFAULT NULL
                );

            CREATE INDEX IF NOT EXISTS idx_hr_host_host_id ON hostgroup_relation (host_host_id);

Quentin Garnier's avatar
Quentin Garnier committed
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
            CREATE TABLE IF NOT EXISTS `host_template_relation` (
                `host_host_id` int(11) DEFAULT NULL,
                `host_tpl_id` int(11) DEFAULT NULL,
                `order` int(11) DEFAULT NULL
                );
            
            CREATE INDEX IF NOT EXISTS idx_htr_host_host_id ON host_template_relation (host_host_id);
            
            CREATE TABLE IF NOT EXISTS `on_demand_macro_host` (
                `host_macro_name` varchar(255) NOT NULL,
                `host_macro_value` varchar(255) NOT NULL,
                `host_host_id` int(11) DEFAULT NULL
                );
              
            CREATE INDEX IF NOT EXISTS idx_odmh_host_host_id ON on_demand_macro_host (host_host_id);
            
Sylvestre Ho's avatar
Sylvestre Ho committed
237
238
239
240
241
242
243
244
245
246
247
248
249
            CREATE TABLE IF NOT EXISTS `host_service_relation` (
                `service_service_id` int(11) DEFAULT NULL,
                `host_host_id` int(11) DEFAULT NULL,
                `hostgroup_hg_id` int(11) DEFAULT NULL
                );

            CREATE INDEX IF NOT EXISTS idx_hsr_host_host_id ON host_service_relation (host_host_id);

            CREATE TABLE IF NOT EXISTS `traps_service_relation` (
                    `traps_id` int(11) DEFAULT NULL,
                    `service_id` int(11) DEFAULT NULL
                    );

Quentin Garnier's avatar
Quentin Garnier committed
250
251
            CREATE INDEX IF NOT EXISTS idx_tsr_mult_ids ON traps_service_relation (service_id, traps_id);            
            
Sylvestre Ho's avatar
Sylvestre Ho committed
252
253
            CREATE TABLE IF NOT EXISTS `traps` (
                    `traps_id` int(11) UNIQUE NOT NULL,
Quentin Garnier's avatar
Quentin Garnier committed
254
                    `traps_name` varchar(255) DEFAULT NULL,
Sylvestre Ho's avatar
Sylvestre Ho committed
255
                    `traps_oid` varchar(255) DEFAULT NULL,
Quentin Garnier's avatar
Quentin Garnier committed
256
                    `traps_args` varchar(255) DEFAULT NULL,
Sylvestre Ho's avatar
Sylvestre Ho committed
257
                    `traps_status` int(11) DEFAULT NULL,
258
                    `severity_id` int(11) DEFAULT NULL,
Quentin Garnier's avatar
Quentin Garnier committed
259
                    `manufacturer_id` int(11) DEFAULT NULL,
Sylvestre Ho's avatar
Sylvestre Ho committed
260
                    `traps_reschedule_svc_enable` int(11) DEFAULT '0',
Quentin Garnier's avatar
Quentin Garnier committed
261
                    `traps_execution_command` varchar(255) DEFAULT NULL,
Sylvestre Ho's avatar
Sylvestre Ho committed
262
                    `traps_execution_command_enable` int(11) DEFAULT '0',
Quentin Garnier's avatar
Quentin Garnier committed
263
                    `traps_submit_result_enable` int(11) DEFAULT '0',
Sylvestre Ho's avatar
Sylvestre Ho committed
264
                    `traps_advanced_treatment` int(11) DEFAULT '0',
Quentin Garnier's avatar
Quentin Garnier committed
265
266
267
268
                    `traps_advanced_treatment_default` int(11) DEFAULT '0',
                    `traps_timeout` int(11) DEFAULT NULL,
                    `traps_exec_interval` int(11) DEFAULT NULL,
                    `traps_exec_interval_type` int(11) DEFAULT '0',
Quentin Garnier's avatar
Quentin Garnier committed
269
					`traps_downtime` int(11) DEFAULT '0',
Quentin Garnier's avatar
Quentin Garnier committed
270
                    `traps_log` int(11) DEFAULT '0',
qgarnier's avatar
qgarnier committed
271
272
                    `traps_routing_mode` int(11) DEFAULT '0',
                    `traps_routing_value` varchar(255) DEFAULT NULL,
Quentin Garnier's avatar
Quentin Garnier committed
273
274
                    `traps_routing_filter_services` varchar(255) DEFAULT NULL,
                    `traps_output_transform` varchar(255) DEFAULT NULL,
Quentin Garnier's avatar
Quentin Garnier committed
275
276
                    `traps_exec_method` int(11) DEFAULT '0',
                    `traps_customcode` text DEFAULT NULL
Sylvestre Ho's avatar
Sylvestre Ho committed
277
278
279
280
281
                    );

            CREATE INDEX IF NOT EXISTS idx_t_traps_oid ON traps (traps_oid);

            CREATE TABLE IF NOT EXISTS `traps_matching_properties` (
Quentin Garnier's avatar
Quentin Garnier committed
282
                    `tmo_id` int(11) DEFAULT NULL,
Sylvestre Ho's avatar
Sylvestre Ho committed
283
284
285
286
                    `trap_id` int(11) DEFAULT NULL,
                    `tmo_order` int(11) DEFAULT NULL,
                    `tmo_regexp` varchar(255) DEFAULT NULL,
                    `tmo_string` varchar(255) DEFAULT NULL,
287
288
                    `tmo_status` int(11) DEFAULT NULL,
                    `severity_id` int(11) DEFAULT NULL
Sylvestre Ho's avatar
Sylvestre Ho committed
289
290
291
292
                    );

            CREATE INDEX IF NOT EXISTS idx_tmp_trap_id ON traps_matching_properties (trap_id);

Sylvestre Ho's avatar
Sylvestre Ho committed
293
294
295
296
297
            CREATE TABLE IF NOT EXISTS `traps_preexec` (
                    `trap_id` int(11) DEFAULT NULL,
                    `tpe_order` int(11) DEFAULT NULL,
                    `tpe_string` varchar(512) DEFAULT NULL
                    );
Quentin Garnier's avatar
Quentin Garnier committed
298
299
                    
            CREATE INDEX IF NOT EXISTS idx_tprexec_trap_id ON traps_preexec (trap_id);
300
            
garnier-quentin's avatar
garnier-quentin committed
301
            CREATE TABLE IF NOT EXISTS `traps_group_relation` (
302
303
304
305
306
307
                    `traps_group_id` int(11) DEFAULT NULL,
                    `traps_id` int(11) DEFAULT NULL
                    );
                    
            CREATE INDEX IF NOT EXISTS idx_tg_traps_id ON traps_group (traps_id);
            CREATE INDEX IF NOT EXISTS idx_tg_traps_group_id ON traps_group (traps_group_id);
Sylvestre Ho's avatar
Sylvestre Ho committed
308

Quentin Garnier's avatar
Quentin Garnier committed
309
310
311
312
313
            CREATE TABLE IF NOT EXISTS `traps_vendor` (
                `id` int(11) DEFAULT NULL,
                `name` int(11) DEFAULT NULL
            );
            
Quentin Garnier's avatar
Quentin Garnier committed
314
            CREATE INDEX IF NOT EXISTS idx_tv_id ON traps_vendor (`id`);
Quentin Garnier's avatar
Quentin Garnier committed
315
            
316
317
318
319
320
321
322
323
            CREATE TABLE IF NOT EXISTS `service_categories` (
                `sc_id` int(11) DEFAULT NULL,
                `sc_name` varchar(255) DEFAULT NULL,
                `level` int(11) DEFAULT NULL
            );
            
            CREATE INDEX IF NOT EXISTS idx_sc_sc_id ON service_categories (`sc_id`);
            
Sylvestre Ho's avatar
Sylvestre Ho committed
324
            ");
Quentin Garnier's avatar
Quentin Garnier committed
325

Sylvestre Ho's avatar
Sylvestre Ho committed
326
327
            $dbh_sqlite->beginTransaction();

Quentin Garnier's avatar
Quentin Garnier committed
328
329
330
331
332
333
334
335
336
            // Poller
            $stmt = $dbh_sqlite->prepare("INSERT INTO nagios_server (`id`, ns_ip_address) VALUES (
                    :id, :ns_ip_address)");
            foreach ($result_pollers as $value) {
                $stmt->bindParam(':id', $value['id'], PDO::PARAM_INT);
                $stmt->bindParam(':ns_ip_address', $value['ns_ip_address'], PDO::PARAM_STR);
                $stmt->execute();	
            }
            
337
338
339
340
341
342
343
344
            // Engine command file
            $stmt = $dbh_sqlite->prepare("INSERT INTO cfg_nagios (`command_file`) VALUES (
                    :command_file)");
            foreach ($result_cfg_engine as $value) {
                $stmt->bindParam(':command_file', $value['command_file'], PDO::PARAM_STR);
                $stmt->execute();	
            }
            
Quentin Garnier's avatar
Quentin Garnier committed
345
346
347
348
349
350
351
352
353
            // poller/host relation
            $stmt = $dbh_sqlite->prepare("INSERT INTO ns_host_relation (host_host_id, nagios_server_id) VALUES (
                    :host_host_id, :nagios_server_id)");
            foreach ($result_pollers_relations as $value) {
                $stmt->bindParam(':host_host_id', $value['host_host_id'], PDO::PARAM_INT);
                $stmt->bindParam(':nagios_server_id', $value['nagios_server_id'], PDO::PARAM_INT);
                $stmt->execute();
            }
            
Sylvestre Ho's avatar
Sylvestre Ho committed
354
            // Insert host
Quentin Garnier's avatar
Quentin Garnier committed
355
356
            $stmt = $dbh_sqlite->prepare("INSERT INTO host (host_id, host_name, host_address, host_snmp_community, host_snmp_version) VALUES (
                    :host_id, :host_name, :host_address, :host_snmp_community, :host_snmp_version)");
Sylvestre Ho's avatar
Sylvestre Ho committed
357
358
359
360
            foreach ($result_hosts as $value) {
                $stmt->bindParam(':host_id', $value['host_id'], PDO::PARAM_INT);
                $stmt->bindParam(':host_name', $value['host_name'], PDO::PARAM_STR);
                $stmt->bindParam(':host_address', $value['host_address'], PDO::PARAM_STR);
Quentin Garnier's avatar
Quentin Garnier committed
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
                $stmt->bindParam(':host_snmp_community', $value['host_snmp_community'], PDO::PARAM_STR);
                $stmt->bindParam(':host_snmp_version', $value['host_snmp_version'], PDO::PARAM_STR);
                $stmt->execute();	
            }
            $stmt = $dbh_sqlite->prepare("INSERT INTO host (host_id, host_snmp_community, host_snmp_version) VALUES (
                    :host_id, :host_snmp_community, :host_snmp_version)");
            foreach ($result_host_templates as $value) {
                $stmt->bindParam(':host_id', $value['host_id'], PDO::PARAM_INT);
                $stmt->bindParam(':host_snmp_community', $value['host_snmp_community'], PDO::PARAM_STR);
                $stmt->bindParam(':host_snmp_version', $value['host_snmp_version'], PDO::PARAM_STR);
                $stmt->execute();	
            }
            
            // Insert host template relations
            $stmt = $dbh_sqlite->prepare("INSERT INTO host_template_relation (host_host_id, host_tpl_id, `order`) VALUES (
                    :host_host_id, :host_tpl_id, :order)");
            foreach ($result_host_templates_relation as $value) {
                $stmt->bindParam(':host_host_id', $value['host_host_id'], PDO::PARAM_INT);
                $stmt->bindParam(':host_tpl_id', $value['host_tpl_id'], PDO::PARAM_INT);
                $stmt->bindParam(':order', $value['order'], PDO::PARAM_INT);                
                $stmt->execute();	
            }
            
            // Insert Host macro
            $stmt = $dbh_sqlite->prepare("INSERT INTO on_demand_macro_host (host_macro_name, host_macro_value, host_host_id) VALUES (
                    :host_macro_name, :host_macro_value, :host_host_id)");
            foreach ($result_host_macros as $value) {
                $stmt->bindParam(':host_macro_name', $value['host_macro_name'], PDO::PARAM_STR);
                $stmt->bindParam(':host_macro_value', $value['host_macro_value'], PDO::PARAM_STR);
                $stmt->bindParam(':host_host_id', $value['host_host_id'], PDO::PARAM_INT);                
Sylvestre Ho's avatar
Sylvestre Ho committed
391
392
393
394
                $stmt->execute();	
            }

            // Insert direct services
Sylvestre Ho's avatar
Sylvestre Ho committed
395
            $insertedServices = array();
Sylvestre Ho's avatar
Sylvestre Ho committed
396
            foreach ($result_host_services as $value) {
Sylvestre Ho's avatar
Sylvestre Ho committed
397
398
399
400
401
402
403
404
405
                if (!isset($insertedServices[$value['service_id']])) {
                    $stmt = $dbh_sqlite->prepare("INSERT INTO service (service_id, service_description, service_template_model_stm_id) VALUES (
                        :service_id, :service_description, :service_template_model_stm_id)");
                    $stmt->bindParam(':service_id', $value['service_id'], PDO::PARAM_INT);
                    $stmt->bindParam(':service_description', $value['service_description'], PDO::PARAM_STR);
                    $stmt->bindParam(':service_template_model_stm_id', $value['service_template_model_stm_id'], PDO::PARAM_INT);
                    $stmt->execute();
                    $insertedServices[$value['service_id']] = true;
                }
Sylvestre Ho's avatar
Sylvestre Ho committed
406
407
408
409
410
                $stmt = $dbh_sqlite->prepare("INSERT INTO host_service_relation (service_service_id, host_host_id) VALUES (
                    :service_service_id, :host_host_id)");
                $stmt->bindParam(':service_service_id', $value['service_id'], PDO::PARAM_INT);
                $stmt->bindParam(':host_host_id', $value['host_id'], PDO::PARAM_INT);
                $stmt->execute();
411
412
413
414
415
416
                
                $stmt = $dbh_sqlite->prepare("INSERT INTO extended_service_information (service_service_id, esi_notes) VALUES (
                    :service_service_id, :esi_notes)");
                $stmt->bindParam(':service_service_id', $value['service_id'], PDO::PARAM_INT);
                $stmt->bindParam(':esi_notes', $value['esi_notes'], PDO::PARAM_STR);
                $stmt->execute();
Sylvestre Ho's avatar
Sylvestre Ho committed
417
418
419
420
            }

            // Insert services by hostgroup
            foreach ($result_services_from_hg as $value) {
Sylvestre Ho's avatar
Sylvestre Ho committed
421
422
423
424
425
426
427
428
429
                if (!isset($insertedServices[$value['service_id']])) {
                    $stmt = $dbh_sqlite->prepare("INSERT INTO service (service_id, service_description, service_template_model_stm_id) VALUES (
                        :service_id, :service_description, :service_template_model_stm_id)");
                    $stmt->bindParam(':service_id', $value['service_id'], PDO::PARAM_INT);
                    $stmt->bindParam(':service_description', $value['service_description'], PDO::PARAM_STR);
                    $stmt->bindParam(':service_template_model_stm_id', $value['service_template_model_stm_id'], PDO::PARAM_INT);
                    $stmt->execute();
                    $insertedServices[$value['service_id']] = true;
                }
Sylvestre Ho's avatar
Sylvestre Ho committed
430
431
432
433
434
435
436
437
438
439
440
441

                $stmt = $dbh_sqlite->prepare("INSERT INTO hostgroup_relation (host_host_id, hostgroup_hg_id) VALUES (
                    :host_host_id, :hostgroup_hg_id)");
                $stmt->bindParam(':host_host_id', $value['host_id'], PDO::PARAM_INT);
                $stmt->bindParam(':hostgroup_hg_id', $value['hostgroup_hg_id'], PDO::PARAM_INT);
                $stmt->execute();

                $stmt = $dbh_sqlite->prepare("INSERT INTO host_service_relation (service_service_id, hostgroup_hg_id) VALUES (
                    :service_service_id, :hostgroup_hg_id)");
                $stmt->bindParam(':service_service_id', $value['service_id'], PDO::PARAM_INT);
                $stmt->bindParam(':hostgroup_hg_id', $value['hostgroup_hg_id'], PDO::PARAM_INT);
                $stmt->execute();
442
443
444
445
446
447
                
                $stmt = $dbh_sqlite->prepare("INSERT INTO extended_service_information (service_service_id, esi_notes) VALUES (
                    :service_service_id, :esi_notes)");
                $stmt->bindParam(':service_service_id', $value['service_id'], PDO::PARAM_INT);
                $stmt->bindParam(':esi_notes', $value['esi_notes'], PDO::PARAM_STR);
                $stmt->execute();
Sylvestre Ho's avatar
Sylvestre Ho committed
448
449
450
            }

            // Insert service templates
451
            $stmt = $dbh_sqlite->prepare("INSERT INTO service (service_id, service_description, service_template_model_stm_id) VALUES (
Sylvestre Ho's avatar
Sylvestre Ho committed
452
                    :service_id, :service_description, :service_template_model_stm_id)");
453
            foreach ($result_services_template as $value) {
Sylvestre Ho's avatar
Sylvestre Ho committed
454
455
456
457
458
459
460
                $stmt->bindParam(':service_id', $value['service_id'], PDO::PARAM_INT);
                $stmt->bindParam(':service_description', $value['service_description'], PDO::PARAM_STR);
                $stmt->bindParam(':service_template_model_stm_id', $value['service_template_model_stm_id'], PDO::PARAM_INT);
                $stmt->execute();
            }

            // Insert traps
461
            $stmt = $dbh_sqlite->prepare("INSERT INTO traps (traps_id, traps_name, traps_oid, traps_args, traps_status, severity_id, manufacturer_id,
Quentin Garnier's avatar
Quentin Garnier committed
462
                                            traps_reschedule_svc_enable, traps_execution_command, traps_execution_command_enable, traps_submit_result_enable, 
Quentin Garnier's avatar
Quentin Garnier committed
463
                                            traps_advanced_treatment, traps_advanced_treatment_default, traps_timeout, traps_customcode,
Quentin Garnier's avatar
Quentin Garnier committed
464
                                            traps_exec_interval, traps_exec_interval_type, traps_log,
Quentin Garnier's avatar
Quentin Garnier committed
465
                                            traps_routing_mode, traps_routing_value, traps_exec_method, traps_downtime, traps_output_transform, traps_routing_filter_services) VALUES (
466
                    :traps_id, :traps_name, :traps_oid, :traps_args, :traps_status, :severity_id, :manufacturer_id,
Quentin Garnier's avatar
Quentin Garnier committed
467
                    :traps_reschedule_svc_enable, :traps_execution_command, :traps_execution_command_enable, :traps_submit_result_enable,
Quentin Garnier's avatar
Quentin Garnier committed
468
                    :traps_advanced_treatment, :traps_advanced_treatment_default, :traps_timeout, :traps_customcode,
Quentin Garnier's avatar
Quentin Garnier committed
469
                    :traps_exec_interval, :traps_exec_interval_type, :traps_log,
qgarnier's avatar
qgarnier committed
470
                    :traps_routing_mode, :traps_routing_value,
Quentin Garnier's avatar
Quentin Garnier committed
471
                    :traps_exec_method, :traps_downtime, :traps_output_transform, :traps_routing_filter_services)");
472
            foreach ($result_traps as $value) {
Sylvestre Ho's avatar
Sylvestre Ho committed
473
                $stmt->bindParam(':traps_id', $value['traps_id'], PDO::PARAM_INT);
Quentin Garnier's avatar
Quentin Garnier committed
474
                $stmt->bindParam(':traps_name', $value['traps_name'], PDO::PARAM_STR);
Sylvestre Ho's avatar
Sylvestre Ho committed
475
                $stmt->bindParam(':traps_oid', $value['traps_oid'], PDO::PARAM_STR);
Quentin Garnier's avatar
Quentin Garnier committed
476
                $stmt->bindParam(':traps_args', $value['traps_args'], PDO::PARAM_STR);
Sylvestre Ho's avatar
Sylvestre Ho committed
477
                $stmt->bindParam(':traps_status', $value['traps_status'], PDO::PARAM_INT);
478
                $stmt->bindParam(':severity_id', $value['severity_id'], PDO::PARAM_INT);
Quentin Garnier's avatar
Quentin Garnier committed
479
                $stmt->bindParam(':manufacturer_id', $value['manufacturer_id'], PDO::PARAM_INT);
Sylvestre Ho's avatar
Sylvestre Ho committed
480
                $stmt->bindParam(':traps_reschedule_svc_enable', $value['traps_reschedule_svc_enable'], PDO::PARAM_INT);
Quentin Garnier's avatar
Quentin Garnier committed
481
                $stmt->bindParam(':traps_execution_command', $value['traps_execution_command'], PDO::PARAM_STR);
Sylvestre Ho's avatar
Sylvestre Ho committed
482
                $stmt->bindParam(':traps_execution_command_enable', $value['traps_execution_command_enable'], PDO::PARAM_INT);
Quentin Garnier's avatar
Quentin Garnier committed
483
484
485
486
                $stmt->bindParam(':traps_submit_result_enable', $value['traps_submit_result_enable'], PDO::PARAM_INT);
                $stmt->bindParam(':traps_advanced_treatment', $value['traps_advanced_treatment'], PDO::PARAM_INT);
                $stmt->bindParam(':traps_advanced_treatment_default', $value['traps_advanced_treatment_default'], PDO::PARAM_INT);
                $stmt->bindParam(':traps_timeout', $value['traps_timeout'], PDO::PARAM_INT);
Quentin Garnier's avatar
Quentin Garnier committed
487
                $stmt->bindParam(':traps_customcode', $value['traps_customcode'], PDO::PARAM_STR);
Quentin Garnier's avatar
Quentin Garnier committed
488
489
490
491
492
                $stmt->bindParam(':traps_exec_interval', $value['traps_exec_interval'], PDO::PARAM_INT);
                $stmt->bindParam(':traps_exec_interval_type', $value['traps_exec_interval_type'], PDO::PARAM_INT);
                $stmt->bindParam(':traps_log', $value['traps_log'], PDO::PARAM_INT);
                $stmt->bindParam(':traps_routing_mode', $value['traps_routing_mode'], PDO::PARAM_INT);
                $stmt->bindParam(':traps_routing_value', $value['traps_routing_value'], PDO::PARAM_STR);
qgarnier's avatar
qgarnier committed
493
                $stmt->bindParam(':traps_exec_method', $value['traps_exec_method'], PDO::PARAM_INT);
Quentin Garnier's avatar
Quentin Garnier committed
494
495
496
				$stmt->bindParam(':traps_output_transform', $value['traps_output_transform'], PDO::PARAM_STR);
				$stmt->bindParam(':traps_routing_filter_services', $value['traps_routing_filter_services'], PDO::PARAM_STR);
				$stmt->bindParam(':traps_downtime', $value['traps_downtime'], PDO::PARAM_INT);
Sylvestre Ho's avatar
Sylvestre Ho committed
497
498
499
                $stmt->execute();
            }

500
501
            $stmt = $dbh_sqlite->prepare("INSERT INTO traps_matching_properties (tmo_id, trap_id, tmo_order, tmo_regexp, tmo_string, tmo_status, severity_id) VALUES (
                    :tmo_id, :trap_id, :tmo_order, :tmo_regexp, :tmo_string, :tmo_status, :severity_id)");
Sylvestre Ho's avatar
Sylvestre Ho committed
502
            foreach ($result_traps_matching as $value) {
Quentin Garnier's avatar
Quentin Garnier committed
503
                $stmt->bindParam(':tmo_id', $value['tmo_id'], PDO::PARAM_INT);
Sylvestre Ho's avatar
Sylvestre Ho committed
504
505
506
507
508
                $stmt->bindParam(':trap_id', $value['trap_id'], PDO::PARAM_INT);
                $stmt->bindParam(':tmo_order', $value['tmo_order'], PDO::PARAM_INT);
                $stmt->bindParam(':tmo_regexp', $value['tmo_regexp'], PDO::PARAM_STR);
                $stmt->bindParam(':tmo_string', $value['tmo_string'], PDO::PARAM_STR);
                $stmt->bindParam(':tmo_status', $value['tmo_status'], PDO::PARAM_INT);
509
                $stmt->bindParam(':severity_id', $value['severity_id'], PDO::PARAM_INT);
Sylvestre Ho's avatar
Sylvestre Ho committed
510
511
512
                $stmt->execute();
            }

513
            $stmt = $dbh_sqlite->prepare("INSERT INTO traps_service_relation (traps_id, service_id) VALUES (
Sylvestre Ho's avatar
Sylvestre Ho committed
514
                    :traps_id, :service_id)");
515
            foreach ($result_traps_relation as $value) {
Sylvestre Ho's avatar
Sylvestre Ho committed
516
517
518
519
                $stmt->bindParam(':traps_id', $value['traps_id'], PDO::PARAM_INT);
                $stmt->bindParam(':service_id', $value['service_id'], PDO::PARAM_INT);
                $stmt->execute();
            }
Sylvestre Ho's avatar
Sylvestre Ho committed
520
            
521
            $stmt = $dbh_sqlite->prepare("INSERT INTO traps_preexec (trap_id, tpe_string, tpe_order) VALUES (
Sylvestre Ho's avatar
Sylvestre Ho committed
522
                    :trap_id, :tpe_string, :tpe_order)");
523
            foreach ($result_traps_preexec as $value) {
Sylvestre Ho's avatar
Sylvestre Ho committed
524
525
526
527
528
529
                $stmt->bindParam(':trap_id', $value['trap_id'], PDO::PARAM_INT);
                $stmt->bindParam(':tpe_string', $value['tpe_string'], PDO::PARAM_STR);
                $stmt->bindParam(':tpe_order', $value['tpe_order'], PDO::PARAM_INT);
                $stmt->execute();
            }
            
garnier-quentin's avatar
garnier-quentin committed
530
            $stmt = $dbh_sqlite->prepare("INSERT INTO traps_group_relation (traps_group_id, traps_id) VALUES (
531
532
533
534
535
536
537
                    :traps_group_id, :traps_id)");
            foreach ($result_traps_group as $value) {
                $stmt->bindParam(':traps_group_id', $value['traps_group_id'], PDO::PARAM_INT);
                $stmt->bindParam(':traps_id', $value['traps_id'], PDO::PARAM_INT);
                $stmt->execute();
            }
            
Quentin Garnier's avatar
Quentin Garnier committed
538
            // Insert traps vendor
539
            $stmt = $dbh_sqlite->prepare("INSERT INTO traps_vendor (`id`, `name`) VALUES (
Quentin Garnier's avatar
Quentin Garnier committed
540
                    :id, :name)");
541
            foreach ($result_traps_vendor as $value) {
Quentin Garnier's avatar
Quentin Garnier committed
542
543
544
545
546
                $stmt->bindParam(':id', $value['id'], PDO::PARAM_INT);
                $stmt->bindParam(':name', $value['name'], PDO::PARAM_STR);
                $stmt->execute();
            }
            
547
548
549
550
551
552
553
554
555
556
            // Insert severities
            $stmt = $dbh_sqlite->prepare("INSERT INTO service_categories (`sc_id`, `sc_name`, `level`) VALUES (
                    :sc_id, :sc_name, :level)");
            foreach ($result_severities as $value) {
                $stmt->bindParam(':sc_id', $value['sc_id'], PDO::PARAM_INT);
                $stmt->bindParam(':sc_name', $value['sc_name'], PDO::PARAM_STR);
                $stmt->bindParam(':level', $value['level'], PDO::PARAM_INT);
                $stmt->execute();
            }
            
Sylvestre Ho's avatar
Sylvestre Ho committed
557
558
            $dbh_sqlite->commit();
            echo "Poller (id:$server_id): Sqlite database successfully created\n";
Sylvestre Ho's avatar
Sylvestre Ho committed
559
            exit(OK);
Sylvestre Ho's avatar
Sylvestre Ho committed
560
561
562
} catch (PDOException $e ) {
    $dbh_sqlite->rollback();
    echo "Error on poller (id:$server_id): " . $e->getMessage() . "\n";
Sylvestre Ho's avatar
Sylvestre Ho committed
563
    exit(NOK);
Sylvestre Ho's avatar
Sylvestre Ho committed
564
}