ISA Firewall Quick Tip: Creating Detailed Reports Using ISA Server 2006 and Excel

Configuration on ISA Server

1. Open ISA Management Console
   
2. Under the Monitoring node, click on Logging
   
   
3. We will create a report with the following filters ( you can customize your reports as needed)
   a. Logging Last 7 Days
   b. Action Allowed Connection
   c. Protocol HTTP
   d. Client Username Not Equal to Anonymous ( Here you can choose a specific user for example )
   e. Specify on which Firewall Policy we want to run the logging for.
4. Click on Edit Filter, to start editing and adding our filters.
5. Click on Log Time, then under the Condition drop down list, choose Last 7 Days, and click on Update
   
6. Click on Action, then under the Condition drop down list, choose Equals, and under the Value drop down list choose  Allow Connection and click on Update
   
7. Under the Filter By choose Protocol, under the Condition choose Equals, and under the Value choose HTTP then click on Add To List
   
8. Under the Filter By choose Client Username, under the Condition choose Not Equals, and under the Value write Anonymous then click on Add To List ( we want to monitor all authenticated users by username )
   
9. Under the Filter By choose Rule, under the Condition choose Equals, and under the Value choose the Firewall Policy you want to run the report for, in this article we have a rule called Allow Internet For All then click on Add To List
   
   
10. The Filter will look like :
    
11. Click on the Start Query
    
12. The Results will be fetched and displayed . Note that a pop up windows will state that a maximum of 10,000 records can be displayed only. Click OK
    
    If you clicked on Help, you will know the reason why a maximum of 10,000 resulted can be displayed.
    
13. Now, to copy the results to Excel, on the Right Side Pane, under Tasks, click on Copy All Result to Clipboard
    
14. Now open Microsoft Excel , and paste the result. You can remove any column you find it not to be necessary and keep only the columns you want.

Summary
In this article, we learned how to create a report without the need for any third party application or web filter. All what we used is ISA
Server 2006 with MSDE logging and Excel.