vbscript logparser的ISA2004 Web流量报告

'ISA 2004 Web Log Query Tool
'Created by mwpq
'Version 1.0
'Date 2007.9.18


On Error Resume Next

Dim startdate, enddate
Dim topweb, topuser,usertop, usertopweb

'Configuration part of the script
'==================================================================
startdate = 1 ' the newest log files to be queried. 1 means one day ago
interday = 7 ' the oldest log files is startdate + interday
' For example startdate =1, interday =7 means script will query log files between 8 days ago and yesterday's.

topweb="Top 20" ' List Top 20 Websites visited. Just change 20 to other No to get what you want like "top 21" will list top 21 websites.
topuser="Top 10" ' List Top 10 users and their total usage.
Usertop = "Top 20" ' List Top 20 Users with their top websites, depend on uesrtopweb. set to "" to list all users web usage
usertopweb = "Top 10"
sMailTo = "mwpq@yahoo.com" 'Send email repor to
sMailFrom = "admin@yourdomain.com 'Email comes from
sMailSub = "ISA Web Traffic Report" 'Email Title
sSMTPServer = "youremailserver" 'Email server
strMessage = "Please see attachment for the ISA Web Traffic Report." 'Email txt body.
satt = "C:\Program Files\Microsoft ISA Server\ISALogs\report.htm" 'Email attachment path. The report.htm will be created under ISA's log folder.

'===================================================================



Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing", _
cdoSendUsingPort = 2, _
cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"

'Create the html reprot and write the html header
'=================================================================================================================
Const BEGIN_TABLE = " TABLE width=100% BORDER=0 CELLSPACING=1 CELLPADDING=2>"
Const END_TABLE = " /TABLE>"
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(".\report.htm") Then
    oFSO.Deletefile(".\report.htm")
End If
If oFSO.FileExists(".\tempsum.w3c") Then
    oFSO.Deletefile(".\tempsum.w3c")
End If
Set oFile = oFSO.OpenTextFile(".\report.htm", ForWriting, True, true)

'Write the HTML head to file suit for IE viewer.
oFile.writeline ("HTML>" vbcrlf _
"HEAD>" vbcrlf _
"TITLE> ISA Web Usage Reports/TITLE>" VbCrLf _
"style type=""text/css"">" vbcrlf)
oFile.writeline ("!--" vbcrlf _
".Title {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 16px; font-weight: bold; color:'#0000cc'}" vbcrlf _
".head {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 14px; font-weight: bold; color:'#ffffff'}" vbcrlf _
".category {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; font-weight: bold; color:'#ffffff'}" vbcrlf _
".result {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; color:'#000000'}" vbcrlf _
".alert {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11px; font-weight: bold; color:'#ff0000'}" vbcrlf _
"a {color: '#000066'; text-decoration:none;}" vbcrlf _
"a:hover {text-decoration:underline}" vbcrlf _
"-->" vbcrlf)

oFile.writeline ("/style>" VbCrLf _
"/HEAD>" VbCrLf _
"body bgcolor=#ffffff>" VbCrLf)

oFile.writeline "p class = Title> ISA Web Traffic Report - From "date-startdate-interday" to "date-startdate
oFile.writeline "p>"
'=================================================================================================================
'End of create html report header part


'build the log file name list
spath = ""

while interday >= 0

dtmDate = date - startdate - interday

'Convert the current Date to UTC
'=================================================================================================================
strDay = Day(dtmDate)
    If Len(strDay) 2 Then
     strDay = "0" strDay
    End If
strMonth = Month(dtmDate)
    If Len(strMonth) 2 Then
     strMonth = "0" strMonth
    End If
strYear = Year(dtmDate)

sdate = strYear strMonth strDay
'=================================================================================================================

stemp2 = "'""ISALOG_"sdate"_WEB_* ""'"
spath = spath stemp2

if interday - startday > 0 then
spath = spath", "
end if

interday = interday - 1
wend





'Create a temp sumary file
set objLogParser = CreateObject("MSUtil.LogQuery")
Set objInputFormat = _
CreateObject("MSUtil.LogQuery.W3CInputFormat")
SET w3cOutputFormat = WScript.CreateObject("MSUtil.LogQuery.W3COutputFormat")
w3cOutputFormat.filemode = 1 ' Set file to overwrite mode
strQuery = "SELECT cs-username, r-host, Sum(add(cs-bytes,sc-bytes)) as SRdata into 'tempsum.w3c' FROM "spath" where sc-Network = 'External' group by cs-username,r-host order by SRdata DESC"
objLogParser.Executebatch strQuery, objInputFormat,w3cOutputFormat

'check tempsum.w3c existed
Set oFSO1 = CreateObject("Scripting.FileSystemObject")
If oFSO1.FileExists(".\tempsum.w3c") Then
oFSO1 = nothing
else
oFSO1=nothing
wscript.echo "Sorry cannot find some of the log files to query! Script Quit."
wscript.quit
End If


'Generate report based on temp file.

'================================================================================
'Generate top web sites.
fl=0
oFile.writeline (BEGIN_TABLE VbCrLf)
mWHeading topweb" Websites"
mWBRow
    mWTitle "Site Name"
    mWTitle "Traffic (MB)"
    mWERow

set objLogParser10 = CreateObject("MSUtil.LogQuery")
Set objInputFormat10 = _
CreateObject("MSUtil.LogQuery.W3CInputFormat")
'objInputFormat.recurse = 2

strQuery10 = "SELECT "topweb" r-host, sum(SRdata) as TSRData FROM 'tempsum.w3c' group by r-host order by TSRdata DESC"
Set objRecordSet10 = objLogParser10.Execute(strQuery10, objInputFormat10)
Do While Not objRecordSet10.AtEnd
Set objRecord10 = objRecordSet10.GetRecord

if fl = 0 then

mWBRow
mWDetail2 objRecord10.GetValue("r-host")
mwDetail2 FormatNumber(objRecord10.GetValue("TSRdata")/1048576,2)
    mWERow
    fl=1
    else
    mWBRow
mWDetail1 objRecord10.GetValue("r-host")
mwDetail1 FormatNumber(objRecord10.GetValue("TSRdata")/1048576,2)
    mWERow
    fl=0
    end if


'wscript.echo "uri" objRecord2.GetValue("r-host") "---" objRecord2.GetValue("SRdata")

objRecordSet10.MoveNext
Loop
spacer(12)
oFile.writeline (END_TABLE VbCrLf)

'================================================================================

'================================================================================
'Generate top user list.
fl=0
oFile.writeline (BEGIN_TABLE VbCrLf)
mWHeading topuser" Users list"
mWBRow
    mWTitle "logon Name"
    mWTitle "Traffic (MB)"
    mWERow

set objLogParser11 = CreateObject("MSUtil.LogQuery")
Set objInputFormat11 = _
CreateObject("MSUtil.LogQuery.W3CInputFormat")
'objInputFormat.recurse = 2

strQuery11 = "SELECT "topuser" cs-username, Sum(SRdata) as TSRdata FROM 'tempsum.w3c' group by cs-username order by TSRdata DESC"
Set objRecordSet11 = objLogParser11.Execute(strQuery11, objInputFormat11)
Do While Not objRecordSet11.AtEnd
Set objRecord11 = objRecordSet11.GetRecord

if fl=0 then
mWBRow
mWDetail2 objRecord11.GetValue("cs-username")
mwDetail2 FormatNumber(objRecord11.GetValue("TSRdata")/1048576,2)
    mWERow
fl =1
else
mWBRow
mWDetail1 objRecord11.GetValue("cs-username")
mwDetail1 FormatNumber(objRecord11.GetValue("TSRdata")/1048576,2)
    mWERow
fl =0
end if
'wscript.echo "uri" objRecord2.GetValue("r-host") "---" objRecord2.GetValue("SRdata")

objRecordSet11.MoveNext
Loop
spacer(12)
oFile.writeline (END_TABLE VbCrLf)

'================================================================================


set objLogParser1 = CreateObject("MSUtil.LogQuery")
Set objInputFormat1 = _
CreateObject("MSUtil.LogQuery.W3CInputFormat")

strQuery1 = "SELECT "usertop" cs-username, Sum(SRdata) as TSRdata FROM 'tempsum.w3c' group by cs-username order by TSRdata DESC"

Set objRecordSet1 = objLogParser1.Execute(strQuery1, objInputFormat1)
oFile.writeline (BEGIN_TABLE VbCrLf)
mWHeading usertop" Users' Web Traffic "

Do While Not objRecordSet1.AtEnd
Set objRecord1 = objRecordSet1.GetRecord
strUsername = objRecord1.GetValue("cs-username")
stt = "'"strUsername"'"


mWBRow
    mWTitle strUsername " ------ Total Web Traffic: " FormatNumber(objRecord1.GetValue("TSRdata")/1048576,2)" MB"
    mWTitle "Traffic (MB)"
    mWERow

'Wscript.echo ""
'wscript.echo stt " >>> data: " objRecord1.GetValue("TSRdata")

set objLogParser2 = CreateObject("MSUtil.LogQuery")
Set objInputFormat2 = _
CreateObject("MSUtil.LogQuery.W3CInputFormat")
'objInputFormat.recurse = 2
fl=0
strQuery2 = "SELECT "usertopweb" r-host, SRdata FROM 'tempsum.w3c' where cs-username =" stt" group by r-host,SRdata"
Set objRecordSet2 = objLogParser2.Execute(strQuery2, objInputFormat2)
Do While Not objRecordSet2.AtEnd
Set objRecord2 = objRecordSet2.GetRecord
if fl=0 then
mWBRow
mWDetail2 objRecord2.GetValue("r-host")
mwDetail2 FormatNumber(objRecord2.GetValue("SRdata")/1048576,2)

    mWERow
    fl=1
    else
    mWBRow
mWDetail1 objRecord2.GetValue("r-host")
mwDetail1 FormatNumber(objRecord2.GetValue("SRdata")/1048576,2)

    mWERow
    fl=0
    end if

'wscript.echo "uri" objRecord2.GetValue("r-host") "---" objRecord2.GetValue("SRdata")

objRecordSet2.MoveNext
Loop
objRecordSet1.MoveNext
spacer(12)

Loop

oFile.writeline (END_TABLE VbCrLf)

' Write the html end to report.
oFile.WriteLine ("/body>")
oFile.WriteLine ("/html>")
oFile.Close


'Methods to create html(title and table) part
'=================================================================================================================
Private Sub mWHeading(sHeading)
            oFile.writeline ( _
" tr>" vbCrLf _
" td colspan=7 bgcolor=#0099cc class=head>" sHeading "/td>" vbCrLf _
" /tr>" VbCrLf)

End Sub

Private Sub mWTitle(sContent)

            oFile.writeline ( _
" TD bgcolor=#0099ff align=left class=category height=14>" sContent "/TD>" VbCrLf)
End Sub

Private Sub mWDetail1(sContent)
            oFile.writeline ( _
" TD bgcolor=#dce3fc align=left class=result height=12>" sContent "/TD>" VbCrLf)
End Sub

Private Sub mWDetail2(sContent)
            oFile.writeline ( _
" TD bgcolor=#e9fbfe align=left class=result height=12>" sContent "/TD>" VbCrLf)
End Sub

Private Sub mWAlert1(sContent)
oFile.writeline ( _
" TD bgcolor=#dce3fc align=left class=alert height=12>" sContent "/TD>" VbCrLf)
End Sub

Private Sub mWAlert2(sContent)
oFile.writeline ( _
" TD bgcolor=#e9fbfe align=left class=alert height=12>" sContent "/TD>" VbCrLf)
End Sub

Private Sub mWBRow
oFile.writeline ( " tr>" VbCrLf)
End Sub

Private Sub mWERow
oFile.writeline ( " /tr>" VbCrLf)
End Sub
Private Sub spacer(iHeight)

oFile.writeline ( _
" tr>td height=" iHeight ">/td>/tr>" VbCrLf)

End Sub

'=================================================================================================================
'End of create html method


'Send email


    Dim iMsg, iConf, Flds    

    '// Create the CDO connections.
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    Set Flds = iConf.Fields

    '// SMTP server configuration.
    With Flds
    .Item(cdoSendUsingMethod) = cdoSendUsingPort

    '// Set the SMTP server address here.
    .Item(cdoSMTPServer) = sSMTPServer
    .Update
    End With

    '// Set the message properties.
    With iMsg
    Set .Configuration = iConf
    .To = sMailTo
    .From = sMailFrom
    .Subject = sMailSub
    .TextBody = strMessage
    End With

    'iMsg.HTMLBody = sMailMessage
    '// Send the message.

    
    iMsg.AddAttachment satt

    iMsg.Send ' send the message.
    Set iMsg = Nothing
    Set iConf = Nothing