Month: February 2014

So I have been “playing” a lot with Exchange, Powershell and SQL.  Essentially on a daily basis, I get an export of all AD Users, mailboxes with stats and then pump it in to SQL, do some magic, and play with Excel Pivot tables and charts to give the powers that be an idea of the mailbox estate.

I wanted to share a simple function that I use run sql queries from powershell, to push or pull data.

Function Run-SqlQuery(
  [string]$SQLserver,
  [string]$SQLdb,
  [string]$SQLcmd,
  [string]$SQLUsername,
  [string]$SQLUserPassword,
  [switch]$verbose=$False){
  $Error.Clear()
  If($verbose){
    Write-Host "SQL Server:..." $SQLserver
    Write-Host "SQL Database:." $SQLdb
    Write-Host "SQL User:....." $SQLUserName
    Write-Host "SQL Pswd:....." $SQLUserPassword
    Write-Host "Verbose:......" $Verbose
    Write-Host "SQLcmd:......."
    Write-Host $SQLcmd -foregroundcolor Yellow
    }

  $Connection = New-Object System.Data.SQLClient.SQLConnection
  IF([string]::IsNullOrEmpty($SQLUserName)){
    #Use Trusted Connection
    Write-Host "Using a trusted connection"  -foregroundcolor Green
    $Connection.ConnectionString ="server=$SqlServer;database=$SqlDb;trusted_connection=true;"
  }ELSE{
    #Use UserName and Password
     Write-Host "Using a specified username & password for connection"  -foregroundcolor Green
    $Connection.ConnectionString ="server=$SqlServer;database=$SqlDb;User ID=$SQLUserName;Password=$SQLUserPassword;"
  }

  $Command = New-Object System.Data.SQLClient.SQLCommand
  $Command.CommandType = [System.Data.CommandType]"Text"
  $Command.Connection = $Connection
  If($Error.Count -gt 0){Exit}
  $Command.CommandText = $SQLCmd 
  $Command.CommandTimeout = 0

  $tmpDT = New-Object "System.Data.DataTable"
  $Connection.Open()
  $Reader = $Command.ExecuteReader()
  $tmpDT.Load($Reader)
  $Connection.Close()

  $i=0;$tmpDT | ForEach{$i++}

  Write-Host $i "Records returned"
  Return $tmpDT
}

Enjoy, have some working examples coming soon too ;-) but in essence run it like:

$sqlquery = "Select * from sometable"
$sqlserver = "mysqlserver"
$sqldb = "mydb"
run-sqlquery -SQLServer $sqlserver -SQLdb $sqldb -SQLcmd $sqlquery

This assumes the account you are using has trusted credentials, otherwise use the -SQLUserName and -SQLUserPassword switches

#Powershell

The place I am working at is using an app called Exclaimer to put signatures on email.  It drives me mad, and it creating mini dumps when it crashes.  Found a server if 10GB of min dump.

Check it out, open control panel and then in the search box type Problem Reports. Then select View all problem reports from the list.  So you can clear them from here, but I wanted to stop them being generated for Exclaimer.

So from Problem reports, click action Centre in the address bar and under maintenance there is setting.  Then at the bottom of Problem Reporting Setting is the option to exclude from reporting.  As you would expect this is just a registry hack, but you have to know what service is running the application you want to exclude.  So check the service and then you can adapt this:

#Start Control Panel and search for "Problem Reporting"
#http://support.microsoft.com/kb/163846
#NT AUTHORITY\LOCAL SERVICE S-1-5-19 
#NT AUTHORITY\NETWORK SERVICE S-1-5-20

#http://blogs.microsoft.co.il/scriptfanatic/2010/05/16/quicktip-additional-powershell-registry-drives/
$null           = New-PSDrive -Name HKU -PSProvider Registry -Root Registry::HKEY_USERS
$path           = "HKCU:\Software\Microsoft\Windows\Windows Error Reporting\ExcludedApplications"
$LocalService   = "HKU:\S-1-5-19\Software\Microsoft\Windows\Windows Error Reporting\ExcludedApplications"
$NetworkService = "HKU:\S-1-5-20\Software\Microsoft\Windows\Windows Error Reporting\ExcludedApplications"

if (-not (Test-Path -Path $path)){$null = New-Item -Path $path}
if (-not (Test-Path -Path $LocalService)){$null = New-Item -Path $LocalService}
if (-not (Test-Path -Path $NetworkService)){$null = New-Item -Path $NetworkService}

$apps  = @()
$apps += "Exclaimer.PolicyProcessingEngine.RemoteDeploymentService.exe"
$apps += "Exclaimer.PolicyProcessingEngine.ConfigurationService.exe"
$apps += "Exclaimer.PolicyProcessingEngine.RemoteDeploymentService.exe"
$apps += "Exclaimer.Connectors.MailRules.ExchangeUpdateService.exe"

ForEach($app in $apps){
  Write-Host $app
  Set-ItemProperty -Path $path           -Name $App -Value 1 -Type DWord
  Set-ItemProperty -Path $LocalService   -Name $App -Value 1 -Type DWord
  Set-ItemProperty -Path $NetworkService -Name $App -Value 1 -Type DWord
}

#Powershell

This has been bugging me for a while.  When testing OWA on an Exchange Server, integrated website tests fail as the domain the server is in, is not in the trust intranet setting for IE.

Thanks to Bing, found some code and adapted it ;-)

if (-not (Test-Path -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains\OSIT.org.loc'))
{
    $null = New-Item -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains\OSIT.org.loc'
}
Set-ItemProperty -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains\OSIT.org.loc' -Name http -Value 1 -Type DWord
Set-ItemProperty -Path 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap\Domains\OSIT.org.loc' -Name https -Value 1 -Type DWord