Send netdisco-users mailing list submissions to
[email protected]
To subscribe or unsubscribe via the World Wide Web, visit
https://lists.sourceforge.net/lists/listinfo/netdisco-users
or, via email, send a message with subject or body 'help' to
[email protected]
You can reach the person managing the list at
[email protected]
When replying, please edit your Subject line so it is more specific
than "Re: Contents of netdisco-users digest..."
Today's Topics:
1. Re: report - ip inventory issue (Christian Ramseyer)
2. Re: report - ip inventory issue (Oliver Gorwits)
3. Re: report - ip inventory issue (Christian Ramseyer)
--- Begin Message ---
Hi Brian
On 02.10.18 22:14, Cuttler, Brian R (HEALTH) wrote:
> Hello NetDisco users,
>
> I am running v2.39.31 and we are on a wild goose chase hunting for missing
> voip phones.
>
> I don't see a button in the web interface to dump the database, or search it
> with the criteria I think I want and have been trying to use the postgres
> interface.
>
> I was able to dump all MAC, with switch ip and switch interface, vlan and
> last-seen time stamp.
>
> I failed to do this correctly, I'd thought
> - select where vlan>'599';
>
> Would do it, but it found me all vlans greater than "5", I ended up with
> Vlan like '...' and vlan>'5';
>
> Which at least got me things with 3 digits (our voip vlans are higher
> numbered then our data network vlans).
> select
> Wrong but it was a start.
The node.vlan field is a bit hard to use as you want since it is defined
as text - even though for me it only contains numbers. You can force
Postgres to use it as a number like this:
select * from node where vlan::integer > 599;
or even
select * from node where vlan::integer between 599 and 630;
>
> Unfortunately that also seems to have pulled the same MAC multiple times as I
> seem to have pulled addresses off trunk lines and not just access ports.
>
> I realize the vlan issue is a matter of my not knowing enough about postgres.
>
> The second issue is an actual rancid/sql issue, I haven't been able to figure
> out from the docs, the archived emails nor dumping the postgres tables where
> the information on interface type is, "trunk" vs "access".
>
> Can someone point me in the right direction?
The trunk/access distinction is stored in the device_port table,
is_uplink column. So to get only the node entries that are not on
uplinks, you can use e.g.
select n.switch, n.port, n.mac
from node n
join device_port dp on n.switch = dp.ip and n.port = dp.port
where
dp.is_uplink is null
If this part of the question still involves VOIP: the kind of phones
that connect to the switch and then a PC is connected to the phone, both
of them on different VLANs, actually appear as uplink ports only. Here
is a query that I use to get all MACs that are either not on uplink
ports, or that are Cisco and Avaya specific "uplinks" with the phone and
PC connected in this manner:
select n.switch, n.port, n.mac
from node n
join device_port dp on n.switch = dp.ip and n.port = dp.port
where
dp.is_uplink is null
or
(dp.is_uplink = true and dp.remote_type ~* '(^IP Phone|^AV.......$)')
If you have other phones, you'll need to tweak the remote_type ~*
regular expression.
Cheers
Christian
>
> Of course I'm open to any more reasonable approach to the problem.
>
> Thank you,
> Brian
>
>
> Brian Cuttler
> Network and System Administrator, ITG - Information Technology Group
> Wadsworth Center, NYS Department of Health
> Biggs Lab, Empire State Plaza, Albany, NY 12201
> (518) 486-1697 | [email protected]
>
>
>
>
> _______________________________________________
> Netdisco mailing list
> [email protected]
> https://sourceforge.net/p/netdisco/mailman/netdisco-users/
>
--- End Message ---
--- Begin Message ---
Thanks Christian! Nice reply.
You (and Brian) might also find the new device_port_properties table
useful. This provides boolean fields for some properties of connected
devices such as WAP or Phone (eg a "remote_is_phone" field):
https://github.com/netdisco/netdisco/blob/master/lib/App/Netdisco/DB/Result/DevicePortProperties.pm
So you can JOIN to this table and check those booleans. They are set
when the remote type matches configuration such as:
https://github.com/netdisco/netdisco/wiki/Configuration#code-phone_capabilities-code
and
https://github.com/netdisco/netdisco/wiki/Configuration#phone_platforms
which hopefully avoids having regular expressions and other messiness in
SQL.
regards,
oliver.
On 2018-10-09 09:52, Christian Ramseyer wrote:
Hi Brian
On 02.10.18 22:14, Cuttler, Brian R (HEALTH) wrote:
Hello NetDisco users,
I am running v2.39.31 and we are on a wild goose chase hunting for
missing voip phones.
I don't see a button in the web interface to dump the database, or
search it with the criteria I think I want and have been trying to use
the postgres interface.
I was able to dump all MAC, with switch ip and switch interface, vlan
and last-seen time stamp.
I failed to do this correctly, I'd thought
- select where vlan>'599';
Would do it, but it found me all vlans greater than "5", I ended up
with
Vlan like '...' and vlan>'5';
Which at least got me things with 3 digits (our voip vlans are higher
numbered then our data network vlans).
select
Wrong but it was a start.
The node.vlan field is a bit hard to use as you want since it is
defined
as text - even though for me it only contains numbers. You can force
Postgres to use it as a number like this:
select * from node where vlan::integer > 599;
or even
select * from node where vlan::integer between 599 and 630;
Unfortunately that also seems to have pulled the same MAC multiple
times as I seem to have pulled addresses off trunk lines and not just
access ports.
I realize the vlan issue is a matter of my not knowing enough about
postgres.
The second issue is an actual rancid/sql issue, I haven't been able to
figure out from the docs, the archived emails nor dumping the postgres
tables where the information on interface type is, "trunk" vs
"access".
Can someone point me in the right direction?
The trunk/access distinction is stored in the device_port table,
is_uplink column. So to get only the node entries that are not on
uplinks, you can use e.g.
select n.switch, n.port, n.mac
from node n
join device_port dp on n.switch = dp.ip and n.port = dp.port
where
dp.is_uplink is null
If this part of the question still involves VOIP: the kind of phones
that connect to the switch and then a PC is connected to the phone,
both
of them on different VLANs, actually appear as uplink ports only. Here
is a query that I use to get all MACs that are either not on uplink
ports, or that are Cisco and Avaya specific "uplinks" with the phone
and
PC connected in this manner:
select n.switch, n.port, n.mac
from node n
join device_port dp on n.switch = dp.ip and n.port = dp.port
where
dp.is_uplink is null
or
(dp.is_uplink = true and dp.remote_type ~* '(^IP Phone|^AV.......$)')
If you have other phones, you'll need to tweak the remote_type ~*
regular expression.
Cheers
Christian
Of course I'm open to any more reasonable approach to the problem.
Thank you,
Brian
Brian Cuttler
Network and System Administrator, ITG - Information Technology Group
Wadsworth Center, NYS Department of Health
Biggs Lab, Empire State Plaza, Albany, NY 12201
(518) 486-1697 | [email protected]
_______________________________________________
Netdisco mailing list
[email protected]
https://sourceforge.net/p/netdisco/mailman/netdisco-users/
_______________________________________________
Netdisco mailing list
[email protected]
https://sourceforge.net/p/netdisco/mailman/netdisco-users/
--- End Message ---
--- Begin Message ---
On 09.10.18 11:33, Oliver Gorwits wrote:
> You (and Brian) might also find the new device_port_properties table
> useful. This provides boolean fields for some properties of connected
> devices such as WAP or Phone (eg a "remote_is_phone" field):
>
> https://github.com/netdisco/netdisco/blob/master/lib/App/Netdisco/DB/Result/DevicePortProperties.pm
>
>
Great thanks Oliver, I never noticed this was added. Very useful.
--- End Message ---
_______________________________________________
Netdisco mailing list - Digest Mode
[email protected]
https://lists.sourceforge.net/lists/listinfo/netdisco-users
