#MsExchange Versions

#IAMMEC

So if you want to find out the version of Exchange you are running you can always run:

Get-ExchangeServer | Select Name, ServerRole, Edition, AdminDisplayVersion

Now this is cool, but it only shows the major version and not the CU.

You can find exchange build information from two main places:

I have dropped the information into a CSV file [ExchangeBuilds].   I found that updating a CSV file with new builds was easier than editing code all the time.

Now code below to create a summary matrix.  Enjoy

#Import the csv and covert to a hash table
$ExchangeBuilds = import-csv .\ExchangeBuilds.csv
$BuildMatrix = @{}
ForEach($Item in $ExchangeBuilds){
  $BuildMatrix.Add($item.Build, $item.version)
}

#Get exchange servers
Get-ExchangeServer | Select Name, ServerRole, Edition, AdminDisplayVersion

#Loop the servers to create the matrix
$matrix =@()
$ExchangeServers = Get-ExchangeServer | Sort Name
ForEach($Item in $ExchangeServers){
  $tmpServer = $item.Name + "." + $item.domain
  Write-Host $tmpServer 
  $tmpMatrix = "" | Select Name, Roles, Edition, FileVersion,UpdateRollup
  $tmpMatrix.Name = $tmpServer 
  $tmpMatrix.Roles = $Item.ServerRole
  $tmpMatrix.Edition = $Item.Edition

  Switch($item.AdminDisplayVersion.Major){
     6 {$key="SOFTWARE\Microsoft\Exchange\Setup"}
     8 {$key="SOFTWARE\Microsoft\Exchange\Setup"}
    14 {$Key="SOFTWARE\Microsoft\ExchangeServer\v14\Setup"}
  }

  #Find the exchange binary path
  $type = [Microsoft.Win32.RegistryHive]::LocalMachine
  $regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $tmpServer )
  $regKey = $regKey.OpenSubKey($key)

  #work out the file we need to use
  Switch($item.AdminDisplayVersion.Major){
     6      {$tmpPath = $regKey.GetValue("Services");      $tmpFile = "mad.exe";    $tmpPath += "\bin\"}
    Default {$tmpPath = $regKey.GetValue("MsiInstallPath");$tmpFile = "ExSetup.exe";$tmpPath += "bin\"}
  }

  #Get the file verision of mad.exe or exsetup.exe
  $tmpPath = $tmpPath.replace(":", "$")
  $tmpunc  = "\\" + $tmpServer + "\" + $tmpPath + $tmpFile

  IF (test-path $tmpunc){
    $tmpVer = (dir "$tmpunc").VersionInfo.FileVersion
    $tmpMatrix.FileVersion = $tmpVer
    $tmpMatrix.UpdateRollup = $BuildMatrix.$tmpver
  }
  $Matrix += $tmpMatrix
}
$Matrix

#MsExchange export GAL and Addresslists to CSV

#IAMMEC

So I was asked to export the GAL so we could give it to another part of the company that has not been assimilated yet.

GAL

Get-GlobalAddressList | ForEach{$n=$_.Name;$p=$pwd.path+"\"+$n+".csv";$l=$_.LdapRecipientFilter;$n;Get-Recipient -RecipientPreviewFilter $l -resultsize unlimited| Where-Object {$_.HiddenFromAddressListsEnabled -ne $true} | Select-Object Name,PrimarySmtpAddress | Export-CSV $p -NoTypeInformation}

Address List

Get-AddressList | ForEach{$n=$_.Name;$p=$pwd.path+"\"+$n+".csv";$l=$_.LdapRecipientFilter;$n;Get-Recipient -RecipientPreviewFilter $l -resultsize unlimited| Where-Object {$_.HiddenFromAddressListsEnabled -ne $true} | Select-Object Name,PrimarySmtpAddress | Export-CSV $p -NoTypeInformation}

Both lumps of code export to a csv file in the current path ($p)

Ejnjoy

#Powershell, SQL and #BlackBerry

So following on from my last post (http://blog.flaphead.com/2014/02/12/powershell-and-sql/) I wanted to add some SQL queries to it.

BlackBerry hosts all it’s information in a SQL database.  Now if you want to get a list of your BlackBerry users you can fire this SQL query at the BlackBerry SQL Database:

$SQLCommand = "
SELECT [ServerConfig]. [ServiceName]
      , [UserConfig]. [DisplayName]
      , [UserConfig]. [UserName]
      , [userconfig]. [MailboxSMTPAddr] as [SMTPAddress]
      , [SyncDeviceMgmtSummary]. [ModelName]
      , [SyncDeviceMgmtSummary]. [PhoneNumber]
      , [SyncDeviceMgmtSummary]. [PlatformVer]
      , [SyncDeviceMgmtSummary]. [IMEI]
      , [SyncDeviceMgmtSummary]. [HomeNetwork]
      , [SyncDeviceMgmtSummary]. [AppsVer]
      , [UserConfig]. [PIN]
      , [ITPolicy2]. [PolicyName]
      , [UserConfig]. [MailboxSMTPAddr]
      , [UserConfig]. [MailboxDN]
      , [UserConfig]. [ServerDN] as [ExchangeServer]
      , [UserConfig]. [AgentId]
      , [UserConfig]. [RoutingInfo]
      , [UserStats]. [MsgsPending]
      , [UserStats]. [LastFwdTime]
      , [UserStats]. [LastSentTime]
      , CASE [UserStats] . [Status]
            WHEN 14 THEN 'Failed to start'
            WHEN 13 THEN 'Stopped'
            WHEN 12 THEN 'Running'
            WHEN 9  THEN 'Redirection disabled'
            WHEN 7  THEN 'No PIN'
            WHEN 0  THEN 'Initializing'
            ELSE 'Unknown [' + CONVERT (varchar , [UserStats]. [Status] ) + ']'
        END AS UserStatus
  FROM [dbo] . [UserConfig]
   LEFT OUTER JOIN [dbo] .[UserStats]
    ON [UserConfig]. [Id] =[UserStats] . [UserConfigId]
   LEFT OUTER JOIN [dbo] .[ITPolicy2]
    ON [UserConfig]. [ITPolicy2Id] =[ITPolicy2] . [Id]
   LEFT OUTER JOIN [dbo] .[ServerConfig]
    ON [UserConfig]. [ServerConfigId] =[ServerConfig] . [Id]
   LEFT OUTER JOIN [dbo] .[SyncDeviceMgmtSummary]
    ON [UserConfig]. [Id] =[SyncDeviceMgmtSummary] . [UserConfigId]
"

$tmpCSVData = Run-SqlQuery "$sql" "$db" $SQLCommand

So this will give you an array ($tmpCSVData) with all your BlackBerry users in it ;-)

Now for some extra Powershell love, what you can do it look at the LastFwdTime and say if the user device has not forwarded any mail in say the last 60 days, create a CSV file.

$today = Get-Date
$AgeInDays = 60
$removalDate = $today.AddDays(-$AgeInDays)

$ToRemove = $tmpCSVData | where {$_.LastFwdTime -le $removaldate} | Select @{Expression={$_.SMTPAddress};Label="-u"}
$tcsv = $ToRemove | ConvertTo-Csv -NoTypeInformation
$csv= $tcsv | ForEach{$_.replace('"','')}
$csv | Out-File InactiveBlackBerryUsers.csv
$csv | Out-File $RootFolder\InactiveBlackBerryUsers.csv

Yeah and? well the CSV can be pushed in to the BESUserAdminClient resource kit tool, and you can delete the inactive accounts using the csv!

Yeah Baby!

#Powershell and SQL

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

Exchange PrepareAD

#MsExchange

If you can remember back when you built you Exchange environment, you ran setup /PrepareAD and you may or not have noticed this message:

Setup will prepare the organization for Exchange 2013 by using 'Setup /PrepareAD'. No Exchange 2010 server roles have been detected in this topology. After this operation, you will not be able to install any Exchange 2010 servers.
For more information, visit: http://technet.microsoft.com/library(EXCHG.150)/ms.exch.setupreadiness.NoE14ServerWarning.aspx

Now if you forgot about this, your are screwed. In this case I would not be able to install any Exchange 2010 in the future.

Wouldn’t it be nice if Microsoft made you press any key or something, so if you have a DOH! moment you can fix things?!

Parsing Message Headers using Powershell

#MsExchange #Powershell

So sometimes I want or need to look at the message headers of an email to work out where it comes from.  You can use something like http://mxtoolbox.com/public/tools/emailheaders.aspx to do that, but I thought, you must be able to so this with Windows Powershell.  Guess what you can ;-)

Meet Parse-EmailHeaders.ps1.  Now I cheated a little by using Sapien Powershell Studio to build the GUI as I couldn’t be bothered to manually create the GUI in notepad ;-)

So fireup powershell and run the script.

Parse-EmailHeader(1)

Essentially, in outlook get the message header of an email and paste it in to the GUI box and click on the Parse Button.

Parse-EmailHeader(2)

In the shell you will see what it’s up to.

PS C:\PS> .\Parse-EmailHeader.ps1
Looking for C:\PS\Parse-EmailHeader_KnownIPs.csv
 - Found
 --  60  Found
Discovering IP Geo Information
 + / +
[fe80::4100:46ba:3a5c:54ce] + / + [fe80::b42c:7f9:e0ef:23d%10]
10.11.123.10 + / + 10.11.250.20
91.206.176.84 + / + 10.47.216.189
2a01:111:f400:7e04::177 + / + 2a01:111:e400:8814::26
10.242.16.26 + / + 10.242.136.153
213.199.154.78 + / + 10.174.65.75
2a01:111:f400:7e00::105 + / + 2a01:111:e400:1000::18
10.242.64.18 + / + 10.242.68.20
unknown unknown GB GB GB unknown unknown GB unknown unknown
http://maps.googleapis.com/maps/api/staticmap?size=800x800&sensor=false&path=color:0xff0000ff|weight:5|UB40SL|E149YY&mar
kers=size:mid%7Ccolor:red%7CUB40SL%7CE149YY
Looking for C:\PS\Parse-EmailHeader_KnownIPs.csv
 - Found
 --  60  Found
Discovering IP Geo Information
 + / +
 + / + 10.180.165.174
 + / + 10.114.67.131
 + / + 10.152.6.199
 + / + mail-la0-f45.google.com
relay146.msgfocus.com. [86.54.102.146] + / + mx.google.com
209.85.215.45 + / + 10.47.216.92
2a01:111:f400:7e04::108 + / + 2a01:111:e400:9414::19
10.141.8.147 + / + 10.242.141.17
unknown unknown unknown unknown unknown unknown unknown CL unknown unknown
http://maps.googleapis.com/maps/api/staticmap?size=800x800&sensor=false&path=color:0xff0000ff|weight:5|UB40SL|E149YY&mar
kers=size:mid%7Ccolor:red%7CUB40SL%7CE149YY
Looking for C:\PS\Parse-EmailHeader_KnownIPs.csv
 - Found
 --  60  Found
Discovering IP Geo Information
 + / +

http://maps.googleapis.com/maps/api/staticmap?size=800x800&sensor=false&path=color:0xff0000ff|weight:5|UB40SL|E149YY&mar
kers=size:mid%7Ccolor:red%7CUB40SL%7CE149YY
PS C:\PS> .\Parse-EmailHeader.ps1
Looking for C:\PS\Parse-EmailHeader_KnownIPs.csv
 - Found
 --  60  Found
Discovering IP Geo Information
 + / +
[10.0.0.189:8127] helo=HOME-?? + / + Momo-dev:3.5.1.0
unknown unknown UA
http://maps.googleapis.com/maps/api/staticmap?size=800x800&sensor=false&path=color:0xff0000ff|weight:5|UB40SL|E149YY&mar
kers=size:mid%7Ccolor:red%7CUB40SL%7CE149YY

The end result is a saved file called Parse-EmailHeaders.html which will automagically open once the parsing is complete.

Parse-EmailHeader(3)

Now, as an added bonus, if a file called Parse-EmailHeaders_KnownIPs.csv exists, it will use it to populate Country and City of IP’s you know about.

IP,Country,City
2a01:111:e400:8000::28,Microsoft EOP (NL),Amsterdam
10.242.80.27,Microsoft EOP (NL),Amsterdam
10.242.77.156,Microsoft EOP (NL),Amsterdam
10.16.249.240,Microsoft EOP (IE),Dublin

Why have this?  Well I put all my known Exchange Hub and Edge servers in, with the Datacentre location so I can see the path it took.  Create the csv file in the same folder as the .ps1 and chop and change as you wish.

Hope you like

Parse-EmailHeader.ps1 (zip)

Happy Birthday to me!

Gawd how old an I am :-|

Another year closer to retirement … but still long way to go!

Add Excluded Applications to Windows Error Reporting

#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
}

Using powershell to add local intranet setting in IE

#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

Fix-ResynchronizingOrInitializing.ps1

#MsExchange #Powershell

Sometimes you get databases in a DAG with an Initializing or Resynchronizing state.  The way I have found to fix this is to Suspend the database copy and then resume it.

If you have quite a few databases its a pain in the butt using the console to do this .. so I wrote this.  You have to run it on the server that has a copy in either Initializing or Resynchronizing state.

##########################################################################################
#Load the Exchange 2010 bits & bobs
#########################################################################################
$xPsCheck = Get-PSSnapin | Select Name | Where {$_.Name -Like "*Exchange*"}
If ($xPsCheck -eq $Null) {Write-Host "Loading Exchange Snapin"; Add-PsSnapin Microsoft.Exchange.Management.PowerShell.e2010}

$gmdcs = Get-MailboxDatabaseCopyStatus
ForEach($item in $gmdcs){
  $tmpstatus = $item.Status
  $tmpName   = $item.Name
  Write-Host $tmpName "["$tmpStatus"]"
  IF (($tmpStatus -eq "Resynchronizing") -OR ($tmpStatus -eq "Initializing")){
    Write-Host "- Suspending"
    suspend-MailboxDatabaseCopy -Identity $tmpName -Confirm:$False
    Write-Host "- Resuming"
    resume-MailboxDatabaseCopy -Identity $tmpName
  }
}