|
#Update Premium Members Rule |
|
#test exchange connectivity and connect to EMS |
|
if((get-command -listimported).Name -notcontains "Get-Mailbox") |
|
{ |
|
. ($env:exchangeinstallpath + 'bin\RemoteExchange.ps1') |
|
Connect-ExchangeServer -auto -ClientApplication:ManagementShell |
|
} |
|
|
|
function Invoke-SQL { |
|
param( |
|
[string] $dataSource = ".\SQLEXPRESS", |
|
[string] $database = "MasterData", |
|
[string] $sqlCommand = $(throw "Please specify a query.") |
|
) |
|
|
|
$connectionString = "Data Source=$dataSource; " + |
|
"Integrated Security=SSPI; " + |
|
"Initial Catalog=$database" |
|
|
|
$connection = new-object system.data.SqlClient.SQLConnection($connectionString) |
|
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection) |
|
$connection.Open() |
|
|
|
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command |
|
$dataset = New-Object System.Data.DataSet |
|
$adapter.Fill($dataSet) | Out-Null |
|
|
|
$connection.Close() |
|
$dataSet.Tables |
|
|
|
} |
|
$SQLQuery = @" |
|
SELECT DISTINCT * FROM |
|
( |
|
SELECT DISTINCT Substring(Contact_Communication_Type.Description,CharIndex('@',Contact_Communication_Type.Description)+1 ,LEN(Contact_Communication_Type.Description)) as Premium_Domains, Company.Company_ID |
|
FROM Contact_Communication_Type |
|
JOIN Contact on contact.Contact_RecID = Contact_Communication_Type.Contact_RecID |
|
JOIN Company on company.Company_RecID = Contact.Company_RecID |
|
JOIN Company_Status on Company_Status.Company_Status_RecID = company.Company_Status_RecID |
|
WHERE Communication_Type_RecID = '1' |
|
AND Company_Status.Description like '%prem%' |
|
AND LEN(Contact_Communication_Type.Description)>1 |
|
AND Company.Company_ID not in ('arrc','connectwise') |
|
|
|
UNION ALL |
|
|
|
SELECT DISTINCT replace(replace(replace(Website_URL,'www.',''),'http://',''),'/','') AS Premium_Domains, Company.Company_ID |
|
FROM Company |
|
JOIN Company_Status ON Company_Status.Company_Status_RecID = company.Company_Status_RecID |
|
WHERE Company_Status.Description like '%prem%' |
|
AND Website_URL != '' |
|
AND Company.Company_ID not in ('arrc','connectwise') |
|
) |
|
AS Premium_Domains |
|
WHERE Premium_Domains NOT IN ('gmail.com','yahoo.com','msn.com','me.com') |
|
ORDER BY Premium_Domains ASC |
|
"@ |
|
|
|
$SQLResult = Invoke-SQL -dataSource "ct-sql1" -database "cwwebapp_chartec" -sqlCommand $SQLQuery |
|
$Premium_Domains = $SQLResult.Premium_Domains |
|
#echo $Premium_Domains |
|
Set-TransportRule -Identity 'Premium Partners Rule' -Name 'Premium Partners Rule' -Comments 'Flag Messages where FROM address contains premium member domain' -FromAddressContainsWords $Premium_Domains |
