LogParser 2.1 COM object cursor as input

I posted this to microsoft.public.win2000.cmdprompt.admin on January
5, where I was advised to "try re-posting in a WSH or SQL newsgroup".
I posted to microsoft.public.scripting.vbscript on January 6 and was
advised to post here, where "... the logParser dudes hang out." So
here goes...

I'm using LogParser 2.1 as a COM object under W2K SP4 via VBS scripts.

I'd like to be able to execute Log Parser on a first pass to obtain a
record set and then pass that cursor back to Log Parser as *input* for
a second query.

Here's a very simple example just for illustrative purposes:

Dim Wshso, oEvtLogQ, strSQL, strSQL2, oRS, oRS2

Set Wshso = WScript.CreateObject("WScript.Shell")
Set oEvtLogQ = WScript.CreateObject("MSUtil.LogQuery")

'count all SysLog 6005 incidents
strSQL = "SELECT COUNT(*) FROM System WHERE EventID = 6005"
Set oRS = oEvtLogQ.Execute(strSQL)
'display the count
MsgBox oRS.GetRecord().getValue(0)
Set oRS=Nothing 'clear oRS for reuse

'redo the same query in two parts

'select all SysLog 6005 incidents into oRS cursor
strSQL = "SELECT EventID FROM System WHERE EventID = 6005"
Set oRS = oEvtLogQ.Execute(strSQL)
'display the first record to confirm the query returned something
MsgBox oRS.GetRecord().getValue(0)

'count all SysLog 6005 incidents using oRS cursor as input
strSQL2 = "SELECT COUNT(*) FROM " & oRS & " GROUP BY EventID"
Set oRS2 = oEvtLogQ.Execute(strSQL2)
'display the count
MsgBox oRS2.GetRecord().getValue(0)

The first two queries complete successfully and the message boxes
display normally. The third query returns an error and points to the
strSQL2 assignment line:

Error: Object doesn't support this property or method
Code 800A01B6

What would be the correct syntax to use the oRS cursor as input to the
second query?

LogParser is a wonderful tool. (Kudos to the team that developed it.)
Being able to use cursors in VBS scripts would allow me to use it as a
"pocket" SQL engine to perform virtually any conceivable analysis of
any line-record log file. (Without the use of cursors, the same
analysis could still be performed, but the intermediate query results
would need to be saved to files.)

regards, Andy

**********

Please send e-mail to: usenet d0t post at aaronoff d0t com

**********

Gabriele
Mon Jan 12 20:06:35 CST 2004

Hello Andrew,
I'm sorry, but there is no way to re-use the recordset as input of another
query.

First off, Log Parser's recordset can only be used once; once you've reached
the end of the recordset, you can't "MoveFirst" and restart...so, reusing
recordsets in the way you describe below wouldn't be much useful, unless
you're trying to do that as a workaround for a 'complex' SQL query (e.g.
"SELECT COUNT(*) FROM (SELECT ....)", which is not supported in the current
version).

However, the upcoming version 2.2 allows users to write their own "Input
Formats" by just implementing a COM interface; with version 2.2 you could
wrap a recordset with such an interface (using the language of your choice,
e.g. jscript) and then use the recordset as input of another query.

Sorry.

--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Andrew Aronoff" <NOSPAM_WRONG.ADDRESS@yahoo.com> wrote in message
news:b0i600tph9vj3799clpdpltkecrlij2886@4ax.com...
> I posted this to microsoft.public.win2000.cmdprompt.admin on January
> 5, where I was advised to "try re-posting in a WSH or SQL newsgroup".
> I posted to microsoft.public.scripting.vbscript on January 6 and was
> advised to post here, where "... the logParser dudes hang out." So
> here goes...
>
> I'm using LogParser 2.1 as a COM object under W2K SP4 via VBS scripts.
>
> I'd like to be able to execute Log Parser on a first pass to obtain a
> record set and then pass that cursor back to Log Parser as *input* for
> a second query.
>
> Here's a very simple example just for illustrative purposes:
>
> Dim Wshso, oEvtLogQ, strSQL, strSQL2, oRS, oRS2
>
> Set Wshso = WScript.CreateObject("WScript.Shell")
> Set oEvtLogQ = WScript.CreateObject("MSUtil.LogQuery")
>
> 'count all SysLog 6005 incidents
> strSQL = "SELECT COUNT(*) FROM System WHERE EventID = 6005"
> Set oRS = oEvtLogQ.Execute(strSQL)
> 'display the count
> MsgBox oRS.GetRecord().getValue(0)
> Set oRS=Nothing 'clear oRS for reuse
>
> 'redo the same query in two parts
>
> 'select all SysLog 6005 incidents into oRS cursor
> strSQL = "SELECT EventID FROM System WHERE EventID = 6005"
> Set oRS = oEvtLogQ.Execute(strSQL)
> 'display the first record to confirm the query returned something
> MsgBox oRS.GetRecord().getValue(0)
>
> 'count all SysLog 6005 incidents using oRS cursor as input
> strSQL2 = "SELECT COUNT(*) FROM " & oRS & " GROUP BY EventID"
> Set oRS2 = oEvtLogQ.Execute(strSQL2)
> 'display the count
> MsgBox oRS2.GetRecord().getValue(0)
>
> The first two queries complete successfully and the message boxes
> display normally. The third query returns an error and points to the
> strSQL2 assignment line:
>
> Error: Object doesn't support this property or method
> Code 800A01B6
>
> What would be the correct syntax to use the oRS cursor as input to the
> second query?
>
> LogParser is a wonderful tool. (Kudos to the team that developed it.)
> Being able to use cursors in VBS scripts would allow me to use it as a
> "pocket" SQL engine to perform virtually any conceivable analysis of
> any line-record log file. (Without the use of cursors, the same
> analysis could still be performed, but the intermediate query results
> would need to be saved to files.)
>
> regards, Andy
>
> **********
>
> Please send e-mail to: usenet d0t post at aaronoff d0t com
>
> **********

David
Mon Jan 12 19:57:51 CST 2004

I do not think that is a feature in LogParser 2.1. I will pass your
thoughts along to the author.

--
//David
IIS
This posting is provided "AS IS" with no warranties, and confers no rights.
//
"Andrew Aronoff" <NOSPAM_WRONG.ADDRESS@yahoo.com> wrote in message
news:b0i600tph9vj3799clpdpltkecrlij2886@4ax.com...
I posted this to microsoft.public.win2000.cmdprompt.admin on January
5, where I was advised to "try re-posting in a WSH or SQL newsgroup".
I posted to microsoft.public.scripting.vbscript on January 6 and was
advised to post here, where "... the logParser dudes hang out." So
here goes...

I'm using LogParser 2.1 as a COM object under W2K SP4 via VBS scripts.

I'd like to be able to execute Log Parser on a first pass to obtain a
record set and then pass that cursor back to Log Parser as *input* for
a second query.

Here's a very simple example just for illustrative purposes:

Dim Wshso, oEvtLogQ, strSQL, strSQL2, oRS, oRS2

Set Wshso = WScript.CreateObject("WScript.Shell")
Set oEvtLogQ = WScript.CreateObject("MSUtil.LogQuery")

'count all SysLog 6005 incidents
strSQL = "SELECT COUNT(*) FROM System WHERE EventID = 6005"
Set oRS = oEvtLogQ.Execute(strSQL)
'display the count
MsgBox oRS.GetRecord().getValue(0)
Set oRS=Nothing 'clear oRS for reuse

'redo the same query in two parts

'select all SysLog 6005 incidents into oRS cursor
strSQL = "SELECT EventID FROM System WHERE EventID = 6005"
Set oRS = oEvtLogQ.Execute(strSQL)
'display the first record to confirm the query returned something
MsgBox oRS.GetRecord().getValue(0)

'count all SysLog 6005 incidents using oRS cursor as input
strSQL2 = "SELECT COUNT(*) FROM " & oRS & " GROUP BY EventID"
Set oRS2 = oEvtLogQ.Execute(strSQL2)
'display the count
MsgBox oRS2.GetRecord().getValue(0)

The first two queries complete successfully and the message boxes
display normally. The third query returns an error and points to the
strSQL2 assignment line:

Error: Object doesn't support this property or method
Code 800A01B6

What would be the correct syntax to use the oRS cursor as input to the
second query?

LogParser is a wonderful tool. (Kudos to the team that developed it.)
Being able to use cursors in VBS scripts would allow me to use it as a
"pocket" SQL engine to perform virtually any conceivable analysis of
any line-record log file. (Without the use of cursors, the same
analysis could still be performed, but the intermediate query results
would need to be saved to files.)

regards, Andy

**********

Please send e-mail to: usenet d0t post at aaronoff d0t com

**********

Andrew
Tue Jan 13 02:31:54 CST 2004

"David Wang [Msft]" <someone@online.microsoft.com> wrote:

>I do not think that is a feature in LogParser 2.1. I will pass your
>thoughts along to the author.

Thanks for your response and thanks for taking note of my question for
a future version.

regards, Andy

**********

Please send e-mail to: usenet d0t post at aaronoff d0t com

**********

Andrew
Tue Jan 13 03:05:35 CST 2004

Hi, Gabriele.

> there is no way to re-use the recordset as input of another query.

Thank very much for your response. That's what I wanted to know.

> reusing recordsets... wouldn't be... useful, unless you're trying to
> do that as a workaround for a 'complex' SQL query

The immediate (but far from unique) need was the problem with
returning EventID when the Message field is null.

For instance, the following query returns over 20 records on my
system:

SELECT EventID FROM System WHERE EventID = 43

However, this next query returns zero records.

SELECT EventID, Message FROM System WHERE EventID = 43

In the event log, EventID 43 has the following description:

----------
The description for Event ID ( 43 ) in Source ( ATMhelpr ) cannot be
found. The local computer may not have the necessary registry
information or message DLL files to display messages from a remote
computer. The following information is part of the event: .
----------

The problem, then, is return of the Message field, which must be null
for this EventID. I then tried the following query:

SELECT EventID, REPLACE_IF_NULL(Message,'null value') FROM System
WHERE EventID = 43

This query returned the error 80004005, "Recordset cannot be
used at this time [ Unknown Error ]":

Since I couldn't reliably return EventID _and_ Message in the same
query, I tried splitting into two, the first to return all EventID=43
events and their RecordNumber, the second to list any existing Message
field contents for those records.

Neither strategy will now work.

IAC, I don't understand why the query using "REPLACE_IF_NULL" doesn't
run. What's the correct query syntax to find events by EventID and
include the Message field *if it exists*?

TIA and regards, Andy
usenet d0t post at aaronoff d0t com

"Gabriele Giuseppini [MS]" <gabriegi@online.microsoft.com> wrote:

>Hello Andrew,
>I'm sorry, but there is no way to re-use the recordset as input of another
>query.
>
>First off, Log Parser's recordset can only be used once; once you've reached
>the end of the recordset, you can't "MoveFirst" and restart...so, reusing
>recordsets in the way you describe below wouldn't be much useful, unless
>you're trying to do that as a workaround for a 'complex' SQL query (e.g.
>"SELECT COUNT(*) FROM (SELECT ....)", which is not supported in the current
>version).
>
>However, the upcoming version 2.2 allows users to write their own "Input
>Formats" by just implementing a COM interface; with version 2.2 you could
>wrap a recordset with such an interface (using the language of your choice,
>e.g. jscript) and then use the recordset as input of another query.
>
>Sorry.

David
Tue Jan 13 15:55:47 CST 2004

Your problem is not with the Message field being NULL.

You're probably running a query against an event log that is on/from a
different machine (or you've installed software that generated a certain
EventID and then subsequently uninstalled it). This is fixable outside of
LogParser.

Eventlog entries are associated with an ID, which have associated and
localizable resources. These resources contain the text that shows up in
the Message field.

The nice thing about the design is that you automatically get localized
event log messages in a I18N friendly manner. The bad thing about the
design is that these resources are not necessarily installed on the machine
browsing the eventlog.

In a simplified manner:
- The event log files contain entries linked to EventIDs like 1, 2, or 43.
- Each event ID has an associated resource file that links the message
string associated with EventID = 1, 2, or 43.
- These resource files are localized, and usually software that logs EventID
43 also installs resources to interpret EventID 43.
- This works fine on the system itself since the software generates EventID
43s, and the resources to interpret EventID 43 are also on the system.
- This doesn't work if you have software on one system generating EventID
43, but try to browse it from another system that doesn't have resources to
interpret EventID 43.
- The solution is to register the resources to interpret EventID 43 on the
system trying to browse the event log.

--
//David
IIS
This posting is provided "AS IS" with no warranties, and confers no rights.
//
"Andrew Aronoff" <NOSPAM_WRONG.ADDRESS@yahoo.com> wrote in message
news:u7b700d07et1vvrqknv6ihrse7jopvrce9@4ax.com...
Hi, Gabriele.

> there is no way to re-use the recordset as input of another query.

Thank very much for your response. That's what I wanted to know.

> reusing recordsets... wouldn't be... useful, unless you're trying to
> do that as a workaround for a 'complex' SQL query

The immediate (but far from unique) need was the problem with
returning EventID when the Message field is null.

For instance, the following query returns over 20 records on my
system:

SELECT EventID FROM System WHERE EventID = 43

However, this next query returns zero records.

SELECT EventID, Message FROM System WHERE EventID = 43

In the event log, EventID 43 has the following description:

----------
The description for Event ID ( 43 ) in Source ( ATMhelpr ) cannot be
found. The local computer may not have the necessary registry
information or message DLL files to display messages from a remote
computer. The following information is part of the event: .
----------

The problem, then, is return of the Message field, which must be null
for this EventID. I then tried the following query:

SELECT EventID, REPLACE_IF_NULL(Message,'null value') FROM System
WHERE EventID = 43

This query returned the error 80004005, "Recordset cannot be
used at this time [ Unknown Error ]":

Since I couldn't reliably return EventID _and_ Message in the same
query, I tried splitting into two, the first to return all EventID=43
events and their RecordNumber, the second to list any existing Message
field contents for those records.

Neither strategy will now work.

IAC, I don't understand why the query using "REPLACE_IF_NULL" doesn't
run. What's the correct query syntax to find events by EventID and
include the Message field *if it exists*?

TIA and regards, Andy
usenet d0t post at aaronoff d0t com

"Gabriele Giuseppini [MS]" <gabriegi@online.microsoft.com> wrote:

>Hello Andrew,
>I'm sorry, but there is no way to re-use the recordset as input of another
>query.
>
>First off, Log Parser's recordset can only be used once; once you've
reached
>the end of the recordset, you can't "MoveFirst" and restart...so, reusing
>recordsets in the way you describe below wouldn't be much useful, unless
>you're trying to do that as a workaround for a 'complex' SQL query (e.g.
>"SELECT COUNT(*) FROM (SELECT ....)", which is not supported in the current

>version).
>
>However, the upcoming version 2.2 allows users to write their own "Input
>Formats" by just implementing a COM interface; with version 2.2 you could
>wrap a recordset with such an interface (using the language of your choice,
>e.g. jscript) and then use the recordset as input of another query.
>
>Sorry.

Paul
Tue Jan 13 17:12:49 CST 2004

alternatively run the LogParser routine on the server in question rather
than accessing remotely

regards
paul

"David Wang [Msft]" <someone@online.microsoft.com> wrote in message
news:efY13Mi2DHA.1740@TK2MSFTNGP09.phx.gbl...
> Your problem is not with the Message field being NULL.
>
> You're probably running a query against an event log that is on/from a
> different machine (or you've installed software that generated a certain
> EventID and then subsequently uninstalled it). This is fixable outside of
> LogParser.
>
> Eventlog entries are associated with an ID, which have associated and
> localizable resources. These resources contain the text that shows up in
> the Message field.
>
> The nice thing about the design is that you automatically get localized
> event log messages in a I18N friendly manner. The bad thing about the
> design is that these resources are not necessarily installed on the
machine
> browsing the eventlog.
>
> In a simplified manner:
> - The event log files contain entries linked to EventIDs like 1, 2, or 43.
> - Each event ID has an associated resource file that links the message
> string associated with EventID = 1, 2, or 43.
> - These resource files are localized, and usually software that logs
EventID
> 43 also installs resources to interpret EventID 43.
> - This works fine on the system itself since the software generates
EventID
> 43s, and the resources to interpret EventID 43 are also on the system.
> - This doesn't work if you have software on one system generating EventID
> 43, but try to browse it from another system that doesn't have resources
to
> interpret EventID 43.
> - The solution is to register the resources to interpret EventID 43 on the
> system trying to browse the event log.
>
> --
> //David
> IIS
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> //
> "Andrew Aronoff" <NOSPAM_WRONG.ADDRESS@yahoo.com> wrote in message
> news:u7b700d07et1vvrqknv6ihrse7jopvrce9@4ax.com...
> Hi, Gabriele.
>
> > there is no way to re-use the recordset as input of another query.
>
> Thank very much for your response. That's what I wanted to know.
>
> > reusing recordsets... wouldn't be... useful, unless you're trying to
> > do that as a workaround for a 'complex' SQL query
>
> The immediate (but far from unique) need was the problem with
> returning EventID when the Message field is null.
>
> For instance, the following query returns over 20 records on my
> system:
>
> SELECT EventID FROM System WHERE EventID = 43
>
> However, this next query returns zero records.
>
> SELECT EventID, Message FROM System WHERE EventID = 43
>
> In the event log, EventID 43 has the following description:
>
> ----------
> The description for Event ID ( 43 ) in Source ( ATMhelpr ) cannot be
> found. The local computer may not have the necessary registry
> information or message DLL files to display messages from a remote
> computer. The following information is part of the event: .
> ----------
>
> The problem, then, is return of the Message field, which must be null
> for this EventID. I then tried the following query:
>
> SELECT EventID, REPLACE_IF_NULL(Message,'null value') FROM System
> WHERE EventID = 43
>
> This query returned the error 80004005, "Recordset cannot be
> used at this time [ Unknown Error ]":
>
> Since I couldn't reliably return EventID _and_ Message in the same
> query, I tried splitting into two, the first to return all EventID=43
> events and their RecordNumber, the second to list any existing Message
> field contents for those records.
>
> Neither strategy will now work.
>
> IAC, I don't understand why the query using "REPLACE_IF_NULL" doesn't
> run. What's the correct query syntax to find events by EventID and
> include the Message field *if it exists*?
>
> TIA and regards, Andy
> usenet d0t post at aaronoff d0t com
>
> "Gabriele Giuseppini [MS]" <gabriegi@online.microsoft.com> wrote:
>
> >Hello Andrew,
> >I'm sorry, but there is no way to re-use the recordset as input of
another
> >query.
> >
> >First off, Log Parser's recordset can only be used once; once you've
> reached
> >the end of the recordset, you can't "MoveFirst" and restart...so, reusing
> >recordsets in the way you describe below wouldn't be much useful, unless
> >you're trying to do that as a workaround for a 'complex' SQL query (e.g.
> >"SELECT COUNT(*) FROM (SELECT ....)", which is not supported in the
current
>
> >version).
> >
> >However, the upcoming version 2.2 allows users to write their own "Input
> >Formats" by just implementing a COM interface; with version 2.2 you could
> >wrap a recordset with such an interface (using the language of your
choice,
> >e.g. jscript) and then use the recordset as input of another query.
> >
> >Sorry.
>
>

Andrew
Thu Jan 15 19:04:27 CST 2004

Hi, David.

Thanks for the very clear explanation.

>You're probably running a query against an event log that is on/from a
>different machine (or you've installed software that generated a certain
>EventID and then subsequently uninstalled it).

I'm running locally, so it's the latter situation -- the software was
installed on my machine, caused problems, and had to be uninstalled.

This leaves me perplexed. I'd like to generate periodic reports
summarizing the Events that have appeared on the system. For errors
and warnings, I'd like to list every incident with the ID and Message.
Of course, programs come and go, but Event Log incidents stay... well
not forever, but a long time.

If I read you correctly, there's no way for me to safely query for
EventIDs and Messages. If the program responsible for the incident has
been uninstalled, the Message may be unretrievable. If I query for the
Message *and* the EventID, the response will be missing both, even
though both are still displayed in the Event Viewer.

It would certainly be useful to be able to request both in the query
and to settle for some default string if the Message string is
unavailable (null or missing). Obtaining a list restricted to EventIDs
in a monthly or quarterly report is not particularly useful. It will
oblige me to go back to the Event Log to view the Message, which is
something that the use of Log Parser was intended to avoid.

regards, Andy

"David Wang [Msft]" <someone@online.microsoft.com> wrote:

>Your problem is not with the Message field being NULL.
>
>You're probably running a query against an event log that is on/from a
>different machine (or you've installed software that generated a certain
>EventID and then subsequently uninstalled it). This is fixable outside of
>LogParser.
>
>Eventlog entries are associated with an ID, which have associated and
>localizable resources. These resources contain the text that shows up in
>the Message field.
>
>The nice thing about the design is that you automatically get localized
>event log messages in a I18N friendly manner. The bad thing about the
>design is that these resources are not necessarily installed on the machine
>browsing the eventlog.
>
>In a simplified manner:
>- The event log files contain entries linked to EventIDs like 1, 2, or 43.
>- Each event ID has an associated resource file that links the message
>string associated with EventID = 1, 2, or 43.
>- These resource files are localized, and usually software that logs EventID
>43 also installs resources to interpret EventID 43.
>- This works fine on the system itself since the software generates EventID
>43s, and the resources to interpret EventID 43 are also on the system.
>- This doesn't work if you have software on one system generating EventID
>43, but try to browse it from another system that doesn't have resources to
>interpret EventID 43.
>- The solution is to register the resources to interpret EventID 43 on the
>system trying to browse the event log.