I have been very pleased with LogParser for analyzing various log files. I have been wondering how can I determine what are the search strings that users are looking for on our intranet. Well, I can now wonder about other things, no problem there!
1. Download and install LogParser 2.2 (http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en) Works best if you have the LogParser.exe on the same machine that the IIS Logfiles are on.
2. I create a cmd file that calls the Logparser.exe and passes a sql file (check the documentation for other methods). "C:\Program Files\IIS Resources\Log Parser\LogParser" -o:csv file:search.sql
3. The Search.sql file contains the following text.
SELECT DISTINCT EXTRACT_VALUE(cs-uri-query, 'k') AS SearchString, COUNT(*) AS HowMany
FROM *.log
TO search.csv
WHERE cs-uri-stem = '/search.aspx'
AND cs-uri-query NOT LIKE '%[Microsoft+Office+SharePoint+Portal+Server+2003+LOG]%'
AND SearchString IS NOT NULL
GROUP BY SearchString
ORDER BY HowMany DESC
Alternative SQL (Cleans up mixed case search strings - Adds Scope)
SELECT DISTINCT TO_UPPERCASE(EXTRACT_VALUE(cs-uri-query, 'k')) AS SearchString, EXTRACT_VALUE(cs-uri-query, 's') AS Scope, COUNT(*) AS HowMany
FROM *.log
TO search.csv
WHERE cs-uri-stem = '/search.aspx'AND cs-uri-query NOT LIKE '%[Microsoft+Office+SharePoint+Portal+Server+2003+LOG]%' AND SearchString IS NOT NULL
GROUP BY SearchString, Scope
ORDER BY HowMany DESC
4. Run the CMD file and Voila! Very fast (example statistics from my Feb 05 logs)
Statistics:
-----------
Elements processed: 22480133
Elements output: 16441
Execution time: 110.55 seconds (00:01:50.55)
Oui Oui Parsie!
