Geolocation - API Endpoints missing in MySQL config table
Oh no, not me again!!
In my recent post I alluded to an issue I was having with Geolocation. After installing Observium CE v2018.9, I couldn't get Geolocation to work with the default setup, so I changed to Google as I already had an API Key setup for other purposes. However, Geolocation still failed to do anything.
After looking in Jira, there seem to be a number of posts on this subject, all closed off with no resolution. For anyone out there that still has an issue with Google Geolocation, then I've found what the problem is. This may also solve the issue for other Geolocation APIs too.
By the way, before I get started on the solution, I've opened a new bug in Jira (OBS-3022), so hopefully this will get fixed.
So, digging into the PHP code, I finally worked out that the API Endpoint URLs are stored in MySQL "config" table. Except, they weren't, "direct_url" and "reverse_url" were missing from the "config" table.
I would think that if you select Google as the API provider in the GUI, that the "config" table should be populated with the correct URLs. Nothing in any documentation states that config.php needs anything added manually to cause MySQL to be populated.
There may actually be an easier way to achieve this by entering values in config.php, however I've only been digging around in the PHP code for less than 2 days and I haven't fully got my head around how Observium does things yet. So my solution involves getting your hands dirty with MySQL / SQL.
To fix the problem manually for Google and potentially other API providers, do the following:-
1. SSH to your Observium host or login to the CLI if you're local. 2. I'm a bit naughty I tend to do everything in a "root" shell, so if you login as non-root you'll probably need to su or sudo to user observium or the owner of the MySQL database first. 3. Connect to MySQL using - mysql observium (don't include the " - ", that just to separate the description from the command) 4. Check the current contents of "config" table - select * from config; (for those of you unfamiliar with SQL the ";" is absolutely required) 5. For the Google Geolocation APIs to work there should be two entries as below:-
| geocoding|direct_url | s:49:"https://maps.googleapis.com/maps/api/geocode/json"; | | geocoding|reverse_url | s:49:"https://maps.googleapis.com/maps/api/geocode/json"; |
6. If these are missing or different, then Google API will fail. At this point you either need to insert/update the Google API Endpoint URLs in the table or URLs that are appropriate to the API you want to use. 7. If missing, to enter for Google, do the following in MySQL. You can copy and paste, both lines are required:-
insert into config (config_key,config_value) values ('geocoding|direct_url','s:49:"https://maps.googleapis.com/maps/api/geocode/json";'); insert into config (config_key,config_value) values ('geocoding|reverse_url','s:49:"https://maps.googleapis.com/maps/api/geocode/json";');
8. If entries are there, but wrong urls, do:-
update config set config_value = 's:49:"https://maps.googleapis.com/maps/api/geocode/json"' where config_key = 'geocoding|direct_url' or config_key = 'geocoding|reverse_url';
9. To enter different URLs you will likely have to change the s:49 value at the beginning. The value 49 is the count of characters in the URL between the double quotes, do not include the double quotes in the count.
10. If the URLs for the Geolocation API you want to use are different for direct and reverse lookups, then you will need a slightly different update statement in step 8.
11. I'm not going to tell you how to delete the entries, as this is a bit dangerous if you don't know what you're doing. So you either know anyway or go read up about MySQL and practice on a dummy table first. In any case there should be no reason to delete anything, unless the table is really in a mess.
12. To close MySQL CLI, enter quit or exit or press CTRL-C.
13. Lastly, don't panic if things don't seem to work immediately, for some reason it seemed to take a while for poller.php to start using the new config and this wasn't just the 5 minute delay of the crontab timings.
14. I actually purged all records from the "devices_locations" table to force all locations to be regenerated, but this is not a good idea if you've added manual lat/long coordinates. If your SNMP sysLocation cannot be converted by the Geolocation API and you've purged the locations table, all your "flags" will end up at the default lat/long, assuming you've configured one.
Regards
Chris Macneill Web: www.cmit.nz
Oh dear lord don't insert things into the config database, just add them to config.php!
I wonder when these default config values disappeared...
They're most certainly not intended to be manually configured, but conferred from setting the correct geo API.
Probably one of my mistakes... 😅
Adam.
Sent from BlueMail
On 26 May 2019, 01:03, at 01:03, Chris Macneill via observium observium@observium.org wrote:
Oh no, not me again!!
In my recent post I alluded to an issue I was having with Geolocation. After installing Observium CE v2018.9, I couldn't get Geolocation to work with the default setup, so I changed to Google as I already had an API Key setup for other purposes. However, Geolocation still failed to do anything.
After looking in Jira, there seem to be a number of posts on this subject, all closed off with no resolution. For anyone out there that still has an issue with Google Geolocation, then I've found what the problem is. This may also solve the issue for other Geolocation APIs too.
By the way, before I get started on the solution, I've opened a new bug in Jira (OBS-3022), so hopefully this will get fixed.
So, digging into the PHP code, I finally worked out that the API Endpoint URLs are stored in MySQL "config" table. Except, they weren't, "direct_url" and "reverse_url" were missing from the "config" table.
I would think that if you select Google as the API provider in the GUI, that the "config" table should be populated with the correct URLs. Nothing in any documentation states that config.php needs anything added manually to cause MySQL to be populated.
There may actually be an easier way to achieve this by entering values in config.php, however I've only been digging around in the PHP code for less than 2 days and I haven't fully got my head around how Observium does things yet. So my solution involves getting your hands dirty with MySQL / SQL.
To fix the problem manually for Google and potentially other API providers, do the following:-
- SSH to your Observium host or login to the CLI if you're local.
- I'm a bit naughty I tend to do everything in a "root" shell, so if
you login as non-root you'll probably need to su or sudo to user observium or the owner of the MySQL database first. 3. Connect to MySQL using - mysql observium (don't include the " - ", that just to separate the description from the command) 4. Check the current contents of "config" table - select * from config; (for those of you unfamiliar with SQL the ";" is absolutely required) 5. For the Google Geolocation APIs to work there should be two entries as below:-
| geocoding|direct_url | s:49:"https://maps.googleapis.com/maps/api/geocode/json"; | | geocoding|reverse_url | s:49:"https://maps.googleapis.com/maps/api/geocode/json"; |
- If these are missing or different, then Google API will fail. At
this point you either need to insert/update the Google API Endpoint URLs in the table or URLs that are appropriate to the API you want to use. 7. If missing, to enter for Google, do the following in MySQL. You can copy and paste, both lines are required:-
insert into config (config_key,config_value) values ('geocoding|direct_url','s:49:"https://maps.googleapis.com/maps/api/geocode/json";'); insert into config (config_key,config_value) values ('geocoding|reverse_url','s:49:"https://maps.googleapis.com/maps/api/geocode/json";');
- If entries are there, but wrong urls, do:-
update config set config_value = 's:49:"https://maps.googleapis.com/maps/api/geocode/json"' where config_key = 'geocoding|direct_url' or config_key = 'geocoding|reverse_url';
- To enter different URLs you will likely have to change the s:49
value at the beginning. The value 49 is the count of characters in the URL between the double quotes, do not include the double quotes in the count.
- If the URLs for the Geolocation API you want to use are different
for direct and reverse lookups, then you will need a slightly different update statement in step 8.
- I'm not going to tell you how to delete the entries, as this is a
bit dangerous if you don't know what you're doing. So you either know anyway or go read up about MySQL and practice on a dummy table first. In any case there should be no reason to delete anything, unless the table is really in a mess.
To close MySQL CLI, enter quit or exit or press CTRL-C.
Lastly, don't panic if things don't seem to work immediately, for
some reason it seemed to take a while for poller.php to start using the new config and this wasn't just the 5 minute delay of the crontab timings.
- I actually purged all records from the "devices_locations" table to
force all locations to be regenerated, but this is not a good idea if you've added manual lat/long coordinates. If your SNMP sysLocation cannot be converted by the Geolocation API and you've purged the locations table, all your "flags" will end up at the default lat/long, assuming you've configured one.
Regards
Chris Macneill Web: www.cmit.nz _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
OK, so what should you enter in config.php to solve this problem?
Regards
Chris Macneill Web: www.cmit.nz
----- Original Message ----- From: "Adam Armstrong" adama@memetic.org To: "observium" observium@observium.org Cc: "Chris Macneill" cmacneill@cmit.nz Sent: Sunday, 26 May, 2019 13:59:10 Subject: Re: [Observium] Geolocation - API Endpoints missing in MySQL config table
Oh dear lord don't insert things into the config database, just add them to config.php!
I wonder when these default config values disappeared...
They're most certainly not intended to be manually configured, but conferred from setting the correct geo API.
Probably one of my mistakes... 😅
Adam.
Sent from BlueMail
On 26 May 2019, 01:03, at 01:03, Chris Macneill via observium observium@observium.org wrote:
Oh no, not me again!!
In my recent post I alluded to an issue I was having with Geolocation. After installing Observium CE v2018.9, I couldn't get Geolocation to work with the default setup, so I changed to Google as I already had an API Key setup for other purposes. However, Geolocation still failed to do anything.
After looking in Jira, there seem to be a number of posts on this subject, all closed off with no resolution. For anyone out there that still has an issue with Google Geolocation, then I've found what the problem is. This may also solve the issue for other Geolocation APIs too.
By the way, before I get started on the solution, I've opened a new bug in Jira (OBS-3022), so hopefully this will get fixed.
So, digging into the PHP code, I finally worked out that the API Endpoint URLs are stored in MySQL "config" table. Except, they weren't, "direct_url" and "reverse_url" were missing from the "config" table.
I would think that if you select Google as the API provider in the GUI, that the "config" table should be populated with the correct URLs. Nothing in any documentation states that config.php needs anything added manually to cause MySQL to be populated.
There may actually be an easier way to achieve this by entering values in config.php, however I've only been digging around in the PHP code for less than 2 days and I haven't fully got my head around how Observium does things yet. So my solution involves getting your hands dirty with MySQL / SQL.
To fix the problem manually for Google and potentially other API providers, do the following:-
- SSH to your Observium host or login to the CLI if you're local.
- I'm a bit naughty I tend to do everything in a "root" shell, so if
you login as non-root you'll probably need to su or sudo to user observium or the owner of the MySQL database first. 3. Connect to MySQL using - mysql observium (don't include the " - ", that just to separate the description from the command) 4. Check the current contents of "config" table - select * from config; (for those of you unfamiliar with SQL the ";" is absolutely required) 5. For the Google Geolocation APIs to work there should be two entries as below:-
| geocoding|direct_url | s:49:"https://maps.googleapis.com/maps/api/geocode/json"; | | geocoding|reverse_url | s:49:"https://maps.googleapis.com/maps/api/geocode/json"; |
- If these are missing or different, then Google API will fail. At
this point you either need to insert/update the Google API Endpoint URLs in the table or URLs that are appropriate to the API you want to use. 7. If missing, to enter for Google, do the following in MySQL. You can copy and paste, both lines are required:-
insert into config (config_key,config_value) values ('geocoding|direct_url','s:49:"https://maps.googleapis.com/maps/api/geocode/json";'); insert into config (config_key,config_value) values ('geocoding|reverse_url','s:49:"https://maps.googleapis.com/maps/api/geocode/json";');
- If entries are there, but wrong urls, do:-
update config set config_value = 's:49:"https://maps.googleapis.com/maps/api/geocode/json"' where config_key = 'geocoding|direct_url' or config_key = 'geocoding|reverse_url';
- To enter different URLs you will likely have to change the s:49
value at the beginning. The value 49 is the count of characters in the URL between the double quotes, do not include the double quotes in the count.
- If the URLs for the Geolocation API you want to use are different
for direct and reverse lookups, then you will need a slightly different update statement in step 8.
- I'm not going to tell you how to delete the entries, as this is a
bit dangerous if you don't know what you're doing. So you either know anyway or go read up about MySQL and practice on a dummy table first. In any case there should be no reason to delete anything, unless the table is really in a mess.
To close MySQL CLI, enter quit or exit or press CTRL-C.
Lastly, don't panic if things don't seem to work immediately, for
some reason it seemed to take a while for poller.php to start using the new config and this wasn't just the 5 minute delay of the crontab timings.
- I actually purged all records from the "devices_locations" table to
force all locations to be regenerated, but this is not a good idea if you've added manual lat/long coordinates. If your SNMP sysLocation cannot be converted by the Geolocation API and you've purged the locations table, all your "flags" will end up at the default lat/long, assuming you've configured one.
Regards
Chris Macneill Web: www.cmit.nz _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
Just replicate what you put into the database in config.php.
$config['geocoding']['direct_url'], etc.
It definitely shouldn't be in the database, since it wouldn't ever be a user editable setting (and once it's in the db it won't be visible in and cant be removed from the ui, but perhaps we need a way to sanitise the database!). It's seemingly not being set by some code somewhere.
Mike will probably already know where, if I don't find it first 😅
Well done finding this stuff though. It's super helpful when people can actually diagnose stuff beyond the minimum, (and geo stuff is usually considered to be an issue with the API, since the API vendors are not friendly)
Adam.
Sent from BlueMail
On 26 May 2019, 03:19, at 03:19, Chris Macneill via observium observium@observium.org wrote:
OK, so what should you enter in config.php to solve this problem?
Regards
Chris Macneill Web: www.cmit.nz
----- Original Message ----- From: "Adam Armstrong" adama@memetic.org To: "observium" observium@observium.org Cc: "Chris Macneill" cmacneill@cmit.nz Sent: Sunday, 26 May, 2019 13:59:10 Subject: Re: [Observium] Geolocation - API Endpoints missing in MySQL config table
Oh dear lord don't insert things into the config database, just add them to config.php!
I wonder when these default config values disappeared...
They're most certainly not intended to be manually configured, but conferred from setting the correct geo API.
Probably one of my mistakes... 😅
Adam.
Sent from BlueMail
On 26 May 2019, 01:03, at 01:03, Chris Macneill via observium observium@observium.org wrote:
Oh no, not me again!!
In my recent post I alluded to an issue I was having with Geolocation. After installing Observium CE v2018.9, I couldn't get Geolocation to work with the default setup, so I changed to Google as I already had
an
API Key setup for other purposes. However, Geolocation still failed to do anything.
After looking in Jira, there seem to be a number of posts on this subject, all closed off with no resolution. For anyone out there that still has an issue with Google Geolocation, then I've found what the problem is. This may also solve the issue for other Geolocation APIs too.
By the way, before I get started on the solution, I've opened a new
bug
in Jira (OBS-3022), so hopefully this will get fixed.
So, digging into the PHP code, I finally worked out that the API Endpoint URLs are stored in MySQL "config" table. Except, they
weren't,
"direct_url" and "reverse_url" were missing from the "config" table.
I would think that if you select Google as the API provider in the
GUI,
that the "config" table should be populated with the correct URLs. Nothing in any documentation states that config.php needs anything added manually to cause MySQL to be populated.
There may actually be an easier way to achieve this by entering values in config.php, however I've only been digging around in the PHP code for less than 2 days and I haven't fully got my head around how Observium does things yet. So my solution involves getting your hands dirty with MySQL / SQL.
To fix the problem manually for Google and potentially other API providers, do the following:-
- SSH to your Observium host or login to the CLI if you're local.
- I'm a bit naughty I tend to do everything in a "root" shell, so if
you login as non-root you'll probably need to su or sudo to user observium or the owner of the MySQL database first. 3. Connect to MySQL using - mysql observium (don't include the " - ", that just to separate the description from the command) 4. Check the current contents of "config" table - select * from
config;
(for those of you unfamiliar with SQL the ";" is absolutely required) 5. For the Google Geolocation APIs to work there should be two entries as below:-
| geocoding|direct_url | s:49:"https://maps.googleapis.com/maps/api/geocode/json"; | | geocoding|reverse_url | s:49:"https://maps.googleapis.com/maps/api/geocode/json"; |
- If these are missing or different, then Google API will fail. At
this point you either need to insert/update the Google API Endpoint URLs in the table or URLs that are appropriate to the API you want to use. 7. If missing, to enter for Google, do the following in MySQL. You can copy and paste, both lines are required:-
insert into config (config_key,config_value) values ('geocoding|direct_url','s:49:"https://maps.googleapis.com/maps/api/geocode/json";'); insert into config (config_key,config_value) values ('geocoding|reverse_url','s:49:"https://maps.googleapis.com/maps/api/geocode/json";');
- If entries are there, but wrong urls, do:-
update config set config_value = 's:49:"https://maps.googleapis.com/maps/api/geocode/json"' where config_key = 'geocoding|direct_url' or config_key = 'geocoding|reverse_url';
- To enter different URLs you will likely have to change the s:49
value at the beginning. The value 49 is the count of characters in the URL between the double quotes, do not include the double quotes in the count.
- If the URLs for the Geolocation API you want to use are different
for direct and reverse lookups, then you will need a slightly
different
update statement in step 8.
- I'm not going to tell you how to delete the entries, as this is a
bit dangerous if you don't know what you're doing. So you either know anyway or go read up about MySQL and practice on a dummy table first. In any case there should be no reason to delete anything, unless the table is really in a mess.
To close MySQL CLI, enter quit or exit or press CTRL-C.
Lastly, don't panic if things don't seem to work immediately, for
some reason it seemed to take a while for poller.php to start using
the
new config and this wasn't just the 5 minute delay of the crontab timings.
- I actually purged all records from the "devices_locations" table
to
force all locations to be regenerated, but this is not a good idea if you've added manual lat/long coordinates. If your SNMP sysLocation cannot be converted by the Geolocation API and you've purged the locations table, all your "flags" will end up at the default lat/long, assuming you've configured one.
Regards
Chris Macneill Web: www.cmit.nz _______________________________________________ observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
observium mailing list observium@observium.org http://postman.memetic.org/cgi-bin/mailman/listinfo/observium
Thanks Adam for teaching me the correct way to do things. :-)
I can confirm that removing the entries I added earlier to MySQL config table and adding the following lines to config.pgp resolves the problem for Google APIs.
So ignore my original MySQL fix and do it the easy way.
$config['geocoding']['direct_url'] = "https://maps.googleapis.com/maps/api/geocode/json"; $config['geocoding']['reverse_url'] = "https://maps.googleapis.com/maps/api/geocode/json";
This should work for other APIs too, just substitute the appropriate URL(s).
Regards
Chris Macneill Web: www.cmit.nz
participants (2)
-
Adam Armstrong -
Chris Macneill