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

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:

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