Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to convert an IP address from the firewall log into a dotted IP address in Internet Security and Acceleration Server or in Microsoft Forefront Threat Management Gateway, Medium Business Edition

How to convert an IP address from the firewall log into a dotted IP address in Internet Security and Acceleration Server or in Microsoft Forefront Threat Management Gateway, Medium Business Edition

View products that this article applies to.

Introduction

This article discusses how to convert an IP address from the Microsoft Internet Security and Acceleration (ISA) Server or Microsoft Forefront Threat Management Gateway, Medium Business Edition firewall log into a dotted IP address by using SQL Query Analyzer to query the firewall log database.

↑ Back to the top

More information

To convert an IP address from an ISA Server 2004 firewall log into a dotted IP address, use the following SQL statement in SQL Query Analyzer: 
SELECT 

CAST(SourceIP / 256 / 256 / 256 % 256 AS VARCHAR) + '.' + 

CAST(SourceIP / 256 / 256 % 256 AS VARCHAR) + '.' + 

CAST(SourceIP / 256 % 256 AS VARCHAR) + '.' + 

CAST(SourceIP % 256 AS VARCHAR) 

AS [Nice Source Ip], FirewallLog.*

From FirewallLog
The SourceIP field of the FirewallLog table is defined using the BigInt data type. For example, if the value of the SourceIP field is 172.16.1.1, the table will store the data value as 2886729985.

Note FirewallLog is the table name for the Firewall log, and WebProxyLog is the table name for the Web Proxy log.

↑ Back to the top

Keywords: KB891223, kbhowto, kbdatabase

↑ Back to the top

Article Info
Article ID : 891223
Revision : 3
Created on : 8/15/2006
Published on : 8/15/2006
Exists online : False
Views : 377