1<?php
2/*
3 +-------------------------------------------------------------------------+
4 | Copyright (C) 2004-2021 The Cacti Group                                 |
5 |                                                                         |
6 | This program is free software; you can redistribute it and/or           |
7 | modify it under the terms of the GNU General Public License             |
8 | as published by the Free Software Foundation; either version 2          |
9 | of the License, or (at your option) any later version.                  |
10 |                                                                         |
11 | This program is distributed in the hope that it will be useful,         |
12 | but WITHOUT ANY WARRANTY; without even the implied warranty of          |
13 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the           |
14 | GNU General Public License for more details.                            |
15 +-------------------------------------------------------------------------+
16 | Cacti: The Complete RRDtool-based Graphing Solution                     |
17 +-------------------------------------------------------------------------+
18 | This code is designed, written, and maintained by the Cacti Group. See  |
19 | about.php and/or the AUTHORS file for specific developer information.   |
20 +-------------------------------------------------------------------------+
21 | http://www.cacti.net/                                                   |
22 +-------------------------------------------------------------------------+
23*/
24
25/* update_replication_crc - update hash stored in settings table to inform
26   remote pollers to replicate tables
27   @arg $poller_id - the id of the poller impacted by hash update
28   @arg $variable  - the variable name to store in the settings table */
29function update_replication_crc($poller_id, $variable) {
30	$hash = hash('ripemd160', date('Y-m-d H:i:s') . rand() . $poller_id);
31
32	db_execute_prepared("REPLACE INTO settings
33		SET value = ?, name='$variable" . ($poller_id > 0 ? "_" . "$poller_id'":"'"),
34		array($hash));
35}
36
37function repopulate_poller_cache() {
38	global $config;
39
40	include_once($config['library_path'] . '/api_data_source.php');
41
42	$poller_data    = db_fetch_assoc('SELECT ' . SQL_NO_CACHE . ' dl.*, h.poller_id
43		FROM data_local AS dl
44		INNER JOIN host AS h
45		ON dl.host_id=h.id
46		WHERE dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != "")
47		ORDER BY h.poller_id ASC');
48
49	$poller_items   = array();
50	$local_data_ids = array();
51	$poller_prev    = 1;
52	$i = 0;
53	$j = 0;
54
55	if (cacti_sizeof($poller_data)) {
56		foreach ($poller_data as $data) {
57			if ($j == 0) {
58				$poller_prev = $data['poller_id'];
59			}
60
61			$poller_id = $data['poller_id'];
62
63			if ($i > 500 || $poller_prev != $poller_id) {
64				poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_prev);
65				$i = 0;
66				$local_data_ids = array();
67				$poller_items   = array();
68			}
69
70			$poller_prev      = $poller_id;
71			$poller_items     = array_merge($poller_items, update_poller_cache($data));
72			$local_data_ids[] = $data['id'];
73			$i++;
74			$j++;
75		}
76
77		if ($i > 0) {
78			poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id);
79		}
80	}
81
82	$poller_ids = array_rekey(
83		db_fetch_assoc('SELECT DISTINCT poller_id
84			FROM poller_item'),
85		'poller_id', 'poller_id'
86	);
87
88	if (cacti_sizeof($poller_ids)) {
89		foreach ($poller_ids as $poller_id) {
90			api_data_source_cache_crc_update($poller_id);
91		}
92	}
93
94	/* update the field mappings for the poller */
95	db_execute('TRUNCATE TABLE poller_data_template_field_mappings');
96	db_execute('INSERT IGNORE INTO poller_data_template_field_mappings
97		SELECT dtr.data_template_id, dif.data_name,
98		GROUP_CONCAT(dtr.data_source_name ORDER BY dtr.data_source_name) AS data_source_names, NOW()
99		FROM data_template_rrd AS dtr
100		INNER JOIN data_input_fields AS dif
101		ON dtr.data_input_field_id = dif.id
102		WHERE dtr.local_data_id = 0
103		GROUP BY dtr.data_template_id, dif.data_name');
104}
105
106function update_poller_cache_from_query($host_id, $data_query_id, $local_data_ids) {
107	global $config;
108
109	include_once($config['library_path'] . '/api_data_source.php');
110
111	$poller_data = db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' *
112		FROM data_local
113		WHERE host_id = ?
114		AND snmp_query_id = ?
115		AND id IN(' . implode(', ', $local_data_ids) . ')
116		AND snmp_index != ""',
117		array($host_id, $data_query_id));
118
119	$poller_id = db_fetch_cell_prepared('SELECT poller_id
120		FROM host
121		WHERE id = ?',
122		array($host_id));
123
124	$i = 0;
125	$poller_items = $local_data_ids = array();
126
127	if (cacti_sizeof($poller_data)) {
128		foreach ($poller_data as $data) {
129			$poller_items     = array_merge($poller_items, update_poller_cache($data));
130			$local_data_ids[] = $data['id'];
131			$i++;
132
133			if ($i > 500) {
134				$i = 0;
135				poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id);
136				$local_data_ids = array();
137				$poller_items   = array();
138			}
139		}
140
141		if ($i > 0) {
142			poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id);
143		}
144	}
145
146	if ($poller_id > 1) {
147		api_data_source_cache_crc_update($poller_id);
148	}
149}
150
151function update_poller_cache($data_source, $commit = false) {
152	global $config;
153
154	include_once($config['library_path'] . '/data_query.php');
155	include_once($config['library_path'] . '/api_poller.php');
156
157	if (!is_array($data_source)) {
158		$data_source = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . ' *
159			FROM data_local AS dl
160			WHERE id = ?
161			AND (dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != ""))',
162			array($data_source));
163	}
164
165	$poller_items = array();
166
167	if (!cacti_sizeof($data_source)) {
168		return $poller_items;
169	}
170
171	$poller_id = db_fetch_cell_prepared('SELECT poller_id
172		FROM host AS h
173		INNER JOIN data_local AS dl
174		ON h.id=dl.host_id
175		WHERE dl.id = ?',
176		array($data_source['id']));
177
178	$data_input = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . '
179		di.id, di.type_id, dtd.id AS data_template_data_id,
180		dtd.data_template_id, dtd.active, dtd.rrd_step
181		FROM data_template_data AS dtd
182		INNER JOIN data_input AS di
183		ON dtd.data_input_id=di.id
184		WHERE dtd.local_data_id = ?',
185		array($data_source['id']));
186
187	if (cacti_sizeof($data_input)) {
188		// Check whitelist for input validation
189		if (!data_input_whitelist_check($data_input['id'])) {
190			return $poller_items;
191		}
192
193		/* we have to perform some additional sql queries if this is a 'query' */
194		if (($data_input['type_id'] == DATA_INPUT_TYPE_SNMP_QUERY) ||
195			($data_input['type_id'] == DATA_INPUT_TYPE_SCRIPT_QUERY) ||
196			($data_input['type_id'] == DATA_INPUT_TYPE_QUERY_SCRIPT_SERVER)){
197			$field = data_query_field_list($data_input['data_template_data_id']);
198
199			$params = array();
200			if ($field['output_type'] != '') {
201				$output_type_sql = ' AND sqgr.snmp_query_graph_id = ' . $field['output_type'];
202			} else {
203				$output_type_sql = '';
204			}
205			$params[] = $data_input['data_template_id'];
206			$params[] = $data_source['id'];
207
208			$outputs = db_fetch_assoc_prepared('SELECT DISTINCT ' . SQL_NO_CACHE . "
209				sqgr.snmp_field_name, dtr.id as data_template_rrd_id
210				FROM snmp_query_graph_rrd AS sqgr
211				INNER JOIN data_template_rrd AS dtr FORCE INDEX (local_data_id)
212				ON sqgr.data_template_rrd_id = dtr.local_data_template_rrd_id
213				WHERE sqgr.data_template_id = ?
214				AND dtr.local_data_id = ?
215				$output_type_sql
216				ORDER BY dtr.id", $params);
217		}
218
219		if ($data_input['active'] == 'on') {
220			if (($data_input['type_id'] == DATA_INPUT_TYPE_SCRIPT) ||
221				($data_input['type_id'] == DATA_INPUT_TYPE_PHP_SCRIPT_SERVER)) {
222				if ($data_input['type_id'] == DATA_INPUT_TYPE_PHP_SCRIPT_SERVER) {
223					$action = POLLER_ACTION_SCRIPT_PHP;
224					$script_path = get_full_script_path($data_source['id']);
225				} else {
226					$action = POLLER_ACTION_SCRIPT;
227					$script_path = get_full_script_path($data_source['id']);
228				}
229
230				$num_output_fields = cacti_sizeof(db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' id
231					FROM data_input_fields
232					WHERE data_input_id = ?
233					AND input_output = "out"
234					AND update_rra="on"',
235					array($data_input['id'])));
236
237				if ($num_output_fields == 1) {
238					$data_template_rrd_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' id
239						FROM data_template_rrd
240						WHERE local_data_id = ?',
241						array($data_source['id']));
242
243					$data_source_item_name = get_data_source_item_name($data_template_rrd_id);
244				} else {
245					$data_source_item_name = '';
246				}
247
248				$poller_items[] = api_poller_cache_item_add($data_source['host_id'], array(), $data_source['id'], $data_input['rrd_step'], $action, $data_source_item_name, 1, $script_path);
249			} elseif ($data_input['type_id'] == DATA_INPUT_TYPE_SNMP) {
250				/* get the host override fields */
251				$data_template_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' data_template_id
252					FROM data_template_data
253					WHERE local_data_id = ?',
254					array($data_source['id']));
255
256				/* get host fields first */
257				$host_fields = array_rekey(
258					db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
259						FROM data_input_fields AS dif
260						LEFT JOIN data_input_data AS did
261						ON dif.id=did.data_input_field_id
262						WHERE (type_code LIKE "snmp_%" OR type_code IN("hostname","host_id"))
263						AND did.data_template_data_id = ?
264						AND did.value != ""', array($data_input['data_template_data_id'])),
265					'type_code', 'value'
266				);
267
268				$data_template_fields = array_rekey(
269					db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
270						FROM data_input_fields AS dif
271						LEFT JOIN data_input_data AS did
272						ON dif.id=did.data_input_field_id
273						WHERE (type_code LIKE "snmp_%" OR type_code="hostname")
274						AND did.data_template_data_id = ?
275						AND data_template_data_id = ?
276						AND did.value != ""', array($data_template_id, $data_template_id)),
277					'type_code', 'value'
278				);
279
280				if (cacti_sizeof($host_fields)) {
281					if (cacti_sizeof($data_template_fields)) {
282						foreach($data_template_fields as $key => $value) {
283							if (!isset($host_fields[$key])) {
284								$host_fields[$key] = $value;
285							}
286						}
287					}
288				} elseif (cacti_sizeof($data_template_fields)) {
289					$host_fields = $data_template_fields;
290				}
291
292				$data_template_rrd_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' id
293					FROM data_template_rrd
294					WHERE local_data_id = ?',
295					array($data_source['id']));
296
297				$poller_items[] = api_poller_cache_item_add($data_source['host_id'], $host_fields, $data_source['id'], $data_input['rrd_step'], 0, get_data_source_item_name($data_template_rrd_id), 1, (isset($host_fields['snmp_oid']) ? $host_fields['snmp_oid'] : ''));
298			} elseif ($data_input['type_id'] == DATA_INPUT_TYPE_SNMP_QUERY) {
299				$snmp_queries = get_data_query_array($data_source['snmp_query_id']);
300
301				/* get the host override fields */
302				$data_template_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' data_template_id
303					FROM data_template_data
304					WHERE local_data_id = ?',
305					array($data_source['id']));
306
307				/* get host fields first */
308				$host_fields = array_rekey(
309					db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
310						FROM data_input_fields AS dif
311						LEFT JOIN data_input_data AS did
312						ON dif.id=did.data_input_field_id
313						WHERE (type_code LIKE "snmp_%" OR type_code="hostname")
314						AND did.data_template_data_id = ?
315						AND did.value != ""', array($data_input['data_template_data_id'])),
316					'type_code', 'value'
317				);
318
319				$data_template_fields = array_rekey(
320					db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
321						FROM data_input_fields AS dif
322						LEFT JOIN data_input_data AS did
323						ON dif.id=did.data_input_field_id
324						WHERE (type_code LIKE "snmp_%" OR type_code="hostname")
325						AND did.data_template_data_id = ?
326						AND data_template_data_id = ?
327						AND did.value != ""', array($data_template_id, $data_template_id)),
328					'type_code', 'value'
329				);
330
331				if (cacti_sizeof($host_fields)) {
332					if (cacti_sizeof($data_template_fields)) {
333						foreach ($data_template_fields as $key => $value) {
334							if (!isset($host_fields[$key])) {
335								$host_fields[$key] = $value;
336							}
337						}
338					}
339				} elseif (cacti_sizeof($data_template_fields)) {
340					$host_fields = $data_template_fields;
341				}
342
343				if (cacti_sizeof($outputs) && cacti_sizeof($snmp_queries)) {
344					foreach ($outputs as $output) {
345						if (isset($snmp_queries['fields'][$output['snmp_field_name']]['oid'])) {
346							$oid = $snmp_queries['fields'][$output['snmp_field_name']]['oid'] . '.' . $data_source['snmp_index'];
347
348							if (isset($snmp_queries['fields'][$output['snmp_field_name']]['oid_suffix'])) {
349								$oid .= '.' . $snmp_queries['fields'][$output['snmp_field_name']]['oid_suffix'];
350							}
351						}
352
353						if (!empty($oid)) {
354							$poller_items[] = api_poller_cache_item_add($data_source['host_id'], $host_fields, $data_source['id'], $data_input['rrd_step'], 0, get_data_source_item_name($output['data_template_rrd_id']), cacti_sizeof($outputs), $oid);
355						}
356					}
357				}
358			} elseif (($data_input['type_id'] == DATA_INPUT_TYPE_SCRIPT_QUERY) ||
359				($data_input['type_id'] == DATA_INPUT_TYPE_QUERY_SCRIPT_SERVER)) {
360				$script_queries = get_data_query_array($data_source['snmp_query_id']);
361
362				/* get the host override fields */
363				$data_template_id = db_fetch_cell_prepared('SELECT ' . SQL_NO_CACHE . ' data_template_id
364					FROM data_template_data
365					WHERE local_data_id = ?',
366					array($data_source['id']));
367
368				/* get host fields first */
369				$host_fields = array_rekey(
370					db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
371						FROM data_input_fields AS dif
372						LEFT JOIN data_input_data AS did
373						ON dif.id=did.data_input_field_id
374						WHERE (type_code LIKE "snmp_%" OR type_code="hostname")
375						AND did.data_template_data_id = ?
376						AND did.value != ""', array($data_input['data_template_data_id'])),
377					'type_code', 'value'
378				);
379
380				$data_template_fields = array_rekey(
381					db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dif.type_code, did.value
382						FROM data_input_fields AS dif
383						LEFT JOIN data_input_data AS did
384						ON dif.id=did.data_input_field_id
385						WHERE (type_code LIKE "snmp_%" OR type_code="hostname")
386						AND data_template_data_id = ?
387						AND did.data_template_data_id = ?
388						AND did.value != ""', array($data_template_id, $data_template_id)),
389					'type_code', 'value'
390				);
391
392				if (cacti_sizeof($host_fields)) {
393					if (cacti_sizeof($data_template_fields)) {
394						foreach ($data_template_fields as $key => $value) {
395							if (!isset($host_fields[$key])) {
396								$host_fields[$key] = $value;
397							}
398						}
399					}
400				} elseif (cacti_sizeof($data_template_fields)) {
401					$host_fields = $data_template_fields;
402				}
403
404				if (cacti_sizeof($outputs) && cacti_sizeof($script_queries)) {
405					foreach ($outputs as $output) {
406						if (isset($script_queries['fields'][$output['snmp_field_name']]['query_name'])) {
407							$identifier = $script_queries['fields'][$output['snmp_field_name']]['query_name'];
408
409							if ($data_input['type_id'] == DATA_INPUT_TYPE_QUERY_SCRIPT_SERVER) {
410								$action = POLLER_ACTION_SCRIPT_PHP;
411
412								$prepend = '';
413								if (isset($script_queries['arg_prepend']) && $script_queries['arg_prepend'] != '') {
414									$prepend = $script_queries['arg_prepend'];
415								}
416
417								$script_path = get_script_query_path(trim($prepend . ' ' . $script_queries['arg_get'] . ' ' . $identifier . ' ' . $data_source['snmp_index']), $script_queries['script_path'] . ' ' . $script_queries['script_function'], $data_source['host_id']);
418							} else {
419								$action = POLLER_ACTION_SCRIPT;
420								$script_path = get_script_query_path(trim((isset($script_queries['arg_prepend']) ? $script_queries['arg_prepend'] : '') . ' ' . $script_queries['arg_get'] . ' ' . $identifier . ' ' . $data_source['snmp_index']), $script_queries['script_path'], $data_source['host_id']);
421							}
422						}
423
424						if (isset($script_path)) {
425							$poller_items[] = api_poller_cache_item_add($data_source['host_id'], $host_fields, $data_source['id'], $data_input['rrd_step'], $action, get_data_source_item_name($output['data_template_rrd_id']), cacti_sizeof($outputs), $script_path);
426						}
427					}
428				}
429			} else {
430				$arguments = array(
431					'poller_items' => $poller_items,
432					'data_source'  => $data_source,
433					'data_input'   => $data_input
434				);
435
436				$arguments = api_plugin_hook_function('data_source_to_poller_items', $arguments);
437
438				// Process the returned poller items
439				if ((isset($arguments['poller_items'])) &&
440					(is_array($arguments['poller_items'])) &&
441					(cacti_sizeof($poller_items) < cacti_sizeof($arguments['poller_items']))) {
442					$poller_items = $arguments['poller_items'];
443				}
444			}
445		}
446	} else {
447		$data_template_data = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . ' *
448			FROM data_template_data
449			WHERE local_data_id = ?',
450			array($data_source['id']));
451
452		if (cacti_sizeof($data_template_data) && $data_template_data['data_input_id'] > 0) {
453			cacti_log('WARNING: Repopulate Poller Cache found Data Input Missing for Data Source ' . $data_source['id'] . '.  Database may be corrupted');
454		}
455	}
456
457	if ($commit && cacti_sizeof($poller_items)) {
458		poller_update_poller_cache_from_buffer((array)$data_source['id'], $poller_items, $poller_id);
459	} elseif (!$commit) {
460		return $poller_items;
461	}
462}
463
464function push_out_data_input_method($data_input_id) {
465	$data_sources = db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . ' dl.*, h.poller_id
466		FROM data_local AS dl
467		INNER JOIN (
468			SELECT DISTINCT local_data_id
469			FROM data_template_data
470			WHERE data_input_id = ?
471			AND local_data_id > 0
472		) AS dtd
473		ON dtd.local_data_id = dl.id
474		INNER JOIN host AS h
475		ON h.id = dl.host_id
476		WHERE dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != "")
477		ORDER BY h.poller_id ASC',
478		array($data_input_id));
479
480	$poller_items = array();
481	$_my_local_data_ids = array();
482
483	if (cacti_sizeof($data_sources)) {
484		$prev_poller = -1;
485		foreach ($data_sources as $data_source) {
486			if ($prev_poller > 0 && $data_source['poller_id'] != $prev_poller) {
487				poller_update_poller_cache_from_buffer($_my_local_data_ids, $poller_items, $prev_poller);
488				$_my_local_data_ids = array();
489				$poller_items = array();
490			} else {
491				$_my_local_data_ids[] = $data_source['id'];
492				$poller_items = array_merge($poller_items, update_poller_cache($data_source));
493			}
494
495			$prev_poller = $data_source['poller_id'];
496		}
497
498		if (cacti_sizeof($_my_local_data_ids)) {
499			poller_update_poller_cache_from_buffer($_my_local_data_ids, $poller_items, $prev_poller);
500		}
501	}
502}
503
504/** mass update of poller cache - can run in parallel to poller
505 * @param array/int $local_data_ids - either a scalar (all ids) or an array of data source to act on
506 * @param array $poller_items - the new items for poller cache
507 * @param int $poller_id - the poller_id of the buffer
508 */
509function poller_update_poller_cache_from_buffer($local_data_ids, &$poller_items, $poller_id = 1) {
510	global $config;
511
512	/* set all fields present value to 0, to mark the outliers when we are all done */
513	$ids = '';
514	if (cacti_sizeof($local_data_ids)) {
515		$ids = implode(', ', $local_data_ids);
516
517		if ($ids != '') {
518			db_execute_prepared("UPDATE poller_item
519				SET present=0
520				WHERE poller_id = ?
521				AND local_data_id IN ($ids)",
522				array($poller_id));
523
524			if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) {
525				db_execute_prepared("UPDATE poller_item
526					SET present=0
527					WHERE poller_id = ?
528					AND local_data_id IN ($ids)",
529					array($poller_id), true, $rcnn_id);
530			}
531		}
532	} else {
533		/* don't mark anything in case we have no $local_data_ids =>
534		 *this would flush the whole table at bottom of this function */
535	}
536
537	/* setup the database call */
538	$sql_prefix   = 'INSERT INTO poller_item (local_data_id, poller_id, host_id, action, hostname, ' .
539		'snmp_community, snmp_version, snmp_timeout, snmp_username, snmp_password, ' .
540		'snmp_auth_protocol, snmp_priv_passphrase, snmp_priv_protocol, snmp_context, snmp_engine_id, ' .
541		'snmp_port, rrd_name, rrd_path, rrd_num, rrd_step, rrd_next_step, arg1, arg2, arg3, present) ' .
542		'VALUES';
543
544	$sql_suffix   = ' ON DUPLICATE KEY UPDATE poller_id=VALUES(poller_id), host_id=VALUES(host_id), action=VALUES(action), hostname=VALUES(hostname), ' .
545		'snmp_community=VALUES(snmp_community), snmp_version=VALUES(snmp_version), snmp_timeout=VALUES(snmp_timeout), ' .
546		'snmp_username=VALUES(snmp_username), snmp_password=VALUES(snmp_password), snmp_auth_protocol=VALUES(snmp_auth_protocol), ' .
547		'snmp_priv_passphrase=VALUES(snmp_priv_passphrase), snmp_priv_protocol=VALUES(snmp_priv_protocol), ' .
548		'snmp_context=VALUES(snmp_context), snmp_engine_id=VALUES(snmp_engine_id), snmp_port=VALUES(snmp_port), ' .
549		'rrd_path=VALUES(rrd_path), rrd_num=VALUES(rrd_num), ' .
550		'rrd_step=VALUES(rrd_step), rrd_next_step=VALUES(rrd_next_step), arg1=VALUES(arg1), arg2=VALUES(arg2), ' .
551		'arg3=VALUES(arg3), present=VALUES(present)';
552
553	/* use a reasonable insert buffer, the default is 1MByte */
554	$max_packet   = 256000;
555
556	/* setup somme defaults */
557	$overhead     = strlen($sql_prefix) + strlen($sql_suffix);
558	$buf_len      = 0;
559	$buf_count    = 0;
560	$buffer       = '';
561
562	if (cacti_sizeof($poller_items)) {
563		foreach ($poller_items as $record) {
564			/* take care of invalid entries */
565			if ($record == '') {
566				continue;
567			}
568
569			if ($buf_count == 0) {
570				$delim = ' ';
571			} else {
572				$delim = ', ';
573			}
574
575			$buffer .= $delim . $record;
576
577			$buf_len += strlen($record);
578
579			if ($overhead + $buf_len > $max_packet - 1024) {
580				db_execute($sql_prefix . $buffer . $sql_suffix);
581
582				if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) {
583					db_execute($sql_prefix . $buffer . $sql_suffix, true, $rcnn_id);
584				}
585
586				$buffer    = '';
587				$buf_len   = 0;
588				$buf_count = 0;
589			} else {
590				$buf_count++;
591			}
592		}
593	}
594
595	if ($buf_count > 0) {
596		db_execute($sql_prefix . $buffer . $sql_suffix);
597
598		if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) {
599			db_execute($sql_prefix . $buffer . $sql_suffix, true, $rcnn_id);
600		}
601	}
602
603	/* remove stale records FROM the poller cache */
604	if ($ids != '') {
605		db_execute_prepared("DELETE FROM poller_item
606			WHERE present = 0
607			AND poller_id = ?
608			AND local_data_id IN ($ids)",
609			array($poller_id));
610
611		if (($rcnn_id = poller_push_to_remote_db_connect($poller_id, true)) !== false) {
612			db_execute_prepared("DELETE FROM poller_item
613				WHERE present = 0
614				AND poller_id = ?
615				AND local_data_id IN ($ids)",
616				array($poller_id), true, $rcnn_id);
617		}
618	} else {
619		/* only handle explicitely given local_data_ids */
620	}
621}
622
623/** for a given data template, update all input data and the poller cache
624 * @param int $host_id - id of host, if any
625 * @param int $local_data_id - id of a single data source, if any
626 * @param int $data_template_id - id of data template
627 * works on table data_input_data and poller cache
628 */
629function push_out_host($host_id, $local_data_id = 0, $data_template_id = 0) {
630	global $config;
631
632	include_once($config['library_path'] . '/api_data_source.php');
633
634	/* ok here's the deal: first we need to find every data source that uses this host.
635	then we go through each of those data sources, finding each one using a data input method
636	with "special fields". if we find one, fill it will the data here FROM this host */
637	/* setup the poller items array */
638	$poller_items    = array();
639	$local_data_ids  = array();
640	$hosts           = array();
641	$template_fields = array();
642	$sql_where       = '';
643
644	/* setup the sql where, and if using a host, get it's host information */
645	if ($host_id != 0) {
646		/* get all information about this host so we can write it to the data source */
647		$hosts[$host_id] = db_fetch_row_prepared('SELECT ' . SQL_NO_CACHE . ' id AS host_id, host.*
648			FROM host WHERE id = ?',
649			array($host_id));
650
651		$sql_where .= ' AND dl.host_id=' . $host_id;
652	}
653
654	/* sql WHERE for local_data_id */
655	if ($local_data_id != 0) {
656		$sql_where .= ' AND dl.id=' . $local_data_id;
657	}
658
659	/* sql WHERE for data_template_id */
660	if ($data_template_id != 0) {
661		$sql_where .= ' AND dtd.data_template_id=' . $data_template_id;
662	}
663
664	$data_sources = db_fetch_assoc('SELECT ' . SQL_NO_CACHE . " dtd.id,
665		dtd.data_input_id, dtd.local_data_id,
666		dtd.local_data_template_data_id, dl.host_id,
667		dl.snmp_query_id, dl.snmp_index
668		FROM data_local AS dl
669		INNER JOIN data_template_data AS dtd
670		ON dl.id=dtd.local_data_id
671		WHERE dtd.data_input_id>0
672		AND (dl.snmp_query_id = 0 OR (dl.snmp_query_id > 0 AND dl.snmp_index != ''))
673		$sql_where");
674
675	/* loop through each matching data source */
676	if (cacti_sizeof($data_sources)) {
677		foreach ($data_sources as $data_source) {
678			/* set the host information */
679			if (!isset($hosts[$data_source['host_id']])) {
680				$hosts[$data_source['host_id']] = db_fetch_row_prepared('SELECT *
681					FROM host
682					WHERE id = ?',
683					array($data_source['host_id']));
684			}
685			$host = $hosts[$data_source['host_id']];
686
687			/* get field information FROM the data template */
688			if (!isset($template_fields[$data_source['local_data_template_data_id']])) {
689				$template_fields[$data_source['local_data_template_data_id']] = db_fetch_assoc_prepared('SELECT ' . SQL_NO_CACHE . '
690					did.value, did.t_value, dif.id, dif.type_code
691					FROM data_input_fields AS dif
692					LEFT JOIN data_input_data AS did
693					ON dif.id=did.data_input_field_id
694					WHERE dif.data_input_id = ?
695					AND did.data_template_data_id = ?
696					AND (did.t_value="" OR did.t_value is null)
697					AND dif.input_output = "in"',
698					array($data_source['data_input_id'], $data_source['local_data_template_data_id']));
699			}
700
701			/* loop through each field contained in the data template and push out a host value if:
702			 - the field is a valid "host field"
703			 - the value of the field is empty
704			 - the field is set to 'templated' */
705			if (cacti_sizeof($template_fields[$data_source['local_data_template_data_id']])) {
706				foreach ($template_fields[$data_source['local_data_template_data_id']] as $template_field) {
707					if (preg_match('/^' . VALID_HOST_FIELDS . '$/i', $template_field['type_code']) && $template_field['value'] == '' && $template_field['t_value'] == '') {
708						// handle special case type_code
709						if ($template_field['type_code'] == 'host_id') {
710							$template_field['type_code'] = 'id';
711						}
712
713						db_execute_prepared('REPLACE INTO data_input_data
714							(data_input_field_id, data_template_data_id, value)
715							VALUES (?, ?, ?)',
716							array($template_field['id'], $data_source['id'], $host[$template_field['type_code']]));
717					}
718				}
719			}
720
721			/* flag an update to the poller cache as well */
722			$local_data_ids[] = $data_source['local_data_id'];
723
724			/* create a new compatible structure */
725			$data = $data_source;
726			$data['id'] = $data['local_data_id'];
727
728			$poller_items = array_merge($poller_items, update_poller_cache($data));
729		}
730	}
731
732	if (cacti_sizeof($hosts)) {
733		foreach($hosts as $host) {
734			if (isset($host['poller_id'])) {
735				$poller_ids[$host['poller_id']] = $host['poller_id'];
736			}
737		}
738
739		if (cacti_sizeof($poller_ids) > 1) {
740			cacti_log('WARNING: function push_out_host() discovered more than a single host', false, 'POLLER');
741		}
742	}
743
744	$poller_id = db_fetch_cell_prepared('SELECT poller_id
745		FROM host
746		WHERE id = ?',
747		array($host_id));
748
749	if (cacti_sizeof($local_data_ids)) {
750		poller_update_poller_cache_from_buffer($local_data_ids, $poller_items, $poller_id);
751	}
752
753	api_data_source_cache_crc_update($poller_id);
754}
755
756function data_input_whitelist_check($data_input_id) {
757	global $config;
758
759	static $data_input_whitelist = null;
760	static $validated_input_ids  = null;
761	static $notified = array();
762
763	// no whitelist file defined, everything whitelisted
764	if (!isset($config['input_whitelist'])) {
765		return true;
766	}
767
768	// whitelist is configured but does not exist, means nothing whitelisted
769	if (!file_exists($config['input_whitelist'])) {
770		return false;
771	}
772
773	// load whitelist, but only once within process execution
774	if ($data_input_whitelist == null) {
775		$data_input_ids = array_rekey(
776			db_fetch_assoc('SELECT * FROM data_input'),
777			'hash', array('id', 'name', 'input_string')
778		);
779
780		$data_input_whitelist = json_decode(file_get_contents($config['input_whitelist']), true);
781		if ($data_input_whitelist === null) {
782			cacti_log('ERROR: Failed to parse input whitelist file: ' . $config['input_whitelist']);
783			return true;
784		}
785
786		if (cacti_sizeof($data_input_ids)) {
787			foreach ($data_input_ids as $hash => $id) {
788				if ($id['input_string'] != '') {
789					if (isset($data_input_whitelist[$hash])) {
790						if ($data_input_whitelist[$hash] == $id['input_string']) {
791							$validated_input_ids[$id['id']] = true;
792						} else {
793							cacti_log('ERROR: Whitelist entry failed validation for Data Input: ' . $id['name'] . '[ ' . $id['id'] . ' ].  Data Collection will not run.  Run CLI command input_whitelist.php --audit and --update to remediate.');
794							$validated_input_ids[$id['id']] = false;
795						}
796					} else {
797						cacti_log('WARNING: Whitelist entry missing for Data Input: ' . $id['name'] . '[ ' . $id['id'] . ' ].  Run CLI command input_whitelist.php --update to remediate.');
798						$validated_input_ids[$id['id']] = true;
799					}
800				} else {
801					$validated_input_ids[$id['id']] = true;
802				}
803			}
804		}
805	}
806
807	if (isset($validated_input_ids[$data_input_id])) {
808		if ($validated_input_ids[$data_input_id] == true) {
809			return true;
810		} else {
811			cacti_log('WARNING: Data Input ' . $data_input_id . ' failing validation check.');
812			$notified[$data_input_id] = true;
813			return false;
814		}
815	} else {
816		return true;
817	}
818}
819
820function utilities_get_mysql_recommendations() {
821	global $config, $local_db_cnn_id;
822
823	// MySQL/MariaDB Important Variables
824	// Assume we are successfully, until we aren't!
825	$result = DB_STATUS_SUCCESS;
826
827	if ($config['poller_id'] == 1) {
828		$variables = array_rekey(db_fetch_assoc('SHOW GLOBAL VARIABLES'), 'Variable_name', 'Value');
829	} else {
830		$variables = array_rekey(db_fetch_assoc('SHOW GLOBAL VARIABLES', false, $local_db_cnn_id), 'Variable_name', 'Value');
831	}
832
833	$memInfo = utilities_get_system_memory();
834
835	if (strpos($variables['version'], 'MariaDB') !== false) {
836		$database = 'MariaDB';
837		$version  = str_replace('-MariaDB', '', $variables['version']);
838
839		if (isset($variables['innodb_version'])) {
840			$link_ver = substr($variables['innodb_version'], 0, 3);
841		} else {
842			$link_ver = '5.5';
843		}
844	} else {
845		$database = 'MySQL';
846		$version  = $variables['version'];
847		$link_ver = substr($variables['version'], 0, 3);
848	}
849
850	$recommendations = array(
851		'version' => array(
852			'value' => '5.6',
853			'class' => 'warning',
854			'measure' => 'ge',
855			'comment' => __('MySQL 5.6+ and MariaDB 10.0+ are great releases, and are very good versions to choose. Make sure you run the very latest release though which fixes a long standing low level networking issue that was causing spine many issues with reliability.')
856			)
857	);
858
859	if (isset($variables['innodb_version']) && version_compare($variables['innodb_version'], '5.6', '<')) {
860		if (version_compare($link_ver, '5.5', '>=')) {
861			if (!isset($variables['innodb_version'])) {
862				$recommendations += array(
863					'innodb' => array(
864						'value' => 'ON',
865						'class' => 'warning',
866						'measure' => 'equalint',
867						'comment' => __('It is STRONGLY recommended that you enable InnoDB in any %s version greater than 5.5.3.', $database)
868					)
869				);
870
871				$variables['innodb'] = 'UNSET';
872			}
873		}
874
875		$recommendations += array(
876			'collation_server' => array(
877				'value' => 'utf8mb4_unicode_ci',
878				'class' => 'warning',
879				'measure' => 'equal',
880				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4_unicode_ci collation type as some characters take more than a single byte.')
881				),
882			'character_set_server' => array(
883				'value' => 'utf8mb4',
884				'class' => 'warning',
885				'measure' => 'equal',
886				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.')
887				),
888			'character_set_client' => array(
889				'value' => 'utf8mb4',
890				'class' => 'warning',
891				'measure' => 'equal',
892				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.')
893				)
894		);
895	} else {
896		if (version_compare($link_ver, '5.2', '>=')) {
897			if (!isset($variables['innodb_version'])) {
898				$recommendations += array(
899					'innodb' => array(
900						'value' => 'ON',
901						'class' => 'warning',
902						'measure' => 'equalint',
903						'comment' => __('It is recommended that you enable InnoDB in any %s version greater than 5.1.', $database)
904					)
905				);
906
907				$variables['innodb'] = 'UNSET';
908			}
909		}
910
911		$recommendations += array(
912			'collation_server' => array(
913				'value' => 'utf8mb4_unicode_ci',
914				'measure' => 'equal',
915				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4_unicode_ci collation type as some characters take more than a single byte.')
916				),
917			'character_set_server' => array(
918				'value' => 'utf8mb4',
919				'class' => 'warning',
920				'measure' => 'equal',
921				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.')
922				),
923			'character_set_client' => array(
924				'value' => 'utf8mb4',
925				'class' => 'warning',
926				'measure' => 'equal',
927				'comment' => __('When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.')
928				)
929		);
930	}
931
932	$recommendations += array(
933		'max_connections' => array(
934			'value'   => '100',
935			'measure' => 'ge',
936			'comment' => __('Depending on the number of logins and use of spine data collector, %s will need many connections.  The calculation for spine is: total_connections = total_processes * (total_threads + script_servers + 1), then you must leave headroom for user connections, which will change depending on the number of concurrent login accounts.', $database)
937			),
938		'table_cache' => array(
939			'value'   => '200',
940			'measure' => 'ge',
941			'comment' => __('Keeping the table cache larger means less file open/close operations when using innodb_file_per_table.')
942			),
943		'max_allowed_packet' => array(
944			'value'   => 16777216,
945			'measure' => 'ge',
946			'comment' => __('With Remote polling capabilities, large amounts of data will be synced from the main server to the remote pollers.  Therefore, keep this value at or above 16M.')
947			),
948		'max_heap_table_size' => array(
949			'value'   => '1.6',
950			'measure' => 'pmem',
951			'class'   => 'warning',
952			'comment' => __('If using the Cacti Performance Booster and choosing a memory storage engine, you have to be careful to flush your Performance Booster buffer before the system runs out of memory table space.  This is done two ways, first reducing the size of your output column to just the right size.  This column is in the tables poller_output, and poller_output_boost.  The second thing you can do is allocate more memory to memory tables.  We have arbitrarily chosen a recommended value of 10%% of system memory, but if you are using SSD disk drives, or have a smaller system, you may ignore this recommendation or choose a different storage engine.  You may see the expected consumption of the Performance Booster tables under Console -> System Utilities -> View Boost Status.')
953			),
954		'tmp_table_size' => array(
955			'value'   => '1.6',
956			'measure' => 'pmem',
957			'class'   => 'warning',
958			'comment' => __('When executing subqueries, having a larger temporary table size, keep those temporary tables in memory.')
959			),
960		'join_buffer_size' => array(
961			'value'   => '3.2',
962			'measure' => 'pmem',
963			'class'   => 'warning',
964			'comment' => __('When performing joins, if they are below this size, they will be kept in memory and never written to a temporary file.')
965			),
966		'innodb_file_per_table' => array(
967			'value'   => 'ON',
968			'measure' => 'equalint',
969			'class'   => 'error',
970			'comment' => __('When using InnoDB storage it is important to keep your table spaces separate.  This makes managing the tables simpler for long time users of %s.  If you are running with this currently off, you can migrate to the per file storage by enabling the feature, and then running an alter statement on all InnoDB tables.', $database)
971			),
972		'innodb_file_format' => array(
973			'value'   => 'Barracuda',
974			'measure' => 'equal',
975			'class'   => 'error',
976			'comment' => __('When using innodb_file_per_table, it is important to set the innodb_file_format to Barracuda.  This setting will allow longer indexes important for certain Cacti tables.')
977			),
978		'innodb_large_prefix' => array(
979			'value'   => '1',
980			'measure' => 'equalint',
981			'class'   => 'error',
982			'comment' => __('If your tables have very large indexes, you must operate with the Barracuda innodb_file_format and the innodb_large_prefix equal to 1.  Failure to do this may result in plugins that can not properly create tables.')
983			),
984		'innodb_buffer_pool_size' => array(
985			'value'   => '25',
986			'measure' => 'pmem',
987			'class' => 'warning',
988			'comment' => __('InnoDB will hold as much tables and indexes in system memory as is possible.  Therefore, you should make the innodb_buffer_pool large enough to hold as much of the tables and index in memory.  Checking the size of the /var/lib/mysql/cacti directory will help in determining this value.  We are recommending 25%% of your systems total memory, but your requirements will vary depending on your systems size.')
989			),
990		'innodb_doublewrite' => array(
991			'value'   => 'ON',
992			'measure' => 'equalint',
993			'class' => 'error',
994			'comment' => __('This settings should remain ON unless your Cacti instances is running on either ZFS or FusionI/O which both have internal journaling to accomodate abrupt system crashes.  However, if you have very good power, and your systems rarely go down and you have backups, turning this setting to OFF can net you almost a 50% increase in database performance.')
995			),
996		'innodb_additional_mem_pool_size' => array(
997			'value'   => '80M',
998			'measure' => 'gem',
999			'comment' => __('This is where metadata is stored. If you had a lot of tables, it would be useful to increase this.')
1000			),
1001		'innodb_lock_wait_timeout' => array(
1002			'value'   => '50',
1003			'measure' => 'ge',
1004			'comment' => __('Rogue queries should not for the database to go offline to others.  Kill these queries before they kill your system.')
1005			),
1006		'innodb_flush_method' => array(
1007			'value'   => 'O_DIRECT',
1008			'measure' => 'eq',
1009			'comment' => __('Maximum I/O performance happens when you use the O_DIRECT method to flush pages.')
1010			)
1011	);
1012
1013	if (isset($variables['innodb_version'])) {
1014		if (version_compare($variables['innodb_version'], '5.6', '<')) {
1015			$recommendations += array(
1016				'innodb_flush_log_at_trx_commit' => array(
1017					'value'   => '2',
1018					'measure' => 'equal',
1019					'comment' => __('Setting this value to 2 means that you will flush all transactions every second rather than at commit.  This allows %s to perform writing less often.', $database)
1020				),
1021				'innodb_file_io_threads' => array(
1022					'value'   => '16',
1023					'measure' => 'ge',
1024					'comment' => __('With modern SSD type storage, having multiple io threads is advantageous for applications with high io characteristics.')
1025					)
1026			);
1027		} elseif (version_compare($variables['innodb_version'], '10.5', '<')) {
1028			$recommendations += array(
1029				'innodb_flush_log_at_timeout' => array(
1030					'value'   => '3',
1031					'measure'  => 'ge',
1032					'comment'  => __('As of %s %s, the you can control how often %s flushes transactions to disk.  The default is 1 second, but in high I/O systems setting to a value greater than 1 can allow disk I/O to be more sequential', $database, $version, $database),
1033					),
1034				'innodb_read_io_threads' => array(
1035					'value'   => '32',
1036					'measure' => 'ge',
1037					'comment' => __('With modern SSD type storage, having multiple read io threads is advantageous for applications with high io characteristics.  Depending on your MariaDB/MySQL versions, this value can go as high as 64.  But try to keep the number less than your total SMT threads on the database server.')
1038					),
1039				'innodb_write_io_threads' => array(
1040					'value'   => '16',
1041					'measure' => 'ge',
1042					'comment' => __('With modern SSD type storage, having multiple write io threads is advantageous for applications with high io characteristics.  Depending on your MariaDB/MySQL versions, this value can go as high as 64.  But try to keep the number less than your total SMT threads on the database server.')
1043					),
1044				'innodb_buffer_pool_instances' => array(
1045					'value' => '16',
1046					'measure' => 'pinst',
1047					'class' => 'warning',
1048					'comment' => __('%s will divide the innodb_buffer_pool into memory regions to improve performance for versions of MariaDB less than 10.5.  The max value is 64.  When your innodb_buffer_pool is less than 1GB, you should use the pool size divided by 128MB.  Continue to use this equation upto the max of 64.', $database)
1049					),
1050				'innodb_io_capacity' => array(
1051					'value' => '5000',
1052					'measure' => 'ge',
1053					'class' => 'warning',
1054					'comment' => __('If you have SSD disks, use this suggestion.  If you have physical hard drives, use 200 * the number of active drives in the array.  If using NVMe or PCIe Flash, much larger numbers as high as 100000 can be used.')
1055					),
1056				'innodb_io_capacity_max' => array(
1057					'value' => '10000',
1058					'measure' => 'ge',
1059					'class' => 'warning',
1060					'comment' => __('If you have SSD disks, use this suggestion.  If you have physical hard drives, use 2000 * the number of active drives in the array.  If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used.')
1061					),
1062				'innodb_flush_neighbor_pages' => array(
1063					'value' => 'none',
1064					'measure' => 'eq',
1065					'class' => 'warning',
1066					'comment' => __('If you have SSD disks, use this suggestion. Otherwise, do not set this setting.')
1067					)
1068			);
1069		} else {
1070			$recommendations += array(
1071				'innodb_flush_log_at_timeout' => array(
1072					'value'   => '3',
1073					'measure'  => 'ge',
1074					'comment'  => __('As of %s %s, the you can control how often %s flushes transactions to disk.  The default is 1 second, but in high I/O systems setting to a value greater than 1 can allow disk I/O to be more sequential', $database, $version, $database),
1075					),
1076				'innodb_read_io_threads' => array(
1077					'value'   => '32',
1078					'measure' => 'ge',
1079					'comment' => __('With modern SSD type storage, having multiple read io threads is advantageous for applications with high io characteristics.')
1080					),
1081				'innodb_write_io_threads' => array(
1082					'value'   => '16',
1083					'measure' => 'ge',
1084					'comment' => __('With modern SSD type storage, having multiple write io threads is advantageous for applications with high io characteristics.')
1085					),
1086				'innodb_io_capacity' => array(
1087					'value' => '5000',
1088					'measure' => 'ge',
1089					'class' => 'warning',
1090					'comment' => __('If you have SSD disks, use this suggestion.  If you have physical hard drives, use 200 * the number of active drives in the array.  If using NVMe or PCIe Flash, much larger numbers as high as 100000 can be used.')
1091					),
1092				'innodb_io_capacity_max' => array(
1093					'value' => '10000',
1094					'measure' => 'ge',
1095					'class' => 'warning',
1096					'comment' => __('If you have SSD disks, use this suggestion.  If you have physical hard drives, use 2000 * the number of active drives in the array.  If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used.')
1097					),
1098				'innodb_flush_neighbor_pages' => array(
1099					'value' => 'none',
1100					'measure' => 'eq',
1101					'class' => 'warning',
1102					'comment' => __('If you have SSD disks, use this suggestion. Otherwise, do not set this setting.')
1103					)
1104			);
1105
1106			unset($recommendations['innodb_additional_mem_pool_size']);
1107		}
1108	}
1109
1110	if ($database == 'MariaDB' && version_compare($version, '10.2.4', '>')) {
1111		$recommendations['innodb_doublewrite'] = array(
1112			'value'   => 'OFF',
1113			'measure' => 'equalint',
1114			'class' => 'error',
1115			'comment' => __('When using MariaDB 10.2.4 and above, this setting should be off if atomic writes are enabled.  Therefore, please enable atomic writes instead of the double write buffer as it will increase performance.')
1116		);
1117
1118		$recommendations['innodb_use_atomic_writes'] = array(
1119			'value'   => 'ON',
1120			'measure' => 'equalint',
1121			'class' => 'error',
1122			'comment' => __('When using MariaDB 10.2.4 and above, you can use atomic writes over the doublewrite buffer to increase performance.')
1123		);
1124	}
1125
1126	if (file_exists('/etc/my.cnf.d/server.cnf')) {
1127		$location = '/etc/my.cnf.d/server.cnf';
1128	} else {
1129		$location = '/etc/my.cnf';
1130	}
1131
1132	if ($database == 'MariaDB') {
1133		$variables_url = 'https://mariadb.com/kb/en/server-system-variables/';
1134	} else {
1135		$variables_url = html_escape('https://dev.mysql.com/doc/refman/' . $link_ver . '/en/server-system-variables.html');
1136	}
1137
1138	print '<tr class="tableHeader tableFixed">';
1139	print '<th colspan="2">' . __('%s Tuning', $database) . ' (' . $location . ') - [ <a class="linkOverDark" target="_blank" href="' . $variables_url . '">' .  __('Documentation') . '</a> ] ' . __('Note: Many changes below require a database restart') . '</th>';
1140	print '</tr>';
1141
1142	form_alternate_row();
1143	print "<td colspan='2' style='text-align:left;padding:0px'>";
1144	print "<table id='mysql' class='cactiTable' style='width:100%'>";
1145	print "<thead>";
1146	print "<tr class='tableHeader'>";
1147	print "  <th class='tableSubHeaderColumn'>" . __('Variable')          . "</th>";
1148	print "  <th class='tableSubHeaderColumn right'>" . __('Current Value'). "</th>";
1149	print "  <th class='tableSubHeaderColumn center'>&nbsp;</th>";
1150	print "  <th class='tableSubHeaderColumn'>" . __('Recommended Value') . "</th>";
1151	print "  <th class='tableSubHeaderColumn'>" . __('Comments')          . "</th>";
1152	print "</tr>";
1153	print "</thead>";
1154
1155	$innodb_pool_size = 0;
1156	foreach ($recommendations as $name => $r) {
1157		if (isset($variables[$name])) {
1158			$class = '';
1159
1160			// Unset $passed so that we only display fields that we checked
1161			unset($passed);
1162
1163			$compare = '';
1164			$value_recommend = isset($r['value']) ? $r['value'] : '<unset>';
1165			$value_current   = isset($variables[$name]) ? $variables[$name] : '<unset>';
1166			$value_display   = $value_current;
1167
1168			switch($r['measure']) {
1169			case 'gem':
1170				$compare = '>=';
1171				$value_display = ($variables[$name]/1024/1024) . ' M';
1172				$value = trim($r['value'], 'M') * 1024 * 1024;
1173				if ($variables[$name] < $value) {
1174					$passed = false;
1175				}
1176				break;
1177			case 'ge':
1178				$compare = '>=';
1179				$passed = (version_compare($value_current, $value_recommend, '>='));
1180				break;
1181			case 'equalint':
1182			case 'equal':
1183				$compare = '=';
1184				if (!isset($variables[$name])) {
1185					$passed = false;
1186				} else {
1187					$e_var = $variables[$name];
1188					$e_rec = $value_recommend;
1189					if ($r['measure'] == 'equalint') {
1190						$e_var = (!strcasecmp('on', $e_var) ? 1 : (!strcasecmp('off', $e_var) ? 0 : $e_var));
1191						$e_rec = (!strcasecmp('on', $e_rec) ? 1 : (!strcasecmp('off', $e_rec) ? 0 : $e_rec));
1192					}
1193					$passed = $e_var == $e_rec;
1194				}
1195				break;
1196			case 'pmem':
1197				if (isset($memInfo['MemTotal'])) {
1198					$totalMem = $memInfo['MemTotal'];
1199				} elseif (isset($memInfo['TotalVisibleMemorySize'])) {
1200					$totalMem = $memInfo['TotalVisibleMemorySize'];
1201				} else {
1202					break;
1203				}
1204
1205				if ($name == 'innodb_buffer_pool_size') {
1206					$innodb_pool_size = $variables[$name];
1207				}
1208
1209				$compare = '>=';
1210				$passed = ($variables[$name] >= ($r['value']*$totalMem/100));
1211				$value_display = round($variables[$name]/1024/1024, 2) . ' M';
1212				$value_recommend = round($r['value']*$totalMem/100/1024/1024, 2) . ' M';
1213				break;
1214			case 'pinst':
1215				$compare = '>=';
1216
1217				// Divide the buffer pool size by 128MB, and ensure 1 or more
1218				$pool_instances = round(($innodb_pool_size / 1024 / 1024 / 128) + 0.5);
1219
1220				if ($pool_instances < 1) {
1221					$pool_instances = 1;
1222				} elseif ($pool_instances > 64) {
1223					$pool_instances = 64;
1224				}
1225
1226				$passed = ($variables[$name] >= $pool_instances);
1227				$value_recommend = $pool_instances;
1228				break;
1229			default:
1230				$compare = $r['measure'];
1231				$passed = true;
1232			}
1233
1234			if (isset($passed)) {
1235				if (!$passed) {
1236					if (isset($r['class']) && $r['class'] == 'warning') {
1237						$class = 'textWarning';
1238						if ($result == DB_STATUS_SUCCESS) {
1239							$result = DB_STATUS_WARNING;
1240						}
1241					} else {
1242						$class = 'textError';
1243						if ($result != DB_STATUS_ERROR) {
1244							$result = DB_STATUS_ERROR;
1245						}
1246					}
1247				}
1248
1249				form_alternate_row();
1250
1251				print "<td>" . $name . "</td>";
1252				print "<td class='right $class'>$value_display</td>";
1253				print "<td class='center'>$compare</td>";
1254				print "<td>$value_recommend</td>";
1255				print "<td class='$class'>" . $r['comment'] . "</td>";
1256
1257				form_end_row();
1258			}
1259
1260		}
1261	}
1262	print "</table>";
1263	print "</td>";
1264	form_end_row();
1265	return $result;
1266}
1267
1268function utilities_php_modules() {
1269	/*
1270	   Gather phpinfo into a string variable - This has to be done before
1271	   any headers are sent to the browser, as we are going to do some
1272	   output buffering fun
1273	*/
1274
1275	ob_start();
1276	phpinfo(INFO_MODULES);
1277	$php_info = ob_get_contents();
1278	ob_end_clean();
1279
1280	/* Remove nasty style sheets, links and other junk */
1281	$php_info = str_replace("\n", '', $php_info);
1282	$php_info = preg_replace('/^.*\<body\>/', '', $php_info);
1283	$php_info = preg_replace('/\<\/body\>.*$/', '', $php_info);
1284	$php_info = preg_replace('/(\<a name.*\>)([^<>]*)(\<\/a\>)/U', '$2', $php_info);
1285	$php_info = preg_replace('/\<img.*\>/U', '', $php_info);
1286	$php_info = preg_replace('/\<div[^<>]*\>\<\/div\>/U', '', $php_info);
1287	$php_info = preg_replace('/\<\/?address\>/', '', $php_info);
1288
1289	return $php_info;
1290}
1291
1292function memory_bytes($val) {
1293	$val  = trim($val);
1294	$last = strtolower($val[strlen($val)-1]);
1295	$val  = trim($val, 'GMKgmk');
1296	switch($last) {
1297		case 'g':
1298			$val *= 1024;
1299		case 'm':
1300			$val *= 1024;
1301		case 'k':
1302			$val *= 1024;
1303	}
1304
1305	return $val;
1306}
1307
1308function memory_readable($val) {
1309	if ($val < 1024) {
1310		$val_label = 'bytes';
1311	} elseif ($val < 1048576) {
1312		$val_label = 'K';
1313		$val /= 1024;
1314	} elseif ($val < 1073741824) {
1315		$val_label = 'M';
1316		$val /= 1048576;
1317	} else {
1318		$val_label = 'G';
1319		$val /= 1073741824;
1320	}
1321
1322	return $val . $val_label;
1323}
1324
1325function utilities_get_system_memory() {
1326	global $config;
1327
1328	$memInfo = array();
1329
1330	if ($config['cacti_server_os'] == 'win32') {
1331		exec('wmic os get FreePhysicalMemory', $memInfo['FreePhysicalMemory']);
1332		exec('wmic os get FreeSpaceInPagingFiles', $memInfo['FreeSpaceInPagingFiles']);
1333		exec('wmic os get FreeVirtualMemory', $memInfo['FreeVirtualMemory']);
1334		exec('wmic os get SizeStoredInPagingFiles', $memInfo['SizeStoredInPagingFiles']);
1335		exec('wmic os get TotalVirtualMemorySize', $memInfo['TotalVirtualMemorySize']);
1336		exec('wmic os get TotalVisibleMemorySize', $memInfo['TotalVisibleMemorySize']);
1337		if (cacti_sizeof($memInfo)) {
1338			foreach ($memInfo as $key => $values) {
1339				$memInfo[$key] = $values[1] * 1000;
1340			}
1341		}
1342	} else {
1343		$file = '';
1344		if (file_exists('/proc/meminfo')) {
1345			$file = '/proc/meminfo';
1346		} elseif(file_exists('/linux/proc/meminfo')) {
1347			$file = '/linux/proc/meminfo';
1348		} elseif(file_exists('/compat/linux/proc/meminfo')) {
1349			$file = '/compat/linux/proc/meminfo';
1350		} elseif(file_exists('/usr/compat/linux/proc/meminfo')) {
1351			$file = '/usr/compat/linux/proc/meminfo';
1352		}
1353
1354		if ($file != '') {
1355			$data = explode("\n", file_get_contents($file));
1356			foreach ($data as $l) {
1357				if (trim($l) != '') {
1358					list($key, $val) = explode(':', $l);
1359					$val = trim($val, " kBb\r\n");
1360					$memInfo[$key] = round($val * 1000,0);
1361				}
1362			}
1363		} elseif (file_exists('/usr/bin/free')) {
1364			$menInfo   = array();
1365			$output    = array();
1366			$exit_code = 0;
1367
1368			exec('/usr/bin/free', $output, $exit_code);
1369			if ($exit_code == 0) {
1370				foreach ($output as $line) {
1371					$parts = preg_split('/\s+/', $line);
1372					switch ($parts[0]) {
1373					case 'Mem:':
1374						$memInfo['MemTotal']  = (isset($parts[1]) ? $parts[1]*1000:0);
1375						$memInfo['MemUsed']   = (isset($parts[2]) ? $parts[2]*1000:0);
1376						$memInfo['MemFree']   = (isset($parts[3]) ? $parts[3]*1000:0);
1377						$memInfo['MemShared'] = (isset($parts[4]) ? $parts[4]*1000:0);
1378						$memInfo['Buffers']   = (isset($parts[5]) ? $parts[5]*1000:0);
1379						$memInfo['Cached']    = (isset($parts[6]) ? $parts[6]*1000:0);
1380						break;
1381					case '-/+':
1382						$memInfo['Active']    = (isset($parts[2]) ? $parts[3]*1000:0);
1383						$memInfo['Inactive']  = (isset($parts[3]) ? $parts[3]*1000:0);
1384						break;
1385					case 'Swap:':
1386						$memInfo['SwapTotal'] = (isset($parts[1]) ? $parts[1]*1000:0);
1387						$memInfo['SwapUsed']  = (isset($parts[2]) ? $parts[2]*1000:0);
1388						break;
1389					}
1390				}
1391			}
1392		}
1393	}
1394
1395	return $memInfo;
1396}
1397
1398function utility_php_sort_extensions($a, $b) {
1399	$name_a = isset($a['name']) ? $a['name'] : '';
1400	$name_b = isset($b['name']) ? $b['name'] : '';
1401	return strcasecmp($name_a, $name_b);
1402}
1403
1404
1405function utility_php_extensions() {
1406	global $config;
1407
1408	$php = cacti_escapeshellcmd(read_config_option('path_php_binary', true));
1409	$php_file = cacti_escapeshellarg($config['base_path'] . '/install/cli_check.php') . ' extensions';
1410	$json = shell_exec($php . ' -q ' . $php_file);
1411	$ext = @json_decode($json, true);
1412
1413	utility_php_verify_extensions($ext, 'web');
1414	utility_php_set_installed($ext);
1415
1416	return $ext;
1417}
1418
1419function utility_php_verify_extensions(&$extensions, $source) {
1420	global $config;
1421
1422	if (empty($extensions)) {
1423		$extensions = array(
1424			'ctype'     => array('cli' => false, 'web' => false),
1425			'date'      => array('cli' => false, 'web' => false),
1426			'filter'    => array('cli' => false, 'web' => false),
1427			'gd'        => array('cli' => false, 'web' => false),
1428			'gmp'       => array('cli' => false, 'web' => false),
1429			'hash'      => array('cli' => false, 'web' => false),
1430			'json'      => array('cli' => false, 'web' => false),
1431			'ldap'      => array('cli' => false, 'web' => false),
1432			'mbstring'  => array('cli' => false, 'web' => false),
1433			'openssl'   => array('cli' => false, 'web' => false),
1434			'pcre'      => array('cli' => false, 'web' => false),
1435			'PDO'       => array('cli' => false, 'web' => false),
1436			'pdo_mysql' => array('cli' => false, 'web' => false),
1437			'session'   => array('cli' => false, 'web' => false),
1438			'simplexml' => array('cli' => false, 'web' => false),
1439			'sockets'   => array('cli' => false, 'web' => false),
1440			'spl'       => array('cli' => false, 'web' => false),
1441			'standard'  => array('cli' => false, 'web' => false),
1442			'xml'       => array('cli' => false, 'web' => false),
1443			'zlib'      => array('cli' => false, 'web' => false)
1444		);
1445
1446		if ($config['cacti_server_os'] == 'unix') {
1447			$extensions['posix'] = array('cli' => false, 'web' => false);
1448		} else {
1449			$extensions['com_dotnet'] = array('cli' => false, 'web' => false);
1450		}
1451	}
1452
1453	uksort($extensions, "utility_php_sort_extensions");
1454
1455	foreach ($extensions as $name=>$extension) {
1456		if (extension_loaded($name)){
1457			$extensions[$name][$source] = true;
1458		}
1459	}
1460}
1461
1462function utility_php_recommends() {
1463	global $config;
1464
1465	$php = cacti_escapeshellcmd(read_config_option('path_php_binary', true));
1466	$php_file = cacti_escapeshellarg($config['base_path'] . '/install/cli_check.php') . ' recommends';
1467	$json = shell_exec($php . ' -q ' . $php_file);
1468	$ext = array('web' => '', 'cli' => '');
1469	$ext['cli'] = @json_decode($json, true);
1470
1471	utility_php_verify_recommends($ext['web'], 'web');
1472	utility_php_set_recommends_text($ext);
1473
1474	return $ext;
1475}
1476
1477function utility_get_formatted_bytes($input_value, $wanted_type, &$output_value, $default_type = 'B') {
1478
1479	$default_type = strtoupper($default_type);
1480	$multiplier = array(
1481		'B' => 1,
1482		'K' => 1024,
1483		'M' => 1024*1024,
1484		'G' => 1024*1024*1024,
1485	);
1486
1487	if ($input_value > 0 && preg_match('/([0-9.]+)([BKMG]){0,1}/i',$input_value,$matches)) {
1488		$input_value = $matches[1];
1489		if (isset($matches[2])) {
1490			$default_type = $matches[2];
1491		}
1492
1493		if (isset($multiplier[$default_type])) {
1494			$input_value = $input_value * $multiplier[$default_type];
1495		}
1496	}
1497
1498	if (intval($input_value) < 0) {
1499		$output_value = $input_value . $wanted_type;
1500	} elseif (isset($multiplier[$wanted_type])) {
1501		$output_value = ($input_value / $multiplier[$wanted_type]) . $wanted_type;
1502	} elseif (isset($multiplier[$default_type])) {
1503		$output_value = ($input_value * $multiplier[$default_type]) . $default_type;
1504	} else {
1505		$output_value = $input_value . 'B';
1506	}
1507	return $input_value;
1508}
1509
1510function utility_php_verify_recommends(&$recommends, $source) {
1511	global $original_memory_limit;
1512
1513	$rec_version    = '5.4.0';
1514	$rec_memory_mb  = (version_compare(PHP_VERSION, '7.0.0', '<') ? 800 : 400);
1515	$rec_execute_m  = 1;
1516	$memory_ini     = (isset($original_memory_limit) ? $original_memory_limit : ini_get('memory_limit'));
1517
1518	// adjust above appropriately (used in configs)
1519	$rec_execute    = $rec_execute_m * 60;
1520	$rec_memory     = utility_get_formatted_bytes($rec_memory_mb, 'M', $rec_memory_mb, 'M');
1521	$memory_limit   = utility_get_formatted_bytes($memory_ini, 'M', $memory_ini, 'B');
1522
1523	$execute_time   = ini_get('max_execution_time');
1524
1525	$timezone       = ini_get('date.timezone');
1526
1527	$recommends = array(
1528		array(
1529			'name'        => 'version',
1530			'value'       => $rec_version,
1531			'current'     => PHP_VERSION,
1532			'status'      => version_compare(PHP_VERSION, $rec_version, '>=') ? DB_STATUS_SUCCESS : DB_STATUS_ERROR,
1533		),
1534		array(
1535			'name'        => 'memory_limit',
1536			'value'       => $rec_memory_mb,
1537			'current'     => $memory_ini,
1538			'status'      => ($memory_limit <= 0 || $memory_limit >= $rec_memory) ? DB_STATUS_SUCCESS : DB_STATUS_WARNING,
1539		),
1540		array(
1541			'name'        => 'max_execution_time',
1542			'value'       => $rec_execute,
1543			'current'     => $execute_time,
1544			'status'      => ($execute_time <= 0 || $execute_time >= $rec_execute) ? DB_STATUS_SUCCESS : DB_STATUS_WARNING,
1545		),
1546		array(
1547			'name'        => 'date.timezone',
1548			'value'       => '<timezone>',
1549			'current'     => $timezone,
1550			'status'      => ($timezone ? DB_STATUS_SUCCESS : DB_STATUS_ERROR),
1551		),
1552	);
1553}
1554
1555function utility_php_set_recommends_text(&$recs) {
1556	if (is_array($recs) && cacti_sizeof($recs)) {
1557		foreach ($recs as $name => $recommends) {
1558			if (cacti_sizeof($recommends)) {
1559				foreach ($recommends as $index => $recommend) {
1560					if ($recommend['name'] == 'version') {
1561						$recs[$name][$index]['description'] = __('PHP %s is the mimimum version', $recommend['value']);
1562					} elseif ($recommend['name'] == 'memory_limit') {
1563						$recs[$name][$index]['description'] = __('A minimum of %s memory limit', $recommend['value']);
1564					} elseif ($recommend['name'] == 'max_execution_time') {
1565						$recs[$name][$index]['description'] = __('A minimum of %s m execution time', $recommend['value']);
1566					} elseif ($recommend['name'] == 'date.timezone') {
1567						$recs[$name][$index]['description'] = __('A valid timezone that matches MySQL and the system');
1568					}
1569				}
1570			}
1571		}
1572	}
1573}
1574
1575function utility_php_optionals() {
1576	global $config;
1577
1578	$php = cacti_escapeshellcmd(read_config_option('path_php_binary', true));
1579	$php_file = cacti_escapeshellarg($config['base_path'] . '/install/cli_check.php') . ' optionals';
1580	$json = shell_exec($php . ' -q ' . $php_file);
1581	$opt = @json_decode($json, true);
1582
1583	utility_php_verify_optionals($opt, 'web');
1584	utility_php_set_installed($opt);
1585
1586	return $opt;
1587}
1588
1589function utility_php_verify_optionals(&$optionals, $source) {
1590	if (empty($optionals)) {
1591		$optionals = array(
1592			'snmp'          => array('web' => false, 'cli' => false),
1593			'gettext'       => array('web' => false, 'cli' => false),
1594			'TrueType Box'  => array('web' => false, 'cli' => false),
1595			'TrueType Text' => array('web' => false, 'cli' => false),
1596		);
1597	}
1598
1599	foreach ($optionals as $name => $optional) {
1600		if (extension_loaded($name)){
1601			$optionals[$name][$source] = true;
1602		}
1603	}
1604
1605	$optionals['TrueType Box'][$source] = function_exists('imagettfbbox');
1606	$optionals['TrueType Text'][$source] = function_exists('imagettftext');
1607}
1608
1609function utility_php_set_installed(&$extensions) {
1610	foreach ($extensions as $name=>$extension) {
1611		$extensions[$name]['installed'] = $extension['web'] && $extension['cli'];
1612	}
1613}
1614