usefull SQL queries

[gernreich]

These are SQL queries I have wrote that I find handy.  I hope others find them handy too.
-Gernreich

Find all devices that backed up their config by regular expression of hostname of devices.  This example looks for devices with "ATT" in the hostname
SELECT devices.name,devices.ip FROM devices WHERE devices.name IN (SELECT device FROM configs WHERE config regexp "." AND devices.name REGEXP "ATT");

Find the contract, service level and end date of contract for the serial number in single quotes
SELECT contract_number,service_level,end_date FROM cisco_contracts WHERE serial_number='123456';

List devices that have contracts
SELECT devices.name, devices.ip FROM devices, cisco_contracts WHERE devices.serial = cisco_contracts.serial_number;

List devices that don't have contracts, (cutting out Cisco phones)
SELECT foobar.name, foobar.ip FROM (SELECT devices.name, devices.ip, cisco_contracts.serial_number FROM devices LEFT OUTER JOIN cisco_contracts ON devices.serial = cisco_contracts.serial_number) AS foobar WHERE foobar.serial_number IS NULL AND NOT foobar.name regexp "^SEP";

[rufer]

Here are some useful queries for the nodes table

filter on (dotted) IP:
SELECT * FROM nodes WHERE ip = INET_ATON('10.1.2.3')

useful formatting for IP address and timestamp:
SELECT name, INET_NTOA(ip), mac, FROM_UNIXTIME(lastseen) FROM nodes WHERE name LIKE 'hostname%'

join to get interface alias with nodes:
SELECT nodes.*, interfaces.alias
FROM nodes LEFT JOIN interfaces ON (nodes.device=interfaces.device AND nodes.ifname=interfaces.ifname)
WHERE nodes.name LIKE 'hostname%';

Greetings
Rufer

[rufer]

Here's an SQL command that defines a "simple node view". Can be very handy to browse the nedi nodes database with an SQL client. The IP address is in dotted format, so you can directly filter on an address like 10.1.2.3 the firstseen/lastseen fields are in human readable format.

the view has the following columns:
name ip mac vlanid device ifname alias firstseen lastseen

alias is from the interfaces table, the other fields from the node table.

DROP VIEW IF EXISTS `nedi`.`nodes_simple`;
CREATE ALGORITHM=UNDEFINED DEFINER=`nedi`@`pc.domain.tld` SQL SECURITY DEFINER VIEW  `nedi`.`nodes_simple` AS select `nodes`.`name` AS `name`,inet_ntoa(`nodes`.`ip`) AS `ip`,
`nodes`.`mac` AS `mac`,`nodes`.`vlanid` AS `vlanid`,`nodes`.`device` AS `device`,
`nodes`.`ifname` AS `ifname`,`interfaces`.`alias` AS `alias`,
from_unixtime(`nodes`.`firstseen`) AS `firstseen`,from_unixtime(`nodes`.`lastseen`) AS `lastseen`
from `nodes` left join `interfaces` on ((`nodes`.`device` = `interfaces`.`device`) and (`nodes`.`ifname` = `interfaces`.`ifname`));

Example query
SELECT * FROM nodes_simple WHERE ip = '10.1.2.3';

Example result:
'host10123.test.ch', '10.1.2.3', '00319c7a54fc', 9, 'testsw', 'Fa0/11', 'blabla if description', '2008-12-03 11:40:01', '2009-01-08 15:40:01'

Greetings
Rufer

[gernreich]

Here is one I came up with to list the devices (except for phones) that have NOT had their configurations backed up ever.

SELECT foobar.name FROM (SELECT devices.name, configs.device FROM devices LEFT OUTER JOIN configs ON devices.name = configs.device WHERE devices.name NOT REGEXP "^SEP") AS foobar WHERE foobar.device IS NULL;

[rickli]

Great topic, made it sticky 

[gernreich]

The list of IPs of devices that contain the chosen regular expression in their config.

SELECT INET_NTOA(ip) FROM devices, configs where configs.config regexp "snmp-server host" and configs.device = devices.name ORDER BY ip;

To create the same query as a procedure with the ability to pass the regexp to the query:

CREATE PROCEDURE configsearch(searchtext TEXT) SELECT INET_NTOA(ip) FROM devices, configs WHERE configs.config REGEXP searchtext AND configs.device = devices.name ORDER BY ip;

[gernreich]

I came up with a list of seven general SQL logic functions from my study of MySQL , this probably belongs in a general SQL forum, but I wanted to post them here in case anyone found them useful

p AND q ::: p INNER JOIN q
p - q ::: p LEFT OUTER JOIN q WHERE q IS NULL
q - p ::: p RIGHT OUTER JOIN q WHERE p IS NULL
p OR q ::: (p R.O.J. q) UNION (p L.O.J. q) [[FULL OUTER JOIN]]
p ::: p L.O.J. q
q ::: p R.O.J. q
p XOR q ::: (p - q) UNION (q - p)

[gernreich]

To find excessive macs on a port (a mini switch plugged in and a person running 5 pcs or somesuch)

CREATE VIEW X_maclist AS SELECT device,ifname,count(ifname) AS numberofmacs FROM nodes GROUP BY ifname;
SELECT * FROM X_maclist WHERE device = 'companynetworkdevice' AND numberofmacs >= 3;

This creates a view, then the next line allows a choice of the network device and how many macs have to be on a port before it shows up in the query.  This example looks at the network device "companynetworkdevice" and will show any port that has three or more macs.  If you do not want create a  view my example using the temporary table "foobar" shows how to do this.  It should be the first example in this thread.

[GuyMontag]

The select for the view isn't working as expected, as group by ifname groups all interfaces with the same name... (I had 1774 nodes at interface fa0/8 on a 8 port switch  )
If you specify group by device, ifname the nodes get first grouped by host and after that by interface
e.g.

SELECT device,ifname,count(ifname) AS numberofmacs
FROM nodes
WHERE substr(ifname,1,2) != 'Po'
AND substr(ifname,1,2) != 'Vl'
GROUP BY device, ifname;

I've added a where clause to exclude port channel and vlan interfaces

[steffen1]

Nice topic . Here are queries to show the quality of discovered topology

1. List all unlinked devices:
SELECT distinct name, ip, os, devices.type, serial FROM devices LEFT JOIN links ON name = device where links.device is NULL

Undiscovered, but linked Devices
SELECT DISTINCT links.neighbour, links.type FROM links LEFT JOIN devices ON name = neighbour WHERE devices.name IS NULL

[rickli]

Cool, I like what you guys make out of it 

BTW, ip's can be made readable with mysql:
SELECT distinct name, inet_ntoa(ip) as ip, os, devices.type, serial FROM devices LEFT JOIN links ON name = device where links.device is NULL;

[ntmark]

I'd created a php interface for finding nodes, a bit like rufers sql some time ago.
If anyone is interested, php and css files attached.
Note you'll need to change the db info around lines 45-48 to match your system.

It was never finished but does the job.
I am not a guru at php or sql, so improvements are more than welcome.

Mark