How To – Microsoft ISA Server Reporting

Accessing ISA Server’s Log Files

Microsoft ISA Server Reporting – The first step in reporting on your ISA Server server is to access the ISA Server log files. ISA Server has three different logging options. WebSpy Vantage can import all of these formats, but some work may be required to access them from your WebSpy Vantage machine:

  • SQL Server Express Database (on local server)  (Default) By default, ISA Server creates log files in it’s own local SQL Express instance. The instance name is MSFW. New databases are created each day, and there is a log table for Firewall and another for Web Proxy data.You can import this log database using the ‘Database Connection’ option in WebSpy Vantage, however you need to first enable network access and permissions to the databases. To do this, please see our detailed article: Accessing Microsoft Forefront TMG Log Files (SQL Express). Although not recommended, you can avoid opening the SQL Express logs to network access by installing Vantage on your ISA Server, and running reports in off-peak times. See our article on how to do this here.
  • SQL Database Logging to a remote SQL Server enables you to centralize all your ISA Server log files, and has some other great advantages for enterprises. You can import these logs using the ‘Database Connection’ option in WebSpy Vantage and you can select whether to connect with Windows Authentication or SQL Authentication. If using Windows Authentication, make sure the User Account running WebSpy Vantage has db_reader permission on the SQL databases and tables.
  • File (W3C or Native .iis formats)Logging to File (Text log) is by far the easiest method of accessing your log files with WebSpy Vantage. We recommend you use the W3C format due to the standards compliant log structure, however the native .iis format is supported as well. Simply share the folder that your log files are stored in, and use the ‘Local Networked Files or Folders’ option when importing the logs in WebSpy Vantage.

To find the log format ISA Server is currently using:

  1. Open the ISA Server Management Console.
  2. Select Logs and Reports in the left hand side.
  3. Click Configure Web Proxy Logging in the left hand side.
  4. The logging options above are selected in this dialog.
  5. If you’re logging to Text or Remote SQL, click the Advanced button to see where those log are being created.  The default ‘ISA Logs Folder’ is C:\Program Files\Microsoft ISA Server\Logs

A Quick Word about Firewall Logs: You will also notice an option to Configure Firewall Logging in step 3 above. Logging is configured in exactly the same way as the Web Proxy logs and the Firewall logs are fully supported in WebSpy Vantage. However, if you are mainly interested in analyzing web browsing behaviour, simply import and analyze the Web Proxy log files into WebSpy Vantage.

Importing ISA Server logs into WebSpy Vantage

Once you have access to your ISA Server log files, you can import them into a Storage in WebSpy Vantage. Storages are WebSpy Vantage’s internal database format, optimized for fast data access.

In WebSpy Vantage, go to the Storages tab and click Import logs. The options to select vary slightly depending on your log file type:

For SQL Express Databases

  • Storage Name: Enter anything you like such as ‘ISA Web Proxy Logs’
  • Input Type Page: Select Database connection
  • Loader Selection: Select Microsoft ISA Server
  • Input Selection: Click Add and select/enter the following:
    • MS SQL
    • Server: Enter the ISA Server’s server name  followed by \MSFW. For example 10.0.0.10\MSFW. If Vantage is installed on your ISA Server, you can enter .\MSFW (‘.’ means localhost)
    • Port: 1433
    • Database Filter: Enter a database filter of *WEB* to only import the web proxy databases, or leave it set to * to import everything including the Firewall databases.
    • Table Filter: Leave this set to * to import all tables in the Databases.

For more information see Accessing Forefront TMG Log Files (SQL Express).

For SQL Server Databases

  • Storage Name: Enter anything you like such as ‘ISA Web Proxy Logs’
  • Input Type Page: Select Database connection
  • Loader Selection: Select Microsoft ISA Server
  • Input Selection: Click Add and select/enter the following:
    • MS SQL
    • Server: Enter the name or IP address of your SQL Server. For example 10.0.0.10.
    • Port: 1433
    • Database Filter: Enter the database name, or a suitable database search string (such as *LogDB*) to select the databases that contains the ISA log tables.
    • Table Filter: Enter the table name or a suitable search string (such as *WebProxy*), where your ISA logs are being written to. Leave it as * to import all tables in the database.

Click OK and you should see a list of the databases and tables appear in the Import Wizard. Click OK again on the Import Wizard to start importing.

For Files (W3C or Native .IIS logs)

  • Storage Name: Enter anything you like such as ‘ISA Web Proxy Logs’
  • Input Type Page: Select Local Networked Files or Folders
  • Loader Selection: Select Microsoft ISA Server
  • Input Selection: Click Add | Folder and select/enter the following:
    • Folder: Browse to the folder containing your ISA Server log files. Make sure you specify a UNC path such as \\servername\logs rather than a mapped network drive (Vantage cannot import from mapped drives when logged off)
    • File Mask: Leave this set to * to import all logs, or enter a suitable search string such as *WEB* to only import web proxy logs, or *FWS* to only import Firewall logs.
    • Timezone Offset: ISA Server logs in GMT time. Make sure you specify a timezone offset so that your reports show activity in your local timezone rather than in GMT.
    • Leave all other options as default

Click OK and you should see a list of ISA log files appear in the Import Wizard. Click OK again on the Import Wizard to start importing.

Microsoft ISA Server Reporting with WebSpy Vantage

Now that you have imported your ISA Server log data, you can Run an Analysis on the Summaries tab and drilldown into users, sites, applications and so on.

You can also go to the Reports tab and generate any of the standard Web and Firewall reports, as well as the ISA Server specific Reports.

Running Reports

To generate a report on your ISA Server log data:

  1. Click the Reports tab at the top of the screen. This takes you to the Reports screen.
  2. Select the desired Report Template, such as the Microsoft ISA – Web Searches template.
  3. Click Generate report. This launches the Generate Report dialog.
  4. On the Storages page, check the storage that contains your ISA Server log data. Click Next.
  5. On the Format page, select the desired format for the report. Click Next.
  6. On the Publish page, enter a name for the report, and select Display the report using the default viewer if you would like the report to open after it has been generated.
  7. Leave the DocumentsFilters, and Email  pages as default and then click OK to generate the report.

Running an Analysis

Each field in your Microsoft ISA Server log files can be reported on using WebSpy Vantage. Vantage produces ‘Summaries’ for each field in your logs. Sometimes, Vantage produces more than one summary per field. For example, Vantage produces several summaries from the URL field in ISA log files, such as Site Domains (e.g. google.com), Site Names (e.g. images.google.com) and Site Keywords (e.g. My search term).

To get an idea of the range of Summaries that you can use in your reports and filters, run an Ad-hoc analysis on a small amount of data (such as one day). To do this:

  1. Click the Summaries tab at the top of the screen. This takes you to the Summaries screen.
  2. Click the New Analysis link in the ‘Summaries’ task pad to launch the Create Analysis dialog.
  3. Select your ISA Server storage from the Storage list.
  4. Select ISA Server Web from the Schema list and then click Next.
  5. On the Analysis Type page, select the Ad-hoc Analysis radio button and ensure ‘Use precalculated analysis if available’ checkbox is checked.

Once the analysis is complete, all the available summaries are displayed on the left hand side.

Click a Summary, such as Site Domains to see the list of websites that have been accessed, or Usernames to see the list of users accessing the web.

You can analyse a specific user or site by right-clicking them and selecting Drilldown from the popup menu along with another Summary. For example, right-click your top user and select Drilldown | Site Domains to view the sites they have accessed.

Note: When Analyzing data on the Summaries screen, it is best to use a small amount of data. It is good practise to create a separate test storage containing only one day’s worth of data for Ad-hoc analysis on this screen, then use this information to build your desired report template(s) and run these reports on your full data set.

Learning More

Now that you have imported your Isa server log files into WebSpy Vantage, generated a report, and run an analysis, you may like to automate your importing and reporting process using the Tasks tab. You may also like to import your organization structure from Active Directory and publish reports to users and managers via the Vantage Web Module. For more information on getting started, take a look at our getting started videos.