Analyzing Forefront TMG log files with WebSpy
To anaylze and report on your Forefront TMG log files with WebSpy you need to:
- Setup TMG Logging
- Import your log files into a Vantage storage
- Download and Open the Forefront TMG Report template and Aliases
- Report on your storage
- Analyze your storage
Setup TMG Logging
TMG logs to an internal SQL Express database by default. There are two ‘easy’ options for getting your data into WebSpy Vantage.
- Change logging to W3C text log (recommended) and import the text logs into WebSpy Vantage.
- Install Vantage on the TMG server and import from the default database. When choosing this option, just make sure you run reports at off peak times as Vantage can be memory intensive and you don’t want the reports impacting on the performance of the server. If you go with this option then this is what you need to enter in the import wizard:
- Input Type: Database connection
- Loader Selection: Microsoft FTMG
- Input Selection: Click Add and enter the server name .\MSFW. Enter a database filter of *WEB* to only import the web proxy databases.
The other option is leaving the TMG server logging to the default SQL Express database, and install Vantage on a separate machine. But if you do this, you need to open up the database so that it can be accessed over the network. This article explains how to do this: http://www.webspy.com.au/blogs/index.php/accessing-microsoft-forefront-tmgs-log-files-sql-express/
Once logfiles have been generated by Forefront TMG you can import them into a storage in Vantage to begin analyzing and reporting.
Importing into a Storage
Before you can start analyzing and reporting on your Forefront TMG logs, you need to import your log file data into a storage. Storages are optimized for quick data access so you can analyze and report on the data you are interested in faster.
The Input Dialog wizard is used to import log files. This wizard can also be launched by clicking Import logs on the Inputs pane.
- On the 'Storages' page, enter a name for a new storage or select an existing storage to import to.
- On the 'Input Type' page select Local or networked files and folders
- Select the Microsoft FTMG format on the 'Loader Selection' page.
- On the 'Input Selection' page, click Add | Add Folder. To import only your Web Proxy logs, enter a file mask of *WEB*. Check Add Sub Folders and click OK.
- The Forefront TMG access log files will then be displayed
- Click OK to begin importing your data.
Please ensure you are using Vantage build 184.108.40.206 or higher. If you cannot see Microsoft FTMG format on the Loader Selection page, please go to Tools | Check for Updates on the main menu and install the latest updates.
As Vantage imports your Forefront TMG logs, you can view the progress of the import on the Storages dock. The Storages dock displays the size of the log file (illustrated as size imported / total size), the number of records imported, and the percentage complete (shown in the progress column).
Download and Open the Forefront TMG Report template and Aliases
WebSpy has created a report template and a set of aliases for Forefront TMG. The aliases convert raw log data such as numbers and codes into more meaningful names for your reports. The report template extracts information for each of your top users and each of your top websites, along with overview information regarding categories, threats, file types and other traffic activity.
Download the template and aliases here:
‘FTMG Template and Aliases.zip’
This zip file contains two files:
To open the aliases file, go to the Aliases tab, click ‘Open aliases’ and navigate to the “MS FTMG Aliases.” file. Select Merge when prompted. The following aliases have now been added to your Aliases list: Action (FTMG), Cache Descriptions (FTMG), Cache Values (FTMG), Categories (MRS), Error Info (FTMG), Productivity (FTMG), and Status Code Names (FTMG).
- MS FTMG Summary Report.Template
To open the report template, go to the Reports tab in Vantage, select ‘Open Templates’, and navigate to the “MS FTMG Summary Report.Template” file. Select Merge when prompted. You will now see a report template called ‘Forefront TMG Summary Report (Web Proxy)’. You can now select this template and click the ‘Generate report’ link to run the report on your Storage (see ‘Reporting on your Storage’ below)
Reporting on your Storage
Ensure you have downloaded and opened the FTMG Templates and Aliases as above.
To generate a report on your Forefront TMG log data:
- Click the 'Reports' tab at the top of the screen. This takes you to the Reports dock.
- Select the Forefront TMG Summary Report (Web Proxy) template.
- Click Generate report. This launches the Generate Report dialog.
- On the 'Storages' page, check the storage that contains your Forefront TMG log data. Click Next.
- On the 'Format' page, select the desired format for the report. Click Next.
- 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.
- Leave the Filters, File Selection and Partition pages as default and then click OK to generate the report.
Your report will now be generated.
Analyzing your Storage
Each field in your Microsoft Forefront TMG 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 TMG 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:
- Click the Summaries tab at the top of the screen.
- This takes you to the Summaries dock.
- Click the New Analysis link in the 'Summaries' task pad to launch the Create Analysis dialog.
- Select your Forefront TMG storage from the Storage list.
- Select Forefront Threat Management Gateway 2009 Web from the Schema list and then click Next.
- 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.