Device Location drop down does not always populate correctly

[adilinden]

Describe the bug

I am running Cacti 1.2.24.

The location table in the device form isn't populating with pre-defined values.

To Reproduce

Define different locations for different devices. Create a new device and the previously defined values won't show in the location drop down to select from.

Expected behavior

Have a choice of locations that have already been defined for other devices

Screenshots

Desktop (please complete the following information)

• OS: Windows 11

• Browser Edge

• Version 113.0.1774.57 (Official build) (64-bit)

Smartphone (please complete the following information)

Additional context

I came across https://forums.cacti.net/viewtopic.php?t=60215 looking for a solution, it provided me some places to look for a solution. Here is what is have in the location column in my host table:

MariaDB [cacti]> SELECT location FROM host;
+---------------+
| location      |
+---------------+
|               |
| Core          |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Core          |
| Cellular Site |
| Cellular Site |
| Core          |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
| Cellular Site |
+---------------+
33 rows in set (0.000 sec)

Looking up the SQL query from the forum post it is in cacti/include/global_form.php around line 1199. It reads:

        'location' => array(
                'method' => 'drop_callback',
                'friendly_name' => __('Location'),
                'description' => __('The physical location of the Device.  This free form text can be a room, rack location, etc.'),
                'none_value' => __('None'),
                'sql' => 'SELECT DISTINCT location AS id, location AS name FROM host ORDER BY location',
                'action' => 'ajax_locations',
                'id' => '|arg1:location|',
                'value' => '|arg1:location|',
                ),

Running the SQL query on my Cacti install I get:

MariaDB [cacti]> SELECT DISTINCT location AS id, location AS name FROM host ORDER BY location;
+---------------+---------------+
| id            | name          |
+---------------+---------------+
|               |               |
| Cellular Site | Cellular Site |
| Core          | Core          |
+---------------+---------------+
3 rows in set (0.000 sec)

However the form in the UI with the modern theme does not populate with those values. Here is what I see when editing a device:

And this is what I see for a new device:

Looking at the HTML source of the page using the developer menu, I also only see the 'none' value predefine despite the SQL query returning two values in addition to the empty value.

[netniV]

Check for any browser console level errors.

[adilinden]

Check for any browser console level errors.

Not seeing any errors in the browser console.

[TheWitness]

Did you update to the latest changes?

[adilinden]

Did you update to the latest changes?

Would it be fixed in 1.2.x branch? I tried the 1.2.x branch at initial install but wasn't able to proceed through the initial installation dialog. I instead checked out the 1.2.24 release and it worked just fine. That's why I am currently running 1.2.24 specifically.

[adilinden]

Nevermind this last post. I missed to do a pull on my git checkout after switching to 1.2.x branch. The latest 1.2.x I can upgrade to just fine now.

[adilinden]

Did you update to the latest changes?

I upgraded to latest in 1.2.x and it is still not populating the location field. Not for new devices nor when editing old devices. But now I am getting a server error in the console, not on page loading but when accessing the location drop down box.

Here is the error expanded:

[netniV]

If you check the logs tab, there will be a corresponding error at the same time. If you can provide that detail and the backtrace, that would be most useful.

[TheWitness]

Yea, that's a bad error. Might be a plugin. You should disable one at a time and see if that improves things.

[TheWitness]

What's you PHP version?

[netniV]

The error was addressed and should now work properly.

[adilinden]

The error was addressed and should now work properly.

With that latest fix applied the error no longer occurs. However, the location still doesn't populate.

[adilinden]

It is my understanding that I should be able to define a new value or pick an existing value from the location field. When I create a new site it pre-populates "None". I can type over "None" and provide my own value. Otherwise the location list is empty. I can only write one value into the location list, I cannot have multiple locations selected for the same single site.

My expectations is that the location dropdown list should be populated with all locations that have previously been used for any other site. For example, this is my actual list of sites and corresponding locations from the host table.

MariaDB [cacti]> select id,site_id,location from host order by location;
+----+---------+---------------+
| id | site_id | location      |
+----+---------+---------------+
|  1 |       0 |               |
| 20 |      19 | Cellular Site |
| 21 |      20 | Cellular Site |
| 22 |      21 | Cellular Site |
| 23 |      22 | Cellular Site |
| 24 |      23 | Cellular Site |
| 25 |      24 | Cellular Site |
| 27 |      25 | Cellular Site |
| 28 |       8 | Cellular Site |
| 29 |      26 | Cellular Site |
| 30 |      27 | Cellular Site |
| 31 |      28 | Cellular Site |
| 32 |      29 | Cellular Site |
| 33 |      30 | Cellular Site |
| 34 |      31 | Cellular Site |
| 19 |      18 | Cellular Site |
| 18 |      17 | Cellular Site |
| 17 |       8 | Cellular Site |
|  3 |       3 | Cellular Site |
|  4 |       4 | Cellular Site |
|  5 |       5 | Cellular Site |
|  7 |       6 | Cellular Site |
|  8 |       9 | Cellular Site |
|  9 |      10 | Cellular Site |
| 10 |      11 | Cellular Site |
| 16 |      16 | Cellular Site |
| 11 |      12 | Cellular Site |
| 12 |      13 | Cellular Site |
| 13 |      14 | Cellular Site |
| 15 |      15 | Cellular Site |
|  2 |       8 | Core          |
| 26 |       7 | Core          |
| 14 |       2 | Core          |
| 44 |      10 | Health Center |
| 43 |      21 | K-Net POP     |
| 42 |      37 | K-Net POP     |
| 41 |      36 | K-Net POP     |
| 40 |      35 | K-Net POP     |
| 39 |      34 | K-Net POP     |
| 37 |      33 | K-Net POP     |
| 36 |      27 | K-Net POP     |
| 35 |      32 | K-Net POP     |
| 46 |      18 | K-Net POP     |
| 45 |       8 | K-Net Tower   |
+----+---------+---------------+
44 rows in set (0.000 sec)

I see this list of distinct locations:

MariaDB [cacti]> select distinct location from host order by location;
+---------------+
| location      |
+---------------+
|               |
| Cellular Site |
| Core          |
| Health Center |
| K-Net POP     |
| K-Net Tower   |
+---------------+
6 rows in set (0.001 sec)

I modified the SQL query starting on line 229 in host.php to this:

        $locations = db_fetch_assoc_prepared('SELECT DISTINCT location
                FROM host
                WHERE location != ""
                AND location IS NOT NULL
                ORDER BY location',
                array($site_id, "%$term%"));

It now provides me the result I was expecting:

I am sorry, but I don't quite understand the ? in the original query and how it gets populated. It's been a few years since I worked with PHP.

[netniV]

So I believe that this is a bug because the settings array does not filter by a specific site. However, this may have been done to allow a speed increase for those systems with a lot of devices, so I created a config option to allow the current functionality to enabled.

One side effect is that when creating a new device, it will now shoe all locations where the broken functionality only showed None via the ajax calls.