Reporting from Web Server Logs

It s now a given that most organizations need to document what pages web users are pointing their browsers to, as well as when and from where. Most web servers, such as those from Microsoft, Netscape, and others, keep various amounts and levels of data about web site visits . What better tool to create web activity reports than Crystal Reports ?

There are several types of web server logs that you may encounter, depending mostly on the web server that you are using. Most web server logs are standard delimited ASCII files (a term you don t necessarily need to be familiar with, but it is a common denominator for data-file formats). However, the layout of fields in the files and the information they contain can vary from server to server. There are several standard types of log files, including a file type simply known as Standard, and another known as the NCSA Standard, defined by the National Center for Supercomputing Applications for its original web server. Other web server developers have adopted both standards. In addition, if you are using Microsoft Internet Information Server (IIS), you ll find additional types of log files available, such as Microsoft s extended log format. When you configure IIS, you can choose the type of log file that is generated, as well as the fields that are kept in the log file.

As a general rule, web servers will create a new log file every day, adding statistics to the daily log file as they occur. The filenames consist of variations of the date on which they were created, with a standard file extension. For example, you may see a file named EX040302.LOG, indicating a Microsoft extended-format log for March 2, 2004. These files will be located in a standard directory, such as \WinNT\System32\LogFiles\W3Csvc1 (this is the default location for Microsoft IIS versions running on Windows NT/2000).

To report on web server logs, start a new report just as you would for a standard database. You can use the report wizards or the Blank Report option. In either case, expand the Create New Connection category of the Database Expert to proceed. You ll see two categories that may interest you: MS IIS/Proxy Log Files and Web/IIS Log Files. MS IIS/Proxy Log Files will report on standard formats, the various Microsoft IIS formats, and Web Proxy and Winsock Proxy formats. The Web/IIS Log Files folder will simply report on standard and NCSA formatted logs.

Regardless of whether you re using a wizard or the Blank Report option, the rest of the prompts will be consistent. If you choose the more generic Web Log option, you ll simply be prompted to choose a drive and directory where the standard or NCSA Web logs are located. You can type or browse to select a local or network drive and directory. If you choose the IIS option, you ll see the Select Log Files And Dates dialog box, as illustrated in Figure 20-6.

Figure 20-6: IIS Select Log Files and Dates dialog box

Choose the type of log file your web server uses (look for files in a standard location with the .LOG extension to help determine this). You can also choose the format your web server uses when writing log files, based on how often it creates a new log file. And, if you prefer to limit Crystal Reports to using only log files within a certain date range, make that selection as well.

When you re finished with your choices, click OK. A new database entry will appear in the Available Data Sources list of the Database Expert showing the directory name of the log files. Underneath this category, a table-type entry will appear, showing a somewhat cryptic entry for the log files you just chose. Add this table to your report (by double-clicking on the table name, dragging it to the Selected Tables list, or selecting the table name and clicking the right arrow) and close the Database Expert. The list of web server log fields you can report from will appear in the wizard or in the Field Explorer.

Note  

The log fields you see here will vary, depending on the web server you are using and the log settings you ve chosen for that web server. If you re using Microsoft IIS with Windows 2000 or XP, you can configure the server to include a variety of helpful statistical fields.

Use these web server log fields just like other database fields when creating your report. You can sort , group , and format the report as you normally would. You can use the fields to create flexible selection criteria to narrow down your report. You can also create formulas, if necessary, to calculate or modify the way web server log material appears in the report. Figure 20-7 shows a sample report based on a Windows 2000/ IIS 5 web server log.

Figure 20-7: XML sample file