So revisiting this a year later, 2 questions/issues:
1. While this worked a year ago, sometime in the past year it stopped. I had tested it back then, but never actually rolled into into any ongoing state.
I had to update cursor.execute(query_template, this_device) to: cursor.execute(query_template, { this_device })
I didn't dig into what changed on the python side, but found that it wasn't feeding into cursor correctly with the old way, at least on my system.
So in case anyone finds this old thread and runs into a truple error, hopefully this helps.
2. A friend is trying the same code that works in mine but not on his. We found his observium db doesn't have the table `ports-state`. Curious if this has gone away in newer versions or if it only gets created in certain circumstances. We are a large number of versions behind and my system was initially built 3 or so years ago, where his is a new build in the past 6-12 months.
Comparing table list I found tables in my setup that were not in his: alert_table-state bgpPeers-state bgpPeers_cbgp-state mac_accounting-state mempools-state oids_assoc ports-state processors-state pseudowires-state sensors-state slas-state status-state storage-state ucd_diskio-state
Tables in his observium db not in mine: lb_pool_members lb_snatpools oids_entries
Are the tables based on specific devices it finds in the scans or have these just changed in newer builds?
If they did change, did the information collected in ports-state migrate into one of the existing tables such as ports, or go away?
Thanks
On Tue, Nov 1, 2016 at 2:43 PM, Eric Kollmann xnih13@gmail.com wrote:
Once you verified it had to be changed and indicated links table is no longer there, think I got it updated to work now:
have to actually remove the 2 lines starting with //, but basically changed links to neighbours and updated l.local_port_id to l.port_id below. Running this with 1100+ devices in observium kicks out a really large map, but got to say great product!
SELECT UPPER(d.hostname) AS local_hostname, p.port_id AS local_port_id, p.ifName AS local_port, p.ifSpeed/1000000 as local_port_speed, l.remote_port_id AS remote_port_id, rp.ifName AS remote_port, UPPER(l.remote_hostname) AS remote_hostname, d.hardware AS hardware, rd.hardware AS remote_hardware, s.ifInErrors_rate AS local_in_errors_rate, s.ifOutErrors_rate AS local_out_errors_rate, s.ifInOctets_rate*8 AS local_in_octets_rate, s.ifOutOctets_rate*8 AS local_out_octets_rate, s.ifInOctets_perc AS local_in_octets_perc, s.ifOutOctets_perc AS local_out_octets_perc FROM `devices` AS d, `devices` as rd, `ports` AS p, `ports` as rp, // `links` AS l, `neighbours` AS l, `ports-state` AS s WHERE // p.port_id = l.local_port_id AND p.port_id = l.port_id AND p.device_id = d.device_id AND rp.port_id = l.remote_port_id AND rp.device_id = rd.device_id AND p.port_id = s.port_id AND UPPER(d.hostname) = UPPER(%s); """
On Tue, Nov 1, 2016 at 12:50 PM, Eric Kollmann xnih13@gmail.com wrote:
Perfect, happy to test it out if/when you get a chance to send it on. If php stuff needs tweaked I'll see what I can come up with there.
Eric
On Tue, Nov 1, 2016 at 12:06 PM, Adam Armstrong adama@memetic.org wrote:
Hi Eric,
I rewrote the query that script uses and it's saved to a text file. I've not gotten around to rewriting the rest of the code in php yet,since I don't have a CDP network to test on.
I can dig out the SQL when I'm not driving :) (bathroom stop, I can't write email and drive! ;))
Adam.
Sent from BlueMail http://www.bluemail.me/r
On 1 Nov 2016, at 16:11, Eric Kollmann xnih13@gmail.com wrote:
First, I just took over our install of this, wasn't involved in the initial setup of observium and I understand the piece I'm asking about is not part of observium, just uses the data it collects and I want to verify if structure has changed since it was released or if there is something incorrectly setup with our system.
I'm looking to build out graphviz maps with the data observium has gathered, specifically using: https://github.com/kedare/observium_map_generator
Going through their code, it queries the db for the links table and fails. Looking at the database I have a neighbours table, but not links. Google search I see links table discussed back in 2014, but nothing much recently.
So, did the links table go away in newer versions, or is the fact that the table is missing an issue with our setup? If it is supposed to be there and missing, I'll work on figuring that out next, if it did just go away I just want to verify that.
Thanks
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