When starting at an organisation or engaging in any kind of infrastructure task in IT getting the lay of the land is a crucial first step in coming up with a new solution or fix for a operational problem. Often getting an accurate inventory from any team in a department can be a challenge as teams will keep their own decentralised inventories of equipment.
If this story sounds familiar then this blog post might be able to assist or at least give you some hopefully interesting ideas for creating an inventory automatically.
mRemoteNG is a fantastic multi-protocol utility that allows system administrator & network engineers alike to organise systems into a graphical hierarchical folder structure. This removes some of the legwork in searching for a spreadsheet hidden in the belly of a shared folder in the organisation and then going through the arduous task of working out which credentials are needed for that system. mRemoteNG will even import Windows servers and workstations into it’s inventory automatically from Active Directory with the OU structure intact. mRemoteNG is great for Windows endpoints but for network equipment it’s a different story, it’s on the user to manually add each device into mRemoteNG an error prone and time consuming task.
Enter this blog post, what if you didn’t need to manually all the hundreds and thousands of network devices that you have? Well with a little a lot a fair amount of elbow grease you don’t have to.
Veterans of mRemoteNG may not be aware that mRemoteNG also supports storing the list of devices in an SQL database as well as using the locally stored XML file, that’s the focus of this blog post. The aim of this blog post is to shine a light on the experimental SQL support and show how it could be leveraged to automatically populate an mRemoteNG inventory file. In this example I will use MSSQL and Powershell to achieve this but once you have an understanding of the mRemoteNG table you could use a different method.
Step 1. Creating the mRemoteNG database
Officially documented steps can be found here for creating the database, in our case we will need to follow the instruction for MSSQL.
Step 2. Creating the mRemoteNG folder structure
Once mRemoteNG has been configured to use the newly created database go ahead and create your folder structure as normal.
Step 3. Creating the additional tables
In this step we will then create an additional table within SQL to match hosts to each folder, later this table will be used to automatically sort hosts into each folder based on the IP address of the host.
CREATE TABLE [dbo].[tblIPAM](
[SiteName] [varchar](128) NULL,
[mRemoteContainer] [varchar](128) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[IPLookupMask] [varchar](512) NULL,
CONSTRAINT [PK_tblIPAM] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
We can then run the following command to see the table we have just created.
SELECT [SITENAME],[mRemoteContainer],[ID],[IPLOOKUPMASK] FROM[YourDatabaseName].[dbo].[tblIPAM]
I have already added one record as an example.
The relationship if you haven’t realised yet that we’re aiming to define in the database is that every record in tblCons that has an IP address in the 192.168.1.0/24 range should be placed into the container (or folder) with the GUID f63851f2-bf32-4c68-b206-4dd082b2bd40.
Step 4. The Script
The next step is to create a stored procedure that will go through all of the records in the database and assign each host to folder defined by the tblIPAM table.
UPDATE tblCons
SET tblCons.ParentID = tblIPAM.mRemoteContainer
FROM tblIPAM
JOIN tblCons
ON tblCons.Hostname + ‘%’ LIKE tblIPAM.IPLookupMask + ‘%’
Bonus SQL Snippets
The extensibility that is presented by mRemoteNG SQL support is pretty endless and I think the majority of my collegues would agree that I later took this too far which I will blog about in a later post. However here are some other usefull SQL snippet your may also find useful.
Joe Lunn - Informatiks 