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.

Advertisements

One thought on “VMM SQL Database Query to find assigned IP Addresses

  1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s