深藍色論壇

LogParser 是一套用來分析 log 的工具, 除了支援 iis log 外, 還能利用很接近 sql command 的語法分析或統計 log 的資訊. 新的版本 (2.2) 支援了更多種的輸入資料, 如

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.

輸出的格式除了一些文字型態外, 更棒的是可以配合 OWC (Office Web Component)產生相當多種型態的圖形, 也很容易使用, 參考範例如下,

example: http://www.microsoft.com/technet/script ... mples.mspx

下載地點:
http://www.microsoft.com/downloads/deta ... laylang=en

另外還有非官方網站, 有相當多的討論及使用資源:
http://www.logparser.com/

最重要的, 也十分容易和程式結合, 因為安裝完成後, 就註冊了一個 com 的元件進來, 操作起來相當方便且容易, 如在 console 下查系統事件的查詢, 統計來源名稱的個數:

LogParser "SELECT SourceName, count(*) as counts FROM system group by SourceName"

至於要產生圖檔, 就這麼下:
LogParser -chartTitle:"Sample Statistics" -ChartType:Column3d -GroupSize:800x600 "SELECT SourceName, count(*) as counts into c:my.gif FROM system group by SourceName"

圖形: 使用 delphi 的範例如下,
(放個 Memo, 並 uses Comobj)

真的是一個相當好用的分析工具啊. 還有相當多的應用, 相信你會發現功能真的很強大!

LogParser 的一些 sample (引用官方說明檔):

IIS log files queries

These examples assume the log format used is W3C Extended. If a different format is used, the field names may need to be changed.

Get the number of requests and total bytes sent during each hour

SELECT QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour,
COUNT(*) AS Total,
SUM(sc-bytes) AS TotBytesSent
FROM ex*.log
GROUP BY Hour
ORDER BY Hour

Create a pie chart with the total number of bytes generated by each extension
To use: "LogParser file:ExtensionByte.sql -charttype:PieExploded -charttitle:"Bytes per extension" -categories:off"

SELECT EXTRACT_EXTENSION( cs-uri-stem ) AS Extension,
MUL(PROPSUM(sc-bytes),100.0) AS Bytes
INTO Pie.gif
FROM <1>
GROUP BY Extension
ORDER BY Bytes DESC

Get the Top 20 verbs with the maximum and average time taken, and with the average number of bytes sent

SELECT TOP 20 cs-method,
COUNT(*) AS Total,
MAX(time-taken) AS MaxTime,
AVG(time-taken) AS AvgTime,
AVG(sc-bytes) AS AvgBytesSent
FROM ex*.log
GROUP BY cs-method
ORDER BY Total DESC

Get the Top 20 URIs with the maximum and average time taken, and with the average number of bytes sent

SELECT TOP 20 cs-uri-stem,
COUNT(*) AS Total,
MAX(time-taken) AS MaxTime,
AVG(time-taken) AS AvgTime,
AVG(sc-bytes) AS AvgBytesSent
FROM ex*.log
GROUP BY cs-uri-stem
ORDER BY Total DESC

Get the full HTTP status codes by number of hits

SELECT STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS Status,
COUNT(*) AS Total
FROM ex*.log
GROUP BY Status
ORDER BY Total DESC

For each ASP error, get the Url and the ASP script line number

SELECT EXTRACT_TOKEN(FullUri, 0, '|') AS Uri,
EXTRACT_TOKEN(cs-uri-query, -1, '|') AS ErrorMsg,
EXTRACT_TOKEN(cs-uri-query, 1, '|') AS LineNo,
COUNT(*) AS Total
USING STRCAT( cs-uri-stem,
REPLACE_IF_NOT_NULL(cs-uri-query, STRCAT('?', cs-uri-query))
) AS FullUri
FROM ex*.log
WHERE (sc-status = 500) AND (cs-uri-stem LIKE '%.asp')
GROUP BY Uri, ErrorMsg, LineNo
ORDER BY Total DESC

Get requests and full status by number of hits

SELECT STRCAT( cs-uri-stem,
REPLACE_IF_NOT_NULL(cs-uri-query, STRCAT('?',cs-uri-query))
) AS Request,
STRCAT( TO_STRING(sc-status),
STRCAT( '.',
COALESCE(TO_STRING(sc-substatus), '?' )
)
) AS Status,
COUNT(*) AS Total
FROM ex*.log
WHERE (sc-status >= 400)
GROUP BY Request, Status
ORDER BY Total DESC

Get hit counts for each extension

SELECT EXTRACT_EXTENSION( cs-uri-stem ) AS Extension,
COUNT(*) AS Total
FROM ex*.log
GROUP BY Extension
ORDER BY Total DESC

Get authentication and authorization failures

SELECT cs-username,
sc-status,
COUNT(*) AS Total
FROM ex*.log
WHERE cs-username IS NOT NULL AND sc-status BETWEEN 401 AND 403
GROUP BY cs-username,sc-status
ORDER BY Total DESC


Event log queries

Get logon failures from the Security Event Log

SELECT STRCAT( EXTRACT_TOKEN( Strings,
1,
'|'),
STRCAT( '\',
EXTRACT_TOKEN( Strings,
0,
'|'
)
)
) AS User,
COUNT(*) AS Total
FROM Security
WHERE EventType = 16 AND EventCategory = 2
GROUP BY User
ORDER BY Total DESC

Get logon failure statistics from the Security Event Log
To use: "LogParser file:LogonFailureStats.sql?machine=MyMachineName"

SELECT
COUNT(EventID) AS TotalLogonFailures,
TO_LOWERCASE(EXTRACT_TOKEN(Strings,0,'|')) AS User,
TO_LOWERCASE(EXTRACT_TOKEN(Strings,1,'|')) AS Domain,
TO_LOWERCASE(EXTRACT_TOKEN(Strings,5,'|')) AS WorkStation,
CASE TO_INT(EXTRACT_TOKEN(Strings,2,'|'))
WHEN 2 THEN 'Interactive - Intended for users who will be interactively using the machine, such as a user being logged on by a terminal server, remote shell, or similar process.'
WHEN 3 THEN 'Network - Intended for high performance servers to authenticate clear text passwords. LogonUser does not cache credentials for this logon type.'
WHEN 4 THEN 'Batch - Intended for batch servers, where processes may be executing on behalf of a user without their direct intervention; or for higher performance servers that process many clear-text authentication attempts at a time, such as mail or web servers. LogonUser does not cache credentials for this logon type.'
WHEN 5 THEN 'Service - Indicates a service-type logon. The account provided must have the service privilege enabled.'
WHEN 6 THEN 'Proxy - Indicates a proxy-type logon.'
WHEN 7 THEN 'Unlock - This logon type is intended for GINA DLLs logging on users who will be interactively using the machine. This logon type allows a unique audit record to be generated that shows when the workstation was unlocked.'
WHEN 8 THEN 'NetworkCleartext - Windows 2000; Windows XP and Windows Server 2003 family: Preserves the name and password in the authentication packages, allowing the server to make connections to other network servers while impersonating the client. This allows a server to accept clear text credentials from a client, call LogonUser, verify that the user can access the system across the network, and still communicate with other servers.'
WHEN 9 THEN 'NewCredentials - Windows 2000; Windows XP and Windows Server 2003 family: Allows the caller to clone its current token and specify new credentials for outbound connections. The new logon session has the same local identity, but uses different credentials for other network connections.'
WHEN 10 THEN 'RemoteInteractive - Terminal Server session that is both remote and interactive.'
WHEN 11 THEN 'CachedInteractive - Attempt cached credentials without accessing the network.'
WHEN 12 THEN 'CachedRemoteInteractive - Same as RemoteInteractive. This is used for internal auditing.'
WHEN 13 THEN 'CachedUnlock - Workstation logon'
ELSE EXTRACT_TOKEN(Strings,2,'|')
END AS Type
INTO DATAGRID
FROM \%machine%security
WHERE EventID IN (529)
GROUP BY User,Domain,WorkStation,Type
ORDER BY TotalLogonFailures DESC

Get logon successes from the Security Event Log

SELECT STRCAT( EXTRACT_TOKEN( Strings,
1,
'|'),
STRCAT( '\',
EXTRACT_TOKEN( Strings,
0,
'|'
)
)
) AS User,
COUNT(*) AS Total
FROM Security
WHERE EventType = 8 AND EventCategory = 2
GROUP BY User
ORDER BY Total DESC

Get the distribution of EventID values for each Source

SELECT SourceName,
EventID,
MUL(PROPCOUNT(*) ON (SourceName), 100.0) AS Percent
FROM System
GROUP BY SourceName, EventID
ORDER BY SourceName, Percent DESC


UrlScan queries

Get the clients whose requests have been rejected by UrlScan

SELECT ClientIP,
COUNT(*)
FROM URLSCAN
WHERE Comment LIKE 'Url%'
GROUP BY ClientIP

Get all the UrlScan comments

SELECT DISTINCT Comment
FROM URLSCAN


ETW log queries

Get the number of hits for each IIS AppPool
To use: "LogParser file:AppPools.sql -fmode:full -providers:"HTTP Service Trace""

SELECT AppPoolName,
COUNT(*) AS Total
FROM *.etl
WHERE EventTypeName = 'Deliver'
GROUP BY AppPoolName
ORDER BY Total DESC


ADS queries

Get the IIS Virtual Directories that allow WRITE access
To use: "LogParser file:VirtualDirWrite.sql -i:ADS -class:IIsWebVirtualDir"

SELECT ObjectPath
FROM IIS://localhost/W3SVC
WHERE BIT_AND(AccessFlags, 0x02) <> 0


NETMON queries

Get the total network bytes per second

SELECT QUANTIZE(DateTime, 1) AS Second,
SUM(FrameBytes)
FROM myCapture.cap
GROUP BY Second


Generic queries

TEXTWORD Input: Get a list of all the words in these text files ordered by number of times they appear
To use: "LogParser file:Words.sql -i:TEXTWORD"

SELECT Text,
COUNT(*) AS Total
FROM C:*.txt
GROUP BY Text
ORDER BY Total DESC

FS Input: Get a list of all the hidden files in the C: drive ordered by their size
To use: "LogParser file:HiddenFiles.sql -i:FS"

SELECT Path,
Size
FROM C:*.*
WHERE NOT Attributes LIKE '%D%' AND Attributes LIKE '%H%'
ORDER BY Size DESC

FS Input: Get the distribution of file name lengths in the C: drive
To use: "LogParser file:FilenameLengths.sql -i:FS"

SELECT STRLEN(Name) AS NameLen,
COUNT(*) AS Total
FROM C:*.*
GROUP BY NameLen
ORDER BY Total



Helper functions

Get the status code in the "status.substatus" form:

STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) AS Status

Get the request in the "uri-stem?uri-query" form:

STRCAT(cs-uri-stem, REPLACE_IF_NOT_NULL(cs-uri-query,STRCAT('?',cs-uri-query))) AS Request

-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-=-
另外這裡也將 iis 的欄位名列出來方便查找:

#Fields: date time s-sitename s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) sc-status sc-substatus sc-win32-status sc-bytes