MS LogParser Query

[bill[y]]

Charlie wrote an excellent article a while back on using Microsoft's
free LogParser utility. I was also interested in using it on
FusionReactor logs. It took a couple of hours, as I'm no admin log
expert, but once I learned that the LOG TYPE for the FusionReactor
files are TSV (tab/space separated value), as seen by LogParser, and
that you can feed it a separate header file, all that needed to be
done was to specify those things, and the rest fell into place nicely:

Example:
<code>
LogParser "select top 1000 date, time, requrl, querystring,
div(exectime,1000) as et,div(jdbctotaltime,1000) as jdbcet,statuscode
into mylog.csv from request-0.log order by et desc" -i:TSV -
iSeparator:space -iHeaderFile:request-headers.txt -o:csv
</code>

Note the iSeparator:space and iHeaderFile:request-headers.txt. So,
this reads 1000 of the longest running requests and writes it to
mylog.csv.

It looks like you can do a _lot_ with this ... graphs, xml, etc...


bill

[Charlie Arehart]

Bill, it's an amazing coincidence that you wrote this when you did. I was
on-site with a customer last week and the very day you wrote I had myself
come to notice that FusionReactor 3 included the request-headers.txt file
you point to in your Log Parser line. That's new (along with one for the
jdbc, resource, and cp logs). These header files weren't in 2. And in fact,
I hadn't myself noticed them (nor mentioned it in my blog on things I'd
found new in FR3, nor pointed out how it could be used with Log Parser)
until I was at the client site this week.

Well, it turns out there's a bug and those who upgraded from 2 to 3 don't
see these header files. I reported it while on the visit and the Intergral
support folks wrote back that "The header text files are only created when a
new instance is created. If you run FusionReactor 3 Setup to update a
previous version it does not created these files."

So for those who are wondering what Bill and I are talking about, these are
4 files that each have just a single line listing the names of the columns
for each column in the log files (though none for the reactor log
file--unless I'm mistaken).

I don't suppose the Intergral folks will mind me sharing this here. Of
course, these only work for FR3, not for FR2, since the log files are very
different (and have a LOT more info) in FR3.

But as Bill notes, they make it A LOT easier to work with Log Parser or any
other log analysis tools.

As for the article Bill mentions, it was in the now defunct CFDJ, though
still available at:

Monitoring Your CF Environment with the Free Log Parser Toolkit
(CFDJ Oct 2006)
http://coldfusion.sys-con.com/read/296223.htm

As I explain in the article, Log Parser is a free tool form Microsoft. It's
a command-line tool that can process any log files, allowing you to use SQL
statements against them. As Bill notes, and the article elaborates, you can
also output charts, graphs, other log files, and much more.

I've long wanted to point out the cool things you can do with Log Parser and
the awesome logs from FR. With the new header files, it's a lot easier. Here
are a few others I came up with last week:

logparser "select top 10 * from resource-0.log where reqsrunning >
10" -i:tsv -iheaderfile:resource-headers.txt -iseparator:space -nsep:1
-fixedsep:on -itsformat:"yyyy-MM-dd" -headerrow:off -rtp:-1

logparser "select top 10 * from request-0.log where exectime >
10000" -i:tsv -iheaderfile:request-headers.txt -iseparator:space -nsep:1
-fixedsep:on -itsformat:"yyyy-MM-dd" -headerrow:off -rtp:-1

logparser "select top 10 * from jdbc-0.log where totaltimeto >
10000" -i:tsv -iheaderfile:jdbc-headers.txt -iseparator:space -nsep:1
-fixedsep:on -itsformat:"yyyy-MM-dd" -headerrow:off -rtp:-1

I did have some problems, where some of the cols weren't working as they
should for their numeric comparisons. I seem to recall that there are ways
to tell the Log Parser tool that a column should be compared as a numeric.
As I expand on these, I'll share more. In fact, rather than hope people
might find this thread, I decided to create a page (using the Google Group
"Pages" feature) to hold such Log Parser command contributions. You can find
it at:

If you find you can't edit the page (or add new ones), that's because you
aren't logged into groups.google.com using the same address on which you
receive these emails.

http://groups.google.com/group/fusionreactor/web/log-parser-commands-for-fr-
3?hl=en

/charlie

[stevedev]

Does anyone have headers for FR2 ?

-Steve

On Feb 27, 5:41 pm, "bill[y]" <vir...@gmail.com> wrote:
> Charlie wrote an excellent article a while back on using Microsoft's

> freeLogParserutility. I was also interested in using it on

> FusionReactor logs. It took a couple of hours, as I'm no admin log
> expert, but once I learned that the LOG TYPE for the FusionReactor

> files are TSV (tab/space separated value), as seen byLogParser, and

[charlie arehart]

Well, they weren't provided with the product as in FR3, so someone would
need to have put them together manually. Any takers?

Beyond that, though, the files also sometimes weren't formatted consistently
so that they'd always work even if you did have the headers. Not saying no
one should try it, but some of us did back then and had issues. That's why
this was addressed in FR3, and it's a really important reason (along with
many other improvements--150 changes in all) that should motivate folks to
move from 2 to 3. Not trying to sound like a shill. Just sayin'.

/charlie

-----Original Message-----
From: fusion...@googlegroups.com [mailto:fusion...@googlegroups.com]

[Lee]

I was wondering if anyone knew of any *nix alternatives to the MS
LogParser. It seems like there would be something out there, but I
haven't been able to find anything that uses the same SQL-like
commands.

I am quite new to this, so any help would be appreciated.

Cheers,
Lee

On Feb 27, 5:41 pm, "bill[y]" <vir...@gmail.com> wrote:

[Peter Boughton]

One workaround solution could be to convert the tab-seperated file to
a comma-seperated one, then cfhttp it to get a query object which you
can QoQ on.

Although it is surprising that there isn't an equivalent *nix tool -
maybe go post it as a challenge on a linux programming forum and
someone will make one.

[charlie arehart]

Peter, here are a few thoughts:

1) First, some good news: you don't need to convert it from TSV to CSV.
CFHTTP's ability to convert such text files into a query is not limited to
CSVs.

2) That said, I don't think it's quite the same to propose querying the log
files in CFML as it is to use LogParser. The latter has a lot of tools
designed specifically for this work--including quantize functions, log file
checkpoint features (to keep you from reprocessing part of a log already
processed), and more. But still, it's not an entirely bad idea. Good
thinking outside the box. But really, Log Parser is truly unique. I've been
looking and have never heard of an alternative, at all, even for Unix.

3) That said, alternatives may well exist. Here's a page that lists some
alternatives:
http://www.loganalysis.org/sections/parsing/generic-log-parsers/. And while
the MS Log Parser does a lot that others may not, maybe you don't need all
that it does. Besides those alternatives, I have my own list of alternatives
at:

Log Analysis Tools
http://carehart.org/resourcelists/tools_to_consider/#logan

This is part of a larger list I have of now over 700 tools/resources for
CFers in over 100 categories. Always worth a peek when looking for some
alternative for some need, as a CFer.

4) Note, however, that while you can't run the MS LP tool on Windows, you
can indeed point logparser at logs on a remote machine: the logs being
analyzed do not need to be on Windows. That could be huge for some.

5) Finally, for those not familiar with the use of CFHTTP to read a CSV (and
have it converted automatically into a query), it's a relatively hidden (but
long-existing) feature. You can certainly find it discussed in the CF docs.

FWIW, I also discuss it briefly as one of several ways to process text files
in CFML, in a tips column I did for the first edition of the FusionAuthority
Quarterly Update. That column (just that, not the whole magazine) is
available online at:

http://www.fusionauthority.com/tipical-charlie/FA1_TipicalCharlie.pdf

/charlie

-----Original Message-----
From: fusion...@googlegroups.com [mailto:fusion...@googlegroups.com]
On Behalf Of Peter Boughton
Sent: Tuesday, April 22, 2008 1:43 PM
To: fusion...@googlegroups.com
Subject: FusionReactor Group: Re: MS LogParser Query