Using LogParser - part 2
Posted
by fatherjack
on Simple Talk
See other posts from Simple Talk
or by fatherjack
Published on Wed, 02 Jun 2010 16:15:00 GMT
Indexed on
2010/06/02
16:15 UTC
Read the original article
Hit count: 690
How to
PersonAddress.csv
SalesOrderDetail.tsv
In part 1 of this series we downloaded and installed LogParser and used it to list data from a csv file. That was a good start and in this article we are going to see the different ways we can stream data and choose whether a whole file is selected. We are also going to take a brief look at what file types we can interrogate.
If we take the query from part 1 and add a value for the output parameter as -o:datagrid so that the query becomes LOGPARSER "SELECT top 15 * FROM C:\LP\person_address.csv" -o:datagrid and run that we get a different result. A pop-up dialog that lets us view the results in a resizable grid. 
Notice that because we didn't specify the columns we wanted returned by LogParser (we used SELECT *) is has added two columns to the recordset - filename and rownumber. This behaviour can be very useful as we will see in future parts of this series. You can click Next 10 rows or All rows or close the datagrid once you are finished reviewing the data.
You may have noticed that the files that I am working with are different file types - one is a csv (comma separated values) and the other is a tsv (tab separated values). If you want to convert a file from one to another then LogParser makes it incredibly simple. Rather than using 'datagrid' as the value for the output parameter, use 'csv':
logparser "SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate into C:\Sales_SalesOrderDetail.csv FROM C:\Sales_SalesOrderDetail.tsv" -i:tsv -o:csv
Those familiar with SQL will not have to make a very big leap of faith to making adjustments to the above query to filter in/out records from the source file. Lets get all the records from the same file where the Order Quantity (OrderQty) is more than 25:
logparser "SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate into C:\LP\Sales_SalesOrderDetailOver25.csv FROM C:\LP\Sales_SalesOrderDetail.tsv WHERE orderqty > 25" -i:tsv -o:csv
Or we could find all those records where the Order Quantity is equal to 25 and output it to an xml file:
logparser "SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate into C:\LP\Sales_SalesOrderDetailEq25.xml FROM C:\LP\Sales_SalesOrderDetail.tsv WHERE orderqty = 25" -i:tsv -o:xml
All the standard comparison operators are to be found in LogParser; >, <, =, LIKE, BETWEEN, OR, NOT, AND.
Input and Output file formats.
LogParser has a pretty impressive list of file formats that it can parse and a good selection of output formats that will let you generate output in a format that is useable for whatever process or application you may be using.
| From any of these | To any of these | |
| IISW3C: parses IIS log files in the W3C Extended Log File Format. | NAT: formats output records as readable tabulated columns. | |
| IIS: parses IIS log files in the Microsoft IIS Log File Format. | CSV: formats output records as comma-separated values text. | |
| BIN: parses IIS log files in the Centralized Binary Log File Format. | TSV: formats output records as tab-separated or space-separated values text. | |
| IISODBC: returns database records from the tables logged to by IIS when configured to log in the ODBC Log Format. | XML: formats output records as XML documents. | |
| HTTPERR: parses HTTP error log files generated by Http.sys. | W3C: formats output records in the W3C Extended Log File Format. | |
| URLSCAN: parses log files generated by the URLScan IIS filter. | TPL: formats output records following user-defined templates. | |
| CSV: parses comma-separated values text files. | IIS: formats output records in the Microsoft IIS Log File Format. | |
| TSV: parses tab-separated and space-separated values text files. | SQL: uploads output records to a table in a SQL database. | |
| XML: parses XML text files. | SYSLOG: sends output records to a Syslog server. | |
| W3C: parses text files in the W3C Extended Log File Format. | DATAGRID: displays output records in a graphical user interface. | |
| NCSA: parses web server log files in the NCSA Common, Combined, and Extended Log File Formats. | CHART: creates image files containing charts. | |
| TEXTLINE: returns lines from generic text files. | ||
| TEXTWORD: returns words from generic text files. | ||
| EVT: returns events from the Windows Event Log and from Event Log backup files (.evt files). | ||
| FS: returns information on files and directories. | ||
| REG: returns information on registry values. | ||
| ADS: returns information on Active Directory objects. | ||
| NETMON: parses network capture files created by NetMon. | ||
| ETW: parses Enterprise Tracing for Windows trace log files and live sessions. | ||
| COM: provides an interface to Custom Input Format COM Plugins. |
So, you can query data from any of the types on the left and really easily get it into a format where it is ready for analysis by other tools. To a DBA or network Administrator with an enquiring mind this is a treasure trove.
In part 3 we will look at working with multiple sources and specifically outputting to SQL format. See you there!
© Simple Talk or respective owner
