Logparser Sql Engine In Autoit

[ptrex]

MS Logparser SQL Engine In Autoit

For those who remember Episode 1 , SQLite semi Embedded database functionality in AutIT.

I want to introduce to you the next level of SQL integrated, on your Files System, using the MS LogParser.

Let me first give you a small introduction of what the LogParser SQL engine is about.

"Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®.

You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart.

The world is your database with Log Parser."

This is a next step to the LINQ (Language Integrated Query) Concept . LINQ Concept

MS LogParSer is a standalone command line tool, as well as a fully scriptable COM API.

To get started you need to download it here : MS LogParser 2.2

The LogParser has a standard SQL syntax, extended with Expression and Functions.

It is even possible to extend the functionalities with Custom PlugIns.

Concept overview

After installing, you need to register the "regsvr32 LogParser.dll",

in order to run the examples from my next post.

It has a small footprint and for this reason, it is a perfect marriage for AutoIT.

"SQL is the name and AutoIT is the game."

Edited September 14, 2012 by ptrex

[ptrex]

(Don' t forget to register the LogParser DLL.)

FS (Files System)

I have created 4 examples that take your file system as an input.

And generates 3 different Outputs on the fly.

1. LogParser using Functions Syntax.

LogParserFS_Exec_Functions.au3

2. LogParser using the on the fly DataGrid Output method.

LogParserFS_ExecBatch_DataGrid.au3

3. Logparser using the on the fly Chart Output method.

LogParserFS_ExecBatch_Chart.au3

4. Logparser using the Native Output

LogParserFS_ExecBatch_Native.au3

Here are some examples of on the fly outputs :

REG (Registry)

1. LogParser using the on the fly DataGrid Output method.

It will list all the AutoIT Regkeys and the dates they where modified.

LogParserREG_ExecBatch_DataGrid.au3

2. Logparser using the on the fly Chart Output method.

It will make a chart of the Regkeys Grouped by Type

LogParserREG_ExecBatch_Chart.au3

XML (RSS reader)

1. LogParser using the on the fly DataGrid Output method.

It will list the AutoIT RSS output in a Grid

LogParserXML_ExecBatch_DataGrid.au3

TEXT (XP PFirewall.log)

1. LogParser using the on the fly DataGrid Output method.

Top 25 Source IP addresses output in a Grid

LogParserTEXT_ExecBatch_DataGrid.au3

2. Logparser using the on the fly Chart Output method.

Top 25 Source IP addresses output in a Pie Chart

LogParserTEXT_ExecBatch_Chart.au3

3. Logparser using the on the fly Chart Output method.

Top 25 Source IP addresses output in an XML File

ADS (Active Directory)

1. LogParser using the on the fly DataGrid Output method.

ADS User output in a Grid - Don't forget to change the LDAP input to your ADS settings

LogParserADS_ExecBatch_DataGrid.au3

2. Logparser using the on the fly Chart Output method.

ADS User Count per Department output in a Pie Chart - Don't forget to change the LDAP input to your ADS settings

LogParserADS_ExecBatch_Chart.au3

These show only a very limlited number of possibilities.

Enjoy !!

LogParserTEXT_ExecBatch_XML.au3

Edited May 3, 2006 by ptrex

[ptrex]

This is an overview of the Input Formats that can be used :

IIS Log File Input Formats

IISW3C: parses IIS log files in the W3C Extended Log File Format.

IIS: parses IIS log files in the Microsoft IIS Log File Format.

BIN: parses IIS log files in the Centralized Binary Log File Format.

IISODBC: returns database records from the tables logged to by IIS when configured to log in the ODBC Log Format.

HTTPERR: parses HTTP error log files generated by Http.sys.

URLSCAN: parses log files generated by the URLScan IIS filter.

Generic Text File Input Formats

CSV: parses comma-separated values text files.

TSV: parses tab-separated and space-separated values text files.

XML: parses XML text files.

W3C: parses text files in the W3C Extended Log File Format.

NCSA: parses web server log files in the NCSA Common, Combined, and Extended Log File Formats.

TEXTLINE: returns lines from generic text files.

TEXTWORD: returns words from generic text files.

System Information Input Formats

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.

Special-purpose Input Formats

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.

This is an overview of the Output Formats that can be used :

Generic Text File Output Formats

NAT: formats output records as readable tabulated columns.

CSV: formats output records as comma-separated values text.

TSV: formats output records as tab-separated or space-separated values text.

XML: formats output records as XML documents.

W3C: formats output records in the W3C Extended Log File Format.

TPL: formats output records following user-defined templates.

IIS: formats output records in the Microsoft IIS Log File Format.

Special-purpose Output Formats

SQL: uploads output records to a table in a SQL database.

SYSLOG: sends output records to a Syslog server.

DATAGRID: displays output records in a graphical user interface.

CHART: creates image files containing charts.

[randallc]

Hi

Looks intertesting; would not register for me after install ; does it need a special Windows pack as well as the msi download?

Randall

[Zedna]

Hey it looks really very promising

[ptrex]

@randallc

Did you get an error message ?

Please give more details.

@Zedna

Thanks

If you all played around with this please post some examples so people get to see the power of this.

Till later.

[DaleHohm]

Hi

Looks intertesting; would not register for me after install ; does it need a special Windows pack as well as the msi download?

Randall

Try this:

regsvr32 C:\Program Files\Log Parser 2.2\LogParser.dll

@ptrex -- this looks very interesting, but there does not appear to be a redistributable for it... that would not limit the utility of it, only the likelyhood of any sort of integration.

Dale

[ptrex]

I have updated the 2nd post.

Two extra examples of Querying the REGISTRY and directing the output to a DATAGRID and a CHART.

More to come ...

[ptrex]

@DaleHohm

There are 2 ways of distribution.

One is shipping the Commandline Exe tool with AutoIT.

Second is shipping the DLL with AutoIT

Regarding Integration. As far as it can go, is that some finds the API's and use DLLCall.

But I am not that much interested in getting all these tools integrated into AutoIT,

but rather get SQL integrated as a syntax language in the AutoIT tool.

The LogParser is one of these examples that show the Power of SQL !!

SQL in this case is used against the FILES SYSTEM, REGISTRY, ACTIVE DIRECTORY, LOGFILES, TEXTFILES, ETC

This is concept of LINQ where the Query syntax is 100% part of the programming language.

Where in the past, to use Query syntax it could only be used on database objects.

Now it can be used on (practically) any object !!

And because in the LINQ concept, you don't need to rely on an external utility to run a Query, because it is an integral part of the development tool.

Maybe I am still dreaming loud, but the LogParser makes it reality (but still needs a external DLL as a source).

For the time being, if you like the LogParser functionality,

upload some examples and share it with us.

PS: Don't forget to rate this topic.

Edited April 29, 2006 by ptrex

[randallc]

Thanks

regsvr32 C:\Program Files\Log Parser 2.2\LogParser.dll

Still no joy; but worked with quotes

regsvr32 "C:\Program Files\Log Parser 2.2\LogParser.dll"

Best, Randall

[ptrex]

Added XML (RSS reader) Output to Grid in the 2nd Post.

[randallc]

@ptrex,

thanks for this;

I assume I cannot run a compiled script with this on a machine without the full install?

best, randall

[ptrex]

@Randallc

Yes it is possible (not tested yet).

Compile a script to run on another machine. Copy the LogParser.dll to there as well.

Run the famous "REGSVR32 Path of the LOGPARSER.DLL"

And that's it

Let us know if you got it working.

[ptrex]

Added TEXT file Output to Grid / Chart and XML, in the 2nd Post.

[randallc]

Hi

OK, seems to work with.au3 script on another computer, so far,; should be OK for compile; can I run "run regsvr" run quietly without the box coming up?

Thanks, Randall

[Dickb]

Hi

OK, seems to work with.au3 script on another computer, so far,; should be OK for compile; can I run "run regsvr" run quietly without the box coming up?

Thanks, Randall

Yes, use regsvr /s "path to parser.dll"

/s for silent. Works also for unregister, then use /s /u

[randallc]

Hi,

Yes, use regsvr /s "path to parser.dll"

Many thanks for helping my slowness,

Best, Randall

[ptrex]

Added ADS (Active Directory) Output to Grid / Chart, in the 2nd Post.

Runnig the Chart has a slow performance, depending on the # of objects in the ADS.

But it does do the job.

Edited May 2, 2006 by ptrex

[Cancer]

Added XML (RSS reader) Output to Grid in the 2nd Post.

Thank you for script and it looks like working, but would it be possible to use "rootXPath" and/or "XPath" in there?

Edit: Ok. Actually I got my answer. By this:

LogParser "SELECT * INTO rtp FROM http://www.pingviin.org/news_rss.php" -i:XML -fMode:Tree -o:DATAGRID -queryInfo

I was looking for "SELECT title2, link2, description#CDATA, PubDate" and now it parses content perfectly.

Edited August 15, 2006 by Cancer

[ptrex]

@Cancer

Glad to see it's usefull.

regards

ptrex