netdisco-users Digest, Vol 100, Issue 5

Thu, 09 Oct 2014 09:00:05 -0700 

Send netdisco-users mailing list submissions to
        netdisco-users@lists.sourceforge.net

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
        netdisco-users-requ...@lists.sourceforge.net

You can reach the person managing the list at
        netdisco-users-ow...@lists.sourceforge.net

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 for number of ports connected at 10/100/1000 ?
      (Oliver Gorwits)
   2. Re: Automated switch change digest email scripts (Oliver Gorwits)
   3. Re: report for number of ports connected at 10/100/1000 ?
      (Deshong, Kenneth)
   4. Re: report for number of ports connected at 10/100/1000 ?
      (Joseph Bernard)
--- Begin Message --- 

Thanks Brian :-)
For the benefit of those following who've not used Netdisco 2's custom reports feature, below is the config to add to the main configuration file, and then a new "Port Speeds" report will automagically appear in the menu: 

reports:
  - tag: port_speeds
    label: 'Port Speeds'
    columns:
      - {total: 'Count'}
      - {speed: 'Speed'}
    query: |
      SELECT count(*) AS total, speed
        FROM device_port
        WHERE up = 'up'
      GROUP BY speed
      ORDER BY total

As easy as that!

https://metacpan.org/pod/distribution/App-Netdisco/lib/App/Netdisco/Manual/Configuration.pod#reports

regards,
oliver.

On 2014-10-08 17:29, Joseph Bernard wrote:

That is perfect!  I have exactly what I need.

Thanks!
Joseph B.
On Oct 8, 2014, at 12:09 PM, Brian Marshall <br...@netcents.com> wrote: 


If you know SQL then doing ad-hoc reports in netdisco is super easy
go into your postgres database with psql (eg. "psql netdisco netdisco" in my case) and run the following:
select count(*), speed from device_port where up = 'up' group by speed;
That's the result of 2 minutes work, so anyone feel free to correct me if I'm wrong 

  -Brian Marshall

On 10/8/2014 7:32 AM, Joseph Bernard wrote:
I’m running Netdisco 1.1 at the moment. How hard would it be to make a report to show me the number of connected ports at 10Mbps, at 100Mbps, and at 1000Mbps? I just need the counts. I can install Netdisco 2 if it would be way easier to do this kind of thing with that. 

Thanks,
Joseph B.



------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer 

http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Netdisco mailing list
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users







------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports 
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer

http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Netdisco mailing list
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users

--- End Message ---
--- Begin Message --- 

That's quite some SQL query, nice work :-)

Here's a formatted pastie in case anyone wants a clearer version:

  http://pastie.org/9634125

regards,
oliver.

On 2014-10-08 18:03, Brian Marshall wrote:

Hey list,
I've created a pair of scripts to mail out a list of connection change 
digests to myself and my team. First script is called by the second
script. It tells you anything that has been added or changed in the last 
24h and anything that has been off the network for a week. It assumes
you are in 10.0.0.0/8 carving it up by /16 so the third octet is a
variable, but that should be easy to hack around. There is also some
special logic in there to take HP MSM WAP clients out of the results,
and another bit attempting to suppress multiple results coming through 
from unmanaged switches, but other than that its not too weird.
I call the email_port_change_digests.sh script from a cron job every
morning.
Hope someone finds it useful!

FILE 1 - email_port_change_digest.sh:

#!/bin/bash
output=`/usr/bin/psql netdisco netdisco -c "select 'Connected' as
Change_Type, d.ip as Switch_IP, d.name as switch_name, d.location as
switch_location, ni.ip as discovered_ip, nbt.nbname as computer_name,
n.port, ni.mac, oui.company from device as d join node as n on d.ip =
n.switch inner join (select mac, max(time_
last) as time_last from node where active = 't' group by mac) as niu on 
n.mac = niu.mac and n.time_last = niu.time_last join node_ip as ni on
n.mac = ni.mac left outer join node_nbt as nbt on ni.mac = nbt.mac left 
outer join oui on n.oui = oui.oui where ni.active = 't' and
ni.time_first > now() - interval '1 day' a
nd inet '10.$1.0.0/16' >> d.ip and text(d.ip) || '_' || text(n.port) not 
in (select text(ip) || '_' || text(port) from device_port where
remote_type like '%AP%' or remote_type like '%MSM4%') UNION ALL select 'Disconnected' as Change_Type, d.ip as Switch_IP, d.name as switch_name, 
d.location as switch_location, ni.i
p as discovered_ip, nbt.nbname as computer_name, n.port, ni.mac,
oui.company from device as d join node as n on d.ip = n.switch join
node_ip as ni on n.mac = ni.mac inner join (select mac, max(time_last) as time_last from node where active = 't' group by mac) as niu on n.mac 
= niu.mac and n.time_last = niu.time_la
st left outer join node_nbt as nbt on ni.mac = nbt.mac left outer join 
oui on n.oui = oui.oui where ni.time_last between now() - interval '7
days' and now() - interval '6 day' and inet '10.$1.0.0/16' >> d.ip and 
text(d.ip) || '_' || text(n.port) not in (select text(ip) || '_' ||
text(port) from device_port where rem
ote_type like '%AP%' or remote_type like '%MSM4%') and ni.mac not in
(select mac from node where time_last > now() - interval '6 day') and
nbt.nbname not in (select nbname from node_nbt where time_last > now() - 
interval '6 day');"  -H | sed '1i<html>' | sed ' $a <\/html>'`
if (echo $output | grep '<p>(0 rows)<br />' >> /dev/null); then

if (echo $output | grep '<p>(0 rows)<br />' >> /dev/null); then
         echo "No rows!" >> /dev/null
else
         echo $output | mail -s "Daily Switch Change Digest - $3" -a
'Content-type: text/html; charset="iso-8859-1"' $2
fi


FILE 2 - email_port_change_digests.sh:

#!/bin/bash

# Team Lead
/usr/share/netdisco/email_port_change_digest.sh 8 teaml...@example.com 
"Site 1"
/usr/share/netdisco/email_port_change_digest.sh 9 teaml...@example.com 
"Site 2"
/usr/share/netdisco/email_port_change_digest.sh 10 teaml...@example.com 
"Site 3
/usr/share/netdisco/email_port_change_digest.sh 17 teaml...@example.com 
"Site 4"
/usr/share/netdisco/email_port_change_digest.sh 37 teaml...@example.com 
"Site 5"
/usr/share/netdisco/email_port_change_digest.sh 38 teaml...@example.com 
"Site 6"
/usr/share/netdisco/email_port_change_digest.sh 39 teaml...@example.com 
"Site 7"

# Site 1
/usr/share/netdisco/email_port_change_digest.sh 8 g...@example.com "Site 1" 

# Site 2
/usr/share/netdisco/email_port_change_digest.sh 9 g...@example.com "Site 2" 

# Site 3
/usr/share/netdisco/email_port_change_digest.sh 10 g...@example.com "Site 3" 

# Site 4
/usr/share/netdisco/email_port_change_digest.sh 17 g...@example.com "Site 4" /usr/share/netdisco/email_port_change_digest.sh 17 g...@example.com "Site 4" 

# Site 5 6 7
/usr/share/netdisco/email_port_change_digest.sh 37 g...@example.com "Site 5" /usr/share/netdisco/email_port_change_digest.sh 38 g...@example.com "Site 6" /usr/share/netdisco/email_port_change_digest.sh 39 g...@example.com "Site 7" 


------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports 
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer

http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Netdisco mailing list
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users

--- End Message ---
--- Begin Message --- 
Thanks for this report, this will come in handy. I ran the report but I show a 
lot of weird speeds.  Do you know why my report shows as such...


Count   Speed
2       200000000
7       4000000000
8       20 Gbps
10      3000000000
16      1410065408
58      10 Gbps
62      2000000000
572     10 Mbps
1209    
3439    1.0 Gbps
4830    100 Mbps



Ken DeShong
Network Engineer
    

Amazing Things Happen When You Connect the Unconnected

 
 
 

-----Original Message-----
From: Oliver Gorwits [mailto:oli...@cpan.org] 
Sent: Thursday, October 09, 2014 8:36 AM
To: netdisco-users@lists.sourceforge.net
Subject: Re: [Netdisco] report for number of ports connected at 10/100/1000 ?


Thanks Brian :-)

For the benefit of those following who've not used Netdisco 2's custom reports 
feature, below is the config to add to the main configuration file, and then a 
new "Port Speeds" report will automagically appear in the menu:

reports:
   - tag: port_speeds
     label: 'Port Speeds'
     columns:
       - {total: 'Count'}
       - {speed: 'Speed'}
     query: |
       SELECT count(*) AS total, speed
         FROM device_port
         WHERE up = 'up'
       GROUP BY speed
       ORDER BY total

As easy as that!

https://metacpan.org/pod/distribution/App-Netdisco/lib/App/Netdisco/Manual/Configuration.pod#reports

regards,
oliver.

On 2014-10-08 17:29, Joseph Bernard wrote:
> That is perfect!  I have exactly what I need.
>
> Thanks!
> Joseph B.
>
> On Oct 8, 2014, at 12:09 PM, Brian Marshall <br...@netcents.com>
> wrote:
>
>> If you know SQL then doing ad-hoc reports in netdisco is super easy 
>> go into your postgres database with psql (eg. "psql netdisco 
>> netdisco" in my case) and run the following:
>>
>> select count(*), speed from device_port where up = 'up' group by 
>> speed;
>>
>> That's the result of 2 minutes work, so anyone feel free to correct 
>> me if I'm wrong
>>
>>   -Brian Marshall
>>
>> On 10/8/2014 7:32 AM, Joseph Bernard wrote:
>>> I’m running Netdisco 1.1 at the moment.  How hard would it be to 
>>> make a report to show me the number of connected ports at 10Mbps, at 
>>> 100Mbps, and at 1000Mbps?  I just need the counts.  I can install 
>>> Netdisco 2 if it would be way easier to do this kind of thing with 
>>> that.
>>>
>>> Thanks,
>>> Joseph B.
>>>
>>>
>>> 
>>> --------------------------------------------------------------------
>>> ---------- Meet PCI DSS 3.0 Compliance Requirements with EventLog 
>>> Analyzer Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box 
>>> PCI DSS Reports Are you Audit-Ready for PCI DSS 3.0 Compliance? 
>>> Download White paper Comply to PCI DSS 3.0 Requirement 10 and 11.5 
>>> with EventLog Analyzer
>>> 
>>> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ost
>>> g.clktrk _______________________________________________
>>> Netdisco mailing list
>>> netdisco-users@lists.sourceforge.net
>>> https://lists.sourceforge.net/lists/listinfo/netdisco-users
>>>
>>
>
>
> 
> ----------------------------------------------------------------------
> -------- Meet PCI DSS 3.0 Compliance Requirements with EventLog 
> Analyzer Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI 
> DSS Reports Are you Audit-Ready for PCI DSS 3.0 Compliance? Download 
> White paper Comply to PCI DSS 3.0 Requirement 10 and 11.5 with 
> EventLog Analyzer
> 
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.
> clktrk _______________________________________________
> Netdisco mailing list
> netdisco-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/netdisco-users


------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer Achieve PCI DSS 
3.0 Compliant Status with Out-of-the-box PCI DSS Reports Are you Audit-Ready 
for PCI DSS 3.0 Compliance? Download White paper Comply to PCI DSS 3.0 
Requirement 10 and 11.5 with EventLog Analyzer 
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Netdisco mailing list
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users

--- End Message ---
--- Begin Message --- 
Some of the ports are virtual or non-ethernet.  I had to add “AND type = 
'ethernetCsmacd’ “ to my queries to get what I was looking for.

Thanks,
Joseph B.

On Oct 9, 2014, at 11:51 AM, Deshong, Kenneth <kdesh...@health.usf.edu> wrote:

> Thanks for this report, this will come in handy. I ran the report but I show 
> a lot of weird speeds.  Do you know why my report shows as such...
> 
> 
> Count Speed
> 2     200000000
> 7     4000000000
> 8     20 Gbps
> 10    3000000000
> 16    1410065408
> 58    10 Gbps
> 62    2000000000
> 572   10 Mbps
> 1209  
> 3439  1.0 Gbps
> 4830  100 Mbps
> 
> 
> 
> Ken DeShong
> Network Engineer
> 
> 
> Amazing Things Happen When You Connect the Unconnected
> 
>  
>  
>  
> 
> -----Original Message-----
> From: Oliver Gorwits [mailto:oli...@cpan.org] 
> Sent: Thursday, October 09, 2014 8:36 AM
> To: netdisco-users@lists.sourceforge.net
> Subject: Re: [Netdisco] report for number of ports connected at 10/100/1000 ?
> 
> 
> Thanks Brian :-)
> 
> For the benefit of those following who've not used Netdisco 2's custom 
> reports feature, below is the config to add to the main configuration file, 
> and then a new "Port Speeds" report will automagically appear in the menu:
> 
> reports:
>   - tag: port_speeds
>     label: 'Port Speeds'
>     columns:
>       - {total: 'Count'}
>       - {speed: 'Speed'}
>     query: |
>       SELECT count(*) AS total, speed
>         FROM device_port
>         WHERE up = 'up'
>       GROUP BY speed
>       ORDER BY total
> 
> As easy as that!
> 
> https://metacpan.org/pod/distribution/App-Netdisco/lib/App/Netdisco/Manual/Configuration.pod#reports
> 
> regards,
> oliver.
> 
> On 2014-10-08 17:29, Joseph Bernard wrote:
>> That is perfect!  I have exactly what I need.
>> 
>> Thanks!
>> Joseph B.
>> 
>> On Oct 8, 2014, at 12:09 PM, Brian Marshall <br...@netcents.com>
>> wrote:
>> 
>>> If you know SQL then doing ad-hoc reports in netdisco is super easy 
>>> go into your postgres database with psql (eg. "psql netdisco 
>>> netdisco" in my case) and run the following:
>>> 
>>> select count(*), speed from device_port where up = 'up' group by 
>>> speed;
>>> 
>>> That's the result of 2 minutes work, so anyone feel free to correct 
>>> me if I'm wrong
>>> 
>>>  -Brian Marshall
>>> 
>>> On 10/8/2014 7:32 AM, Joseph Bernard wrote:
>>>> I’m running Netdisco 1.1 at the moment.  How hard would it be to 
>>>> make a report to show me the number of connected ports at 10Mbps, at 
>>>> 100Mbps, and at 1000Mbps?  I just need the counts.  I can install 
>>>> Netdisco 2 if it would be way easier to do this kind of thing with 
>>>> that.
>>>> 
>>>> Thanks,
>>>> Joseph B.
>>>> 
>>>> 
>>>> 
>>>> --------------------------------------------------------------------
>>>> ---------- Meet PCI DSS 3.0 Compliance Requirements with EventLog 
>>>> Analyzer Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box 
>>>> PCI DSS Reports Are you Audit-Ready for PCI DSS 3.0 Compliance? 
>>>> Download White paper Comply to PCI DSS 3.0 Requirement 10 and 11.5 
>>>> with EventLog Analyzer
>>>> 
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ost
>>>> g.clktrk _______________________________________________
>>>> Netdisco mailing list
>>>> netdisco-users@lists.sourceforge.net
>>>> https://lists.sourceforge.net/lists/listinfo/netdisco-users
>>>> 
>>> 
>> 
>> 
>> 
>> ----------------------------------------------------------------------
>> -------- Meet PCI DSS 3.0 Compliance Requirements with EventLog 
>> Analyzer Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI 
>> DSS Reports Are you Audit-Ready for PCI DSS 3.0 Compliance? Download 
>> White paper Comply to PCI DSS 3.0 Requirement 10 and 11.5 with 
>> EventLog Analyzer
>> 
>> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.
>> clktrk _______________________________________________
>> Netdisco mailing list
>> netdisco-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/netdisco-users
> 
> 
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer Achieve PCI 
> DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports Are you 
> Audit-Ready for PCI DSS 3.0 Compliance? Download White paper Comply to PCI 
> DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer 
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
> _______________________________________________
> Netdisco mailing list
> netdisco-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/netdisco-users
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
> _______________________________________________
> Netdisco mailing list
> netdisco-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/netdisco-users

--- End Message ---
------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
_______________________________________________
Netdisco mailing list - Digest Mode
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users

Reply via email to