LOG PARSER.

  • Article

Hello everyone, today I'd like to write about the LOG PARSER.

First time I have encountered this tool was around 2004 when I had to load a big mass of data (IIS Log) into a database.

A month ago I was asked by a customer, how can is it possible to read and load into SQL Server flat files / application files. I recommended using the log parser and I was surprised to see that he was not aware of its existence. a fast survey among my customers reviled that there is a rather large group of DBAs that are not aware of this tool.

Log Parser is a command line tool that allows the user to use SQL in order to query log files.

This is how it works:

The logparser command has three parts: INPUT, DATA, OUTPUT.

Input – will be the part that supports the file format such as IIS LOG, Windows event logs, CSV, XML, TSV and other file formats.

Data – the data is the engine that make it possible to run SQL queries on the input file, much like a database engine.

Output – this part supports many types of outputs, you can format the output table into plain text files, SQL databases and XML.

Opening the logparser starts with a CMD command.

image

Executing a command with log parser basic command on an IIS log file with a group by:

logparser “SELECT * FROM C:\1\iislog.log”

This query will return the file output in a table format:

image

We can also do some manipulation on the file just like T-SQL command.

This query will show the Requests per day.

logparser"SELECT TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 'yyyy-MM-dd') AS [Day], COUNT(*) AS [Requests] FROM u_ex*.log GROUP BY [Day] ORDER BY [Day]"

And now for the SQL fun part, we can upload the log/logs output directly to a database table and keep it for inquiries and history.

logparser"SELECT * INTO EmailLogs FROM C:\1\iislog.log"-i:W3C-o:SQL-oConnString"Server=SQL2012; Database=IIS_LOGS; Trusted_Connection=True;" -oConnString:"Driver={SQL Server};Server=MyServer;db=pubs;uid=sa;pwd=MyPassword"

The -i:W3C parameter for the input format (IIS file)

The -o:SQL parameter for the output format (to SQL)

The -oConnString parameter is the connection string to the database