Jouledoc Notes on Web

Berikut adalah beberapa script log parser yang akan sering digunakan, untuk hasil reporting.

• Checking Jenis Browser yang akses web server

Select top 50 to_int(mul(100.0,PropCount(*))) as Percent, count(*) as TotalHits, cs(User-Agent) as Browserfrom [LogFileName]group by Browserorder by Totalhits desc

• Melihat total bandwidth harian yang ada di web server

Select To_String(To_timestamp(date, time), ‘MM-dd’) As Day, Div(Sum(cs-bytes),1024) As Incoming(K), Div(Sum(sc-bytes),1024) As Outgoing(K)From [LogFileName]Into BandwidthByDay.gifGroup By Day

• Total Hits per Hours

SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(date,time)),3600) As Hour, cs-uri-stem As Url, COUNT(*) As HitsFROM [LogFileName]WHERE cs-uri-stem LIKE ‘%index.htm%’GROUP BY Url, HourORDER By Url

• Request by URI

SELECT top 80

QUANTIZE(TO_TIMESTAMP(date, time), 3600) as Hour,
TO_LOWERCASE(STRCAT(’/',EXTRACT_TOKEN(cs-uri-stem,1,’/'))) as URI,
COUNT(*) AS RequestsPerHour,
SUM(sc-bytes) AS TotBytesSent,
AVG(sc-bytes) AS AvgBytesSent,
Max(sc-bytes) AS MaxBytesSent,
ADD(1,DIV(Avg(time-taken),1000)) AS AvgTime,
ADD(1,DIV(MAX(time-taken),1000)) AS MaxTime

FROM [LogFileName]
GROUP BY Hour, URI
Having RequestsPerHour > 10
ORDER BY RequestsPerHour ASC

• Top 10 Images by size

Select Top 10 StrCat(Extract_Path(TO_Lowercase(cs-uri-stem)),’/') AS RequestedPath, Extract_filename(To_Lowercase(cs-uri-stem)) As RequestedFile, Count(*) AS Hits, Max(time-taken) As MaxTime, Avg(time-taken) As AvgTime, Max(sc-bytes) As BytesSentFrom [LogFileName]Where (Extract_Extension(To_Lowercase(cs-uri-stem)) IN (’gif’;'jpg’;'png’)) AND (sc-status = 200)Group By To_Lowercase(cs-uri-stem)Order By BytesSent, Hits, MaxTime DESC

• Top 20 clients yang akses ke webserver

Select Top 20 c-ip AS Client, Count(*) AS HitsINTO Chart.gifFROM [LogFileName]GROUP BY c-ipORDER BY Hits Desc

• Waktu yang dibutuhkan untuk load virtual directory

SELECT CASE EXTRACT_TOKEN(cs-uri-stem, 2, ‘/’) WHEN NULL THEN ‘/’ ELSE EXTRACT_PREFIX(cs-uri-stem, 1, ‘/’) END As VDir, QUANTIZE(time-taken, 100) As TimeTaken, COUNT(*) As NumHits
FROM [LogFileName]
GROUP BY TimeTaken, VDirORDER BY TimeTaken, VDir DESC
SELECT CASE EXTRACT_TOKEN(cs-uri-stem, 2, ‘/’) WHEN NULL THEN ‘/’ ELSE EXTRACT_PREFIX(cs-uri-stem, 1, ‘/’) END As VDir, QUANTIZE(time-taken, 100) As TimeTaken, COUNT(*) As NumHits
FROM ex05111606.log
GROUP BY TimeTaken, VDirORDER BY TimeTaken, VDir DESC

Powered by ScribeFire.

Share This