Manual in PDF format here


LanMngmtXL has the purpose of documenting in Excel all main network information by retrieving and processing configurations and operational data from network devices - mainly Cisco and Checkpoint devices - and documenting that information into tables. It saves information about network objects, security/NAT rules and protocols. A list of what is documented is shown in appendix Table 1.


With this automatic documentation is possible to easily lookup IP or MAC addresses, create a report about firewall rules, lookup rules that allow a specific flow or get all rules related to flows between two networks. It also allows verifying network objects not in use, firewall rules that are redundant or look for free network ranges available. It permits to compare easily between two previously generated document versions and to verify changes. With Network Visualization Tools like NodeXL it is possible to create automated layouts.


The automatic documentation is not intended to replace manual documentation but to help update it and provide support for reporting and analysis. There is no change to the configuration of devices - only information is retrieved. This program is distributed with the hope that it will be useful, but WITHOUT ANY WARRANTY. Program is under development and hasn't been widely tested. Results produced may contain bugs.


This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 2 of the License, or (at your option) any later version.

How it works

LanMngmtXL is a Perl script developed under Windows OS. Appendix A shows some useful information about Perl. The script has 3 modes:

  •  get: retrieves information from network devices and saves in text files;
  •  exec: processes the text files generated with get action or manually created and stores all main network information in one Excel file. Depending on the features enabled, does further processing in order to create extended information;
  •  search: reads the Excel file generated by exec action and generates a new Excel file with the result of the lookup based on the search arguments provided.

There is also an additional mode test that allows to quickly test a script function.


To execute the script run

perl -a get [-d] -u user -p passwd -f devices.txt [-o outputdir]

perl -a exec [-o <output>] [-c] <file/dir> [.]

perl -a search [-v] [-i <input>] [-o <output>] arg1 [arg2 .]

If using the binary file it should be run "LanMngmtXL ." instead of "Perl ."

 Option get saves files into 'cfgdir' directory by default.

Option exec takes a list of files or directories as input and outputs by default to LanMngmtXL.xlsx file.

Option search uses by default file LanMngmtXL.xlsx as input and file LanMngmtXL-search.xlsx as output.


get mode

When executed with the get option the script looks up a file containing the list of network devices and their type. It will then try to connect and retrieve data related to that type of device. The input file should contain a list with one device per line: IP address, the name of the device (used to create the output filename) and the device type separated by ‘;’. A 4th optional parameter for switch and router type can be used to explicitly mention the type of access to use (sshv2, sshv1, telnet).


Example:; dmzfw_01; asa

#; coresw_01; switch-e; telnet

As shown in example, if a line starts with #, then that line will be ignored. If ‘switch-e’ or ‘router-e’ are used then the login password will also be used for ‘enable’ – this only works in telnet mode.


The list of code type and commands executed per type of device is listed in appendix Table 2. The method used by the script to access devices depends on the type of device. Two of the methods use external programs:

  •  SSH: access with SSH is made with plink software (part of PuTTY software). SSH V2 is used to connect to Cisco routers and switches and if it fails will try SSH V1 and Telnet. In some cases with V2 the output is truncated to 16KB – an explicit use of SSH V1 should be chosen in this case. A manual connection should be done first to the devices (either SSH v1 or v2) to allow to “Store key in cache” because in batch mode it will cancel the connection if the key is not stored already;
  • WVT: the Web Visualization Tool from Checkpoint is a tool for exporting policies into XML format. More about this tool in Appendix B. WVT retrieves all policies from SmartCenter: policies not needed for documentation should be removed prior to executing script in exec mode (e.g. mutualized SmartCenter).


The binary file included in the package has been compiled with the same paths of external programs as mentioned in the source file: plink at c:\Putty and WVT at c:\WVT\PROGRAM.

Data retrieved from each network device is saved into files with the special filename format needed for exec mode of the script.

This script has a DEBUG flag to help troubleshoot its execution and a DRYRUN option (-d) for get mode that shows what would be doing without really doing (to first test manually the commands that will be used by the script).

There is  an expanded mode for ACL/NAT rules option (-e) to write several rows per ACL/NAT rule – as an example if a rule contains 3 source, 3 destinations and 3 services in which 1 destination is a group of 3 objects, then expenaded mode will show 45 rows for all combinations possible. This is useful for for searching individual flows and checking for more detailed information on rules covered by others. 
In the event that a table has more than 1 million rows (MS Excel limit) then several tabs will be created.

exec mode

This option generates an Excel file with information about the network. Information is first collected into Perl objects and then organized in a table. The structure for the 'Main' table is shown in appendix Table 1: each object has mandatory fields like 'Origin', 'Name' and 'Class' and then depending on the type of object, some other fields are also defined. The type of data being processed is categorized in the "class' field.


The field 'origin' contains the name of the device from where the object was retrieved. This name is obtained from the 'hostname' configuration parameter when available. Otherwise the name is retrieved from the filename (eg. xml files). For this reason it is important for each file to have the hostname prefixed and followed by '+' sign to separate from the rest of the filename. Otherwise the origin will be 'unknown'. The way the script will process each file depends on the file extension type. Appendix Table 2 shows extensions used.


Example of a correct file name:



The tables created in Excel have the "Freeze Panes" and "Auto Filter" settings configured so it will be easier to lookup data. Filtering the 'class' column will allow to look for some specific type of objects (e.g. all interfaces). Using text filter to look IP addresses in the L3 column will show all ACL, NAT rules or routes that explicitly use those addresses. For implicit lookup, see section about search mode.


Checking for differences that occurred between configurations is made easy by using two Excel files generated in different periods and compare them with WinMerge tool (with a special plugin that allow to compare XLSX files): 

 image_thumb3 image_thumb4


There are several internal options, called features, available for exec mode. By editing the script they can be found at the top of variable declaration:

  •  DEBUG (recommendation: enabled): when enabled writes to a file (LanMngmtXL-debug.txt) very detailed information of the execution of the script including regular expression matching. It also keeps track of time execution which can be easily analyzed by doing

grep @time LanMngmtXL-debug.txt

  • EXCEL (enabled) and MYSQL (disabled): output of execution can be written to Excel file or to MySQL database or both depending on configuration of these options;
  •  ExpandGroups (enabled): this option generates a new table, similar to the 'Main' table but with the name 'Xtend' with similar data but where all group objects have been expanded to their final members (recursive expansion). This replacement is done for ACL and NAT rules and both Layer 3 and 4 objects. For example if a service group object is mentioned in an ACL rule, then the 'Xtend' table will show the members and not the group. Member names are translated in order to avoid duplicates;
  •  TranslateNames (enabled): the script keeps track of name resolution (both L3 and L4) when available. When this option is enabled, the Xtend table will show the names of objects translated. This feature is needed for the XLcode feature;
  •  XLcode (enabled): it creates a new property (column) in table 'Xtend' coding every object with L3 or L4 properties. This feature is explained in more detail in chapter 3. This option depends on the previous feature and is important for remaining features listed next;
  •  NotFound (enabled): when enabled the script will search all objects that were not used. They will have status 'N/A' in the 'Xtend' table and will be removed from 'Main' table along with objects which have status 'disabled', 'down','notconnect', 'off' and 'Backup'. This doesn't mean the object is really not used: for example if an ACL is only used for a capture and because capture objects are not processed by the script it will be considered status 'N/A'. This feature is particularly useful to check which network objects are being used in a firewall (batched 'Where used');
  •  NetList (enabled): this feature creates a new table 'Net' listing all the networks found and also lists free networks segments inside the private network space. This feature is also needed for some of the layouts. Note that if an ACL has a rule that mentions a network but no devices are really allocated, then this will be considered to be in use and no segments of this network will be shown as free;
  •  LayPhysical, LayLogical, LayNetwork, LayFlow (enabled): data visualization can sometimes show information that otherwise would be difficult to detect like asymmetries in a redundant design. These features perform analysis of the 'Xtend' table and create a pair of tables (edges and vertices tables) for each of the 4 layouts: Physical, Logical, Network and Flows. These tables have properties like labels and colors that were designed to be used with XLnode software. Appendix D gives a quick description of the tool and some insight of the results that can be achieved with this information. These features need NetList, TranslateNames and XLcode features enabled. Note: Physical layout is a Layer 1 and 2 layout because connections between devices are based on interfaces, STP and ARP data.
  •  Covered (disabled): this feature should only be enabled in rare occasions when there's need to inspect a firewall ACL and see which rules cover (hide) other rules. As an example Checkpoint doesn't allow a rule to hide another rule that is at the bottom but allows if it is above. There are several reasons to allow this but sometimes it would be useful to find the specific rules that are covered by a later generic rule and remove them. In 'Xtend' table all covered rules status are appended with Xn where n is the rule number that covers it. Exceptionally if instead of 'X' is shown 'W' it means that between the covered and covering rule there is a rule with different action (e.g. deny instead of permit) that those rules have - this is a warning that the rule might still be important. If all flows in the same rule number are covered in 'Xtend' table, then that rule will have status appended with X in 'Main' table; This feature is a time consuming process and can take several hours depending on the number and complexity of rules. For this reason some parts of the script code were optimized for performance (but less human-readable);
  •  Ignored (disabled): all IP and MAC addresses that were found in the configurations but not matched in any class are then categorized as "unknown/ignored". With this feature enabled that will also be shown in the 'Xtend' table. With the feature disabled is still possible to lookup in the debug file by doing

grep -B 1 unknown LanMngmtXL-debug.txt | grep L | sort | uniq

The binary file included in the package has been compiled with the features enabled as mentioned above.


search mode

As previously mentioned explicit searches can be done directly in Excel by using its search option or filters. But with IP and service port ranges some specific IP or port can be part of a rule without being explicitly visible. For that reason, the search option was created. It is important to note that XLcode feature should have been enabled when using the exec mode. In search mode the script will read the previously generated Excel file and will create a new one with the results: a subset of the rows of the original file where there was a match (in both 'Main' and 'Xtend' tables).

In case more than one search argument is provided it will do a 'AND' search and return only the rows where all search arguments were found. But for each search argument it will look up their name OR IP/port OR any object range where XLcode shows to be part of. If -v option is used it will return matches that are covered by (and not covering) the search arguments.

Search is case-insensitive. Search arguments are also looked up in "Desc" as long as they form a complete word.

As an example, looking up 2 IP addresses and 1 service (e.g. . -a search tcp#80) will return all rows where these 3 objects were found explicitly or implicitly (inside a range/group). Example a row from an ACL with flow " -> tcp#80-81" would be matched. If run with -v option then a row from an ACL with flow " -> tcp#80" would match.



XLcode is an extra property of all objects that have a L3 (network) or L4 (service) property. The purpose is to code those properties in order to help processing for other actions (search) or features (layouts).

The format is:



The uppercase elements XYZKXLMNL are separators and not all are mandatory. The section from X to X is L3 related and section L to L is L4 related. Before the first separator (X) there is a lowercase letter which defines the type of object: a=ACL, n=NAT, r=Route, o=other object. Between the Xs, there can be a maximum of 4 IP groups so 3 separators used. Between Ls there can be two port groups and the third element is a combination of protocol (e.g. ip, gre) and service type (e.g. tcp, udp). Finally at the end, after the last separator (L) there can be a combination of additional flags in lowercase: e='group with exceptions', f='not equal service port', s=’service with source port’ and t='rule with time object'. These flags are checked when running ‘covered’ feature for example.


All IP and port ranges are coded in hexadecimal and except for the delimiters the rest of XLcode is in lowercase. When coding a single IP or port number, the range is created with same start and end number. The order that each of the 4 IP or 2 service ports ranges is coded depends on type of object:

  • 1st range is filled with destination of an ACL or NAT or routing rule. Other object types will have only this range.
  • 2nd range will have the source of ACL or NAT rule. In case of routing rule it will have the gateway;
  • 3rd and 4th ranges (in L3 section) apply only to NAT rules and have translated destination and source respectively.

The reason why it is done this way is to allow a sort of the XLcode field having the most significant range first.

Limitations and to do list

The following is a list of developments that could be planned in the future:

  • Support for IPv6 addresses and MAC 64bit
  • Parse DNS and DHCP commands;
  • Process other devices text configurations;
  • Check firewall logs or hit count information to find rules not used;
  • Use SNMP instead of command parsing to retrieve data

List of Tools

Following is a list of software related with this tool


  • Perl for Windows:  ActivePerl [link] or StrawberryPerl [link]
  • Notepad++ [link]
  • MS Excel [link]
  • NodeXL [link]
  • MySQL [link]
  • WinMerge [link] and XLSX Plugin [link]
  • Checkpoint Web Visualization Tool (WVT) [link]
  • PuTTY Plink [link]
  • GNU Core Utils for Windows [link]


Appendix Table 1: logic output for LanMngmtXL



Appendix Table 2: inputs for LanMngmtXL


Appendix A - Perl notes

This software is being developed and tested with Strawberry Perl 5.16 on 64bit. Depending on the need to use some of the features some modules can be commented. For example if there is no need to use MySQL, then these modules can be commented:

#use DBI;

#use DBD::mysql;


Installation of modules is done by executing:

cpan Text::Iconv Data::Dumper::Concise XML::Xpath Excel::Writer::XLSX Spreadsheet::XLSX DBD::mysql Net::IP Net::Telnet::Cisco


With the software an additional module is included for service port translation.


Appendix B - Checkpoint Web Visualization Tool (WVT) notes

Checkpoint Web Visualization Tool is a product from Check Point Software Technologies Ltd

Installation instructions (for version R75.40 but similar for other versions):

It is strongly recommended to use a copy of the installation (first step) and not unpack to original folder.

  1. Copy folder C:\Progra~1\CheckP~1\SmartC~1\R75.40\PROGRAM\ to C:\WVT\PROGRAM\
  2. Download and unpack file cpdb2web_B983000003_1.tgz to C:\WVT\PROGRAM\ and choose to overwrite files
  3. Create folder C:\WVT\PROGRAM\xsl\XML\
  4. Apply a minor patch in order to allow to see the "VPN communities". Edit xsl\security_policy.xsl and replace line 157 from

<xsl:for-each select="./through/through">


<xsl:for-each select="./through/members/reference">



When executed manually:

C:\WVT\PROGRAM\cpdb2web -s SCSserver -u user -p pass -o C:\WVT\PROGRAM\xsl\XML\


Open C:\WVT\PROGRAM\xsl\index.xml

It will open in a browser. It is also possible to import to Excel

Appendix C - MySQL notes

MySQL is a product from Oracle corporation.

By default there is a 'mysql' database installed and this database is used by the script just to start the connection (so it is important that it exists) but by default the script writes data into database 'lan-mngmt'. If the database or table doesn't exist then they are created. Table contents are erased before writing data.

Appendix D - NodeXL notes and layout examples

NodeXL is a project from Social Media Research Foundation. All images in this section were created with NodeXL (


As mentioned at NodeXL website this software is created for network overview, discovery and exploration and was created for Excel. This is the main reason why this tool was selected over other similar tools. It allows to easily generate graphs by copying data from an Excel sheet (LanMngmtXL) to another Excel sheet (NodeXL template). This tool also uses a few of the most common layout methods available and has several good features like grouping. But this tool was developed focusing mainly in social networks study and there might be other layout types more suitable for computer networks that are still not available in this tool.


There are many variations which can make the layouts more appellative and understandable. Exploration is easy with object selection and changes to zoom and scale levels. There are also other features in this program like grouping by connected component or motif.



The first example is a physical layout (Layer 1+2). The default node configuration shows only connections between switches and from device to switch.



The color code is: devices (pink), physical interfaces (yellow), logical interfaces and vlans (green), backup interfaces (white), non-active interfaces (orange) and all MAC addresses (cyan). The MAC addresses from VRRP/HSRP/GLBP/CPHA/F5-TMM are in red: this type of MAC can be repeated along the network so it should be noted that it doesn't mean the associated interfaces are all connected between themselves.

MAC addresses of type multicast of not globally administered are ignored for host to switch direct connections.


Below is an example of a physical layout with all nodes visible.



This is a Logical layout with default settings where only devices and networks are shown:


The color code is: devices (pink), networks (yellow), IP addresses (cyan), gateways(orange) and other networks they allow to reach (white). The default network ( is in red.

Next is an example of a logical layout where all nodes are shown:


This is a network list layout:


Nodes in yellow are networks and they are connected to its subnets. In Excel a selection of all 10.x.x.x addresses is highlighted in red in the layout.


Below is another network list layout showing the IP addresses in cyan and free network ranges in green.


The last layout shows flows authorized by a firewall:


The color code is: devices (pink), destination networks/hosts (yellow), destination services (green), source networks/hosts (cyan), source ports (white). Nodes for "all networks" and "all ports" are in red.

Last edited Oct 23, 2014 at 6:49 PM by MiguelRosa, version 6


No comments yet.