I mean if, for example, devices in "devicelist.csv" could have a new attribute (say site, like current "authgroup" implementation, grouping batches of devices together), and then providing the user the possibility to choose one site (say from a drop down list), resulting in that all queries would return only rows corresponding to data gathered from devices within that site.
Thanks and Best Regards,
Hossein Badbanchi
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
There is currently no site context, but that is a good idea, especially if you are reusing the same VLANs etc at different sites. Implementation would not necessarily be simple but it would be doable. I’ll think about the best way to possible implement this.
Thanks
On May 12, 2015, at 6:42 PM, Hossein Badbanchi hbdbn@users.sf.net wrote:
Hi,
Currently there is no SITE concept in NetDB.
Is there any plan to add this?
I mean if, for example, devices in "devicelist.csv" could have a new attribute (say site, like current "authgroup" implementation, grouping batches of devices together), and then providing the user the possibility to choose one site (say from a drop down list), resulting in that all queries would return only rows corresponding to data gathered from devices within that site.
I am sitting in the headquarter of a company with more than 40 different sites (full Cisco) in more than 15 countries in three regions. Frankly without having distinct views/reports for different sites in WEB interface, the program would not be of much use to me. The alternative would be to install site-specific or at least country-specific NetDB instances.
In the meantime I have been working on this the last two days. My idea was/is to pass the site-name from netdb.cgi.pl to NetDB.pm, copying your logic of passing $searchDays, and using it in SQL statements to restrict the returned results.
I have completed the user interface, adding a drop down list to the main box, whose rows are being populated from a new construct (scopegroup) copying the logic of vlan_change keyword. I mean:
scopegroup SCOPE1 = switch1 switch2 switch3
The above should be defined in /etc/netdb.conf.
I am working on SQL statements now. The goal is to add something like "AND switch IN ('switch1', 'switch2', 'switch3')" to the where clauses of SQL statements currently having something like: "WHERE lastseen < '$search_dt'". The entries in the list should be populated from the corresponding members of the $scope passed in by caller.
Any recommendations/notices?
I intend also to accept Mysql pattern matching syntax in the new scopegroup keyword, instead of a mere list of switch-names.
Any comment?
I have three questions:
1) What about passing the current $scope via a Perl Global variable defined and exported in NetDB.pm, and set to the current scope in netdb.cgi.pl, before calling subroutines in NetDB.pm?
If there are reservations against usage of Global vars, then I should add a new argument to most (if not all) of the NetDB.pm subroutines currently receiving $searchDays as one of their parameters, and calling them with an additional parameter to pass the current $scope.
2) Although I have tried hard not to mess with MySQL table structures (although it might have been the better approach), but I think in one case there is no other choice. I think I need an additional column in "transactions" table, namely scope. I am not sure, but I think this is not 100% necessary for the main part of the application, but mandatory for bookkeeping and reporting. Am I wrong?
3) There is also one point regarding "ip" table which has no clue about any device! There are three queries (SELECTs) having to do only with this table, namely: selectIP_h_string, selectNeverSeen_h_string and selectSeen_h_string. Any idea how can I handle these?
The number of changes I had to make are too many, although the logic is not that complicated.
I haven't had the chance yet to have a close look at other .pl scripts, although quite a number of them are using/importing NetDB.pm. I will have to at least deal with netdb.pl and updatenetdb.pl and specially vlanchange.pl.
After this was functional, I think I will start integrating the scope concept with user authorization, so that I can provide local-site admins, access to the info gathered from their sites.
I have written a new scrapper for Juniper ScreenOS (SGS) firewalls which is being heavily used by us. If interested, please let me know.
I have also a point regarding Cisco WLCs, but I assume it is better to have it in a separate discussion.
Sorry. This post much longer that I had expected.
Thanks and Regards,
Hossein Badbanchi
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sorry I've been out of town. Before you get too far, I would explore
another option for now. Down the road I like the site concept, but
implementation is going to be difficult at the moment. I would consider
using different config files and databases on a single server instead. For
instance, I have a development version on my server that uses
/etc/netdbdev.conf and /etc/netdb-cgidev.conf. I have a second CGI file,
netdbdev.pl in my CGI bin that uses the dev version of the cgi conf file
that points to the dev version of the netdb.conf file. I also have a
separate data directory and devicelist.csv file, as well as a netdbdev
database instance.
Using this concept, you could setup 40 database instances with their own
data directories, devicelists and CGI files to start. Then you could
implement a simple site concept by having a dropdown on the CGI file that
pointed to the different netdb.conf instances within a single CGI instance.
By using different netdb.conf files from a single CGI instance, you could
flip between sites with very little modification of the underlying system.
I would certainly be interested in the ScreenOS scraper. I have another
user who is heavily modifying the junosscraper but doesn't have it finished
yet. I'm not sure if he is going to be implementing ScreenOS as well but it
wouldn't hurt to include both for now.
Let me know what you think of the different config file implementation as
well.
Thanks
On Sun, May 17, 2015 at 1:49 PM, Hossein Badbanchi hbdbn@users.sf.net
wrote:
Hi,
Thank you for the reply.
I am sitting in the headquarter of a company with more than 40 different
sites (full Cisco) in more than 15 countries in three regions. Frankly
without having distinct views/reports for different sites in WEB interface,
the program would not be of much use to me. The alternative would be to
install site-specific or at least country-specific NetDB instances.
In the meantime I have been working on this the last two days. My idea
was/is to pass the site-name from netdb.cgi.pl to NetDB.pm, copying your
logic of passing $searchDays, and using it in SQL statements to restrict
the returned results.
I have completed the user interface, adding a drop down list to the main
box, whose rows are being populated from a new construct (scopegroup)
copying the logic of vlan_change keyword. I mean:
scopegroup SCOPE1 = switch1 switch2 switch3
The above should be defined in /etc/netdb.conf.
I am working on SQL statements now. The goal is to add something like "AND
switch IN ('switch1', 'switch2', 'switch3')" to the where clauses of SQL
statements currently having something like: "WHERE lastseen <
'$search_dt'". The entries in the list should be populated from the
corresponding members of the $scope passed in by caller.
Any recommendations/notices?
I intend also to accept Mysql pattern matching syntax in the new
scopegroup keyword, instead of a mere list of switch-names.
Any comment?
I have three questions:
1) What about passing the current $scope via a Perl Global variable
defined and exported in NetDB.pm, and set to the current scope in
netdb.cgi.pl, before calling subroutines in NetDB.pm?
If there are reservations against usage of Global vars, then I should add
a new argument to most (if not all) of the NetDB.pm subroutines currently
receiving $searchDays as one of their parameters, and calling them with an
additional parameter to pass the current $scope.
2) Although I have tried hard not to mess with MySQL table structures
(although it might have been the better approach), but I think in one case
there is no other choice. I think I need an additional column in
"transactions" table, namely scope. I am not sure, but I think this is not
100% necessary for the main part of the application, but mandatory for
bookkeeping and reporting. Am I wrong?
3) There is also one point regarding "ip" table which has no clue about
any device! There are three queries (SELECTs) having to do only with this
table, namely: selectIP_h_string, selectNeverSeen_h_string and
selectSeen_h_string. Any idea how can I handle these?
The number of changes I had to make are too many, although the logic is
not that complicated.
I haven't had the chance yet to have a close look at other .pl scripts,
although quite a number of them are using/importing NetDB.pm. I will have
to at least deal with netdb.pl and updatenetdb.pl and specially
vlanchange.pl.
After this was functional, I think I will start integrating the scope
concept with user authorization, so that I can provide local-site admins,
access to the info gathered from their sites.
I have written a new scrapper for Juniper ScreenOS (SGS) firewalls which
is being heavily used by us. If interested, please let me know.
I have also a point regarding Cisco WLCs, but I assume it is better to
have it in a separate discussion.
One point I forgot to mention in my previous post. The user interface changes have been implemented in a way that if no "scopegroups" are defined, no drop down list will be added to the main box, and the user wouldn't notice that this feature exists.
Your suggestion of having multiple environments for multiple scopes seems to be doable. Just one point, namely "different databases".
The first option in my drop down list is 'All', meaning that the reports will generate data gathered from all devices. In addition to site, I have also implemented country and region scopes, each country scope includes switches from sites in that country, and each region scope ...
This would mean that after I gather data from switches in say Copenhagen, I should import that data in Copenhagen database, in Netherland database, in EMEA database and eventually in "All" database.
This is essential to be able to see the history of where a certain MAC has been seen in say the last month.
All in all having 40+15+3=58 databases, and corresponding directory structure seems a little frightening! An "Include" directive in conf files is missing here.
Let me see. If I couldn't finish it in a fairly short time, I will consider your approach.
But if I could, would you consider adding the feature to the main code? Why I am asking this is because, you know, when a next version is released, I don't want to throw all my work away.
Regarding the ScreenOS scraper, I will post it here after I clean it up a bit. ScreenOS and Junos are two different worlds.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
Giving your suggestion a second, a third , and then again a fourth thought, it seems quite practical!
The WEB interface remains the same, only a drop box will be added, and upon selecting a specific scope, another database will be connected to extract data. Nice.
I think I can even use the same netdbctl instance to gather data from ALL devices and put everything in the same data.txt files.
Then I should somehow make sure that updatenetdb.pl will distribute the records between appropriate databases based on criteria to which scope the switch/router used to generate that record, belongs.
I will see what needs to be done.
Thanks again.
Best Regards,
Hossein Badbanchi
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I think this will work well for your setup. As far as the data directories,
I would keep those separate at first. I could probably write a manager
script to manage multiple instances and handle the merging of the data at
the end for statistics. You could even then keep a single master database
instance if you wanted and add access controls on the CGI to the site
instances without much trouble.
Eventually I'd like an integrated SITE concept within the database itself,
but that requires some extensive work at the moment, as I'm sure you
started to discover.
On Mon, May 18, 2015 at 5:01 PM, Hossein Badbanchi hbdbn@users.sf.net
wrote:
Hi,
Giving your suggestion a second, a third , and then again a fourth
thought, it seems quite practical!
The WEB interface remains the same, only a drop box will be added, and
upon selecting a specific scope, another database will be connected to
extract data. Nice.
I think I can even use the same netdbctl instance to gather data from ALL
devices and put everything in the same data.txt files.
Then I should somehow make sure that updatenetdb.pl will distribute the
records between appropriate databases based on criteria to which scope the
switch/router used to generate that record, belongs.
Jonathan,
You are right. It was some extensive work, because the SQL statement strings (xxx_h_string) were not all defined (and prepared) in prepareSQL subroutine, but throughout the whole NetDB.pm.
But any way I managed to do it!
Now when I activate general-log in my.cnf file, I see queries like:
==
SELECT * FROM superarp WHERE vlan='523' AND lastseen > '2015-05-13T17:47:21' AND ( superarp.lastswitch IN ('switch1','switch2','switch3')) ORDER BY ip, lastseen
SELECT * FROM supermac WHERE mac='001f.9e17.ecc0' AND ( supermac.lastswitch IN ('switch4','switch5','switch6'))
SELECT switchstatus.switch,switchstatus.port,switchstatus.vlan,switchstatus.status,switchstatus.speed,switchstatus.duplex,switchstatus.description,switchstatus.p_uptime,switchstatus.p_minutes,switchstatus.lastup,superswitch.mac,superswitch.ip,superswitch.s_ip,superswitch.s_name,superswitch.name,superswitch.static,superswitch.mac_nd,superswitch.vendor,superswitch.vrf,superswitch.router,superswitch.uptime,superswitch.minutes,superswitch.firstseen,superswitch.lastseen,nacreg.userID,nacreg.firstName,nacreg.lastName,nd.n_host,nd.n_ip,nd.n_desc,nd.n_model,nd.n_port,nd.n_protocol,nd.n_lastseen,superswitch.s_speed,superswitch.s_ip,superswitch.s_vlan
FROM switchstatus LEFT OUTER JOIN superswitch ON switchstatus.switch = superswitch.switch AND switchstatus.port = superswitch.port AND superswitch.lastseen > '2015-05-13T14:46:31'
LEFT OUTER JOIN neighbor as nd ON ( switchstatus.switch = nd.switch AND switchstatus.port = nd.port )
LEFT OUTER JOIN nacreg ON nacreg.mac = superswitch.mac
WHERE (switchstatus.switch like 'sgil001core' AND switchstatus.port like 'Gi1/2/18' AND ( switchstatus.switch IN ('switch7','switch8'))) ORDER BY lastseen
==
For those statements which have a 'xxx_h_string', prepareSQL changes the string (based on the current scope) and then prepares them; and for other SQL statements which are written simply as strings in prepare function calls, I have appled chanes to theose strings directly. The chanes will be applied only when a scope is defined and has switch members.
Files modified are:
1) netdb.cgi.pl
The main part of the code is for adding a drop box if one or more scopes are defined in netdb.conf.
Some changes are done to add the current scope to $infomsg strings.
The rest of changes are for adding appropriate "&scope=xxx" to all "href=..." strings.
I could also add this to $getOptions variable to avoid adding it to every single "href=..." string, but I just followed your example, because you had not done this for "&days=$searchDays".
A single statement to change $netdbCSVcmd, passing the current scope to netdb.pl
2) netdb.pl has been modified, adding a "-sc scope" switch to limit the output of reports to a certain scope.
3) The majority of changes are done in NetDB.pm.
Three subroutines (parseScopeGroups, prepareWhereClause and getScopeGroups) are to read the scope definitions from /etc/netdb.conf and prepare appropriate "WHERE" clauses for the current active scope.
A few changes to include the current scope to $DEBUG outputs.
The rest of changes are for adding appropriate AND (table.column in ('sw1','sw2',...)) to WHERE clauses in xxx_h_string strings or to inline strings used directly in prepare function calls.
All changes have been implemented in a way that if no scope is defined in netdb.conf, no changes are applied to SQL statements, no drop box will be added, the user will see no difference to the standard version; and in fact, actually none of the functionalities will be affected.
One FLAW, though.
I was not sure if you are interested to integrate this feature in the main code. For this reason I am using a Perl Global Variable (defined in NetDB.pm and set in netdbi.cgi.pl and/or in netdb.pl) which was rather easy to implement. Otherwise I should have modified the number of arguments of almost all subroutines in NetDB.pm and pass the current scope, when calling those subroutines in other Perl scripts (i.e. at least netdbi.cgi.pl and netdbi.pl).
Again this post is getting too long.
I will attach the files after some cleanup.
If you see any chance to add this to the main code, then I will not bother to go for the multi-database approach.
Looking forward to hearing from you.
Best Regards,
Hossein Badbanchi
P.S.
I didn't have a chance to cleanup the ScreenOS scraper script. It is in my TODO list.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'll have to play with this code once you get it going to see how it
functions. I would like to integrate scopes in to the mainline code but I'm
going to need to carefully think about the impact as a whole. It's doable
for sure but it's something I need to carefully consider.
What I would do is create patch files for now for your changes so when the
mainline NetDB changes, you can apply your patches and still stay up to
date. Make sure you are working off the trunk code for now instead of the
1.13 branch. I just updated the 1.13 branch yesterday, but I plan to start
working on v1.14 here soon which will involve SQL changes etc. I'll
consider the site functionality for that release.
I'd also like to clean up the ssid and vlan code for wireless as well as
implement a comprehensive alerting system. I'm going to setup alerting for
CDP, new mac addresses, mac addresses that show up on the network that
haven't been seen in a while etc. I'd like to make it comprehensive so that
someone can alert on any value to extend and replace the macwatch
functionality.
Those are the plans at the moment. I have some time this summer to code so
I'd like to start working on that in June. I'll be out of the office until
mid-next week for the holiday here, but in a couple weeks I'd like to get
started on some of these changes.
Stay in touch,
Jonathan
On Wed, May 20, 2015 at 1:22 PM, Hossein Badbanchi hbdbn@users.sf.net
wrote:
Jonathan,
You are right. It was some extensive work, because the SQL statement
strings (xxx_h_string) were not all defined (and prepared) in prepareSQL
subroutine, but throughout the whole NetDB.pm.
But any way I managed to do it!
Now when I activate general-log in my.cnf file, I see queries like:
SELECT * FROM superarp WHERE vlan='523' AND lastseen >
'2015-05-13T17:47:21' AND ( superarp.lastswitch IN
('switch1','switch2','switch3')) ORDER BY ip, lastseen
SELECT * FROM supermac WHERE mac='001f.9e17.ecc0' AND (
supermac.lastswitch IN ('switch4','switch5','switch6'))
SELECT
switchstatus.switch,switchstatus.port,switchstatus.vlan,switchstatus.status,switchstatus.speed,switchstatus.duplex,switchstatus.description,switchstatus.p_uptime,switchstatus.p_minutes,switchstatus.lastup,superswitch.mac,superswitch.ip,superswitch.s_ip,superswitch.s_name,
superswitch.name
,superswitch.static,superswitch.mac_nd,superswitch.vendor,superswitch.vrf,superswitch.router,superswitch.uptime,superswitch.minutes,superswitch.firstseen,superswitch.lastseen,nacreg.userID,nacreg.firstName,nacreg.lastName,nd.n_host,nd.n_ip,nd.n_desc,nd.n_model,nd.n_port,nd.n_protocol,nd.n_lastseen,superswitch.s_speed,superswitch.s_ip,superswitch.s_vlan
FROM switchstatus LEFT OUTER JOIN superswitch ON switchstatus.switch =
superswitch.switch AND switchstatus.port = superswitch.port AND
superswitch.lastseen > '2015-05-13T14:46:31'
LEFT OUTER JOIN neighbor as nd ON ( switchstatus.switch = nd.switch AND
switchstatus.port = nd.port )
LEFT OUTER JOIN nacreg ON nacreg.mac = superswitch.mac
WHERE (switchstatus.switch like 'sgil001core' AND switchstatus.port like
'Gi1/2/18' AND ( switchstatus.switch IN ('switch7','switch8'))) ORDER
BY lastseen
==
For those statements which have a 'xxx_h_string', prepareSQL changes the
string (based on the current scope) and then prepares them; and for other
SQL statements which are written simply as strings in prepare function
calls, I have appled chanes to theose strings directly. The chanes will be
applied only when a scope is defined and has switch members.
Files modified are:
1) netdb.cgi.pl
The main part of the code is for adding a drop box if one or more scopes
are defined in netdb.conf.
Some changes are done to add the current scope to $infomsg strings.
The rest of changes are for adding appropriate "&scope=xxx" to all
"href=..." strings.
I could also add this to $getOptions variable to avoid adding it to every
single "href=..." string, but I just followed your example, because you had
not done this for "&days=$searchDays".
A single statement to change $netdbCSVcmd, passing the current scope to
netdb.pl
2) netdb.pl has been modified, adding a "-sc scope" switch to limit the
output of reports to a certain scope.
3) The majority of changes are done in NetDB.pm.
Three subroutines (parseScopeGroups, prepareWhereClause and
getScopeGroups) are to read the scope definitions from /etc/netdb.conf and
prepare appropriate "WHERE" clauses for the current active scope.
A few changes to include the current scope to $DEBUG outputs.
The rest of changes are for adding appropriate AND (table.column in
('sw1','sw2',...)) to WHERE clauses in xxx_h_string strings or to inline
strings used directly in prepare function calls.
All changes have been implemented in a way that if no scope is defined in
netdb.conf, no changes are applied to SQL statements, no drop box will be
added, the user will see no difference to the standard version; and in
fact, actually none of the functionalities will be affected.
One FLAW, though.
I was not sure if you are interested to integrate this feature in the main
code. For this reason I am using a Perl Global Variable (defined in
NetDB.pm and set in netdbi.cgi.pl and/or in netdb.pl) which was rather
easy to implement. Otherwise I should have modified the number of arguments
of almost all subroutines in NetDB.pm and pass the current scope, when
calling those subroutines in other Perl scripts (i.e. at least
netdbi.cgi.pl and netdbi.pl).
Again this post is getting too long.
I will attach the files after some cleanup.
If you see any chance to add this to the main code, then I will not bother
to go for the multi-database approach.
Looking forward to hearing from you.
Best Regards,
Hossein Badbanchi
P.S.
I didn't have a chance to cleanup the ScreenOS scraper script. It is in my
TODO list.
Hi,
Rather too late to start it on the newly released code.
I will upload the modified files now and wait for your feedback.
Apart from the main functionality (adding Scopes), I have tried hard to make it transparent for those who do no want to use it. I hope this works.
Now that I am done with this part, and it is working OK for me, I see that without binding this with user authentication, in order to and restrict end-users (or local-site admins) to certain Scopes (at least for writing), I will not be able to release it for general use.
I wait for your feedback before I start on that part.
I have not checked all 'href=' statements in netdb.cgi.pl. Only those which already had a '&days=xxx' directive. This of course should be done.
Another pending point is changing external scripts (like vlanchange.pl) or the part used to change port descriptions. But I think they are trivial.
Recently got my eyes on this fantastic NetDB tool.
Unfortunately, like Hossein, I got the same problem. Around a hundred sites around the world. Steadily closing in on 6k Cisco devices. VLANs are naturally reused between sites and subsites.
Thing is that I'm greener than greenest possible on webservers, databases, programming and scripting. I usually get by with logic and can adjust small things to fit the purpose however. The proposed changes you guys have written is very much out of my league unfortunately.
I would like to, carefully, ask if there has been any new development on this? Any new ideas, beta releases etc? I know this is spare time freeware project and it's bad practice to ask for ETAs, but any update would be greatly appreciated.
Best Regards,
Mattias
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi,
Currently there is no SITE concept in NetDB.
Is there any plan to add this?
I mean if, for example, devices in "devicelist.csv" could have a new attribute (say site, like current "authgroup" implementation, grouping batches of devices together), and then providing the user the possibility to choose one site (say from a drop down list), resulting in that all queries would return only rows corresponding to data gathered from devices within that site.
Thanks and Best Regards,
Hossein Badbanchi
Hossein,
There is currently no site context, but that is a good idea, especially if you are reusing the same VLANs etc at different sites. Implementation would not necessarily be simple but it would be doable. I’ll think about the best way to possible implement this.
Thanks
Hi,
Thank you for the reply.
I am sitting in the headquarter of a company with more than 40 different sites (full Cisco) in more than 15 countries in three regions. Frankly without having distinct views/reports for different sites in WEB interface, the program would not be of much use to me. The alternative would be to install site-specific or at least country-specific NetDB instances.
In the meantime I have been working on this the last two days. My idea was/is to pass the site-name from netdb.cgi.pl to NetDB.pm, copying your logic of passing $searchDays, and using it in SQL statements to restrict the returned results.
I have completed the user interface, adding a drop down list to the main box, whose rows are being populated from a new construct (scopegroup) copying the logic of vlan_change keyword. I mean:
scopegroup SCOPE1 = switch1 switch2 switch3
The above should be defined in /etc/netdb.conf.
I am working on SQL statements now. The goal is to add something like "AND switch IN ('switch1', 'switch2', 'switch3')" to the where clauses of SQL statements currently having something like: "WHERE lastseen < '$search_dt'". The entries in the list should be populated from the corresponding members of the $scope passed in by caller.
Any recommendations/notices?
I intend also to accept Mysql pattern matching syntax in the new scopegroup keyword, instead of a mere list of switch-names.
Any comment?
I have three questions:
1) What about passing the current $scope via a Perl Global variable defined and exported in NetDB.pm, and set to the current scope in netdb.cgi.pl, before calling subroutines in NetDB.pm?
If there are reservations against usage of Global vars, then I should add a new argument to most (if not all) of the NetDB.pm subroutines currently receiving $searchDays as one of their parameters, and calling them with an additional parameter to pass the current $scope.
2) Although I have tried hard not to mess with MySQL table structures (although it might have been the better approach), but I think in one case there is no other choice. I think I need an additional column in "transactions" table, namely scope. I am not sure, but I think this is not 100% necessary for the main part of the application, but mandatory for bookkeeping and reporting. Am I wrong?
3) There is also one point regarding "ip" table which has no clue about any device! There are three queries (SELECTs) having to do only with this table, namely: selectIP_h_string, selectNeverSeen_h_string and selectSeen_h_string. Any idea how can I handle these?
The number of changes I had to make are too many, although the logic is not that complicated.
I haven't had the chance yet to have a close look at other .pl scripts, although quite a number of them are using/importing NetDB.pm. I will have to at least deal with netdb.pl and updatenetdb.pl and specially vlanchange.pl.
After this was functional, I think I will start integrating the scope concept with user authorization, so that I can provide local-site admins, access to the info gathered from their sites.
I have written a new scrapper for Juniper ScreenOS (SGS) firewalls which is being heavily used by us. If interested, please let me know.
I have also a point regarding Cisco WLCs, but I assume it is better to have it in a separate discussion.
Sorry. This post much longer that I had expected.
Thanks and Regards,
Hossein Badbanchi
Hossein,
Sorry I've been out of town. Before you get too far, I would explore
another option for now. Down the road I like the site concept, but
implementation is going to be difficult at the moment. I would consider
using different config files and databases on a single server instead. For
instance, I have a development version on my server that uses
/etc/netdbdev.conf and /etc/netdb-cgidev.conf. I have a second CGI file,
netdbdev.pl in my CGI bin that uses the dev version of the cgi conf file
that points to the dev version of the netdb.conf file. I also have a
separate data directory and devicelist.csv file, as well as a netdbdev
database instance.
Using this concept, you could setup 40 database instances with their own
data directories, devicelists and CGI files to start. Then you could
implement a simple site concept by having a dropdown on the CGI file that
pointed to the different netdb.conf instances within a single CGI instance.
By using different netdb.conf files from a single CGI instance, you could
flip between sites with very little modification of the underlying system.
I would certainly be interested in the ScreenOS scraper. I have another
user who is heavily modifying the junosscraper but doesn't have it finished
yet. I'm not sure if he is going to be implementing ScreenOS as well but it
wouldn't hurt to include both for now.
Let me know what you think of the different config file implementation as
well.
Thanks
On Sun, May 17, 2015 at 1:49 PM, Hossein Badbanchi hbdbn@users.sf.net
wrote:
Hi,
Thanks for the suggestion.
One point I forgot to mention in my previous post. The user interface changes have been implemented in a way that if no "scopegroups" are defined, no drop down list will be added to the main box, and the user wouldn't notice that this feature exists.
Your suggestion of having multiple environments for multiple scopes seems to be doable. Just one point, namely "different databases".
The first option in my drop down list is 'All', meaning that the reports will generate data gathered from all devices. In addition to site, I have also implemented country and region scopes, each country scope includes switches from sites in that country, and each region scope ...
This would mean that after I gather data from switches in say Copenhagen, I should import that data in Copenhagen database, in Netherland database, in EMEA database and eventually in "All" database.
This is essential to be able to see the history of where a certain MAC has been seen in say the last month.
All in all having 40+15+3=58 databases, and corresponding directory structure seems a little frightening! An "Include" directive in conf files is missing here.
Let me see. If I couldn't finish it in a fairly short time, I will consider your approach.
But if I could, would you consider adding the feature to the main code? Why I am asking this is because, you know, when a next version is released, I don't want to throw all my work away.
Regarding the ScreenOS scraper, I will post it here after I clean it up a bit. ScreenOS and Junos are two different worlds.
Hi,
Giving your suggestion a second, a third , and then again a fourth thought, it seems quite practical!
The WEB interface remains the same, only a drop box will be added, and upon selecting a specific scope, another database will be connected to extract data. Nice.
I think I can even use the same netdbctl instance to gather data from ALL devices and put everything in the same data.txt files.
Then I should somehow make sure that updatenetdb.pl will distribute the records between appropriate databases based on criteria to which scope the switch/router used to generate that record, belongs.
I will see what needs to be done.
Thanks again.
Best Regards,
Hossein Badbanchi
I think this will work well for your setup. As far as the data directories,
I would keep those separate at first. I could probably write a manager
script to manage multiple instances and handle the merging of the data at
the end for statistics. You could even then keep a single master database
instance if you wanted and add access controls on the CGI to the site
instances without much trouble.
Eventually I'd like an integrated SITE concept within the database itself,
but that requires some extensive work at the moment, as I'm sure you
started to discover.
On Mon, May 18, 2015 at 5:01 PM, Hossein Badbanchi hbdbn@users.sf.net
wrote:
Jonathan,
You are right. It was some extensive work, because the SQL statement strings (xxx_h_string) were not all defined (and prepared) in prepareSQL subroutine, but throughout the whole NetDB.pm.
But any way I managed to do it!
Now when I activate general-log in my.cnf file, I see queries like:
==
SELECT * FROM superarp WHERE vlan='523' AND lastseen > '2015-05-13T17:47:21' AND ( superarp.lastswitch IN ('switch1','switch2','switch3')) ORDER BY ip, lastseen
SELECT * FROM supermac WHERE mac='001f.9e17.ecc0' AND ( supermac.lastswitch IN ('switch4','switch5','switch6'))
SELECT switchstatus.switch,switchstatus.port,switchstatus.vlan,switchstatus.status,switchstatus.speed,switchstatus.duplex,switchstatus.description,switchstatus.p_uptime,switchstatus.p_minutes,switchstatus.lastup,superswitch.mac,superswitch.ip,superswitch.s_ip,superswitch.s_name,superswitch.name,superswitch.static,superswitch.mac_nd,superswitch.vendor,superswitch.vrf,superswitch.router,superswitch.uptime,superswitch.minutes,superswitch.firstseen,superswitch.lastseen,nacreg.userID,nacreg.firstName,nacreg.lastName,nd.n_host,nd.n_ip,nd.n_desc,nd.n_model,nd.n_port,nd.n_protocol,nd.n_lastseen,superswitch.s_speed,superswitch.s_ip,superswitch.s_vlan
FROM switchstatus LEFT OUTER JOIN superswitch ON switchstatus.switch = superswitch.switch AND switchstatus.port = superswitch.port AND superswitch.lastseen > '2015-05-13T14:46:31'
LEFT OUTER JOIN neighbor as nd ON ( switchstatus.switch = nd.switch AND switchstatus.port = nd.port )
LEFT OUTER JOIN nacreg ON nacreg.mac = superswitch.mac
WHERE (switchstatus.switch like 'sgil001core' AND switchstatus.port like 'Gi1/2/18' AND ( switchstatus.switch IN ('switch7','switch8'))) ORDER BY lastseen
==
For those statements which have a 'xxx_h_string', prepareSQL changes the string (based on the current scope) and then prepares them; and for other SQL statements which are written simply as strings in prepare function calls, I have appled chanes to theose strings directly. The chanes will be applied only when a scope is defined and has switch members.
Files modified are:
1) netdb.cgi.pl
The main part of the code is for adding a drop box if one or more scopes are defined in netdb.conf.
Some changes are done to add the current scope to $infomsg strings.
The rest of changes are for adding appropriate "&scope=xxx" to all "href=..." strings.
I could also add this to $getOptions variable to avoid adding it to every single "href=..." string, but I just followed your example, because you had not done this for "&days=$searchDays".
A single statement to change $netdbCSVcmd, passing the current scope to netdb.pl
2) netdb.pl has been modified, adding a "-sc scope" switch to limit the output of reports to a certain scope.
3) The majority of changes are done in NetDB.pm.
Three subroutines (parseScopeGroups, prepareWhereClause and getScopeGroups) are to read the scope definitions from /etc/netdb.conf and prepare appropriate "WHERE" clauses for the current active scope.
A few changes to include the current scope to $DEBUG outputs.
The rest of changes are for adding appropriate AND (table.column in ('sw1','sw2',...)) to WHERE clauses in xxx_h_string strings or to inline strings used directly in prepare function calls.
All changes have been implemented in a way that if no scope is defined in netdb.conf, no changes are applied to SQL statements, no drop box will be added, the user will see no difference to the standard version; and in fact, actually none of the functionalities will be affected.
One FLAW, though.
I was not sure if you are interested to integrate this feature in the main code. For this reason I am using a Perl Global Variable (defined in NetDB.pm and set in netdbi.cgi.pl and/or in netdb.pl) which was rather easy to implement. Otherwise I should have modified the number of arguments of almost all subroutines in NetDB.pm and pass the current scope, when calling those subroutines in other Perl scripts (i.e. at least netdbi.cgi.pl and netdbi.pl).
Again this post is getting too long.
I will attach the files after some cleanup.
If you see any chance to add this to the main code, then I will not bother to go for the multi-database approach.
Looking forward to hearing from you.
Best Regards,
Hossein Badbanchi
P.S.
I didn't have a chance to cleanup the ScreenOS scraper script. It is in my TODO list.
Hossein,
I'll have to play with this code once you get it going to see how it
functions. I would like to integrate scopes in to the mainline code but I'm
going to need to carefully think about the impact as a whole. It's doable
for sure but it's something I need to carefully consider.
What I would do is create patch files for now for your changes so when the
mainline NetDB changes, you can apply your patches and still stay up to
date. Make sure you are working off the trunk code for now instead of the
1.13 branch. I just updated the 1.13 branch yesterday, but I plan to start
working on v1.14 here soon which will involve SQL changes etc. I'll
consider the site functionality for that release.
I'd also like to clean up the ssid and vlan code for wireless as well as
implement a comprehensive alerting system. I'm going to setup alerting for
CDP, new mac addresses, mac addresses that show up on the network that
haven't been seen in a while etc. I'd like to make it comprehensive so that
someone can alert on any value to extend and replace the macwatch
functionality.
Those are the plans at the moment. I have some time this summer to code so
I'd like to start working on that in June. I'll be out of the office until
mid-next week for the holiday here, but in a couple weeks I'd like to get
started on some of these changes.
Stay in touch,
Jonathan
On Wed, May 20, 2015 at 1:22 PM, Hossein Badbanchi hbdbn@users.sf.net
wrote:
Hi,
Rather too late to start it on the newly released code.
I will upload the modified files now and wait for your feedback.
Apart from the main functionality (adding Scopes), I have tried hard to make it transparent for those who do no want to use it. I hope this works.
Now that I am done with this part, and it is working OK for me, I see that without binding this with user authentication, in order to and restrict end-users (or local-site admins) to certain Scopes (at least for writing), I will not be able to release it for general use.
I wait for your feedback before I start on that part.
I have not checked all 'href=' statements in netdb.cgi.pl. Only those which already had a '&days=xxx' directive. This of course should be done.
Another pending point is changing external scripts (like vlanchange.pl) or the part used to change port descriptions. But I think they are trivial.
Again thanks for this good program.
Best Regards,
H. Badbanchi
Hi,
Could you test the program in your test environment?
Regards,
Hossein Badbanchi
Hi Jonathan & Hossein.
Recently got my eyes on this fantastic NetDB tool.
Unfortunately, like Hossein, I got the same problem. Around a hundred sites around the world. Steadily closing in on 6k Cisco devices. VLANs are naturally reused between sites and subsites.
Thing is that I'm greener than greenest possible on webservers, databases, programming and scripting. I usually get by with logic and can adjust small things to fit the purpose however. The proposed changes you guys have written is very much out of my league unfortunately.
I would like to, carefully, ask if there has been any new development on this? Any new ideas, beta releases etc? I know this is spare time freeware project and it's bad practice to ask for ETAs, but any update would be greatly appreciated.
Best Regards,
Mattias