Proper Sorting at graphs_new.php

[mojiro]

I have realized that the Data Queries tables at graphs_new.php are not ordering using custom order fields that are set inside every xml file, but only using the Index Key.

I have rewrited to order properly. I'm sorry for posting just the code, and not a patch, since I'm not familiar with patches.

- - - - - - - - - - -

1. Open graphs_new.php

2. Locate this comment

Code: Select all

/* determine the order fields */

3. ... and then this comment

Code: Select all

/* generate page list */

4. Replace the following code between these two comments

Code: Select all

					/* determine the order fields */
					$sql_order_fields = array('snmp_index');

					if (isset($xml_array['index_order'])) {
						$sql_order_fields = explode(':', $xml_array['index_order']);
					}

					/* determine the sort order */
					if (isset($xml_array['index_order_type'])) {
						if (
							$xml_array['index_order_type'] == 'numeric'    ||
							$xml_array['index_order_type'] == 'alphabetic' ||
							$xml_array['index_order_type'] == 'natural'
						) {
 							switch ($xml_array['index_order_type']) {

	 							case 'numeric':
									foreach ($sql_order_fields as $k) {
										$sql_order[] = 'CAST(' . $k . ' AS unsigned)';
									}
 								break;
 
 								case 'alphabetic':
									foreach ($sql_order_fields as $k) {
										$sql_order[] = $k;
									}
 								break;

 								case 'natural':
									foreach ($sql_order_fields as $k) {
										$sql_order[] = 'INET_ATON(' . $k . ')';
									}
 								break;

							}

							$sql_order = "\n ORDER BY\n  " . implode(",\n  ", $sql_order);
 						}
					}

					/* get the unique field values from the database */
					$field_names = db_fetch_assoc('
 SELECT DISTINCT
  field_name
 FROM
  host_snmp_cache
 WHERE
  host_id       = ' . $host['id']      . ' AND
  snmp_query_id = ' . $snmp_query['id']
					);

					/* build magic query */
					$sql_query = '';
					$num_visible_fields = sizeof($field_names);
					$i = 0;

					$sql_query_fields     = '';
					$sql_query_fields_sub = '';

					if (sizeof($field_names) > 0) {
						foreach ($field_names as $column) {
							$field_name = $column['field_name'];
							$sql_query_fields     .= ",\n  tmp." . $field_name;
							$sql_query_fields_sub .= ",\n   MAX(CASE WHEN field_name = '" . $field_name . "' THEN field_value ELSE NULL END) AS '" . $field_name . "'";
							$i++;
						}
					}

					$sql_query = '
 SELECT
  tmp.host_id,
  tmp.snmp_query_id,
  tmp.snmp_index' .
$sql_query_fields . '
 FROM
 (
  SELECT
   host_id,
   snmp_query_id,
   snmp_index' .
$sql_query_fields_sub . '
  FROM
   host_snmp_cache
  WHERE
   host_id       = ' . $host['id']       . ' AND
   snmp_query_id = ' . $snmp_query['id'] . '
   ' . $sql_where . '
  GROUP BY
   host_id,
   snmp_query_id,
   snmp_index
 ) AS tmp' . $sql_order . '
 LIMIT
  ' . ($row_limit * ($page - 1)) . ', ' . $row_limit;

					$rows_query = '
 SELECT
  host_id,
  snmp_query_id,
  snmp_index
 FROM
  host_snmp_cache
 WHERE
  host_id       = ' . $host['id']       . ' AND
  snmp_query_id = ' . $snmp_query['id'] . '
  ' . $sql_where . '
 GROUP BY
  host_id,
  snmp_query_id,
  snmp_index
 ';

					$snmp_query_indexes = db_fetch_assoc($sql_query);

					$total_rows = sizeof(db_fetch_assoc($rows_query));

					if (($page - 1) * $row_limit > $total_rows) {
						$page = 1;
						$_REQUEST['page' . $query['id']] = $page;
						load_current_session_value('page' . $query['id'], 'sess_graphs_new_page' . $query['id'], '1');
					}

					if ($total_rows > $row_limit) {
						/* generate page list */

[emiliosic]

This is still broken in Cacti 0.8.8e: inxex_order from the query XML does not seem to be used anywhere; do data_query in graphs_new.php is always sorted by SNMP ID.
I looked at the patch (code sample) from the previous post and doesn't seem to be related to any recent version of Cacti.
Does anyone have working patch?

Thanks!

[BSOD2600]

please report at http://cacti.net/bugs.php and reference this post.