I've got this great tool up and running, but don't seem to be able to get theCSV export sorted. I've put the blank CSV in the root of my www server (Apache22) doc directory (where the 'depends' foder is also located). I've now set it world writeable just to make sure that there's not a permissions problem, but every time I click on the tab for to generate the export I just get an error.
Any pointers would be appreciated …
Oh yes - I take it that your server needs to be on the same network as your hosts to get the Ip addresses and hostnames of connected devices? Uses ARP?
Thanks,
Hube
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I just got back from a much needed vacation, sorry for the delay. The CSV bug is definitely a problem, but I can't seem to reproduce it here for some reason. I am going to be doing a full reinstall of v1.8 on a fresh Ubuntu server soon and I hope to track this down.
In the meantime, you can use the CLI tool to create CSV reports. I know it's not as easy, but you can do something like this:
netdb -vl 180 -c > output.csv
Also, once you get your router data imported, all of that IP information will start showing up. If you only have one subnet on your network and your netdb server is connected to it, you can use this hack to get the local ARP table imported in to netdb. It's not ideal, but it does work:
#!/bin/sh
# Non-cisco router hack to get your local server's ARP table in to the database
nmap -sP 192.168.1.0/24
arp -a | perl -nle 'split(/\s+/); $_ =~ s/\(|\)//g; print "$_,$_,0,$_";' > /opt/netdb/data/arptable.txt
netdbctl -a
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
ARP tables are all now sorted and happy … my fault again - hadn't put the necessary entry in the 'devices.csv' file. Doh!
I have used the command line to generate some CSV reports, but it would be very nice to be able to do that through the web app too.
I had a brief investigate and it looks to me like the query isn't being passed by the ajax action somehow. The error message I get when the CSV fails just refers to the session id, but the script indicates that the query should also be returned as part of the error … leading me to think that the query isn't getting there?
I'm most likely wrong, but just in case it's of use …
Oh yes - I was also wondering how to remove the entries for a specific device from the database. By that I mean … suppose I 'retire' a switch for whatever reason, does the data associated with that device (i.e arp / mac / port status) remain in the database or will it get cleaned out once the device is no longer in 'devices.csv'?
Great tool, btw. Very useful.
Hube
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Good to hear you got the ARP table data. I promise I'll fix the CSV output, I just need to reproduce it to find the bug. I wouldn't try to track it down on your own, it's probably not worth your time.
As far as device aging, switches age out after 7 days, but individual switch entries with mac addresses on them will stay in the database indefinitely. If you replace a switch with a new one, after 7 days, any duplicate data or changed port names will resolve itself.
Since every query has a number of days in the past value, you don't really have to worry about old data in the database unless it get's too large (unlikely), or you want to purge it for auditing purposes etc. Use the updatenetdb.pl script in /opt/netdb/ to delete any old data. I'm going to incorporate these delete methods in to netdbctl in v1.8.
I just cranked out a new VM to test the CSV bug, and hopefully I will be able to reproduce it and put out a new version. Stay posted.
Jonathan
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Extended descriptions is on the to-do list for v1.8, and here is what I have so far in that version:
- Allow searching by the last 4 digits of a mac address, search using the
xx:xx format to use this
- Added support for changing vlans from the CGI interface, you will need to link
this to your own script if you want to use this feature. Supports voice vlans.
- Added speed and duplex to switch reports
- Modified switch report CSV output for speed and duplex, if you have any scripts
tied to this, you will need to update them to match the new fields
- Added support for -vv output to netdbctl as an alternative to the debug settings
Thanks for the FreeBSD report, things like that are always nice to know.
Jonathan
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I already set the owner and infact executed this command 'chmod 777 netdbReport.csv' but still it is empty(when I tried to Download CSV Report). Is there any command to execute using the Terminal to put the data to the netdbReport.csv?
Please help me also understand(Attached files) on why the display in 'Switch Column' is 10. Wherein, it should be the IP of my Switch(10.3.2.24). My entry is the 'devicelist.csv' are the following:
Sorry I didn't get back sooner, I've been crazy busy lately.
Check your apache2 error logs after trying to export the file and see if there are errors, I bet you'll find some clues there as to why it's not working.
To test it from the CLI, you can run any query with the netdb.pl CLI tool and append -c to it to get your output in CSV format.
netdb -vl 200 -c > netdbreport.csv
The problem with your switches showing up with the name "10" is you need to use DNS names in your devicelist.csv, that's all that NetDB understands (it can't handle IP addresses).
To get around this without having to make real DNS entries on your DNS server (recommended), add the entries to your /etc/hosts file:
Thanks Jonathan, for the Hosts advice. However, the CSV file remains blank even though i executed this command in CLI.
netdb -vl 200 -c > netdbreport.csv
-Mike
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Before adding the > netdbreport.csv, make sure you can pull results out of the database at all. If VLAN200 does not exist on your network, you won't get any results. Work with the command line tool a little more to pull some results out of the database that exist on your network, and then try to redirect that to a csv file:
# Everything on vlan1
netdb -vl 1
I don't know your network so I don't know what data you are looking to turn in to a CSV file. If you run netdb by itself, here are all the options:
Search Type: (Note: 7 day search by default)
-i ipaddr Search ARP table for entries using this ip address
-m macaddr Search ARP table for entries using this mac address (any format or short xx:xx)
-p macaddr Search switchport table for the history of a mac address
-n hostname Search ARP table for hostnames that contain this string (case-insensitive)
-u username Search the ARP table for hosts owned by a user in NAC
-vc vendor Search mac table for a partial vendor code (case-insensitive)
-vl number Get all ARP entries on a vlan (combine with -d)
-vs number Get all switch ports on a vlan (combine with -d)
-sw switch Get a switch report over -d days, refine to certain port with -sw switch,port
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I made a check on the other folder like in /var/www/depends and netdbReport.csv is there with the corresponding queried data. I just need to edit the script to point to this folder for CSV Report Download.
Everything is working now except for setting a schedule for the scraper to run and update the Database.
I just noticed in the Switchport Report, under the LAST IP Column, no IP data is getting displayed in the corresponding MAC Address.
-Mike
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
I've got this great tool up and running, but don't seem to be able to get theCSV export sorted. I've put the blank CSV in the root of my www server (Apache22) doc directory (where the 'depends' foder is also located). I've now set it world writeable just to make sure that there's not a permissions problem, but every time I click on the tab for to generate the export I just get an error.
Any pointers would be appreciated …
Oh yes - I take it that your server needs to be on the same network as your hosts to get the Ip addresses and hostnames of connected devices? Uses ARP?
Thanks,
Hube
OK. Just spotted that the IP address / hostname thing is likely to be down to me not having my router in the mix yet - please ignore that bit.
Yet again I seem to be suffering from the inability to read other posts first. Sorry.
Hey,
I just got back from a much needed vacation, sorry for the delay. The CSV bug is definitely a problem, but I can't seem to reproduce it here for some reason. I am going to be doing a full reinstall of v1.8 on a fresh Ubuntu server soon and I hope to track this down.
In the meantime, you can use the CLI tool to create CSV reports. I know it's not as easy, but you can do something like this:
netdb -vl 180 -c > output.csv
Also, once you get your router data imported, all of that IP information will start showing up. If you only have one subnet on your network and your netdb server is connected to it, you can use this hack to get the local ARP table imported in to netdb. It's not ideal, but it does work:
#!/bin/sh
# Non-cisco router hack to get your local server's ARP table in to the database
nmap -sP 192.168.1.0/24
arp -a | perl -nle 'split(/\s+/); $_ =~ s/\(|\)//g; print "$_,$_,0,$_";' > /opt/netdb/data/arptable.txt
netdbctl -a
Hi,
Thanks for the reply …
ARP tables are all now sorted and happy … my fault again - hadn't put the necessary entry in the 'devices.csv' file. Doh!
I have used the command line to generate some CSV reports, but it would be very nice to be able to do that through the web app too.
I had a brief investigate and it looks to me like the query isn't being passed by the ajax action somehow. The error message I get when the CSV fails just refers to the session id, but the script indicates that the query should also be returned as part of the error … leading me to think that the query isn't getting there?
I'm most likely wrong, but just in case it's of use …
Oh yes - I was also wondering how to remove the entries for a specific device from the database. By that I mean … suppose I 'retire' a switch for whatever reason, does the data associated with that device (i.e arp / mac / port status) remain in the database or will it get cleaned out once the device is no longer in 'devices.csv'?
Great tool, btw. Very useful.
Hube
Hube,
Good to hear you got the ARP table data. I promise I'll fix the CSV output, I just need to reproduce it to find the bug. I wouldn't try to track it down on your own, it's probably not worth your time.
As far as device aging, switches age out after 7 days, but individual switch entries with mac addresses on them will stay in the database indefinitely. If you replace a switch with a new one, after 7 days, any duplicate data or changed port names will resolve itself.
Since every query has a number of days in the past value, you don't really have to worry about old data in the database unless it get's too large (unlikely), or you want to purge it for auditing purposes etc. Use the updatenetdb.pl script in /opt/netdb/ to delete any old data. I'm going to incorporate these delete methods in to netdbctl in v1.8.
I just cranked out a new VM to test the CSV bug, and hopefully I will be able to reproduce it and put out a new version. Stay posted.
Jonathan
Hey,
I found the issue, it's a problem with a column in the database. Try doing this and see how it goes:
mysql -u root -p
use netdb;
alter table transactions modify id varchar(50);
I'm going to fix the database creation script and add this modification to the v1.8 sql upgrade script. Let me know how it goes.
Jonathan
Hi,
Wow. That was quick!
I'll certainly give this a go as soon as I get to the office tomorrow. Will let you know how it goes …
Just curious now - Anything new going to be in v1.8? Any chance of increasing the space for the switchport descriptions?
Oh yes - I can confirm that your splendid tool runs no problem on FreeBSD 8.0 p-3, btw :-)
Regards,
Hube.
Hey,
Extended descriptions is on the to-do list for v1.8, and here is what I have so far in that version:
- Allow searching by the last 4 digits of a mac address, search using the
xx:xx format to use this
- Added support for changing vlans from the CGI interface, you will need to link
this to your own script if you want to use this feature. Supports voice vlans.
- Added speed and duplex to switch reports
- Modified switch report CSV output for speed and duplex, if you have any scripts
tied to this, you will need to update them to match the new fields
- Added support for -vv output to netdbctl as an alternative to the debug settings
Thanks for the FreeBSD report, things like that are always nice to know.
Jonathan
Great news regarding the additional space for descriptions. Also know already that the speed and duplex info. will be useful.
Thanks again for the speedy responses. Maybe you should take a break now?
;-)
Hube
Hi Jonathan,
The table update fixed the CSV export problem I was having. Thanks!
Hube.
Hi Jonathan,
I already set the owner and infact executed this command 'chmod 777 netdbReport.csv' but still it is empty(when I tried to Download CSV Report). Is there any command to execute using the Terminal to put the data to the netdbReport.csv?
Please help me also understand(Attached files) on why the display in 'Switch Column' is 10. Wherein, it should be the IP of my Switch(10.3.2.24). My entry is the 'devicelist.csv' are the following:
+++++++ devicelist.csv +++++++++++
10.3.2.24,netdbarp,forcetelnet
10.3.2.10,netdbarp,forcetelnet
++++++++++++++++++++++++++++
-Mike
Mike,
Sorry I didn't get back sooner, I've been crazy busy lately.
Check your apache2 error logs after trying to export the file and see if there are errors, I bet you'll find some clues there as to why it's not working.
To test it from the CLI, you can run any query with the netdb.pl CLI tool and append -c to it to get your output in CSV format.
netdb -vl 200 -c > netdbreport.csv
The problem with your switches showing up with the name "10" is you need to use DNS names in your devicelist.csv, that's all that NetDB understands (it can't handle IP addresses).
To get around this without having to make real DNS entries on your DNS server (recommended), add the entries to your /etc/hosts file:
10.3.2.24 switch1
10.3.2.10 switch2
Then change your devicelist.csv file to this:
switch1,netdbarp,forcetelnet
switch2,netdbarp,forcetelnet
Jonathan
Thanks Jonathan, for the Hosts advice. However, the CSV file remains blank even though i executed this command in CLI.
netdb -vl 200 -c > netdbreport.csv
-Mike
Mike,
Before adding the > netdbreport.csv, make sure you can pull results out of the database at all. If VLAN200 does not exist on your network, you won't get any results. Work with the command line tool a little more to pull some results out of the database that exist on your network, and then try to redirect that to a csv file:
# Everything on vlan1
netdb -vl 1
I don't know your network so I don't know what data you are looking to turn in to a CSV file. If you run netdb by itself, here are all the options:
Search Type: (Note: 7 day search by default)
-i ipaddr Search ARP table for entries using this ip address
-m macaddr Search ARP table for entries using this mac address (any format or short xx:xx)
-p macaddr Search switchport table for the history of a mac address
-n hostname Search ARP table for hostnames that contain this string (case-insensitive)
-u username Search the ARP table for hosts owned by a user in NAC
-vc vendor Search mac table for a partial vendor code (case-insensitive)
-vl number Get all ARP entries on a vlan (combine with -d)
-vs number Get all switch ports on a vlan (combine with -d)
-sw switch Get a switch report over -d days, refine to certain port with -sw switch,port
Hi Jonathan,
Thanks for the advice.
I made a check on the other folder like in /var/www/depends and netdbReport.csv is there with the corresponding queried data. I just need to edit the script to point to this folder for CSV Report Download.
Everything is working now except for setting a schedule for the scraper to run and update the Database.
I just noticed in the Switchport Report, under the LAST IP Column, no IP data is getting displayed in the corresponding MAC Address.
-Mike