Yes, this is an edge case. But if someone randomly asks you to interrogate a Virtual Manager (VMM) SQL Server Database, to determine which IP addresses have been allocated to which VMs, use a query like this:-
SELECT
CAST( CAST( SUBSTRING( [Address], 1, 1) AS INTEGER) AS VARCHAR(3) ) + ‘.’
+ CAST( CAST( SUBSTRING( [Address], 2, 1) AS INTEGER) AS VARCHAR(3) ) + ‘.’
+ CAST( CAST( SUBSTRING( [Address], 3, 1) AS INTEGER) AS VARCHAR(3) ) + ‘.’
+ CAST( CAST( SUBSTRING( [Address], 4, 1) AS INTEGER) AS VARCHAR(3) ) AS ‘IP Address’,
AssignedToObjectName
FROM dbo.tbl_NetMan_AllocatedIPAddress
order by AssignedToObjectName
I was asked to do this today, and spent an entire afternoon figuring this stuff out and working out how to reverse engineer an IP address which is stored in a varbinary column.
I hope, I pray (and I am an atheist) that this blog post saves someone a bit of hassle and time one day.
Btw of course this is possible with Powershell, which will involve a similar kind of query but done in a Powershelly way. The purpose of this post is to help DBAs and Devs who need a quick and easy way to do this – it’d be a trivial enough task to Poweshellize this kind of query.