Wednesday, March 31, 2010

FreeBusy Board for the Exchange EWS Managed API using Powershell

This is a port of one of my previous EWS powershell scripts that produces a freebusy board using EWS. The advantage of this one is that it uses the EWS Managed API and it also exposes the private enums on the appointment details so it avoids publishing any appointments that are marked as private. For those familiar with using the EWS Managed API its also a lot easier to customize to your own needs. For details on how and why it work please see the previous post linked up the top.

I’ve put a download the code here the code itself look like

$dllpath = "C:\Program Files\Microsoft\Exchange\Web Services\1.0\Microsoft.Exchange.WebServices.dll"
[void][Reflection.Assembly]::LoadFile($dllpath)
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)

$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"
$aceuser = [ADSI]$sidbind

$service.AutodiscoverUrl($aceuser.mail.ToString())

$mbHash = @{ }

$tmValHash = @{ }
$tidx = 0
for($vsStartTime=[DateTime]::Parse([DateTime]::Now.ToString("yyyy-MM-dd 0:00"));$vsStartTime -lt [DateTime]::Parse([DateTime]::Now.ToString("yyyy-MM-dd 0:00")).AddDays(1);$vsStartTime = $vsStartTime.AddMinutes(30)){
$tmValHash.add($vsStartTime.ToString("HH:mm"),$tidx)
$tidx++
}

get-mailbox -ResultSize unlimited | foreach-object{
if ($mbHash.ContainsKey($_.WindowsEmailAddress.ToString()) -eq $false){
$mbHash.Add($_.WindowsEmailAddress.ToString(),$_.DisplayName)
}
}
$Attendeesbatch = [activator]::createinstance(([type]'System.Collections.Generic.List`1').makegenerictype([Microsoft.Exchange.WebServices.Data.AttendeeInfo]))

$StartTime = [DateTime]::Parse([DateTime]::Now.ToString("yyyy-MM-dd 0:00"))
$EndTime = $StartTime.AddDays(1)


$displayStartTime = [DateTime]::Parse([DateTime]::Now.ToString("yyyy-MM-dd 08:30"))
$displayEndTime = [DateTime]::Parse([DateTime]::Now.ToString("yyyy-MM-dd 17:30"))

$drDuration = new-object Microsoft.Exchange.WebServices.Data.TimeWindow($StartTime,$EndTime)
$AvailabilityOptions = new-object Microsoft.Exchange.WebServices.Data.AvailabilityOptions
$AvailabilityOptions.RequestedFreeBusyView = [Microsoft.Exchange.WebServices.Data.FreeBusyViewType]::DetailedMerged

$batchsize = 100
$bcount = 0
$bresult = @()
if ($mbHash.Count -ne 0){
$mbHash.GetEnumerator() | Sort Value | foreach-object {
if ($bcount -ne $batchsize){
$Attendee1 = new-object Microsoft.Exchange.WebServices.Data.AttendeeInfo($_.Key)
$Attendeesbatch.add($Attendee1)
$bcount++
}
else{
$availresponse = $service.GetUserAvailability($Attendeesbatch,$drDuration,[Microsoft.Exchange.WebServices.Data.AvailabilityData]::FreeBusy,$AvailabilityOptions)
foreach($avail in $availresponse.AttendeesAvailability.OverallResult){$bresult += $avail}
$Attendeesbatch = [activator]::createinstance(([type]'System.Collections.Generic.List`1').makegenerictype([Microsoft.Exchange.WebServices.Data.AttendeeInfo]))
$bcount = 0
$Attendee1 = new-object Microsoft.Exchange.WebServices.Data.AttendeeInfo($_.Key)
$Attendeesbatch.add($Attendee1)
$bcount++
}
}
}
$availresponse = $service.GetUserAvailability($Attendeesbatch,$drDuration,[Microsoft.Exchange.WebServices.Data.AvailabilityData]::FreeBusy,$AvailabilityOptions)
$usrIdx = 0
$frow = $true
foreach($res in $availresponse.AttendeesAvailability){
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 = $displayStartTime;$stime -lt $displayEndTime;$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 = $displayStartTime;$stime -lt $displayEndTime;$stime = $stime.AddMinutes(30)){
if ($stime -eq $displayStartTime){
$fbBoard = $fbBoard + "<td bgcolor=`"#CFECEC`"><b>" + $mbHash[$Attendeesbatch[$usrIdx].SmtpAddress] + "</b></td>" + "`r`n"
}
$title = "title="
if ($res.MergedFreeBusyStatus[$tmValHash[$stime.ToString("HH:mm")]] -ne $null){
$gdet = $false
$FbValu = $res.MergedFreeBusyStatus[$tmValHash[$stime.ToString("HH:mm")]]
switch($FbValu.ToString()){
"Free" {$bgColour = "bgcolor=`"#41A317`""}
"Tentative" {$bgColour = "bgcolor=`"#52F3FF`""
$gdet = $true
}
"Busy" {$bgColour = "bgcolor=`"#153E7E`""
$gdet = $true
}
"OOF" {$bgColour = "bgcolor=`"#4E387E`""
$gdet = $true
}
"NoData" {$bgColour = "bgcolor=`"#98AFC7`""}
"N/A" {$bgColour = "bgcolor=`"#98AFC7`""}
}
if ($gdet -eq $true){
if ($res.CalendarEvents.Count -ne 0){
for($ci=0;$ci -lt $res.CalendarEvents.Count;$ci++){
if ($res.CalendarEvents[$ci].StartTime -ge $stime -band $stime -le $res.CalendarEvents[$ci].EndTime){
if($res.CalendarEvents[$ci].Details.IsPrivate -eq $False){
$subject = ""
$location = ""
if ($res.CalendarEvents[$ci].Details.Subject -ne $null){
$subject = $res.CalendarEvents[$ci].Details.Subject.ToString()
}
if ($res.CalendarEvents[$ci].Details.Location -ne $null){
$location = $res.CalendarEvents[$ci].Details.Location.ToString()
}
$title = $title + "`"" + $subject + " " + $location + "`" "
}
}
}
}
}

}

else{
$bgColour = "bgcolor=`"#98AFC7`""
}
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"
$usrIdx++
}
$fbBoard = $fbBoard + "</table>" + " "
$fbBoard | out-file "c:\fbboard.htm"

8 comments:

Darko MesaroŇ° said...

Hi Glen,
I've been trying to make this script work, but I seem to get this error:
'New-Object : Could not load file or assembly 'System.Core, Version=3.5.0.0, Cul
ture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The
system cannot find the file specified.'
I am using the latest EWS API (1.1) - and yes, I have corrected the dll path from 1.0 to 1.1 ..
Do you have any ideas how can I solve this?

Best regards,
Darko

Glen said...

Those errors point to .net 3.5 not being installed on the machine your trying to run the script from. The EWS Managed Api has a requirement for the .net 3.5 framework

Cheers
Glen

Darko MesaroŇ° said...

Ok, thank you for your reply.
Darko

Anonymous said...

Hi.
Love this script - but got one problem thou.. i got about 800 users. How should i go about this.
Time isnt really an issue.. if it takes 1 or 5 minutes.

Tried to alter $batchsize to 1000.. but PS halts on an error with something like "only 100 allowed"

Kind regards
Daniel Lauritzen

Anonymous said...

Hi again :)

I "found the error". I finally got the meaning about the "100" batch thingy.. And thats also the problem.

Everytime it gets 100 FB informations, it doesnt seem to store it.. therefor i only got "number of users MOD 100" in the html file.

I just did a quick "fix" and add the 100 results to $fbboard everytime, and that solved it for me.. doesnt look pretty atm. but - i could get on with my thing :)

Kind regards
Daniel Lauritzen

Anonymous said...

Hello,

Can you make the same board for an entire month, with day timeslot ?

There is only one booking per day an we want to see a entire month on the page.

Thank's

Glen Scales said...

You can query a time windows of 42 days so it should be possible

Cheers
Glen

Anonymous said...

You can replace
$Attendeesbatch = [activator]::createinstance(([type]'System.Collections.Generic.List`1').makegenerictype([Microsoft.Exchange.WebServices.Data.AttendeeInfo]))
By
$Attendeesbatch = new-object 'System.Collections.Generic.List[Microsoft.Exchange.WebServices.Data.AttendeeInfo]'