remove_graphs.php doesn't allow for removal of graphs where Host ID = 0

[tddcacti]

Sometimes when a team member deletes a device out of Cacti, they mistakenly select the option to "Leave all Graph(s) and Data Source(s) untouched. Data Source(s) will be disabled however."

This leaves data sources that are disabled and graphs that are never updated again. In our environment, we don't want these hanging around, so I'm looking for a way to get rid of them automatically.

Side note, if there is already a way to do this built into Cacti, I haven't been able to find it. These graphs and data sources don't meet the current definition of "orphaned" that Cacti uses.

So I'm looking into creating a cron job that looks for graphs for which the Host ID has been set to 0 ("None" in the web UI). Then I would use remove_graphs.php to delete both the graph and the data source(s).

However the issue I'm running into, remove_graphs.php as currently written doesn't support --host-id=0. It gives an error:

FATAL: Host ID 0 is invalid

I've made a few edits to this file on my development server and gotten it working, so I would like to submit this as a potential enhancement / bug fix.

There are two edits to be made. First, change this section of code:

if (cacti_sizeof($host_ids)) {
	foreach ($host_ids as $id) {
		if (!is_numeric($id) || $id <= 0) {
			print "FATAL: Host ID $id is invalid" . PHP_EOL;

			exit(1);
		}
	}
}

To this (remove the equals sign):

if (cacti_sizeof($host_ids)) {
	foreach ($host_ids as $id) {
		if (!is_numeric($id) || $id < 0) {
			print "FATAL: Host ID $id is invalid" . PHP_EOL;

			exit(1);
		}
	}
}

This allows valid host IDs to include zero, and not just positive numbers.

Next, edit this section of code:

	$graphs = db_fetch_assoc("SELECT gl.id, gtg.title_cache
		FROM graph_local AS gl
		INNER JOIN graph_templates_graph AS gtg
		ON gl.id=gtg.local_graph_id
		INNER JOIN host AS h
		ON h.id = gl.host_id
		$sql_where");

Remove the reference to the host table altogether:

	$graphs = db_fetch_assoc("SELECT gl.id, gtg.title_cache
		FROM graph_local AS gl
		INNER JOIN graph_templates_graph AS gtg
		ON gl.id=gtg.local_graph_id
		$sql_where");

This last change would impact the ability to use the --host-template-id option, so some additional edits would be necessary. I'm open to suggestions on other ways to do this, if someone's already solved this another way.

[somethingwithproof]

Part 1 of the fix ($id <= 0 to $id < 0) looks correct. Host ID 0 is a valid internal state for "no host assigned," and the CLI tool should support it for cleanup of orphaned graphs.

Part 2 (removing the INNER JOIN host entirely) would work for the basic case but breaks --host-template-id as you noted. It also changes the query behavior for all callers, not just the host_id=0 case.

A more targeted approach: only include the host JOIN when --host-template-id filtering is active, since that is the only clause that references the h table alias.

if (cacti_sizeof($host_template_ids)) {
    $sql_join = 'INNER JOIN host AS h ON h.id = gl.host_id';
    $sql_where .= ' AND h.host_template_id IN (' . implode(',', $host_template_ids) . ')';
} else {
    $sql_join = '';
}

This preserves --host-template-id functionality while allowing host_id=0 graphs to be found. Happy to put together a PR if that approach works.

[tddcacti]

Thanks, yes that makes sense to me. If you're willing to put together a PR, that would be great!

[TheWitness]

Host 0 Graphs should only be visible for and managable if:

1. They are Aggregate Graphs where you manage/remove under Aggregate Graphs
2. If Advanced Graph Creation is enabled under Settings > General > Graph/Data Source Creation Method == 'Advanced'

Please double confirm your setup and report back.

[tddcacti]

Thanks for pointing out that aggregate graphs are Host 0 as well, I hadn't realized that. If I end up using this approach I'll need to be careful not to delete aggregates accidentally.

I have Graph/Data Source Creation Method set to Simple.

In my testing I find that if I select "Leave all Graph(s) and Data Source(s) untouched. Data Source(s) will be disabled however." when deleting a device, the host_id value in the graph_local table gets updated to zero. For example, here are the graphs for a device with host_id=33, before deleting that device:

mysql> select * from graph_local where host_id=33;
+-----+-------------------+---------+---------------+---------------------+------------+
| id  | graph_template_id | host_id | snmp_query_id | snmp_query_graph_id | snmp_index |
+-----+-------------------+---------+---------------+---------------------+------------+
| 545 |                35 |      33 |             0 |                   0 |            |
| 546 |                45 |      33 |             0 |                   0 |            |
| 547 |               316 |      33 |             0 |                   0 |            |
| 548 |               349 |      33 |             0 |                   0 |            |
| 549 |               351 |      33 |             0 |                   0 |            |
| 550 |               357 |      33 |             0 |                   0 |            |
+-----+-------------------+---------+---------------+---------------------+------------+
6 rows in set (0.00 sec)

And here are those same six graphs, after deleting the device:

mysql> select * from graph_local where host_id=0;
+-----+-------------------+---------+---------------+---------------------+------------+
| id  | graph_template_id | host_id | snmp_query_id | snmp_query_graph_id | snmp_index |
+-----+-------------------+---------+---------------+---------------------+------------+
| 545 |                35 |       0 |             0 |                   0 |            |
| 546 |                45 |       0 |             0 |                   0 |            |
| 547 |               316 |       0 |             0 |                   0 |            |
| 548 |               349 |       0 |             0 |                   0 |            |
| 549 |               351 |       0 |             0 |                   0 |            |
| 550 |               357 |       0 |             0 |                   0 |            |
+-----+-------------------+---------+---------------+---------------------+------------+
6 rows in set (0.00 sec)

[TheWitness]

Yea, that is a bit of a mess up in the logic.

[tddcacti]

Thanks, are you saying that host_id should not be set to 0 when a host is deleted? Is it supposed to retain the original host_id? Or is there some other value it should be set to?

[TheWitness]

No, it should be set to 0, but the issue is that the setting of the host_id to zero was never fully vetted in the context of the Simple and Advanced Graph/Data Source creation methods. So, the quick fix is to go to Advance mode, and remove things, and then switch back to 'Simple' mode when you are done. In the mean time, we have to fully rationalize this undesirable behavior. I had initially completely removed host_id 0 from Cacti, but then the original author (Ian Berry) called me out on it (as he was using host 0 for some internal work at the time).

[tddcacti]

@TheWitness thanks for the additional background. I see that @somethingwithproof has submitted commits/PR with the suggested modifications, are we good with proceeding with that approach or do you have an alternate option in mind?