« Previous 1 2 3 Next »
Evaluating logfiles with Microsoft Log Parser Studio
Clear View
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.
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).
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.
Listing 1
Access by Browser
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.
« Previous 1 2 3 Next »
Buy this article as PDF
(incl. VAT)