ISA2004 Web Traffic Report for vbscript logparser

'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@" 'Send email repor to
sMailFrom = "admin@ '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\" 'Email attachment path. The will be created under ISA's log folder.

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

Const cdoSendUsingMethod = "/cdo/configuration/sendusing", _
cdoSendUsingPort = 2, _
cdoSMTPServer = "/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("")
If (".\") Then
    (".\")
End If
If (".\tempsum.w3c") Then
    (".\tempsum.w3c")
End If
Set oFile = (".\", ForWriting, True, true)

'Write the HTML head to file suit for IE viewer.
("<HTML>" & vbcrlf & _
"<HEAD>" & vbcrlf & _
"<TITLE> ISA Web Usage Reports</TITLE>" & VbCrLf & _
"<style type=""text/css"">" & vbcrlf)
("" & vbcrlf)

("</style>" & VbCrLf & _
"</HEAD>" & VbCrLf & _
"<body bgcolor=#ffffff>" & VbCrLf)

"<p class = Title> ISA Web Traffic Report - From "&date-startdate-interday&" to "&date-startdate
"<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("")
Set objInputFormat = _
CreateObject(".W3CInputFormat")
SET w3cOutputFormat = (".W3COutputFormat")
= 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"
strQuery, objInputFormat,w3cOutputFormat

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

End If

'Generate report based on temp file.

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

set objLogParser10 = CreateObject("")
Set objInputFormat10 = _
CreateObject(".W3CInputFormat")
' = 2

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

if fl = 0 then

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

' "uri"& ("r-host") & "---" & ("SRdata")

Loop
spacer(12)
(END_TABLE & VbCrLf)

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

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

set objLogParser11 = CreateObject("")
Set objInputFormat11 = _
CreateObject(".W3CInputFormat")
' = 2

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

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

Loop
spacer(12)
(END_TABLE & VbCrLf)

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

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

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

Set objRecordSet1 = (strQuery1, objInputFormat1)
(BEGIN_TABLE & VbCrLf)
mWHeading usertop&" Users' Web Traffic "

Do While Not
Set objRecord1 =
strUsername = ("cs-username")
stt = "'"&strUsername&"'"

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

' ""
' stt &" >>> data: " & ("TSRdata")

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

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

    mWERow
    fl=0
    end if

' "uri"& ("r-host") & "---" & ("SRdata")

Loop

spacer(12)

Loop

(END_TABLE & VbCrLf)

' Write the html end to report.
("</body>")
("</html>")

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

End Sub

Private Sub mWTitle(sContent)

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

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

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

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

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

Private Sub mWBRow
( " <tr>" & VbCrLf)
End Sub

Private Sub mWERow
( " </tr>" & VbCrLf)
End Sub
Private Sub spacer(iHeight)

( _
" <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("")
    Set iConf = CreateObject("")
    Set Flds =

    '// 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

    ' = sMailMessage
    '// Send the message.


     satt

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