{"id":3066,"date":"2010-06-02T11:15:00","date_gmt":"2010-06-02T11:15:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/using-logparser-part-2\/"},"modified":"2016-09-02T11:37:41","modified_gmt":"2016-09-02T11:37:41","slug":"using-logparser-part-2","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/using-logparser-part-2\/","title":{"rendered":"Using LogParser – part 2"},"content":{"rendered":"

PersonAddress.csv<\/a>
SalesOrderDetail.tsv<\/a>
In part 1 of this series<\/a> we downloaded and installed LogParser<\/a> 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.<\/p>\n

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:LPperson_address.csv” -o:datagrid <\/strong>and run that we get a different result. A pop-up dialog that lets us view the results in a resizable grid.<\/p>\n

\"fxofn5le_thumb.jpg\"<\/a><\/p>\n

Notice that because we didn’t specify the columns we wanted returned by LogParser (we used SELECT *<\/strong>) is has added two columns to the recordset – filename <\/em>and rownumber<\/em>. This behaviour can be very useful as we will see in future parts of this series. You can click N<\/u>ext 10 rows or A<\/u>ll rows or close the datagrid once you are finished reviewing the data.<\/p>\n

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’:<\/p>\n

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<\/strong><\/p>\n

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:<\/p>\n

logparser “SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate into C:LPSales_SalesOrderDetailOver25.csv FROM C:LPSales_SalesOrderDetail.tsv WHERE orderqty > 25” -i:tsv -o:csv<\/strong><\/p>\n

Or we could find all those records where the Order Quantity is equal to 25 and output it to an xml file:<\/p>\n

logparser “SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, LineTotal, rowguid, ModifiedDate into C:LPSales_SalesOrderDetailEq25.xml FROM C:LPSales_SalesOrderDetail.tsv WHERE orderqty = 25” -i:tsv -o:xml<\/strong><\/p>\n

All the standard comparison operators are to be found in LogParser; >, <, =, LIKE, BETWEEN, OR, NOT, AND.<\/p>\n

Input and Output file formats.<\/h3>\n

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.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

From any of these<\/strong><\/p>\n<\/td>\n

\u00a0<\/td>\nTo any of these<\/strong><\/td>\n<\/tr>\n
\n

IISW3C<\/a>: parses IIS log files in the W3C Extended Log File Format.<\/p>\n<\/td>\n

 \u00a0<\/td>\n\n

NAT<\/a>: formats output records as readable tabulated columns.<\/p>\n<\/td>\n<\/tr>\n

\n

IIS<\/a>: parses IIS log files in the Microsoft IIS Log File Format.<\/p>\n<\/td>\n

\n

CSV<\/a>: formats output records as comma-separated values text.<\/p>\n<\/td>\n<\/tr>\n

\n

BIN<\/a>: parses IIS log files in the Centralized Binary Log File Format.<\/p>\n<\/td>\n

\n

TSV<\/a>: formats output records as tab-separated or space-separated values text.<\/p>\n<\/td>\n<\/tr>\n

\n

IISODBC<\/a>: returns database records from the tables logged to by IIS when configured to log in the ODBC Log Format.<\/p>\n<\/td>\n

\n

XML<\/a>: formats output records as XML documents.<\/p>\n<\/td>\n<\/tr>\n

\n

HTTPERR<\/a>: parses HTTP error log files generated by Http.sys.<\/p>\n<\/td>\n

\n

W3C<\/a>: formats output records in the W3C Extended Log File Format.<\/p>\n<\/td>\n<\/tr>\n

\n

URLSCAN<\/a>: parses log files generated by the URLScan IIS filter.<\/p>\n<\/td>\n

\n

TPL<\/a>: formats output records following user-defined templates.<\/p>\n<\/td>\n<\/tr>\n

\n

CSV<\/a>: parses comma-separated values text files.<\/p>\n<\/td>\n

\n

IIS<\/a>: formats output records in the Microsoft IIS Log File Format.<\/p>\n<\/td>\n<\/tr>\n

\n

TSV<\/a>: parses tab-separated and space-separated values text files.<\/p>\n<\/td>\n

\n

SQL<\/a>: uploads output records to a table in a SQL database.<\/p>\n<\/td>\n<\/tr>\n

\n

XML<\/a>: parses XML text files.<\/p>\n<\/td>\n

\n

SYSLOG<\/a>: sends output records to a Syslog server.<\/p>\n<\/td>\n<\/tr>\n

\n

W3C<\/a>: parses text files in the W3C Extended Log File Format.<\/p>\n<\/td>\n

\n

DATAGRID<\/a>: displays output records in a graphical user interface.<\/p>\n<\/td>\n<\/tr>\n

\n

NCSA<\/a>: parses web server log files in the NCSA Common, Combined, and Extended Log File Formats.<\/p>\n<\/td>\n

\n

CHART<\/a>: creates image files containing charts.<\/p>\n<\/td>\n<\/tr>\n

\n

TEXTLINE<\/a>: returns lines from generic text files.<\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n
\n

TEXTWORD<\/a>: returns words from generic text files.<\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n
\n

EVT<\/a>: returns events from the Windows Event Log and from Event Log backup files (.evt files). <\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n
\n

FS<\/a>: returns information on files and directories.<\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n
\n

REG<\/a>: returns information on registry values.<\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n
\n

ADS<\/a>: returns information on Active Directory objects.<\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n
\n

NETMON<\/a>: parses network capture files created by NetMon.<\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n
\n

ETW<\/a>: parses Enterprise Tracing for Windows trace log files and live sessions.<\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n
\n

COM<\/a>: provides an interface to Custom Input Format COM Plugins.<\/p>\n<\/td>\n

\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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.<\/p>\n

In part 3 we will look at working with multiple sources and specifically outputting to SQL format. See you there!<\/p>\n","protected":false},"excerpt":{"rendered":"

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……<\/p>\n","protected":false},"author":101210,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[7630],"class_list":["post-3066","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3066","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101210"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=3066"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3066\/revisions"}],"predecessor-version":[{"id":67550,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/3066\/revisions\/67550"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=3066"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=3066"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=3066"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=3066"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}