Very much like the learning atmosphere here, but also admire the technical level of two moderators, I hope to be here with you a lot of exchanges.
As a gift, share the script that you wrote a while ago to generate an Isa WEB traffic report. Description, the school-based is the starting point and only here, I did not in the chrysanthemum or anywhere in the hair. You can change the script to suit your request, reprint please keep the author information thank you.
Many of my customers want to be able to get a daily or weekly report of their employees browsing the web, such as the sites visited, traffic, and so on, and they all use ISA2004 as Web Proxy. Since ISA2004 's report doesn't meet their requirements, I wrote this script to generate reports. Please read Readme.txt before use
' ISA 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 would query log files between 8 days ago and yesterday ' s.
topweb= "Top" ' List Websites visited. Just to the other No. What you are want like "top" would list top websites.
topuser= "Top" ' List of users and their total usage.
Usertop = "Top" ' List of 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" ' Email Title
Ssmtpserver = "Youremailserver" ' Email server
strmessage = "Please click Attachment for the ISA WEB traffic." ' Email txt body.
Satt = "C:\Program Files\Microsoft ISA server\isalogs\report.htm" ' Email attachment path. The report.htm would be created under ISA ' 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 '} ' & VBC Rlf & _
". 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-s Tartdate
Ofile.writeline "<p>"
'============================================================================================================== ===
' End of Create HTML # header part
' Build the log file name list
spath = ""
While Interday >= 0
Dtmdate = Date-startdate-interday
' Convert ' 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&" wh ere 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 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 as 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 as 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 as 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 as 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 the '.
Ofile.writeline ("</body>")
Ofile.writeline ("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 the 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 is 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
