Extracting Per-User Details from ISA Logs
Log data can be written to a text file, however, even in small and medium-sized business environments the text file can quickly grow to a cumbersome size. The better logging options are to use a SQL Server database, or SQL Server’s scaled down cousin- MSDE. There are some security and performance advantages to using an external SQL Server for maintaining log data, but SQL Server requires separate licensing. MSDE provides many of the same features as SQL Server, but in a less robust database that runs locally on the ISA Server.
Regardless of whether you store log data in MSDE or SQL Server, you can use SQL queries to extract information. SQL queries enable you to filter log data on a very granular level and export data to Excel spreadsheets where it can be easier to work with and manipulate it.
For example, assume that while reviewing a standard daily ISA Server 2006 Security report you notice an inordinate number of failed authorization attempts by one specific user account, MaryN. The user in question is out on maternity leave and the Failed Access events appear to indicate an attempt to compromise the user account and breach the network. What you would want to do is to review data which is specific to this user account over the past week and try to determine the scope of the problem and at what point the issue began.
When filtering the log data, you need to specify the criteria that you want to filter, the conditional argument to apply, and the value. In our example you might filter on the following:
Filter by: ‘Client Username’
Condition: ‘equal to’