Query Output Format

Transcript Query Output Format

“IIS Data Mining
with Log Parser 2.X”
Alexis Eller
Program Manager
Internet Information Services
Microsoft
What we will cover

Log Parser Basics





Building on Log Parser



Input Formats, Output Formats
Functions
Output Templates
How to get started…
Scripting with LogParser.dll
C# Interop
Advanced Features


New ‘CHART’ output format
CheckPoint
Helps to have…

Experience with:



Familiarity with:


Command Line tools
SQL Query Language
IIS Log Files, Event Log
… also nice to have experience with:


VBScript, JScript, C#
SQL Server
Log Parser Basics
Log Parser is…

Freely downloadable, stand-alone tool





Version 2.2: www.microsoft.com/download, search
for “Log Parser”
Version 2.1: www.microsoft.com/download, search
for “IIS 6.0 Resource Kit Tools”
Developed by Gabriele Giuseppini, former IIS
Not officially supported, try www.logparser.com
Provided in two forms:



Command line exe
Scriptable COM object
[no GUI available at this time]
Log Parser Basics
Key Concepts

Log Parser requires…

Input Format



Query
Output Format



[+options]
[+options]
[+options]
Query Language:


supports most of the true SQL language syntax
extends the language with additional functions
First Walk-Through
Log Parser Cmd-Line Help Simple
Simple Query
Using Input/Output Parameters
Log Parser Basics
Functions

To get a list of functions


logparser –h FUNCTIONS
Examples:

Conversion Functions




String Handling




TO_TIMESTAMP
TO_LOCALTIME
TO_INT
STRLEN
SUBSTR
STRCAT
Other




REVERSEDNS
QUANTIZE
EXTRACT_EXTENSION
CASE
Log Parser Basics
Function Example

Convert log file timestamp from UTC time to local time:
SELECT TO_DATE( TO_LOCALTIME( TO_TIMESTAMP(date, time))) AS date,
TO_TIME( TO_LOCALTIME( TO_TIMESTAMP(date, time))) AS time,
c-ip,
cs-username,
s-ip,
s-port,
cs-method,
cs-uri-stem,
cs-uri-query,
sc-status,
sc-win32-status,
sc-bytes,
cs-bytes,
time-taken,
cs(User-Agent)
FROM C:\WINDOWS\system32\Logfiles\W3SVC1\ex040308.log
Functions
Top 10 URL's
Convert Log File Time from
UTC Time to Local Time
Examine Time Taken Per VDir
Log Parser Basics
Output Templates




Header
Body
Footer
Best for
report-style
results
HEADER

logparser -h
-o:TPL
Consists of:
FOOTER BODY

TEMPLATE OUTPUT
<HTML>
<HEAD>
<TITLE>Hits/Hour for Ledbury Home Page</TITLE>
</HEAD>
<BODY BGCOLOR="#EFEFFF">
<TABLE BORDER="1" CELLPADDING="2“ CELLSPACING="2">
<TR>
<TH COLSPAN="2" ALIGN="CENTER">
Hits/Hour for Ledbury Home Page
</TH>
</TR>
<TR>
<TH ALIGN="LEFT">Hour</TH>
<TH ALIGN="LEFT"># Hits</TH>
</TR>
<TR><TD>2004-03-07 22:00:00</TD><TD>1</TD></TR>
<TR><TD>2004-03-09 09:00:00</TD><TD>2</TD></TR>
<TR><TD>2004-03-10 18:00:00</TD><TD>2</TD></TR>
</TABLE>
</BODY>
</HTML>
Templates
Broken Links Report
Event Log Messages
Log Parser Basics
Importing Data into SQL Server
SELECT 1,
TO_TIMESTAMP(date, time) AS LogTimeStamp,
s-ip,
cs-method,
cs-uri-stem,
cs-uri-query,
s-port,
cs-username,
c-ip,
cs(User-Agent),
sc-status,
sc-substatus,
sc-win32-status
INTO W3SVC2
database table
FROM ex040309.log

allows for an
auto-generated
identity column
in SQL
Note: Field names in query output are not important -- the field position
determines what column the data is inserted into in the SQL table
Log Parser Basics
How do I get started?

Find the column names in your data
source:



LogParser –h –i:<IISW3C|IIS|EVT>
or… “SELECT * FROM <datasource>”
For TSV or CSV files:




LogParser –h –i:CSV/TSV <filename>
Understand data provided in the columns
Apply any functions necessary to convert
that data into a useful form
–queryinfo Option
Log Parser Basics
-queryinfo
C:\DemoScripts>logparser file:SQLDB_Insert.sql -queryinfo
WARNING: Output format not specified - using NAT output format.
Query:
SELECT 1, TO_TIMESTAMP(date,time) AS LogTimeStamp, [s-ip], [cs-method],
[cs-uri-stem], [cs-uri-query], [s-port], [cs-username], [c-ip],
[cs(User-Agent)], [sc-status], [sc-substatus], [sc-win32-status],
[sc-bytes], [cs-bytes], [time-taken]
INTO W3SVC2
FROM ex040309.log
Formats selected:
Input Format : IISW3C (IIS W3C Extended Log Format)
Output Format: NAT (Native Format)
Query fields:
1 (I)
cs-uri-stem (S)
c-ip (S)
sc-win32-status (I)
LogTimeStamp (T)
cs-uri-query (S)
cs(User-Agent) (S)
sc-bytes (I)
s-ip (S)
s-port (I)
sc-status (I)
cs-bytes (I)
cs-method (S)
cs-username (S)
sc-substatus (I)
time-taken (I)
Building on Log Parser
Log Parser COM Architecture


MSUtil.LogQuery
Input and Output Format objects





MSUtil.LogQuery.IISW3CInputFormat
MSUtil.LogQuery.EventLogInputFormat
MSUtil.LogQuery.SQLOutputFormat
MSUtil.LogQuery.CSVOutputFormat
Two ways to run query:

MSUtil.LogQuery.Execute


Returns a LogRecordSet object that allows the script to
iterate through the query results
MSUtil.LogQuery.ExecuteBatch

Runs a query with a pre-specified output target (e.g. SQL or
CSV)
Building on Log Parser
Input/Output Format Parameters

Log Parser 2.2


Parameters have same name for both
command line tool and COM interface
Log Parser 2.1 and 2.0

Parameters do not always have same name
for cmd-line version and COM version, for
example:



logparser file:query.sql –o:NAT –rtp:-1
NativeOutputFormat.rowsToPrint
http://www.logparser.com/InstantKB/article.as
px?id=10039
Building on Log Parser
VBScript

Create LogQuery object and input/output
format objects
SET objLogQuery = WScript.CreateObject("MSUtil.LogQuery")
' Get the IIS Input and W3C output formats
ex05111606.log
SET w3cInputFormat = WScript.CreateObject("MSUtil.LogQuery.IISW3CInputFormat")
SET w3cOutputFormat = WScript.CreateObject("MSUtil.LogQuery.W3COutputFormat")
w3cOutputFormat.filemode = 0
' Create a SQL query
query = "SELECT TOP 20
cs-uri-stem, COUNT(*) as Total " & _
"INTO results.log FROM C:\WINDOWS\system32\Logfiles\W3SVC1\ex*.log " & _
"GROUP BY cs-uri-stem ORDER BY Total DESC "
objLogQuery.ExecuteBatch query, w3cInputFormat, w3cOutputFormat
For an example that uses Execute and LogRecordSet, see \Samples\Scripts\ErrorCodes.js in Log
Parser installation directory (also available on TechNet:
http://www.microsoft.com/technet/community/scriptcenter/logs/logparser/scripts/logpar01.mspx).
Script Samples
List Error Codes and Reasons
Hacker Scan
Building on Log Parser
C# Interop

Uses System.Reflection and System.Activator
Type comLogQueryType = Type.GetTypeFromProgID("MSUtil.LogQuery", true);
object comLogQueryObject = Activator.CreateInstance(comLogQueryType);
// Get the IIS Input and W3C output formats
Type inputFormatType = Type.GetTypeFromProgID("MSUtil.LogQuery.IISW3CInputFormat", true);
object inputFormatObject = Activator.CreateInstance(inputFormatType);
Type outputFormatType = Type.GetTypeFromProgID("MSUtil.LogQuery.W3COutputFormat", true);
object outputFormatObject = Activator.CreateInstance(outputFormatType);
// Create a SQL query
string query = "SELECT TOP 20 cs-uri-stem, COUNT(*) as Total ";
query += "INTO results.log FROM C:\\WINDOWS\\system32\\Logfiles\\W3SVC1\\ex*.log ";
query += "GROUP BY cs-uri-stem ORDER BY Total DESC ";
// Invoke the ExecuteBatch method
object[] inputArgs = { query, inputFormatObject, outputFormatObject };
comLogQueryType.InvokeMember("ExecuteBatch", BindingFlags.InvokeMethod, null,
comLogQueryObject, inputArgs);
Advanced Features
Chart Output Format

Uses Microsoft Office Web
Components ChartSpace
Object Model


You will need:


See this link for object model:
http://msdn.microsoft.com/libr
ary/default.asp?url=/library/enus/owcvba10/html/octocMSOW
CObjectModels.asp
Licensed version of Microsoft
Office Web Components,
available in Microsoft Office
XP© or better
Use Excel to view different
CHART types:

Insert Menu, Chart option –
shows the different chart types
Charts
Status Code Breakdown – Pie Chart
Hits Per Hour – Radial Chart
Advanced Features
CheckPoint – incremental parsing

Parse only what has not been parsed before:




ex*.log
<1>
System
… from all log files in a directory
… from all log files for site 1
… from the System Event Log
Requires a CheckPoint file to store state:
logparser "SELECT TimeGenerated, EventTypeName, Strings FROM
System WHERE SourceName = 'W3SVC'" -icheckpoint event.lpc
CheckPoint
Event Log Update E-mail
Session Summary
Log Parser has as many applications
as you can imagine…

Flexible and powerful


Building Blocks for Auditing and Monitoring


As with all data mining, the application starts with a
question…
Log Parser can parse almost any data source on your server…
with the additional input and output formats in Log Parser 2.2,
there’s really nothing more you need to build custom auditing
and monitoring for your system
Any Text, Any Time

LogParser can read most log files, text documents, and
STDOUT/text streams, and generate results in a HUGE number
of formats to make them useful and intelligible
For More Information

www.logparser.com has:



Articles about Log Parser:





SecurityFocus: Forensic Log Parsing with Microsoft's LogParser
http://www.securityfocus.com/infocus/1712
TechRepublic: Consolidating Events with Free Log Parser 2.0 Tool
http://techrepublic.com.com/5100-6329-5034923.html
Windows & .NET Magazine: Using SQL-Like Queries to Extract File-Format Information
http://www.winnetmag.com/Article/ArticleID/39233/39233.html
ComputerWorld
http://www.computerworld.com/securitytopics/security/story/0,10801,84230,00.html?from=i
mutopicheads
Presentations:


an active forum for questions
KB articles specifically for Log Parser
BlackHat Windows 2004: Forensic Secrets for Windows Servershttp://www.blackhat.com/presentations/win-usa-04/bh-win-04-burnett.pdf
Blog Mentions:




Amazing Log Parser - http://www.larkware.com/Articles/AmazingLogParser.html
Using Log Parser to Read Log Files http://geekswithblogs.net/drewby/articles/LogParser.aspx
Using LogParser from C# - http://www.furrygoat.com/archives/000858.html
michaelw.net - http://michaelw.net/Articles/LogParser.html
[email protected]

Query Output Format

Transcript Query Output Format

Directory