Retrieving a Fixed Number of Records

One of the most common log reports is a "TOP 10" list showing the top entries appearing in a ranking.
This is usually achieved with a query that calculates some aggregate function within groups, orders the groups by the value of the aggregate function, and then uses the TOP keyword in the SELECT clause to return only a few records at the top of the ordered output.

As an example, the following query returns the TOP 10 URL's requested from an IIS log file:

SELECT  TOP 10  cs-uri-stem AS Url,
				COUNT(*) AS Hits
FROM <1>
GROUP BY Url
ORDER BY Hits DESC

Url							Hits
------------------------------ -----
/police/laws.nsf			 25183
/cgi-bin/counts.exe			5694
/police/rulesinfo.nsf		5202
/police/laws.nsf			 3980
/images/address.gif			3609
/image/1_m.jpg				 3540
/npanews0.htm				3305
/images/tibg.gif			 2955
/startopen/startopen920707.htm 2502
/police/find.nsf			 2465

This kind of reports is a perfect candidate for the CHART Output Format; assuming that the following query is saved in the "querytop.sql" text file, the following command will generate an image file containing a chart of the query output above:

SELECT  TOP 10  cs-uri-stem AS Url,
				COUNT(*) AS Hits
INTO Urls.gif
FROM <1>
GROUP BY Url
ORDER BY Hits DESC

C:\>LogParser file:querytop.sql -o:chart -chartType:Bar3d -chartTitle:"TOP 10 URL"