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.
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?