Forefront TMG Log Export with MSDEToText.vbs messing up IPs

Logging Firewall or Web Proxy traffic on a Forefront TMG/ISA node into the local SQL Express-based database (which is the default setting) has a few advantages, like being able to query past logs through the TMG console. But sometimes it’s better to have logs stored in a plain text format as well for a 3rd party tool or your own log analysis scripts.

For this purpose, Microsoft provides the MSDEToText.vbs tool to export logs from a TMG/ISA SQL database into text files.

why

why

However, the MSDEToText script is producing some weird results for my TMG environments, namely it fails to convert the source and destination IP-addresses properly:
For example, what should be exported as “192.168.1.11” ends up as “-63.-87.-254.-245”, with negative numbers per octet in the text log. Notice something? Yeah, subtracting each value from 255 yields us the correct IP (well, almost except for the last octet which is off by 1). This happens only for IPs that don’t have an existing computer object defined in the TMG policy.

There is obviously something wrong with the logic inside the MSDEToText VB script. Being completely clueless about VBS (I can’t even remember ever seriously coding/editing something longer than two lines), I dug into the script to see what makes it go bonkers and found the following function to be responsible:

Private Function IPFromDbl(rowValue)
    Dim ipDouble         ' Double
    Dim dot              ' string
    Dim count,octet      ' integers

    ipDouble = CDbl(rowValue)
    IPFromDbl= ""
    dot = "" 
    For count = 1 To 4
        ipDouble = (Fix(ipDouble)) / 256
        octet = 256 * (ipDouble - Fix(ipDouble))
        IPFromDbl = CStr(octet) & dot & IPFromDbl
        dot = "."
    Next

End Function

I added a few lines as you can see below to cope with negative values, and it now exports the correct IPs in my text logs:

Private Function IPFromDbl(rowValue)
    Dim ipDouble         ' Double
    Dim dot              ' string
    Dim count,octet      ' integers

    ipDouble = CDbl(rowValue)
    IPFromDbl= ""
    dot = "" 
    For count = 1 To 4
        ipDouble = (Fix(ipDouble)) / 256
        octet = 256 * (ipDouble - Fix(ipDouble))
	If (octet <= 0) Then
		octet = octet + 255
		If (count = 1) Then
			octet = octet + 1 'because otherwise, the last octet of the IP (first iteration) is always off by one
		End If
	End If
        IPFromDbl = CStr(octet) & dot & IPFromDbl
        dot = "."
    Next

End Function

There is probably a better way to fix this within the preceding lines, but I didn’t want to waste too much time on it. Feel free to share a better idea.

 

Oh god, there is VBS code on my blog! What has the world come to?

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