Welcome, Guest. Please login or register.

Author Topic: Import Nodes per SQL as Devices  (Read 5375 times)

raider82

  • Jr. Member
  • **
  • Posts: 91
    • View Profile
Import Nodes per SQL as Devices
« on: July 04, 2012, 01:44:30 PM »
If somebody is interested in devices that do not support CDP/ LLDP and SNMP, but appear as nodes, you can use SQL for that:
INSERT INTO devices
    (SELECT  substr(n.name, 1, 10) device, n.nodip, 'unknown', 'Lightweight AP', n.firstseen, n.lastseen, '2', 'Manually added', '-', '-', d.location, '', '', 0, 0, '', 0, '', 'waon', n.nodip, null, null, null, null, null, null, ''
    FROM    nodes n
    JOIN    devices d
    ON      n.device = d.device
    WHERE   UPPER(n.name) LIKE '<pattern>'
        AND EXISTS(
                SELECT  di.device
                FROM    devices di
                WHERE   upper(substr(n.name, 1, 10)) = upper(di.device)
                AND     di.serial = 'unknown' or di.serial is null
            )
    )
ON DUPLICATE KEY UPDATE lastdis = n.lastseen
;

INSERT INTO links
    (SELECT tmp1.id, tmp1.device, tmp1.ifname, tmp1.neighbor, tmp1.nbrifname, i.speed, tmp1.type, tmp1.description, i.duplex, tmp1.vlanid
    FROM
        (SELECT  l.id, substr(n.name, 1, 10) device, 'unknown' as ifname, n.device neighbor, n.ifname as nbrifname, 'MAC' type, 'Manually added' description, n.vlanid
        FROM    nodes n
        LEFT OUTER JOIN links l
        ON      upper(substr(n.name, 1, 10)) = upper(l.device)
            WHERE   UPPER(n.name) LIKE '<pattern>'
                AND EXISTS(
                        SELECT  di.device
                        FROM    devices di
                        WHERE   upper(substr(n.name, 1, 10)) = upper(di.device)
                        AND     di.serial = 'unknown' or di.serial is null
                    )
        ) tmp1
    LEFT OUTER JOIN interfaces i
    ON      i.device = tmp1.neighbor
        AND i.ifname = tmp1.nbrifname
    )
ON DUPLICATE KEY UPDATE neighbor = tmp1.neighbor,
                        nbrifname = tmp1.nbrifname,
                        bandwidth = i.speed,
                        nbrduplex = i.duplex,
                        nbrvlanid = tmp1.vlanid
;

I have tested this script for a few months now for Aruba Lightweight APs.
@Remo, maybe you want to add that as a command line option? Make patterns configurable and run sql as nedi.pl -sqlimport or something similar?

harry

  • Full Member
  • ***
  • Posts: 131
    • View Profile
Re: Import Nodes per SQL as Devices
« Reply #1 on: July 04, 2012, 02:46:05 PM »
so what will result look like..? any screen shot...?

raider82

  • Jr. Member
  • **
  • Posts: 91
    • View Profile
Re: Import Nodes per SQL as Devices
« Reply #2 on: July 04, 2012, 05:03:23 PM »
It is similar to the nosnmpdev devices (with a bit less of information). However, it is very useful if you are accessing the system with sql commands, export assets to Excel or something similar. It just makes the big picture on inventory a bit more complete.

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2777
    • View Profile
    • NeDi
Re: Import Nodes per SQL as Devices
« Reply #3 on: July 04, 2012, 05:54:16 PM »
Heh, cool  :D Something I had on my list for consideration as well. This would also allow to add location and contact info, since devices won't be retired (removed) automatically. tx
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

pc_sg

  • Guest
Re: Import Nodes per SQL as Devices
« Reply #4 on: July 05, 2012, 01:24:42 PM »
Hi raider82, if you use Windows on your Nedi management console, you can use a really beautiful screenshot capture tool, open source, called Greenshot
http://sourceforge.net/projects/greenshot/

Have a look and you love it!

This, ad the magnific "FAR Manager" (also now Open Source) are part of my "essential tools" I install on any PC I use...

(I don't know if there is something similar on other platforms)

Paolo

cwaters

  • Newbie
  • *
  • Posts: 9
  • What I wish I was doing.
    • View Profile
Re: Import Nodes per SQL as Devices
« Reply #5 on: July 10, 2012, 02:21:41 AM »
Heh, cool  :D Something I had on my list for consideration as well. This would also allow to add location and contact info, since devices won't be retired (removed) automatically. tx

So could this same method be used to create "fake" devices for the purposes of creating a more reasonable view of a topology with BGP?  For example the "device" could be for a given AS number.  Then links in linked could be created between real devices that are actually connected to a single cloud as in large managed services from a provider but represented by a single "fake" device with corresponds to an AS number.

Did that make sense?
"The conclusive proof that intelligent life exists elsewhere in the universe lies within the fact that no one has bothered to contact us."  --unknown

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2777
    • View Profile
    • NeDi
Re: Import Nodes per SQL as Devices
« Reply #6 on: July 10, 2012, 06:33:31 PM »
Can you draw, what's on your mind? Basically creating a fake cloud-device woulnd't be hard at all...
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

cwaters

  • Newbie
  • *
  • Posts: 9
  • What I wish I was doing.
    • View Profile
Re: Import Nodes per SQL as Devices
« Reply #7 on: July 10, 2012, 07:56:27 PM »
Sure, I'll work on a diagram but maybe this helps.  Think of a hub an spoke scenario where the "hub" is a device that represents an AS number for example.  While the real topology is a full mesh (all sites can access all others with out a specific dependence on another), it could be visualized as a hub and spoke where the hub is the AS for which all other BGP routed devices connect to and are linked to the fake device and a fake interface represented by the AS number.  It simplifies the mapping complexity because you won't actually see a full mesh but a simplified view of all devices connecting to a single hub that represents the mesh.  Hope that makes sense but I'll work on a diagram.
"The conclusive proof that intelligent life exists elsewhere in the universe lies within the fact that no one has bothered to contact us."  --unknown

cwaters

  • Newbie
  • *
  • Posts: 9
  • What I wish I was doing.
    • View Profile
Re: Import Nodes per SQL as Devices
« Reply #8 on: July 10, 2012, 08:13:02 PM »
So, the image on the left would be the simplified view I am talking about.  If you expand what the "correct" view might look like with a lot of devices, practically unreadable if you have a large network.
"The conclusive proof that intelligent life exists elsewhere in the universe lies within the fact that no one has bothered to contact us."  --unknown

raider82

  • Jr. Member
  • **
  • Posts: 91
    • View Profile
Re: Import Nodes per SQL as Devices
« Reply #9 on: July 11, 2012, 04:14:25 PM »
Hi raider82, if you use Windows on your Nedi management console, you can use a really beautiful screenshot capture tool, open source, called Greenshot
Thanks for the tipp.

cwaters

  • Newbie
  • *
  • Posts: 9
  • What I wish I was doing.
    • View Profile
Re: Import Nodes per SQL as Devices
« Reply #10 on: August 20, 2012, 08:27:15 PM »
I am still hoping this is something that can be added as feature in upcoming versions of NeDi.  Anything else I can forward on to assist with this?  Another idea I had was to create the fake devices automatically by reading the AS PATH from the BGP information on Cisco routers.  Each AS could be it's own devices that is automatically created during discovery.  What do you think?
"The conclusive proof that intelligent life exists elsewhere in the universe lies within the fact that no one has bothered to contact us."  --unknown

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2777
    • View Profile
    • NeDi
Re: Import Nodes per SQL as Devices
« Reply #11 on: August 24, 2012, 06:23:36 PM »
I'll note this idea too for 1.0.9...
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2777
    • View Profile
    • NeDi
Re: Import Nodes per SQL as Devices
« Reply #12 on: July 20, 2013, 10:59:08 PM »
As promised cwaters, how about this?
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo