Home / server / Questions / 217696
Accepted
TrevJen
Asked: 2011-01-01 07:33:32 +0800 CST

Convert IP Address format from ForeFront Firewall logs with SQL

  • 772

I am trying to query IP addresses from Forefront Firewall logs, and I am a little stuck on the IP formatting

C0A8E008-FFFF-0000-0000-000000000000

Can anyone give me the MSSQL command to turn this into standard human redable?

UPDATE, I now see that I kust need to convert the first 8 charecters from hex to decimal....which I can then convert to IP. the trick is to parse those first charecters from the field with SQL

logging sql ip microsoft-forefront

2 Answers

  1. Best Answer

    You can use substring function to get the four octets of IP address. You can do:

    SELECT hex_to_dec(substring(field, 1, 2)) + '.' + hex_to_dec(substring(field, 3, 2))
 + '.' + hex_to_dec(substring(field, 5, 2)) + '.' + hex_to_dec(substring(field, 7, 2))
 <FROM, WHERE ...>

    I am not sure whether MSSQL has a function that does hex to decimal conversion hex_to_sec. You just need to find a proper way to implement it.

    • 1

  2. I don't know how to do it in MSSQL but to give you an idea of the method, in your example above C0A8E008 is 192.168.224.08. Split C0A8E008 into four 2 hexdigit blocks and convert each into decimal.

    • 0