UPDATE (12/5/2013): Well it appears that I might be at the end of the line with the in/out board script. Due to some backend changes, the EWSUtil.dll no longer works consistently. Microsoft has some instructions on how to brew your own by extracting XML output from the service that can then be digested into calendar data, but no one at my company uses the in/out board enough to justify the monumental amount of time that would be required to re-code the connector. If you have Exchange 2007/2010 then feel free to read on, but if you have Exchange 2013 this page may only be good as a reference.
UPDATE (5/20/2013): I had to make a few changes so that the free/busy calendar script would run properly on Windows Server 2012/Exchange 2013. First, I changed the line(s) in the batch file to [C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command “.’C:\inetpub\schedule\fb9to5v2.ps1′”]. Secondly I had to change the name of the Exchange server for the $casUrl in the script. Lastly I had to add the line [Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn] as the first line in all the script files that I call.
UPDATE (12/12/12): An update to PowerShell breaks the way that I have this set up, leading to an error of “An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, blah, blah blah“. I did two things after which I got it to work again, but unfortunately I don’t know which one it was that made it work. The first (and easiest) is to force PowerShell to run as an earlier version (in this case the command line to launch the script is [C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -version 2 -PSConsoleFile “C:\Program Files\Microsoft\Exchange Server\v14\bin\exshell.psc1” -command “.’C:\inetpub\schedule\fb9to5v2.ps1′”]. The other thing I did (which I think did the trick, but required PowerShell to clean itself out of memory perhaps as it didn’t work right away) was using a related tip here to add the LoadFromRemoteSources to the powershell.exe.config (I had to create the file). Anyway…
I’ve had some experience with the Windows PowerShell, but it’s been limited to making obvious changes to scripts that have been exported from Scriptomatic. Examples of this would include automatically adding an account into the local admins group account on every PC in a domain so that ‘downstream’ admin tasks will execute, and pulling the hard drive capacity and utilization from all the servers in an organization for virtualization sizing data.
That changed for me when I migrated my workplace to Exchange 2007. Previously we used the old collaborative data objects to build an HTML In/Out schedule board. Manipulating those simple constructs was fairly easy and a lot of coworkers had grown to rely on the web pages exported by the scripts. Unfortunately CDOs don’t work with Exchange 2007, necessitating redoing the scripts in Windows PowerShell.
Fortunately Glen Scales had already taken the ball 90% of the way with his FreeBusy In/Out Board script (Note: to make any of this work you will need the EWSUtil.dll that Glen references). Unfortunately, the last 10% proved to be daunting due to my lacking a high level understanding of PowerShell and (even worse) the general opacity of the programing language itself. There were some minor HTML duties to attend to, which were easy enough, but the most aggravating aspect of Glen’s script was the fact that the names as exported were not in alphabetical order. I figured this had to be easy so I did something like this:
$mbHash = @{$mbHash1 | Sort-Object -property Value}
But then I discovered “no dice” as hash tables are one way with the key and cannot be sorted by value ‘subdata’. How about resorting it using the old trusty nested ‘For’ loops? Although this may be possible in some fashion, hash tables cannot be indexed (i.e. $array[1]=1, but $hashtable[1]=’something’ cannot be done), and since my method relied on a sorted array (as the hash table itself could not be sorted directly) and the hash table entries didn’t work right with the various string commands (since the destination would be cast to ‘hash’), and… well you get the picture. I decided to ‘brute force’ the sorting when I found that the list order in the final table was reliant upon the $key variable later in the script (this had to be ‘discovered’ as there seems to be some Internet rule about NOT putting comments in PowerShell scripts). My successful plan was to co-op the entries that Glen was feeding it with my own sorted list ($key1):
########### Code that alphabetizes the ‘key’ file used to build the In/Out data grid
$mbn = @()
$key1 = @()
#Grab the name list from the hash table and sort it
$mbn=$mbHash.values|sort-object
#write the list out to the hard drive and read it back in
#to convert the data to a regular text array
#This kludgey way was the only way I could convert $mbn to a string array. Is there a better way? I sure hope!
$mbn|out-file mbn.txt
$mbnt=Get-Content mbn.txt
for($i=0; $i -lt $mbnt.Count; $i++){
#$hashidx will hold the unsorted e-mail address list. When the sorted name ($mbnt) equals the value of
# the e-mail key ($mbhash.$hashidx), then add the key (i.e. email address) to $key1 in the proper sequence.
foreach($hashidx in $mbHash.keys){
$a=$mbnt[$i]
$b=$mbhash.$hashidx.ToString()
if($a -eq $b){
$key1+=$hashidx
}
}
}
########### End of alphabetizing code
I then switched out Glen’s line that read:
foreach($key in $fbents.keys){
With:
foreach($key in $key1){
That was my big change, but I include the rest of the code that includes a number of changes, including a ‘key’ grid, logo functionality, and a date stamp:
[void][Reflection.Assembly]::LoadFile(“EWSUtil.dll”)
########### Pause function for troubleshooting
function Pause ($Message=”Press any key to continue…”){
Write-Host -NoNewLine $Message
$null = $Host.UI.RawUI.ReadKey(“NoEcho,IncludeKeyDown”)
Write-Host “”
}
$casUrl = https://mail.domain.com/ews/exchange.asmx
$mbHash = @{ }
########### Added ‘ResultSize unlimited to pull in Forest scope rather than just Domain
get-mailbox -ignoredefaultscope -ResultSize unlimited | foreach-object{
if ($mbHash.ContainsKey($_.WindowsEmailAddress.ToString()) -eq $false){
$mbHash.Add($_.WindowsEmailAddress.ToString(),$_.DisplayName)
}
}
$mbs = @()
$ewc = new-object EWSUtil.EWSConnection($mbMailboxEmail,$false,”USERNAME”,”PASSWORD”,”DOMAIN”,$casUrl)
$drDuration = new-object EWSUtil.EWS.Duration
########### Decreased start time to 06:30
$drDuration.StartTime = [DateTime]::Parse([DateTime]::Now.ToString(“yyyy-MM-dd 06:30”))
$drDuration.EndTime = [DateTime]::Parse([DateTime]::Now.ToString(“yyyy-MM-dd 17:00”))
$batchsize = 100
$bcount = 0
#Note: changed bresult to hash table to take advantage of hash table commands
$bresult = @{}
if ($mbHash.Count -ne 0){
foreach($key in $mbHash.keys){
if ($bcount -ne $batchsize){
$mbs += $key
$bcount++
}
else{
$bresult += $ewc.GetAvailiblity($mbs, $drDuration, 30)
$mbs = @()
$bcount = 0
$mbs += $key
$bcount++
}
}
}
$bresult += $ewc.GetAvailiblity($mbs, $drDuration, 30)
########### Dump system address from the tables
$mbHash.remove(“USERNAME@DOMAIN.com”)
$bresult.remove(“USERNAME@DOMAIN.com”)
$frow = $true
########### Begin build of HTML document
$fbdate=Get-Date
$fbBoard = $fbBoard + “<html>”
$fbBoard = $fbBoard + “<head>”
$fbBoard = $fbBoard + “<title>In & Out Board</title>”
$fbBoard = $fbBoard + “</head>”
$fbBoard = $fbBoard + “<body bgcolor=#FFFFFF text=#0000CC link=#0000CC vlink=#660099 alink=#0000FF>”
### Originally I had put in a shaded color background
#$fbBoard = $fbBoard + “<body bgcolor=#FFFFFF background=graphics/bg.gif text=#0000CC link=#0000CC vlink=#660099 alink=#0000FF>”
#### Below is the code I used to put company art on the page
#$fbBoard = $fbBoard + “<table width=37% border=0 cellspacing=0 align=center>”
#$fbBoard = $fbBoard + “<tr>”
#$fbBoard = $fbBoard + “<td width=31% height=58><img src=graphics/LOGO.gif width=100 height=75></td>”
#$fbBoard = $fbBoard + “<td width=39% height=58>”
#$fbBoard = $fbBoard + “<div align=center><img src=graphics/bg.jpg width=207 height=65></div>”
#$fbBoard = $fbBoard + “</td>”
#$fbBoard = $fbBoard + “</tr>”
#$fbBoard = $fbBoard + “</table>”
$fbBoard = $fbBoard + “<hr align=center width=80%>”
$fbBoard = $fbBoard + “<p align=center><i>Last updated on: ” + $fbdate + “</p>”
########### Code that alphabetizes the ‘key’ file used to build the
########### In/Out data grid
$mbn = @()
$key1 = @()
#Grab the name list from the hash table and sort it
$mbn=$mbHash.values|sort-object
#write the list out to the hard drive and read it back in
#to convert the data to a regular text array
$mbn|out-file mbn.txt
$mbnt=Get-Content mbn.txt
for($i=0; $i -lt $mbnt.Count; $i++){
#$hashidx will hold the unsorted e-mail address list. When the sorted name ($mbnt) equals the value of
# the e-mail key ($mbhash.$hashidx), then add the key (i.e. email address) to $key1 in the proper sequence.
foreach($hashidx in $mbHash.keys){
$a=$mbnt[$i]
$b=$mbhash.$hashidx.ToString()
if($a -eq $b){
$key1+=$hashidx
}
}
}
########### End of alphabetizing code
foreach($fbents in $bresult){
foreach($key in $key1){
if ($frow -eq $true){
$fbBoard = $fbBoard + “<table><tr bgcolor=`”#95aedc`”>” +”`r`n”
$fbBoard = $fbBoard + “<td align=`”center`” style=`”width=200;`” ><b>User</b></td>” +”`r`n”
for($stime = $drDuration.StartTime;$stime -lt $drDuration.EndTime;$stime = $stime.AddMinutes(30)){
$fbBoard = $fbBoard + “<td align=`”center`” style=`”width=50;`” ><b>” + $stime.ToString(“HH:mm”) + “</b></td>” +”`r`n”
}
$fbBoard = $fbBoard + “</tr>” + “`r`n”
$frow = $false
}
for($stime = $drDuration.StartTime;$stime -lt $drDuration.EndTime;$stime = $stime.AddMinutes(30)){
$valuehash = $fbents[$key]
if ($stime -eq $drDuration.StartTime){
#Note: added missing ‘<tr>’
$fbBoard = $fbBoard + “<tr><td bgcolor=`”#CFECEC`”><b>” + $mbHash[$valuehash[$stime.ToString(“HH:mm”)].MailboxEmailAddress.ToString()] + “</b></td>” + “`r`n”
}
switch($valuehash[$stime.ToString(“HH:mm”)].FBStatus.ToString()){
“0” {$bgColour = “bgcolor=`”#C0C0C0`””}
“1” {$bgColour = “bgcolor=`”#52F3FF`””}
“2” {$bgColour = “bgcolor=`”#153E7E`””}
“3” {$bgColour = “bgcolor=`”#4E387E`””}
“4” {$bgColour = “bgcolor=`”#98AFC7`””}
“N/A” {$bgColour = “bgcolor=`”#98AFC7`””}
}
$title = “title=”
if ($valuehash[$stime.ToString(“HH:mm”)].FBSubject -ne $null){
if ($valuehash[$stime.ToString(“HH:mm”)].FBLocation -ne $null){
$title = $title + “`”” + $valuehash[$stime.ToString(“HH:mm”)].FBSubject.ToString() + ” ” + $valuehash[$stime.ToString(“HH:mm”)].FBLocation.ToString() + “`” ”
}
else {
$title = $title + “`”” + $valuehash[$stime.ToString(“HH:mm”)].FBSubject.ToString() + “`” ”
}
}
else {
if ($valuehash[$stime.ToString(“HH:mm”)].FBLocation -ne $null){
$title = $title + “`”” + $valuehash[$stime.ToString(“HH:mm”)].FBLocation.ToString() + “`” ”
}
}
if($title -ne “title=”){
$fbBoard = $fbBoard + “<td ” + $bgColour + ” ” + $title + “></td>” + “`r`n”
}
else{
$fbBoard = $fbBoard + “<td ” + $bgColour + “></td>” + “`r`n”
}
}
$fbBoard = $fbBoard + “</tr>” + “`r`n”
}
}
$fbBoard = $fbBoard + “</table>” + ” ”
$fbBoard = $fbBoard + “<br>”
########## Key grid
$fbBoard = $fbBoard + “<center><table align=center cellpadding=’0′ cellspacing=’0′ cols=’2′ width=’80%’ bordercolor=’#FFFFFF’ border=’1′ bordercolorlight=’#FFFFFF’ bordercolordark=’#FFFFFF’>”
$fbBoard = $fbBoard + “<tr valign=’top’>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#52F3FF’> </td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’> Tentative</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#C0C0C0′> </td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’> Free</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#153E7E’> </td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’> Busy</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#4E387E’> </td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’> Out of Office</font></th>”
$fbBoard = $fbBoard + “<td width=’2%’ bgcolor=’#000000′> </td>”
$fbBoard = $fbBoard + “<td align=’left’ width=’18%’ valign=’top’><font color=’#000000′ face=’Arial, Helvetica, sans-serif’> No Information</font></th>”
$fbBoard = $fbBoard + “</tr>”
$fbBoard = $fbBoard + “</table></center>”
$fbBoard = $fbBoard + “<br>”
$fbBoard = $fbBoard + “<table border=’0′ cellspacing=’10’ height=’12’ width=’100%’>”
$fbBoard = $fbBoard + “<tr>”
$fbBoard = $fbBoard + “<a name=inout></a><b><u>More…</u></b><br>Black blocks on the grid indicate that nothing has been scheduled within a couple weeks or so of today’s date. In order to view more detail or days in the future you will need to use the robust features from within Outlook. This can be done most easily by <a href=http://support.microsoft.com/kb/293162>adding a group Calendar</a> (first tip). ”
$fbBoard = $fbBoard + “</tr>”
$fbBoard = $fbBoard + “</table>”
$fbBoard = $fbBoard + “</body>”
$fbBoard = $fbBoard + “</html>”
$fbBoard | out-file “inout.htm”
To schedule this Exchange 2007 script I put the following command in a batch file and referenced it in the task scheduler:
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -PSConsoleFile “C:\Program Files\Microsoft\Exchange Server\bin\exshell.psc1” -command “.’fb9to5v2.ps1′”
An example of the output from the full script is here.
Extra Credit! Later I also added code and changed two lines so that I could build multiple web pages showing info for future dates. I’m sure that it’s possible to feed the days forward desired in as a command line variable, but I don’t have time to work on that at the moment. The applicable code is:
########### Add a day
$a=[DateTime]::Now.AddDays(1)
[string]$aYear=$a.year
[string]$aDay=$a.day
[string]$aMonth=$a.month
$StartDate=$aYear + “-” + $aMonth + “-” + $aDay + ” 06:30″
$EndDate=$aYear + “-” + $aMonth + “-” + $aDay + ” 17:30″
# snag dates for web header (not shown)
[string]$WebDate=$aMonth + “/” + $aDay + “/” + $aYear
$WebDateDay=$a.dayofweek
$b=get-date
$WebDateNow=$b.ToShortDateString()
$ewc = new-object EWSUtil.EWSConnection($mbMailboxEmail,$false,”USERNAME”,”PASSWORD”,”DOMAIN”,$casUrl)
$drDuration = new-object EWSUtil.EWS.Duration
########### Decreased start time to 06:30, changed lines to process time strings
$drDuration.StartTime = [DateTime]::Parse($StartDate)
$drDuration.EndTime = [DateTime]::Parse($EndDate)
I’ll have to leave it to individuals to decide how to integrate the code.