'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