Ask yourself this question: what if everything could be queried with SQL?Microsoft's LogParser does just that. It lets you slice and dice a variety of log file types using a common SQL-like syntax. It's an incredibly powerful concept, and the LogParser implementation doesn't disappoint. This architecture diagram from the LogParser documentation explains it better than I could:
I’ve been playing with this tool again recently to analyse IIS log files.
Something that I miss is the ability to do sub-queries. The work-around is to just run another query on the output of the first query, but it would be much nicer to have this feature supported.
Maybe I should write up a blog post myself and share my queries with everyone else.
LogParser is a good tool. It comes with a straight-forward dll that you can easily use with your custom .net (or other) applications.
I only have 1 problem. I use it to parse windows media streaming logs. It sees the sc-bytes as integer. But it happens that I parse more data that an integer can hold. The LogParser parses it as a negatif number
Sorry for chiming in so late but I think I might have a solution for one of the problems you raised with LogParser, personally I’ve only used it a few times and liked it very much.
You raise the issue of automated detection of column type, I encountered that in the past and I believe it is caused by the MS Text Driver implementation, the same one that does the job for Excel or SQL server when reading from a file. The solution I used back then was using the schema.ini file (read more here: http://msdn2.microsoft.com/en-us/library/ms709353.aspx) it allows you to define exactly what you want the driver to do explictly - and leave the implicit stuff well alone)
I am trying to set up LogParser to output Netmon data (.cap file) to a SQL DB on a daily basis. In order to do this, I need to be able to set up the .sql file to read the .cap file using a wildcard.
So far, I have tried using all the variants that I could think of, find or otherwise attempt to use in my testbed.
Basically, my task will be to do:
Select frame,dateTime,SrcIp,SrcPort,DstIp,DstPort,PayloadBytes,Connection from ‘filename.cap’ to myTable
where DstPort = x
or DstPort = y
The difficulty I am having is finding the ‘right’ wildcard to replace filename, since the file will be changing daily.
I for one would love to find the source code to microsoft log parser some place, and then give it to some kid to do a nice C# version using interfaces and AppDomain.CurrentDomain.CreateInstanceAndUnwrap instead of COM.
Sure it has got its limitations, but it has proven to be just good enough to do the job and a huge time saver when compared with other more enterprise solutions.
On the other hand I wonder if Google Mapreduce might be a better
way to go, to avoid the confusion that people can have with
limited SQL subsets.
Or is LOG PARSE to go the way of DEC DATATRIEVE product?
Another cute query system that worked over all sorts of file formats.
Pete, one idea (haven’t tested) is to use SQL to dynamically generate the command to execute. There’s probably a better way to do it from a batch file or whatnot, but this should work. Mind the syntax.
declare @sqlstatement varchar(500)
select @sqlstatement = ‘Select frame,dateTime,SrcIp,SrcPort,DstIp,DstPort,PayloadBytes,Connection from ‘’’ + convert(char(8),getdate(),112) + ‘.cap’’ to myTable where DstPort = x or DstPort = y’
exec master…xp_cmdshell (@sqlstatement)
Regarding Standard Deviation in LogParser - I found this LogParser query a while back (forgot who the original author was, sorry). I checked it against SQL’s built in STDEV function and returned the same data so I am confident it is accurate - enough so for my needs anyway.
SELECT DIV ( MUL(1.0, SUM(time-taken)), Hits ) as AvgTime, SQRROOT ( SUB ( DIV ( MUL(1.0, SUM(SQR(time-taken)) ), Hits ) , SQR(AvgTime) ) ) AS StDev
