Solved

export statistics of meeting room mailbox on exchange 2013

Posted on 2016-09-29
27
100 Views
Last Modified: 2016-10-12
hello,

i need a script or a tools to be able to get the statistic of 100 meeting room (statistic of booking...)

thanks for help
0
Comment
Question by:cawasaki
  • 16
  • 11
27 Comments
 

Author Comment

by:cawasaki
ID: 41821411
i have found this http://www.msexchange.org/articles-tutorials/exchange-server-2013/management-administration/exchange-meeting-room-statistics.html

but not understand how i can execute this on outlook??

the part of add pemrisison is ok.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41821556
You need to assign yourself full access on all meeting rooms.
Add-MailboxPermission <meeting_room> -User <user> -AccessRights FullAccess -InheritanceType All

Open in new window


Once we have access to all the meeting rooms we want to process, we just need to open Outlook and ensure their mailboxes appear on the left hand side:

Once it's ready..

Copy complete PowerShell code shown in end of the article and save the code in a Script.ps1 (or any name) file and run it from PowerShell, you will get the results in csv..
If you want to specify date..
.\Script.ps1 -From "12/01/2015" -To "01/01/2016"

Open in new window

Code is pasted here, incase the link breaks in future..
[CmdletBinding()]
Param (
       [Parameter(Position = 0, Mandatory = $False)]
       [DateTime] $From = "12/01/2015",
       [Parameter(Position = 0, Mandatory = $False)]
       [DateTime] $To = "01/01/2016"
)

Try {
       $outlook = New-Object -Com Outlook.Application -ErrorAction Stop
       $mapi = $outlook.GetNameSpace("MAPI")
       $roomList = $mapi.Folders
} Catch {
       Write-Warning "Unable to create Outlook COM Object and connect to Outlook"
       Exit
}

# Array to include all the objects for all meeting rooms
[Array] $roomsCol = @()
# Process all the meeting rooms one by one. Please note that mailboxes to which we have access in Outlook will also be processed
ForEach ($room in $roomList) {
    $roomName = $room.Name
       # Exclude our own mailbox from processing.
       If ($roomName -eq "nuno.mota@domain.com") {Continue}
       # Print the name of the current meeting room being processed
       Write-Host $roomName -ForegroundColor Green
      
       # Check if the meeting room has any calendar items. If yes, then get all the items within the timespan specified at the beginning of the script

       If ($room.Folders.Item("Calendar").Items) {

        $calItems = $room.Folders.Item("Calendar").Items

        $calItems.Sort("[Start]")

        $calItems.IncludeRecurrences = $True

        $dateRange = "[End] >= '{0}' AND [Start] <= '{1}'" -f $From.ToString("g"), $To.ToString("g")

        $calItems = $calItems.Restrict($dateRange)

              $totalItems = ($calItems | Measure-Object).Count

            # Set some variables that will be used to save meeting information and process all meetings one by one

              [Int] $count = $totalMeetings = $totalDuration = $totalAttendees = $totalAM = $totalPM = $totalRecurring = 0

              ForEach ($meeting in $calItems) {

                     Write-Progress -Activity "Processing $count / $totalItems"

                     $totalMeetings++

                     $totalDuration += $meeting.Duration

                     $totalAttendees += ($meeting.RequiredAttendees.Split(";")).Count

                     If ((Get-Date $meeting.Start -UFormat %p) -eq "AM") {$totalAM++} Else {$totalPM++}

                     If ($meeting.IsRecurring) {$totalRecurring++}

                     $count++

              }
             
              # Save the information gathered into an object and add the object to our object collection

              $romObj = New-Object PSObject -Property @{

                     Room          = $roomName

                     Meetings      = $totalMeetings

                     Duration      = $totalDuration

                     AvgDuration   = If ($totalMeetings -ne 0) {[Math]::Round($totalDuration / $totalMeetings, 0)} Else {0}

                     AvgAttendees  = If ($totalMeetings -ne 0) {[Math]::Round($totalAttendees / $totalMeetings, 0)} Else {0}

                     AMperc        = If ($totalMeetings -ne 0) {[Math]::Round($totalAM * 100 / $totalMeetings, 0)} Else {0}

                     PMperc        = If ($totalMeetings -ne 0) {[Math]::Round($totalPM * 100 / $totalMeetings, 0)} Else {0}

                     RecPerc              = If ($totalMeetings -ne 0) {[Math]::Round($totalRecurring * 100 / $totalMeetings, 0)} Else {0}

              }

              $roomsCol += $romObj

    }

}

$roomsCol | Select Room, Meetings, Duration, AvgDuration, AvgAttendees, AMperc, PMperc, RecPerc | Sort Room | Export-Csv "C:\Scripts\MeetingRoomStats.csv" -NoTypeInformation

Open in new window

0
 

Author Comment

by:cawasaki
ID: 41821577
hello

thank you i found it, but some problem:

for 50 room mailbox for one yeard stats, it will take many time to get it with this method

the man who rote the script talk about to use ews unstead outlook but i dont now how.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41821600
With this approach, once mailbox may take few seconds, I guess It would take same amount of time using EWS also, only advantage you are not dependent on outlook. But you need to install Exchange Managed API and assign impersonation permission to your admin account .

You can find the EWS examples from following articles..
http://gsexdev.blogspot.in/2009/11/basic-powershell-script-to-show.html#!/2009/11/basic-powershell-script-to-show.html

http://stackoverflow.com/questions/10921563/extract-the-report-of-room-calendar-from-exchage-server-using-powershell-scripti
0
 

Author Comment

by:cawasaki
ID: 41821621
ok i must test, whre i can get the api for exchange 2013?
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41821626
You can search for Microsoft Exchange Web Services Managed API download, I think latest version is 2.1 or 2.2.

https://msdn.microsoft.com/en-us/library/office/dd877012(v=exchg.150).aspx
0
 

Author Comment

by:cawasaki
ID: 41821664
and no way to export the meeting name and date and duration directly from room mailbox?
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41821710
There is no conventional method in exchange to do it. You need to use either EWS or outlook method.

Outlook method example..
Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null
$olFolders = “Microsoft.Office.Interop.Outlook.OlDefaultFolders” -as [type] 
$outlook = new-object -comobject outlook.application
$namespace = $outlook.GetNameSpace(“MAPI”)
$folder = $namespace.getDefaultFolder($olFolders::olFolderCalendar)
$folder.items | Select-Object -Property Subject, Start,End,Duration,Location

Open in new window

EWS Option example can be found in following article..
https://blogs.msdn.microsoft.com/emeamsgdev/2015/02/25/powershell-search-for-appointments/
0
 

Author Comment

by:cawasaki
ID: 41821758
ok for outlook method, how i can use your code with all troom mailbox calendar i have on my outlook profile, its about 30 calendar
0
 

Author Comment

by:cawasaki
ID: 41821772
Add-type -assembly “Microsoft.Office.Interop.Outlook” | out-null
$olFolders = “Microsoft.Office.Interop.Outlook.OlDefaultFolders” -as [type] 
$outlook = new-object -comobject outlook.application
$namespace = $outlook.GetNameSpace(“MAPI”)
$folder = $namespace.getDefaultFolder($olFolders::olFolderCalendar)
$folder.items | Select-Object -Property Subject, Start,End,Duration,Location

Open in new window


this code only return my own calendar meeting
0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 41821822
Try..
((New-object -comobject outlook.application).GetNameSpace("MAPI")).Folders | 
	?{$_.Name -notmatch "Public Folders"}| %{
	$RoomName = $_.Name
	Write-host "Getting Calendar Info from $RoomName" -B Green
	$_.Folders.Item("Calendar").Items| Select-Object @{N="Room";E={$RoomName}},Subject, Start,End,Duration,Location
} | Export-Csv C:\Temp\report.csv -nti

Open in new window

0
 

Author Comment

by:cawasaki
ID: 41821842
i have test the ews script you give me:

https://blogs.msdn.microsoft.com/emeamsgdev/2015/02/25/powershell-search-for-appointments/

the script is here:

https://code.msdn.microsoft.com/exchange/PowerShellEWS-Search-e0f9c169

i have execute it for one room mailbox and is work well with one error i dont now why:

Exception calling "Load" with "1" argument(s): "The request failed. The underlying connection was closed: An
unexpected error occurred on a receive."
At D:\script\script4.ps1:353 char:5
+     $item.Load($propSet)
+     ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ServiceRequestException

Open in new window


its possible to help me to:

1-execute this script to a list of room mailbox from a txt or csv file?

2-this is the default header of the csv file result:

Mailbox, Subject,IsRead,Sent, Received,Sender,Organizer,Start,End,AppointmentType

its possible to remove this one IsRead,Sent, Received,Sender   and add the duration of meeting?

thanks for help
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41821857
Did you try the script posted in comment ID: 41821822?
0
How does your email signature look on mobiles?

Do your employees use mobile devices to reply to emails? With mobile becoming increasingly important to the business world, it is in your best interest to make sure that your email signature looks great across all types of devices.

 

Author Comment

by:cawasaki
ID: 41822120
test it now, its seems work but it seems not get all meeting only few month.

for certain room i hve this error:

Exception calling "Item" with "1" argument(s): "Échec de l'opération. Impossible de trouver un objet."
At D:\script\SCRIPT5.ps1:5 char:2
+     $_.Folders.Item("Calendar").Items| Select-Object @{N="Room";E={$RoomName}},Subj ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Open in new window


may be a right problem?
0
 

Author Comment

by:cawasaki
ID: 41822132
sorry i have verify it contain all meeting on room calendar, so no problem.

only the error for certain room:

Exception calling "Item" with "1" argument(s): "Échec de l'opération. Impossible de trouver un objet."
At D:\script\SCRIPT5.ps1:5 char:2
+     $_.Folders.Item("Calendar").Items| Select-Object @{N="Room";E={$RoomName}},Subj ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Open in new window


and finally this a good solution but not easy because i cannot add on outlook more 32 full acess mailbox ....so now way with ews? if not no problem
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41822142
Script is not limited with any duration, I was able to get last two years meeting from my test environment.

Regarding the error, If It's on specific items, probably the meeting is corrupted or some other permission reasons like private items etc..
0
 

Author Comment

by:cawasaki
ID: 41822143
script just finiched it have only get information for 5 calendar and i have 31 mailbox on my outlook
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41822152
If there is no items on calendar then it wont export in the csv file. Does it list all room names in green while running the script?
0
 

Author Comment

by:cawasaki
ID: 41822177
all this room mailbox have more 1000 entries on caelndar and i have full access on it and it added to outlook 2013 profile.
0
 

Author Comment

by:cawasaki
ID: 41822183
yes its displayed green only those 5 i get and 3 other with the error
0
 

Author Comment

by:cawasaki
ID: 41822207
oh sorry i think its my fault, first time i have opened the csv report file before script finish, now the script seems to access more calendar, so i need to wait now a few hours :)

report you tomorow :)
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41822215
Oh OK.. Good luck.. :-)
0
 

Author Comment

by:cawasaki
ID: 41826138
hello Subsun,

it look good :)

its possible to add on the script the date of meeting to export?  for exemple the last 6 moth or only begin 01 september 2015?

thank you
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41826146
To get last 6 months data, change last line to..
} | ?{$_.Start -ge (Get-Date).AddMonths(-6)}| Export-Csv C:\Temp\report.csv -nti

Open in new window

0
 

Author Closing Comment

by:cawasaki
ID: 41827624
thank you
0
 

Author Comment

by:cawasaki
ID: 41837005
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41839656
I am away due to some personal emergency.. I will take a look as soon as I can.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Marketers need statistics and metrics like everybody else needs oxygen. In this article we explain how to enable marketing campaign statistics for Microsoft Exchange mail.
If you don't know how to downgrade, my instructions below should be helpful.
In this video we show how to create an Address List in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Organization >> Ad…
This video discusses moving either the default database or any database to a new volume.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now