What’s up with the long unique IDs in DHCP? Or why SQL is so cool :)

If you have seen DHCP lease pools lately you may have come across unique IDs that look nothing like a MAC addresses you are used to see.

Below a sample of what you may find. Taking a closer look you may recognize 48 character long hexadecimal strings.

So now what?

HAZ DHCP Long Unique IDs
Potentially hazardous Unique IDs in DHCP

Thankfully Microsoft SQL Server can easily help you make sense out of the hexadecimal string.

The converted alphanumerical representation of the above depicted hexadecimal string looks like this:

cisco-0017.5910.f140-Vl1

which in return looks very much like it includes a mac address 00:17:59:10:F1:40 – potentially something you can make a reservation for in your DHCP scope.

Now the question – How did I get from the long hexadecimal string to the alphanumeric outcome?

Easy enough using transact SQL.

In the code listed below 2 temporary tables are used to help translate the long unique IDs I obtained from the DHCP server into understandable output

Create table #HexToAlpha

(

  hexstring varchar(48)

, alphastring varchar(24)

)

Insert into #HexToAlpha values(‘53617373616E’, )

Insert into #HexToAlpha values( ‘636973636f2d303031372e353931302e663134302d566c31’,)

Insert into #HexToAlpha values( ‘yourhexadeximalstring’,)

create table #NHC (numbr smallint identity(0,1), hex char(2), chr varchar(1))

insert into #NHC (chr) select top 256 null from sysobjects

declare @hexes char(16)

set @hexes = ‘0123456789ABCDEF’

update #NHC set hex = substring(@hexes,numbr/16+1,1)+ substring(@hexes, numbr (numbr/16 * 16)+1, 1), chr=char(numbr)

while exists (select * from #hextoalpha where len(hexstring) <> len(alphastring)*2)

update #hextoalpha set alphastring= alphastring +

(select chr from #NHC where substring(hexstring, len(alphastring)*2 +1,2) = hex )

where len(hexstring) <> len(alphastring)*2

–select * from #NHC – in case you want to see what the hex lookup table contains.

drop table #NHC

select * from #hextoalpha

drop table #hextoalpha

Hopefully you’ll find this Segway between SQL Server and Windows infrastructure helpful.

Enjoy

Advertisements

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