Sorting Results Correctly

Before I now turn to other types of logfiles, another weak point of the graphical user interface still needs to be mentioned. If you subsequently try to sort the results of a query in the table view, you will notice that this does not work properly. To sort by this value, click on the header of the Logons column, as in the output of the previous example.

The result unfortunately does not conform with expectations, because the display does not sort the numbers by amount, but by digit. The result is a string like "1, 10, 12, 15, 2, 21, 3, …" and not the ascending order of the values. Exporting the data into another program and anchoring the desired sorting directly in the query by adjusting the appropriate SQL clause are possible alternatives. For example, change the last line of the example to:

ORDER BY [Logons] DESC

Now the second column is sorted in descending order in Log Parser Studio, and the day with the most failed logons appears at the top.

Evaluating Text Files

That exercise was great for getting started, but up to this point, I have only used the event log as a source of information. However, Log Parser Studio's true strengths lie in accessing text-based logs – in particular if the systems to be evaluated distribute their information across numerous files. Internet Information Services (IIS) and Exchange are prominent representatives in this category.

Up to Windows Server 2003 R2, IIS saved its logs by default in the C:\WINDOWS\system32\LogFiles\W3SVC1 directory; newer systems store their logs under C:\Inetpub\Wwwroot logs\LogFiles\W3SVC1. Call up the Log File Manager in Log Parser Studio by clicking on the fifth icon from the left. The manager opens in a separate dialog. Now, you can select either individual logfiles or whole folders using the Add Files or Add Folder buttons. Navigate to the desired location in the following Explorer window. Even if you have opted for the Add Folder option, you need to select at least one file here.

The Log File Manager automatically replaces the file with a wild card and processes all files in the selected folder. You have more control using the Add Files option. If you add files to the analysis in this way, they are individually listed in the Log File Manager and can be activated or deactivated individually for queries using the checkboxes (Figure  3). This method is useful for switching between sets of logs quickly if you want to work in various queries on files with different paths or file types.

Figure 3: The Log File Manager manages the files to be evaluated.

Querying IIS Logs

Queries relevant to the web server start with "IIS" and others start with "HTTPERR." If you run the IIS: User-agent report query against a set of IIS logs, you will receive a list of the accessing client browsers grouped by the user-agent string that the clients submit. The user-agent string is a combination of operating system and browser and is not necessarily easy to understand at first glance.

A user-defined query makes the information easier to read. Select File | New | Query in the menu and change the log type from NOT set to IISW3 CLOG via the drop-down menu above the query editor (Figure 4).

Figure 4: Studio supports a variety of log types.

In the editor box, add the query to evaluate by browser name (Listing  1) and execute the query. The result is a list of accessing browsers in descending order. Of course, you can vary the CASE statements as required to output more browser types separately.

01 SELECT
02 CASE strcnt(cs(user-agent),'Firefox/28') WHEN 1 THEN 'Firefox 28' ELSE
03 CASE strcnt(cs(user-agent),'Firefox/27') WHEN 1 THEN 'Firefox 27' ELSE
04 CASE strcnt(cs(user-agent),'Firefox') WHEN 1 THEN 'Firefox <= 26' ELSE
05 CASE strcnt(cs(user-agent),'MSIE+10') WHEN 1 THEN 'IE 10' ELSE
06 CASE strcnt(cs(user-agent),'MSIE+9') WHEN 1 THEN 'IE 9' ELSE
07 CASE strcnt(cs(user-agent),'MSIE+8') WHEN 1 THEN 'IE 8' ELSE
08 CASE strcnt(cs(user-agent),'MSIE+7') WHEN 1 THEN 'IE 7' ELSE
09 CASE strcnt(cs(user-agent),'Safari') WHEN 1 THEN 'Safari' ELSE
10 CASE strcnt(cs(user-agent),'Opera') WHEN 1 THEN 'Opera' ELSE 'Other'
11 END END END END END END END END END as Browser, count(cs(User-Agent)) as Hits
12 FROM '[LOGFILEPATH]'
13 GROUP BY Browser
14 ORDER BY Hits DESC

Blogs and forums on IIS and Exchange offer numerous suggestions for further queries [4]. These are often written as the Log Parser command-line tool. However, it is usually sufficient to replace the path to the logfiles in the FROM clause with the variable '[LOGFILEPATH]' so that the query works in Log Parser. This way, the following query resolves the accessing IP addresses to their hostnames and displays the results in Studio as a table:

SELECT c-ip As Machine,
REVERSEDNS(c-ip) As Name,
COUNT(*) As Hits
FROM '[LOGFILEPATH]'
GROUP BY Machine ORDER BY Hits DESC

Because of the reverse DNS lookup for each IP address, this query can run for a very long time depending on the volume of log data. As an alternative to outputting the results directly in the GUI, you can redirect the output to a file. In the previous example, add an INTO clause before the FROM statement:

[...]
INTO '[OUTFILEPATH]\Hostnames.CSV'
FROM '[LOGFILEPATH]'
[...]

Log Parser Studio writes the results in the Hostnames_[date]_[time].CSV file. You can change the destination directory in the Default Output Path field in the Options | Preferences menu. You can also uncheck the Auto-open output file box there. Otherwise, Log Parser attempts to open the file and complains if neither Excel nor another program with the file extension CSV is linked to the local system.