RPS IPSheet Parser

The RPS IPSheet Excel file is a Microsoft Excel spreadsheet that contains CMDB endpoint data for a customer. The IPSheet will contain a set of worksheets for NIPR, SIPR, and Colorless. Each worksheet will contain Endpoint data for Subnets and IPAddresses needed for the CMDB. These Subnets and IPAddresses will be stored as Resource Groups and Items in the RPS database.

A sample IPSheet Excel file can be found at:

RPS > Source > Rps.Extension.IpSheet > Samples > TestIpSheet.xlsx

Assumptions

  • The 1st column in the worksheet is ignored.
  • Subnet/IPAddress are used as the name of the imported Subnet or IpAddress and are expected to be unique.
  • Column headers are exactly, "Telephony Rng", "Subnet", "Mask", "Gateway", "Reserved", "Assignment"
  • Subnet name is expected to be named "SIPR", "NIPR" or "Colorless", exactly.
  • Reserved IPAddresses are expected to be listed in the same order as the Subnets are.
  • Each set of reserved IpAddresses for a Subnet will list the subnet name in the first row ONLY.
  • Note: This is important because subnet names aren't unique ("Tactical Spare") so we use a combination of sequence and presence of the name to identify a new group, rather than IP Logic.
  • Row 1 contains column headers.
  • Row 2 contains header information.
  • Rows 3 -> x contain Subnets.
  • Row x+1 contains value "Unit Base Info" in the 2nd column.
  • Row y contains value "Detailed Info" in the 2nd column.
  • Row y+1 -> z-1 contains reserved IPAddresses.
  • Row z contains value "End" in the 2nd column

RPS IPSheet Parser .NET Library (DLL)

The RPS IPSheet Parser is a .NET library (DLL) written in C#. This library contains two key Powershell Cmdlets for importing or removing IPSheet data to/from the RPS CMDB database.

The PS Module defined by the RPS IPSheet Parser library assembly is imported in Powershell through the module’s manifest (psd1). Subsequently, the Cmdlets can be called to import and parse IPSheet data into RPS.

Example:

Import-Module Rps-IpSheet

Import-RpsIpSheet Cmdlet

The Import-RpsIpSheet Powershell Cmdlet is used to import IPSheet data into the RPS database. This cmdlet accepts one parameter “Path” that accepts an absolute or relative path to the IPSheet Excel file location. Results will show the total number of Subnets and IpAddresses imported. Warnings will show any import errors, including invalid file paths.

Note

The import will not overwrite or merge with existing data, so if there is a collision on subnet or ipaddress, then a warning is displayed.

Example:

Import-RpsIpSheet -Path c:\ipsheet\abc-tactical-2018.xls

Import-RpsIpSheet -Path .\abc-tactical-2018.xls

Remove-RpsIpSheetCmdlet

The Remove-RpsIpSheet Powershell Cmdlet is used to remove previously imported IPSheet data from the RPS database. This cmdlet has no parameters. It will remove all previous imports of IPSheet data. Results will display all resource groups (subnets) and resource items (ip addresses) removed from the RPS database.

Example:

Remove-RpsIpSheet

Powershell Script

The IPSheet data can be imported into an RPS database using Powershell. The Powershell script will import PS Module from the RPS.Extension.IpSheet .NET assembly. The PS Module is distributed to the Content Store located at ContentStore\Modules\Rps-IpSheet. Then the Import-RpsIpSheet cmdlet is called with an absolute or relative path to the location of the IpSheet Excel file that contains the Subnet and IpAddress endpoint data for the CMDB.

Example:

Import-Module Rps-IpSheet
$start = Get-Date
Import-RpsIpSheet -Path C:\ipsheet\86ESB-A-Tactical-Template-11-17-2015.xls -Verbose
(Get-Date).Subtract($start).TotalSeconds

More Resources