Hi Readers,

As agreed in previous post I am sharing the script that I have created for sending quota warning messages when vault quota ia approacing.

This script will read excel file & match the conditions that are defined by if statement.

Prerequisites:-

1. Create subscriptions in SQL reporting so that files are dropped in

C:\EvReports\VaultQuota\ (We have 6 partitions so there are 6 files in my case)

2.  Script should be run from c:\scripts

3. Machine should have exchange snapin installed.

4. EV version is 9.0.3 in our enviornment

5. Change the paths & names according to your enviornment( let me know if you are having issues)

6. Open word & write a message that you want to send it to users(save as Enterprise Vault.htm at location c:\scripts\Enterprise Vault.htm)

##########################################################################
# Author: Vikas Sukhija
# Date:- 05/02/2012
# Description:- This script will read the file generated from Enterprise vault reporting services
#               & will send email to users that are approaching quota #Limit.

#added start & stop transcript to log the whole console
###########################################################################
Start-Transcript

function travrese ($Path)

{
# —————————————————–
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
# —————————————————–

$objExcel = new-object -comobject excel.application
$objExcel.Visible = $False
$objWorkbook = $objExcel.Workbooks.Open($path)
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$date = get-date -format d
# replace \ by –

$date = $date.ToString().Replace(“/”, “-”)

$intRow = 14

Do {

$Name = $objWorksheet.Cells.Item($intRow, 1).Value()
$Alias = $objWorksheet.Cells.Item($intRow, 2).Value()
$Quota = $objWorksheet.Cells.Item($intRow, 4).Value()
$Limit = $objWorksheet.Cells.Item($intRow, 7).Value()

# replace the domain\ with nothing so that we get user id

$Alias =  $Alias.ToString().Replace(“ABCD\”, “”)

Write-host $Alias

# Change conditions accordingly

If ((($Quota -gt 2764) -and  ( $Limit -eq 3072)) -or (($Quota -gt 4608) -and  ( $Limit -eq 5120)) -or (($Quota -gt 5529) -and  ( $Limit -eq 6144)) -or (($Quota -gt 9216) -and  ( $Limit -eq 10240)))
{
$output = “C:\scripts” + “\” + “output” + “\” + “Notify” + $date + “_.log”

Add-content $output $Alias
}

$intRow++

}
While ($objWorksheet.Cells.Item($intRow, 2).Value() -ne $null )

$objExcel.Quit()

$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)

}

$Path1 = “C:\EvReports\VaultQuota\Vault Store Usage by Archive 01VS.xls”
$Path2 = “C:\EvReports\VaultQuota\Vault Store Usage by Archive 02VS.xls”
$Path3 = “C:\EvReports\VaultQuota\Vault Store Usage by Archive 03VS.xls”
$Path4 = “C:\EvReports\VaultQuota\Vault Store Usage by Archive 4VS.xls”
$Path5 = “C:\EvReports\VaultQuota\Vault Store Usage by Archive 05VS.xls”
$Path6 = “C:\EvReports\VaultQuota\Vault Store Usage by Archive 06VS.xls”

travrese $Path1
Sleep 20
travrese $Path2
Sleep 20
travrese $Path3
Sleep 20
travrese $Path4
Sleep 20
travrese $Path5
Sleep 20
travrese $Path6
Sleep 20

##########################################################################################
#Send email function
##########################################################################################

# Added exchange snapin so that to fetch users primary email #address

Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin

$date = get-date -format d

# replace \ by –

$date = $date.ToString().Replace(“/”, “-”)

$input = “C:\Scripts\output\notify” + $date + “_.log”

write-host $input

$UserList = Get-Content $input

$log = “c:\scripts\logs\emailsent_” + $date +”.log”

write-host $log

foreach($user in $UserList)
{

$mailbox = get-mailbox $user
$user = $mailbox.PrimarySmtpAddress
write-host $user
add-content $log $user

$message = new-object System.Net.Mail.MailMessage(“donotreply@abcd.com“, $user)
$message.IsBodyHtml = $True
$message.Subject = “ACTION REQUIRED – Your Email Vault is Full”
$smtp = new-object Net.Mail.SmtpClient(“SmTpserver”)
$body = get-content “c:\scripts\Enterprise Vault.htm”
$message.body = $body
$smtp.Send($message)

}

#################################################################################################

Stop-Transcript