''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Copyright (c) Microsoft Corporation. All rights reserved.
' THIS CODE IS MADE AVAILABLE AS IS, WITHOUT WARRANTY OF ANY KIND. THE ENTIRE 
' RISK OF THE USE OR THE RESULTS FROM THE USE OF THIS CODE REMAINS WITH THE 
' USER. USE AND REDISTRIBUTION OF THIS CODE, WITH OR WITHOUT MODIFICATION, IS 
' HEREBY PERMITTED.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This script converts an MSDE log into a text file or displays the content of 
' the MSDE log on the console screen. This script can be run only on the local
' computer where the MSDE database files are stored.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

' Declare constants needed.
Const adBoolean = 11
Const adDBTimeStamp = 135

MSDEToText

Sub MSDEToText()

    ' Declare objects needed.
    Dim args              ' A WScript Arguments object
    Dim dbName            ' A String
    Dim tableName         ' A String
    Dim outFileName       ' A String
    Dim outFile           ' A TextStream object
    Dim fso               ' A FileSystem object
    Dim cn                ' An ADODB Connection object

    Set args = WScript.Arguments
    If args.Count <> 2 And args.Count <> 3 Then
        Usage()
    Else

        DbName = args.Item(0)
        tableName  = args.Item(1)
   
        Set fso = CreateObject("Scripting.FileSystemObject")
        If args.Count = 3 Then
           outFileName = args.Item(2)
           Set outFile = fso.CreateTextFile(outFileName, True)
        Else
            outFileName = ""
            Set outFile = WScript.StdOut
        End If
        Set fso = Nothing
    End If


    Set cn = CreateObject("ADODB.Connection")

    cn.ConnectionTimeout = 25
    cn.Provider = "SQLOLEDB"
    cn.Properties("Data Source").Value = "(local)\msfw"
    cn.Properties("Initial Catalog").Value = DbName
    cn.Properties("Integrated Security").Value = "SSPI"

    cn.Open

    Call PrintTable(cn, tableName, outFile)

    Set outFile = Nothing
    WScript.Echo "Done!"
End Sub

Sub PrintTable(cn, tableName, outFile)
    Dim recSet           ' An ADODB Recordset
    Dim field            ' An ADODB Field
    Dim queryText        ' A String
    Dim rowString        ' A String
    Dim rowValue         ' A String   
    Dim boolValue        ' A Boolean
    Dim firstTime        ' A Boolean
    Dim logCollection    ' A Scripting Dictionary object
    Dim logItem          ' An item in a Scripting Dictionary object
    Dim columnsArray     ' An array
    Dim columnString     ' A String
    Dim i, count         ' Integers
    Dim ipDouble         ' A Double
    Dim octet            ' An Integer
    Dim dot              ' A String
    
    columnString = ""
    firstTime = True

    Set logCollection = CreateObject("Scripting.Dictionary")
  
    Select Case Trim(UCase(tableName))
        Case "WEBPROXYLOG"
            BuildWebProxyLogCollection(logCollection)
        Case "FIREWALLLOG"
            BuildFirewallLogCollection(logCollection)
    End Select
    columnsArray = logCollection.Keys
    queryText = "SELECT * FROM " & tableName

    Set recSet = CreateObject("ADODB.Recordset")

    ' Open the recordset.
    recSet.Open queryText, cn

    ' Move to the first row.
    recSet.MoveFirst
   
    rowString = "#"
    For i = 0 To logCollection.Count - 1
        columnString = columnString & CStr(columnsArray(i)) & vbTab
    Next
  
    Do While Not recSet.EOF
        With recSet
            rowValue = ""
            rowString = ""

            ' Concatenate the column values for each record.
            For i = 0 to logCollection.Count - 1
                logItem = logCollection.Item(columnsArray(i))
                'WScript.Echo(logItem)

                ' Retrieve the appropriate name of MSDE column as a function
                ' of the real log column.
                Set field = recSet.Fields.Item(logItem) 
                If Not IsNull(field.Value) Then
                    Select Case field.Type
                        ' Catch ADO Boolean type values, convert to VB string.
                        Case adBoolean:
                            boolValue = field.Value
                            rowValue = CStr(boolValue)
                        Case adDBTimeStamp:            
                            rowValue = ConvertDateToText(field.Value) & vbTab & ConvertTimeToText(field.Value)
                        Case Else:
                            rowValue = field.Value
                            Select Case Trim(UCase(field.Name))
                                Case "RESULTCODE", "ERRORINFO", "CACHEINFO":
                                    rowValue = "0x" & Hex(rowValue)
                                Case "CLIENTIP", "DESTHOSTIP", "ORIGINALCLIENTIP", "SOURCEIP", "DESTINATIONIP":
                                    ipDouble =  CDbl(rowValue)
                                    rowValue = ""
                                    dot = "" 
                                    For count = 1 To 4
                                        ipDouble = (Fix(ipDouble)) / 256
                                        octet = 256 * (ipDouble - Fix(ipDouble))
                                        rowValue = CStr(octet) & dot & rowValue
                                        dot = "."
                                    Next
                                Case Else:
                            End Select
                    End Select        
                Else
                    ' Catch NULL values, convert to string "NULL" for display.
                    rowValue = "-"
                End If   
                rowString = rowString & rowValue & vbTab
            Next ' End of inner loop
            If firstTime Then
                outFile.writeLine("#Software: Microsoft (R) ISA Server 2004")
                outFile.writeLine("#Version: 2.0")
                outFile.writeLine("#Date: " & ConvertDateToText(recSet.Fields.Item("logTime")) & " 00:00:00")
                outFile.WriteLine("#Fields: " & columnString)
                WScript.Echo("")
                firstTime = False
            End If
            outFile.WriteLine(rowString)
            rowString = ""
        End With
        recSet.MoveNext
    Loop                    ' End of outer loop
End Sub
                                          
Sub BuildWebProxyLogCollection(wpLogCollection)
    
    wpLogCollection.Add "c-ip", "ClientIP"
    wpLogCollection.Add "cs-username", "ClientUserName"
    wpLogCollection.Add "c-agent","ClientAgent"
    wpLogCollection.Add "sc-authenticated", "ClientAuthenticate"
    wpLogCollection.Add "date" & vbTab & "time", "logTime"
    wpLogCollection.Add "s-svcname", "service"
    wpLogCollection.Add "s-computername", "servername"
    wpLogCollection.Add "cs-referred", "referredserver"
    wpLogCollection.Add "r-host", "DestHost"
    wpLogCollection.Add "r-ip", "DestHostIP"
    wpLogCollection.Add "r-port", "DestHostPort"
    wpLogCollection.Add "time-taken", "processingtime"
    wpLogCollection.Add "sc-bytes", "bytesrecvd"
    wpLogCollection.Add "cs-bytes", "bytessent"
    wpLogCollection.Add "cs-protocol", "protocol"
    wpLogCollection.Add "cs-transport", "transport"
    wpLogCollection.Add "s-operation", "operation"
    wpLogCollection.Add "cs-uri", "uri"
    wpLogCollection.Add "cs-mime-type", "mimetype"
    wpLogCollection.Add "s-object-source", "objectsource"
    wpLogCollection.Add "sc-status", "resultcode"
    wpLogCollection.Add "s-cache-info", "CacheInfo"
    wpLogCollection.Add "rule","rule"
    wpLogCollection.Add "FilterInfo","FilterInfo"
    wpLogCollection.Add "cs-Network", "SrcNetwork"
    wpLogCollection.Add "sc-Network", "DstNetwork"
    wpLogCollection.Add "error-info", "ErrorInfo"
    wpLogCollection.Add "action", "action"
End Sub

Sub BuildFirewallLogCollection(fwLogCollection)
    
    fwLogCollection.Add "computer", "servername"
    fwLogCollection.Add "date" & vbTab & "time", "logTime"
    fwLogCollection.Add "IP protocol","protocol"
    fwLogCollection.Add "source", "SourceIP"                
    fwLogCollection.Add "destination","DestinationIP"
    fwLogCollection.Add "original client IP", "OriginalClientIP"
    fwLogCollection.Add "source network","SourceNetwork"
    fwLogCollection.Add "destination network", "DestinationNetwork"
    fwLogCollection.Add "action","Action"
    fwLogCollection.Add "status", "resultcode"
    fwLogCollection.Add "rule","rule"
    fwLogCollection.Add "application protocol", "ApplicationProtocol"
    fwLogCollection.Add "bidirectional", "Bidirectional"
    fwLogCollection.Add "bytes sent","bytessent"
    fwLogCollection.Add "bytes sent intermediate", "bytessentDelta"
    fwLogCollection.Add "bytes received", "bytesrecvd"        
    fwLogCollection.Add "bytes received intermediate","bytesrecvdDelta"
    fwLogCollection.Add "connection time", "connectiontime"
    fwLogCollection.Add "connection time intermediate", "connectiontimeDelta"
    fwLogCollection.Add "source proxy", "SourceProxy"
    fwLogCollection.Add "destination proxy", "DestinationProxy"
    fwLogCollection.Add "source name", "SourceName"
    fwLogCollection.Add "destination name", "DestinationName"
    fwLogCollection.Add "username", "ClientUserName"
    fwLogCollection.Add "agent","ClientAgent"
    fwLogCollection.Add "session ID","sessionid"
    fwLogCollection.Add "connection ID","connectionid"
    fwLogCollection.Add "interface", "Interface"
    fwLogCollection.Add "IP Header", "IPHeader"
    fwLogCollection.Add "protocol payload", "Payload"
End Sub

Private Function ConvertDateToText(msdeTime)
    Dim monthStr
    Dim yearStr
    Dim dayStr
    Dim dateVal

    dateVal = CDate(msdeTime)
    
    monthStr = AddZero(Month(dateVal))    
    dayStr = AddZero(Day(dateVal))
    yearStr = cStr(Year(dateVal))
    
    ConvertDateToText = yearStr & "-" & monthStr  & "-" & dayStr 
End Function

Private Function AddZero(theObj)
    If(Len(CStr(theObj)) < 2) Then
        AddZero = "0" & CStr(theObj)
    Else
        AddZero = CStr(theObj)
    End If
End Function

Private Function convertTimeToText(msdeTime)
    Dim hourStr
    Dim minStr
    Dim secStr
    
    Dim dateVal
    
    dateVal = CDate(msdeTime)
    hourStr = AddZero(Hour(dateVal))
    minStr = AddZero(Minute(dateVal))
    secStr = AddZero(Second(dateVal))
    convertTimeToText = hourStr & ":" & minStr & ":" & secStr
End Function

Private Sub PrintCollection(coll)
    For Each item In coll
        Wscript.Echo item
    Next
End Sub

Sub Usage()
    WScript.Echo "Usage:" & VbCrLf _
        & "  CScript " & WScript.ScriptName & " DataBase Table [OutputFile]" & VbCrLf _
        & "" & VbCrLf _
        & "  DataBase    - The file name of the MSDE database without the mdf extension" & VbCrLf _
        & "  Table       - The type of log. Possible values:" & VbCrLf _
        & "                    WebProxyLog" & VbCrLf _
        & "                    FirewallLog" & VbCrLf _
        & "  OutputFile  - The name of the output file" 

    WScript.Quit(0)
End Sub