Environment: SQL Server, Windows Server, vb-script, EAS, Exchange
Description: This script is run as nightly job to notify users if their email EAS mailbox storage is nearing quota. The assumption is that the EAS storage for email attachments will fill up first and that the users have been getting a weekly report of the usage: EASUsageAudit.vbs
Features:
- Configurable quota to begin sending notification
- Custom wording to send in email
- bcc notification to administrator(s) of email sent
- Write to application event log for easy trouble-shooting
Sample:
Code Listing:
'=========================================================================
' FILE: EASNearQuotaEmail.vbs
' AUTHOR: Robert Lawson
' COMPANY: Soka University of America
' DATE: 6/01/2006 Robert Lawson, Creation Date
' 11/12/2006 Added EAS VIEW_STORAGE_BY_USER_REF2, not "as" correct,
' but is used for enforcing quota.
' COMMENT: Audit EAS Usage and notification
' - Set value of "conSendMail" if you send mail or not, also conDEBUG
' - Relies heavily on t-sql view VWEASUserQuotaUsage
' - Server run on has to be allow SMTP relay on Exchange
' - VWEASUserQuotaUsage can not calc percent, must be done in program
'=========================================================================
option explicit
On Error Resume Next
const conScriptName = "EASNearQuotaEmail.vbs"
const conDEBUG = TRUE
const conSendMail = FALSE ' TRUE = will send email, FALSE=will not
Dim objShell, strMsg, intLoc
Dim objConn, objRS, objRS2, strSQL, strConn
Dim numQuotaSize, numMsgSize, numEASPct, numMsgCount
Dim numREF_COUNT, numUNCOMPRESSEDSIZESUM, numCOMPRESSEDSIZESUM
Dim strQuotaSize, strMsgSize, strEASPct, strUserName
Dim strMailBody, numCount, numUsers, strUSERID, strDetail, strQuotaDesc
Dim iMsg, iConf, Flds
Dim strTo, strBCC, strCC, strFrom, strSubject, strTextBody
Const conEASQuotaUnlimited = -1
Const conMaxEASNotificationPct = .95 ' EAS percent to warn at (1=100%)
' Do not set your smtp server information here.
Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing", _
cdoSendUsingPort = 2, _
cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const conSMTPserver = "smtp.campus.edu"
Const conFromUser = "E-Admini@campus.edu"
Const conEmailAdminUser = "robert.lawson@campus.edu" ' comma seperated
' Setup
Set objShell = CreateObject( "WScript.Shell" )
' Write event log that you started
strMsg = conScriptName & ": Begin execution"
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg
' Email stuff (SMTP)
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Fields.Item(cdoSendUsingMethod) = cdoSendUsingPort
iConf.Fields.Item(cdoSMTPServer) = conSMTPserver
iConf.Fields.Update
' DB stuff
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
Set objRS2 = CreateObject("ADODB.Recordset")
strConn = "Provider=SQLOLEDB; SERVER=YourServer; DATABASE=DBname;Integrated Security = SSPI"
objConn.Open strConn
' Get summary for active EAS users with space used
strSQL = "SELECT USERID, UserName, QuotaSize, QuotaType, Email, MsgCount, MsgSize " & _
"FROM VWEASUserQuotaUsage"
strSQL = strSQL & " WHERE QuotaSize > 0" ' Not unlimited
' strSQL = strSQL & " AND USERID = 497" ' **** DEBUG ****
if conDEBUG then Wscript.Echo "strSQL = " & strSQL
objRS.Open strSQL, objConn, 2
if (objRS.BOF or objRS.EOF) then
strMsg = conScriptName & ": No EAS records to process"
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg
WScript.Quit
end if
numUsers = 0
do while not objRS.EOF
strUSERID = objRS("USERID")
strSQL = "SELECT USERID, REF_COUNT, UNCOMPRESSEDSIZESUM, COMPRESSEDSIZESUM " & _
"FROM VIEW_STORAGE_BY_USER_REF2 WHERE USERID = '" & strUSERID & "'"
if conDEBUG then Wscript.Echo "strSQL = " & strSQL
objRS2.Open strSQL, objConn, 2
if (objRS2.BOF or objRS2.EOF) then
strMsg = conScriptName & ": Unable to open VIEW_STORAGE_BY_USER_REF2 for user " & strUSERID
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg
WScript.Quit '******????
end if
numREF_COUNT = cDbl(objRS2("REF_COUNT")) ' EAS Total email count
numUNCOMPRESSEDSIZESUM = cDbl(objRS2("UNCOMPRESSEDSIZESUM")) ' EAS MB size un-compressed
numCOMPRESSEDSIZESUM = cDbl(objRS2("COMPRESSEDSIZESUM")) ' EAS MB size compressed
strUserName = RTRIM(objRS("UserName"))
numQuotaSize = cDbl(objRS("QuotaSize"))
' numMsgCount = cDbl(objRS("MsgCount")) Use numREF_COUNT
' numMsgSize = cDbl(objRS("MsgSize")) Use numUNCOMPRESSEDSIZESUM
if numQuotaSize <= 0 then numEASPct = 1.0 else numEASPct = numUNCOMPRESSEDSIZESUM / numQuotaSize end if if numEASPct > conMaxEASNotificationPct then
numUsers = numUsers + 1
if conDEBUG then Wscript.Echo "--USERID/Name = " & strUSERID & "/" & strUserName & " pct: " & numEASPct
if numQuotaSize = conEASQuotaUnlimited then
strQuotaDesc = " (Unlimited)"
strEASPct = " "
else
strQuotaDesc = " "
strEASPct = FormatPercent(numEASPct,0,false,false,true)
end if
strMsgSize = FormatNumber(numUNCOMPRESSEDSIZESUM,0,false,false,true)
strQuotaSize = FormatNumber(numQuotaSize,0,false,false,true)
strMsgCount = FormatNumber(numREF_COUNT,0,false,false,true)
' Create header FormatNumber(objRS("QuotaSize"),0,false,false,true)
strMailBody = ""
strMailBody = _
"You have received this message because your mailbox is approaching its size limit." & vbCrLF & _
"Your mailbox size is currently " & strEASPct & " full (size (MB): " & strMsgSize & "; limit (MB): " & strQuotaSize & "). " & _
"When your mailbox fills up you will not be able to send nor receive any more email. " & _
"Please do the following to reduce mailbox volume:" & vbCrLF & _
"1. Delete items in the Sent-Items folder" & vbCrLF & _
"2. Empty the Deleted-Items folder" & vbCrLF & _
"3. Sort messages by the attachment icon and delete messages with large attachments" & vbCrLF & _
"Mailbox space can be freed up most efficiently by focusing on deleting emails with attachments. In most cases a large portion of a mailbox’s volume is occupied by a collection of emails with attachments like photos, music, videos and scans. This also includes emails you sent to others and remain in your “Sent Items” folder. Note the mailbox size and limits refer to the EAS system." & vbCrLF & _
"Thank you" & vbCrLF & "SUA-Email-Administrators" & vbCrLF & vbCrLF & _
"source: " & conScriptName & vbCrLF
' Prep email
strTo = objRS("Email")
strBCC = conEmailAdminUser
strCC = ""
strFrom = conFromUser
strSubject = "Your Mailbox is Approaching its Size Limit" & strUserName
strTextBody = strMailBody
if conDEBUG then Wscript.Echo strMailBody
' Send thu sucku
if conSendMail then
With iMsg
Set .Configuration = iConf
.To = strTo
if len(strBCC) > 0 then .BCC = strBCC
if len(strCC) then .CC = strCC
.From = strFrom
.Subject = strSubject
.TextBody = strTextBody
End With
iMsg.Send ' send the message.
if err.number <> 0 then
intLoc = 5
strMsg = conScriptName & ": Error @ " & intLoc & ". smtp mail send failed for " & strUSERID
DoError(strMsg)
else ' at some point write to db log
strMsg = conScriptName & ": email sent to " & strTo & ", subject: " & strSubject
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg
end if ' ierr
end if ' conSendMail
end if ' numEASPct
objRS.movenext
objRS2.Close
Loop ' All users
strMsg = conScriptName & ": end execution. Total emails sent = " & numUsers
if conDEBUG then Wscript.Echo strMsg
objShell.LogEvent 0,strMsg
objRS.Close
objConn.Close
' ======================================================
Function DoError(strErrMsg)
On Error Resume Next
Dim objShell
' wscript.Echo strErrMsg
Set objShell=CreateObject("wscript.shell")
objShell.LogEvent 1,strErrMsg
End Function
No comments:
Post a Comment