export statistics of meeting room mailbox on exchange 2013

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
cawasakiAsked:
Who is Participating?
 
SubsunCommented:
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
 
cawasakiAuthor Commented:
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
 
SubsunCommented:
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
cawasakiAuthor Commented:
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
 
SubsunCommented:
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
 
cawasakiAuthor Commented:
ok i must test, whre i can get the api for exchange 2013?
0
 
SubsunCommented:
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
 
cawasakiAuthor Commented:
and no way to export the meeting name and date and duration directly from room mailbox?
0
 
SubsunCommented:
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
 
cawasakiAuthor Commented:
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
 
cawasakiAuthor Commented:
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
 
cawasakiAuthor Commented:
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
 
SubsunCommented:
Did you try the script posted in comment ID: 41821822?
0
 
cawasakiAuthor Commented:
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
 
cawasakiAuthor Commented:
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
 
SubsunCommented:
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
 
cawasakiAuthor Commented:
script just finiched it have only get information for 5 calendar and i have 31 mailbox on my outlook
0
 
SubsunCommented:
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
 
cawasakiAuthor Commented:
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
 
cawasakiAuthor Commented:
yes its displayed green only those 5 i get and 3 other with the error
0
 
cawasakiAuthor Commented:
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
 
SubsunCommented:
Oh OK.. Good luck.. :-)
0
 
cawasakiAuthor Commented:
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
 
SubsunCommented:
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
 
cawasakiAuthor Commented:
thank you
0
 
cawasakiAuthor Commented:
0
 
SubsunCommented:
I am away due to some personal emergency.. I will take a look as soon as I can.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.