Querying Logs

  • Article

With Microsoft ISA Server 2004, you can programmatically query the online log or the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) log using the built-in log query facility. These logs can be queried for information contained in the Microsoft Firewall service and Web proxy logs using a filter that extracts only the log entries that satisfy the filter expressions defined. Then selected log fields from the log entries retrieved can be displayed or exported to a file.

A filter is represented by an FPCFilterExpressions collection. This collection is not part of the object hierarchy based on the ISA Server administration root object and must be created separately. Each FPCFilterExpression object in this collection represents a single filter expression. New filter expressions are created by calling the AddDateFilter, AddEnumFilter, AddIPAddressFilter, AddNumericFilter, and AddStringFilter methods of the collection. The FPCFilterExpressions collection is passed as a parameter in a call to the ExecuteQuery method on an FPCLogContent collection, into which the log entries that satisfy the filter conditions are copied.

The Microsoft Visual Basic Scripting Edition (VBScript) code in LogQuery.vbs (listed later in this Web page) provides an example of how to create a filter and use it in a query. The script extracts information that has already been logged from the MSDE log using a filtering query. Note that in this example, one of the filter expressions defined limits the information extracted to information from the Firewall service log. The output generated by the script consists of selected log fields from the log entries retrieved, which are displayed on the screen or exported to a file. The example filter expressions defined in this script can be replaced by customized filter expressions to create a script that queries a log for entries with values for specified log fields that fall within specified ranges. This script must be run on an ISA Server computer with the Firewall service installed, but it can be modified to run on a remote management computer.

Usage:CScript LogQuery.vbs [OutputFile]

OutputFile specifies the name of the file to which the query results are to be exported.

The procedure title

  1. If the user wants to export the query output to a text file, create a FileSystem object, and then call the CreateTextFile method on the object created to obtain a TextStream object. Otherwise, write the query output to the output stream retrieved through the StdOut property of the WScript object for the script.

  2. Create an instance of the FPC COM object, which is known as the root object and provides access to the other ISA Server administration COM objects.

  3. Obtain references to the FPCArray object representing the ISA Server array and to the FPCLogContent collection that will contain the log entries retrieved from the MSDE log in the query.

  4. Create an FPCFilterExpressions collection and configure it as a log viewer filter. Note that this collection is not part of the object hierarchy based on the ISA Server administration root object and must be created separately.

  5. Call the AddDateFilter, AddEnumFilter, AddIPAddressFilter, AddNumericFilter, and AddStringFilter methods of the FPCFilterExpressions collection to define one filter expression of each possible type. Note that the filter expression created by the AddEnumFilter method limits the information extracted to log entries from the Firewall service log.

  6. Call the ExecuteQuery method to run the query.

  7. In a loop, call the WriteLine method to export selected log fields from the log entries retrieved to the specified output file, or to display them on the screen.

Script Listing: LogQuery.vbs

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' 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 demonstrates the log query feature.

' We recommend running this script from a command prompt by entering the

' following command:

' CScript LogQuery.vbs [OutputFile]

' When the optional argument is specified, the output is exported to a text

' file.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub LogQuery()

' Define constants and enumeration values.

Const records = 5000

Const Error_FileNotFound = &H80070002

Const fpcLogViewerFilter = 2

Const date = #12/31/2004 11:59:59 PM#

Const lowIp = "157.0.0.1"

Const excludedCode = 401

Const protocol = "DNS"

Const fpcFilterByClientIP = 1

Const fpcFilterByProtocol = 10

Const fpcFilterByLogTime = 18

Const fpcFilterByLogType = 23

Const fpcFilterByResultCode = 37

Const fpcEqual = &H000000001

Const fpcNotEqual = &H000000002

Const fpcGreaterOrEqual = &H000000004

Const fpcProxyFwLog = &H000000002

Const fpcLastHour = &H00000100

'Declare the other objects needed.

Dim args ' A WScript Arguments object

Dim outFileName ' A String

Dim outFile ' A TextStream object

Dim fso ' A FileSystem object

Dim root ' The FPCLib.FPC root object

Dim isaArray ' An ISA Server array object

Dim filter ' An FPCFilterExpressions collection

Dim log ' An FPCLogContent collection

Dim entry ' An FPCLogEntry object

' Find out if the user wants the output on the screen

' or in a text file.

Set args = WScript.Arguments

If args.Count = 1 Then

Set fso = CreateObject("Scripting.FileSystemObject")

outFileName = args.Item(0)

Set outFile = fso.CreateTextFile(outFileName, True)

Set fso = Nothing

Else

Set outFile = WScript.StdOut

End If

' Create the root object.

Set root = CreateObject("FPC.Root")

' Get references to the array object and

' the MSDE log content collection.

Set isaArray = root.GetContainingArray

Set log = isaArray.LogViewer.LogContentMSDE

WScript.Echo "The MSDE log content collection has been retrieved."

' Create an FPCFilterExpressions collection.

Set filter = CreateObject("FPC.FPCFilterExpressions")

filter.FilterType = fpcLogViewerFilter

WScript.Echo "The filter object has been created."

On Error Resume Next

filter.AddDateFilter fpcFilterByLogTime, fpcLastHour, date

CheckError

filter.AddEnumFilter fpcFilterByLogType, fpcEqual, fpcProxyFwLog

CheckError

filter.AddIPAddressFilter fpcFilterByClientIP, fpcGreaterOrEqual, lowIp

CheckError

filter.AddNumericFilter fpcFilterByResultCode, fpcNotEqual, excludedCode

CheckError

filter.AddStringFilter fpcFilterByProtocol, fpcEqual, protocol

CheckError

log.ExecuteQuery filter, records

WScript.Echo "Executing the log query..."

Dim Index

Index = 1

outFile.WriteLine("Date and Time" & vbTab & "Client IP" & vbTab & _

"Protocol" & vbTab & "Destination Port" & vbTab & "Result Code")

On Error Resume Next

Do

Set entry = log.Item(Index)

' A File_Not_Found (0x80070002) error is raised when the index points

' beyond the end of the log content collection.

If Err.Number = Error_FileNotFound Then

WScript.Echo "All existing entries have been retrieved."

Exit Do

End If

outFile.WriteLine(entry.LogTime & vbTab & entry.ClientIP & vbTab & _

entry.Protocol & vbTab & vbTab & entry.DestHostPort & vbTab & _

"0x" & Hex(entry.ResultCode))

Index = Index + 1

Loop Until Err.Number <> No_Error

Err.Clear

End Sub

Sub CheckError()

If Err.Number <> 0 Then

WScript.Echo "An error occurred: 0x" & Hex(Err.Number) & " " & _

Err.Description

Err.Clear

End If

End Sub

LogQuery