Sign in to follow this  
Followers 0
ptrex

Logparser Sql Engine In Autoit

20 posts in this topic

#1 ·  Posted (edited)

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 by ptrex

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

(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 by ptrex

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites

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


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

@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. :think:

PS: Don't forget to rate this topic.

Edited by ptrex

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites

#16 ·  Posted

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

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

Share this post


Link to post
Share on other sites

#19 ·  Posted (edited)

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. :whistle: 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 by Cancer

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0