The simplest kind of report we can build will just list data from our log tables. However, as you saw in the previous chapter, the base tables that collect the data from our site are not suitable for querying efficiently. Instead, we use the special 'summary' tables that we update every week from the main log files. The tables we have available are shown in the next diagram:
The fact that we have several different tables, combined with the requirement to sort and summarize the information in various ways, means that there are a lot of different combinations. We could provide a different page for each report that was required, but this means a lot of work. There's also the possibility that, even then, we won’t cover all the requirements. Instead, as you've seen earlier, we chose to build a reusable report that will work with any of the tables. By providing suitable parameters when we load it, we can display selected data from the chosen table.
The file showlog.asp accepts several parameters in the query string, which change the way it behaves. The parameters are:criteria - an extra WHERE clause to select and limit the values returned
As you saw in the previous section, the traffic reports menu page provides the first two of these values in the link that references our showlog.asp page. The third and fourth values are provided by a separate page. We'll look at the main showlog.asp page first, and then see where the other two values come from afterwards.The sample files for this book also include a line of code to implement the admin security technique we saw in Chapter 5, redirecting the user to a login page if they haven't provided a suitable username and password. We've omitted these from the printed code here to avoid unnecessary complication.
The first part of the page sets the script timeout value, inserts the connection details for our IISLogs database, and defines a couple of values for use later in the page. It also collects the value from the ASP QueryString and Form collections for the first three of our parameters:<%@ LANGUAGE=VBSCRIPT %>
<% Server.ScriptTimeOut = 600 'the report may take some time to run %>
<!-- #include virtual="/common/iislog.inc" -->
<%
QUOT = Chr(34)
CRLF = Chr(13) & Chr(10)
strQuery = Request.QueryString("query") 'the name of the query we're going to run
strSite = Request.QueryString("site") 'the site(s) that we want to include
strCriteria = Request.Form("criteria") 'the criteria for the report
...We use thesiteparameter to specify a query name that indicates the IP address of the site we're interested in. All the tables except for WeekSummary (which presents information for all sites) include the IP address in each record, so we can pull out the appropriate ones for our report. Because there are two different field names for these fields in the tables (yes, we'll admit it, it was an oversight during the original design), we create two different WHERE clauses in each case. If the report will include all sites, however, we can leave the string empty:
...
Select Case strSite
Case "all"
strWhere = ""
strSessW = ""
Case "wd" 'Web-Developer site only
strWhere = " WHERE TSiteIP='194.73.51.228'"
strSessW = " WHERE HostIP='194.73.51.228'"
Case "cd" 'COMDeveloper site only
strWhere = " WHERE TSiteIP='194.73.51.229'"
strSessW = " WHERE HostIP='194.73.51.229'"
Case "wa" 'World Of ATL site only
strWhere = " WHERE TSiteIP='194.73.51.230'"
strSessW = " WHERE HostIP='194.73.51.230'"
End Select
If Len(strCriteria) > 1 Then 'add the optional criteria to the WHERE clause
If Len(strWhere) Then
strWhere = strWhere & " AND " & strCriteria
Else
strWhere = " WHERE " & strCriteria
End If
End If
...At the end of the previous section of code you can see how we add on to the end of the WHERE clause (or create a new one if the report covers all sites) the value of thecriteriaparameter sent to this page. So, where does this value come from? The answer is in the next part of the code.
Given the sitequeryparameter value, we can set up the required SQL statement that will extract the data from our table, and summarize and sort it in the required order. Then we just need to add on the WHERE clause we created in the previous section of code.
However, things are made more complex because, as well as listing the individual results of the query, we want to provide a summary of the totals in some of the reports as well. This means that we will need two SQL statements in these cases—strSQL is the statement that creates a subset of records for the main listing, and strSum is the statement that creates a single summary record from the same subset of records:...
Select Case strQuery 'the type of query we want
Case "weeks" 'the special weekly summary report
strSum = "" 'no overall summary required
strSQL = "SELECT * FROM WeekSummary ORDER BY TYearNumber DESC, TWeekNumber DESC"
Case "hits" 'traffic volume sorted by number of hits
If Len(strCriteria) < 1 Then 'we need to collect a criteria string
Response.Redirect "getcriteria.asp?query=" & strQuery & "&site=" & strSite
End If
strSum = "SELECT start=MIN(TSumDate), finish=MAX(TSumDate), " _
& "TotalHits=SUM(THitCount), TotalKBytes=SUM(TKBytes) " _
& "FROM DaySummary" & strWhere
strSQL = "SELECT HostIP=MAX(TSiteIP), TargetURL=MAX(TTarget), " _
& "Hits=SUM(THitCount), KBytes=SUM(TKBytes) FROM DaySummary" _
& strWhere & " GROUP BY TTarget ORDER BY SUM(THitCount) DESC"
Case "volume" 'traffic volume sorted by number of Kbytes
If Len(strCriteria) < 1 Then
Response.Redirect "getcriteria.asp?query=" & strQuery & "&site=" & strSite
End If
strSum = "SELECT start=MIN(TSumDate), finish=MAX(TSumDate), " _
& "TotalHits=SUM(THitCount), TotalKBytes=SUM(TKBytes) " _
& "FROM DaySummary" & strWhere
strSQL = "SELECT HostIP=MAX(TSiteIP), TargetURL=MAX(TTarget), " _
& "Hits=SUM(THitCount), KBytes=SUM(TKBytes) FROM DaySummary" _
& strWhere & " GROUP BY TTarget ORDER BY SUM(TKBytes) DESC"
...
... 'repeated for other query types
...
Case "lang" 'listing by browser language/country code
strSum = "" 'no overall summary required
strSQL = "SELECT Hits=SUM(ItemCount), Language=MAX(ItemText) " _
& "FROM CountrySummary" & strSessW & " GROUP BY ItemText " _
& "ORDER BY SUM(ItemCount) DESC"
End Select
%>
...In the code above, you can see the various SQL statements that select, summarize (GROUP) and sort (ORDER) the data into recordsets ready for listing. However, notice how, for thehitsandvolumequeries, we demand a value for thecriteriaparameter. These (and some other) listings are huge, because they include all the pages on our site. We try to persuade the user to limit the records that will be included in the final report by redirecting them to another page that will create a value for thecriteriaparameter.
We'll look at this criteria page, getcriteria.asp, in more detail later on. For the meantime, you just need to know that it does two things. Firstly, it creates a SQL WHERE clause string that further limits the records that will be included in the result recordset, but without the 'WHERE' keyword at the start of the string. It then adds this parameter onto the original query string that we send to the page. Secondly, it can add the parametershowsql=onto the query string as well. The getcriteria.asp page then loads our showlog.asp page again, sending back the query string with its new additions.
We're now ready to send something back to the user from our showlog.asp page. We output the HTML for the start of the page, then check to see if theshowsqlparameter was supplied. If it was, we print the SQL statements into the page. Then we can get on and open our database connection ready for some real work:
...
<html>
<head><title>Results of your traffic query</title></head>
<body BGCOLOR="#FFFFFF">
The results of your query:<p>
<% '--show the contents of the two SQL strings, useful when debugging--
If Request("showsql") = "on" Then
Response.Write strSum & "<P>"
Response.Write strSQL & "<HR>"
End If
On Error Resume Next
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect 'open the database connection
...Some of the reports, as we saw earlier, also display a summary of all the records included in the listing. In this case, the variable strSum will hold a suitable SQL statement to generate these. We execute the statement to create a recordset, which will have just one record in it. Using these values, we can calculate and display the summary information:...
If Len(strSum) Then 'this query provides a summary for the period
Set oRs = oConn.Execute(strSum) 'this is the first SQL query declared earlier
If (Not oRs.EOF) And (Err.Number = 0) Then
datStart = CDate(oRs("start")) 'date of the earliest record
datEnd = CDate(oRs("finish")) 'date of the latest record
intDays = DateDiff("d", datStart, datEnd) + 1 'period covered in days
lngTotalHits = oRs("TotalHits") 'total number of hits
lngTotalKBytes = oRs("TotalKBytes") 'total number of bytes
%>
For the period <B><% = datStart %></B> to <B><% = datEnd %></B>,
a total of <B><% = intDays %></B> days.<P>
Total number of hits: <B><% = lngTotalHits %></B><BR>
Total traffic volume: <B><% = lngTotalKBytes %></B> KBytes<BR>
Hits per day: <B><% = CLng(lngTotalHits / intDays) %></B><BR>
Traffic volume per day: <B><% = CLng(lngTotalKBytes /intDays) %></B> KBytes<P>
<%
End If
End If
...Here's how it looks in the browser. In this case, we chose to display the SQL statements as well, so that you can see what they look like. We also chose to include all sites, and we didn't specify any extra criteria for the query:
It would be possible to create the summary using ADO with the recordset that creates the main listing (from the SQL statement in strSQL), rather than by executing a separate SQL statement strSum as we have here. We could count and total the values as we listed the records, then calculate the daily summary as shown above. However, this would place the summary at the end of the listing rather than at the start (unless we used some browser-dependent trick to create page divisions). Otherwise, to get it at the start of the page would mean looping through the records twice. Either way, this is likely to be a lot less efficient than letting our database do the work.It's now time to execute the main query that creates the recordset for our results listing. The showlog.asp page contains an On Error Resume Next statement near the beginning, so if the previous SQL query failed, or returned no records, the user will just see zeros or nothing at all in the page. When we come to do the listing, it would be nice to tell them if something went wrong. So, after we execute the main query, we check to see if we got any records, and if there was an error. In both cases, we display some helpful text. We also add instructions on how to abort the listing if it seems to be taking too long, and include a link back to the reports menu:
...
Set oRs = oConn.Execute(strSQL)
If Err.Number > 0 Then
Response.Write "<FONT FACE=" & QUOT & "Arial" & QUOT & " SIZE=3>" _
& "<B>Sorry, the database cannot be accessed.</B></FONT></BODY></HTML>"
Response.End
End If
If oRs.EOF Then
Response.Write "<FONT FACE=" & QUOT & "Arial" & QUOT & " SIZE=3>" _
& "<B>Your query returned no records.</B></FONT></BODY></HTML>"
Response.End
End If
%>
Hit '<B>Stop</B>' to see a partial listing...<BR>
Return to <B><A HREF="trafficreports.asp">Query Menu</A> page.<P>
<%
...At this point, unless there has been an error or we got no records from the main query, we're ready to list the results. Like most ASP/ADO operations, we just have to loop through the recordset and output the values. But before we do that, we need to think about what our recordset contains. We could have run a query against any of eight different tables, so it's going to be kind of hard to guess what the column headings need to be.
In fact, we've got one particular report (the weekly summary of hits and sessions for all sites) that needs some special formatting of its own, so we will treat this as a special case. The rest are generic reports that broadly follow the same format. For the special weekly report, we have a separate section of code:
...
If strQuery = "weeks" Then 'this is the special weekly summary report %>
<table>
<tr>
<th> </th>
<th align="center" colspan=3 nowrap> WebDeveloper </th>
<th align="center" colspan=3 nowrap> COMDeveloper </th>
<th align="center" colspan=3 nowrap> WorldOfATL </th>
</tr>
<tr>
<th nowrap>Week </th>
<th align="center" nowrap> Hits </th>
<th align="center" nowrap>KBytes </th>
<th align="center" nowrap>Sessions </th>
<th align="center" nowrap> Hits </th>
<th align="center" nowrap>KBytes </th>
<th align="center" nowrap>Sessions </th>
<th align="center" nowrap> Hits </th>
<th align="center" nowrap>KBytes </th>
<th align="center" nowrap>Sessions </th>
</tr>
<%
Do While Not oRs.EOF
%>
<tr>
<td align="right" nowrap align=right><% = oRs("TWeekNumber") %>
: <% = oRs("TYearNumber") %> </td>
<td align="center" nowrap> <% = oRs("WDHitCount") %> </td>
<td align="center" nowrap><b><% = oRs("WDKBytes") %></B> </td>
<td align="center" nowrap><b><% = oRs("WDSessions") %></B> </td>
<td align="center" nowrap> <% = oRs("CDHitCount") %> </td>
<td align="center" nowrap><b><% = oRs("CDKBytes") %></B> </td>
<td align="center" nowrap><b><% = oRs("CDSessions") %></B> </td>
<td align="center" nowrap> <% = oRs("WAHitCount") %> </td>
<td align="center" nowrap><b><% = oRs("WAKBytes") %></B> </td>
<td align="center" nowrap><b><% = oRs("WASessions") %></B> </td>
</tr>
<%
oRs.MoveNext
Loop
...This produces the report you see below, with the three sites neatly divided into sections in the table. This report (as you can confirm from the code earlier in this chapter) doesn’t have a separate overall summary section:
If the report we're producing is not the special weekly summary, we know we will have some recordset containing results to be listed. However, we don't know what column headings to use, or what the data actually represents. As it turns out, we don’t need to know either of these things.
When we created the SQL statement to extract the main listing results, we gave the fields in the recordset specific names—based on which table we were extracting them from. For example, the CountrySummary table has fields named ItemCount and ItemText, but our SQL statement renames these as Hits and Language:SELECT Hits=SUM(ItemCount), Language=MAX(ItemText) FROM CountrySummary ... etc.The main limitation is that we can't include spaces and some other non-alphabetic characters. The characters that are legal in a field name depend on the database system you are running.
The syntax we use in the code listings for assigning a name to a calculated field, such as start=MIN(TSumDate), works in SQL Server and should work in the same way for most other enterprise-level database systems. If you are using Access or another desktop database system, however, you will need to change the statement. Access uses the format calc_field AS fieldname, for example MIN(TSumDate) AS start. (In fact, SQL Server also supports this format, but other systems might not). Alternatively, you might prefer to create the SQL queries as stored procedures or Access Queries, rather than sending them to the database as SQL strings.
Using ADO, and the VBScript For..Each syntax for iterating through a collection, we can iterate through the recordset's collection of field names to create the column headings, and then again to get their values: ...
Else 'this is the generic 'any other table' report
Response.Write "<table><tr>" & CRLF
For Each objItem In oRs.Fields
Response.Write "<th nowrap align=" & QUOT & "left" & QUOT _
& "> " & objItem.Name & " </th>" & CRLF
Next
Response.Write "</tr>" & CRLF
Do While Not oRs.EOF
Response.Write "<tr>" & CRLF
For Each objItem In oRs.Fields
Response.Write "<td nowrap align=" & QUOT & "left" & QUOT _
& "> " & objItem.value & " </td>" & CRLF
Next
Response.Write "</tr>" & CRLF
oRs.MoveNext
Loop
End If
Set oRs = Nothing
Set oConn = Nothing %>
</table>
</body>
</html>That's it. This section of code can cope with any recordset, and will automatically extract the field names. It's a useful trick in many situations where you want to display the contents of some arbitrary recordset. Here’s the result for a query on our CountrySummary table:
