LogParser (download) is a command line tool from Microsoft which allows you to query any text-based log file using SQL-like syntax. The basic list of supported formats is quite impressive: IISW3C, NCSA, IIS, IISODBC, BIN, IISMSID, HTTPERR, URLSCAN, CSV, TSV, W3C, XML, EVT, ETW, NETMON, REG, ADS, TEXTLINE, TEXTWORD, FS and COM.
I usually use it for querying IIS Log files and believe me it is super-fast. On my Lenovo X1 i7/16GB/SSD it was able to query 8.97GB of log files 2min 12sec!
SELECT Date, TO_INT(COALESCE(EXTRACT_VALUE(cs-uri-query, 'id'), EXTRACT_VALUE(cs-uri-query, 'SouborSablonyID'))) AS SouborID, COUNT(*) AS Total FROM '[LOGFILEPATH]' WHERE (cs-uri-stem = '/business/sablony/soubor-partner.aspx') OR (cs-uri-stem = '/business/sablony/soubor.aspx') GROUP BY Date, SouborID ORDER BY Total DESC
Output to database
It is not only able to query the logs but you can use it to push the results to SQL database and many other supported data-sources (CSV, XML, …), e.g.
C:\Program Files (x86)\Log Parser 2.2>logparser “SELECT * INTO iisLogs FROM c:\temp\logs\*.log ” -i:iisw3c -o:SQL -server:localhost -database:MyLogs -username:sa -password:sa -createTable: ON
Note: If you want a plain import of log to DB (without any filtering, projection or aggregation) consider using Import Flat File… wizard from SQL Management Studio for better performance. If you want to use LogParser for feeding your DB, check the transactionRowCount option to batch uploaded rows into single transcation (e.g.
Is there any GUI for LogParser?
LogParser itself has always been a command-line utility. As an alternative it has a COM API which allows you to use it from your application. This API has been used to produce several GUIs which make the use of LogParser much easier:
- Microsoft LogParser Studio (download) is a Microsoft product which brings not only the GUI itself but is shipped with many (181) pre-defined query templates for different log types.
- Log Parser Lizard GUI is another free tool (with a paid Pro edition) produced outside Microsoft which might be even more powerful. I haven’t tested it yet but it looks promising for those of you who need to play with the logs on daily basis.
You might find following links useful when starting to play with LogParser:
- How to analyse IIS logs using LogParser / LogParser Studio [IIS Field Readiness]
- LogParser Functions [logparserplus.com]
- Analyze your IIS Log Files – Favorite Log Parser Queries [CarlosAg]
- Log Parser Rocks! More than 50 Examples! [Mike Lichtenberg]
- LogParser Forums [forums.iis.net]
- LogParser Performance Inserting into SQL Server