Bulk export calendars from Exchange mailboxes

I have never really had the need for a script like this, so when our catering manager at the office logged a support call, requesting an export of all calendars for all of our meeting rooms, I had to investigate the possibilities. He basically needed this information in order to determine how busy the individual meeting rooms were during the last year.
Following a quick, unsuccessful, Internet probing for tools or scripts that could do this, my initial feeling was to say “No sorry, can’t be done, or if we do it, it was going to be a manual task.”

A manual task, which involves, granting access to the room mailbox, logging onto the mailbox using Outlook, and exporting the calendar data to Excel. Sounds easy, but doing that a hundred times is very unproductive and torturous to say the least.

I decided to attempt to script it, and the result is something I am both proud of and ashamed of at the same time, as I am convinced there must be a better way.

It’s a very rough method, which involves the following process: 

  • Get a list of rooms from a text file (as it was emailed to me). You could use get-mailbox instead.
  • Add-mailbox permission to the current user  
  • Create an Outlook profile 
  • Logon to the profile 
  • Export the Calendar to CSV 
  • Remove-MailboxPermission

I could automate most of the above, but creating new profiles on demand is something I’ve never had to do, and frankly, I had no idea how to get around this problem. After speaking to some of the developers at work, who promised me some dotnet code which could do it (which I am still waiting for might I add :)), I decided to use PRF files.

I have used PRF files very successfully in the past, on Terminal server deployments to automatically setup Outlook profiles. I downloaded the ORK and created a PRF which I used as a template for the script. The blank PRF is attached to this post to save you the time and effort of using ORK.

The script finds and replaces the UserName and HomeServer in the PRF, although any Exchange server should resolve you to your mailbox server. It then creates a PRF and starts Outlook with the /importPRF switch. Some extra information, for anyone wanting to actually deploy or use the PRF file; the %HomeServer% variable in the PRF does not work the same way %UserName% works, if you want use the PRF, you need to specify one of your mailbox servers instead.

While Outlook is open on that profile, the script attaches to Outlook using a COM object and downloads the calendar for the specified date.

The calendar fields can be customised to suit your needs. In my case we simply needed the Start and End date, the duration, and the Organizer.

The export data is saved and the PRF is removed, sadly the swarm of profiles will remain, and you have to manually remove them. You could remove them from HKEY_CURRENT_USERSoftwareMicrosoftWindows NTCurrentVersionWindows Messaging SubsystemProfiles but I have not added that to the script.

I hope this can help you, if you ever get a freaky request like this.

The script and the PRF template can be downloaded from here:

Collecting Exchange database white space from the event log using .NET

A recent comment from a reader, prompted me to do some updates and bug fixes to my Exchange 2007 audit script. As a part of this process, I decided to add the white space count into the mailbox store check.

I discovered an extremely helpful post, as usual, from Shay Levy, which pointed me in the right direction.

Although this function does get exactly what I needed, I did however want to search for the white space by mailbox store name, in order to get the value, as each mailbox store was passed during the script processing.

I changed my script to use .NET instead of WMI for event logs so I decided to continue using this method for the white space as well.

The basic script to collect the white space sizes from the event log using .NET is as follows:

$now = Get-Date
$colMailboxStores = Get-MailboxDatabase -Server SERVER -Status | Sort-Object Name
$spaceLog=[System.Diagnostics.EventLog]::GetEventLogs('SERVER') | where {($_.LogDisplayName -eq "Application")}
foreach ($objMailboxStore in $colMailboxStores)
    Write-Host "..Getting database white space for" $objMailboxStore.Name
    $store = @{Name="store";Expression={$_.ReplacementStrings[1]}}
    $freeMB = @{Name="freeMB";Expression={[int]$_.ReplacementStrings[0]}}
    $whiteSpace = @()
    $whiteSpace += $spaceLog.entries | where {($_.TimeWritten -ge $now.AddDays(-1))} | where {($_.EventID -eq "1221")} | where {($_.ReplacementStrings[1] -match $objMailboxStore.Name)} | select $store,$freeMB -last 1
This method is very slow, as it has to dredge through the entire event log for every database. It’s really not a problem if you have a small number of databases, but in a large environment like ours, with multiple mailbox servers, this could take ages to complete.

It was was painful during testing to wait for the above script to complete and I really felt that the speed of this process should be increased, so instead I came up with the following solution:

$now = Get-Date
$spaceLog=[System.Diagnostics.EventLog]::GetEventLogs('SERVER') | where {($_.LogDisplayName -eq "Application")}
$db = @{Name="database";Expression={$_.ReplacementStrings[1]}}
$freeMB = @{Name="MB";Expression={[int]$_.ReplacementStrings[0]}}
$whiteSpace = $spaceLog.entries | where {($_.TimeWritten -ge $now.AddDays(-1))} | where {($_.EventID -eq "1221")} | select $db,$freeMB

($whitespace | where {$_.database -match $objMailboxStore.Name} | select -last 1).mb

The code above will collect all of the Event ID 1221’s for the last day and store them in a variable with the customised place holders from the expressions.

This happens once per server only and any subsequent searches can be performed against the variable instead.

The select statement at the end, also selects the last item in the list to ensure that you also look at the latest event for each database. This literally reduces the runtime of the script by a factor equal to the amount of databases on your server.

I will be posting an update to the Exchange 2007 audit script soon, so stay tuned.

Maximize My SendSize

Someone asked me the other day, “How could I go about using Security Groups, to control users’ send size limits?” He basically had a limit of 2mb for all users, and wanted to allow users in a specific Security Group to send up to 50mb messages. Here is a basic breakdown of the process I suggested: Firstly, you need to confirm that the global transport limit is raised to 50mb.

You can view and set these limits using get-transportconfig and set-transportconfig respectively:

Get-TransportConfig | select MaxSendSize  

The next step would involve setting the send connector to allow 50mb messages. You can use get-sendconnector to get a list of all send connectors, and their respective limits.

Get-SendConnector | Select Name, MaxMessageSize   

And then use set-sendconnector to set the MaxSendSize Set-SendConnector “Connector Name” –MaxSendSize 50mb Finally, you need to control the individual users’ send limits. If you have to control it via groups, you can use the following command to first enumerate the users in the group, and then pipe that to the set-mailbox command. Replace testsizegroup with the group you need to control the size limits for.

((get-group "testsizegroup").members) | foreach {set-mailbox -identity $_.Name -maxsendsize 52428800}

This will set the MaxSendSize for all users in that group to 50 mb. This command will have to be rerun every time to add users to the group, so it would be advisable to schedule this command to run hourly / daily etc.