Read IP from TMG Log

2012/04/23

 You can use SQL Server as TMG log destination. You can also use BCP to save data to local csv file then edit with your favorite excel. The only problem here is that columns ClientIP, DestHostIP and NATAddress are uniqueidentifier type. Record is like "C0A84C4A-FFFF-0000-0000-000000000000". The script below is to transfer them to readable IPv4 format. Enjoy it.

PS. C0A84C4A == 192.168.76.74.

SELECT *,
    CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),ClientIP),1,2), 1)))
    +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),ClientIP),3,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),ClientIP),5,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),ClientIP),7,2), 1)))
      as ClientIP_New
        ,
      CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),DestHostIP),1,2), 1)))
    +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),DestHostIP),3,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),DestHostIP),5,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),DestHostIP),7,2), 1)))
      as DestHostIP_New
        ,
      CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),NATAddress),1,2), 1)))
    +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),NATAddress),3,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),NATAddress),5,2), 1)))
      +‘.’ + CONVERT(varchar(3),CONVERT(int, CONVERT(varbinary, ’0x’+SUBSTRING(CONVERT(varchar(max),NATAddress),7,2), 1)))
      as NATAddress_new
from dbo.WebProxyLog
 

One Response to “Read IP from TMG Log”

  1. MS Says:

    Good Job


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>