Gather Microsoft Teams Statistics using Graph API

This script uses Graph API to gather statistics regarding Microsoft Teams.


IMPORTANT:

• You will need to have, or create, an 'app registration' in Azure and create a 'client secret';
• The app registration will need the following API permissions to Graph API: 'Group.Read.All' and 'User.Read.All', both of type 'Application'.

The script gathers and exports the following stats for each team:

• Team: the display name of the team;
• Description: the team's description as set by its owner;
• Email: the team's email address;
• CreatedOn: the date and time the team was created;
• RenewedOn: the date and time when the team was renewed;
• Visibility: if the team is 'Private' or 'Public';
• Owners: the number of owners;
• OwnerNames: the display name of all the team's owners;
• Members: the number of members;
• Channels: the number of channels for the team;
• ChannelNames: the display name of all the team's channels.

To call the script, simply run:

.\GetTeamsDetails.ps1

Exchange Online Meeting Room Statistics - Graph API

A few years ago I wrote an article named "Exchange Meeting Room Statistics" about a script to gather statistics regarding Exchange meeting room usage for MSExchange.org (now techgenix.com). For this script to work, we have to give ourselves FullAccess to the meeting rooms’ mailbox, add them into our Outlook profile, and then use an Outlook COM Object to connect to Outlook and gather this information. Far from ideal, especially when trying to analyse dozens of rooms!

A couple of years after, I wrote a new version that uses Exchange Web Services to gather the same information, plus some further stats. However, this script wasn't very reliable with Exchange Online as it would work in some environments, but not in other.

I have finally written this newer version, specifically targeted at Exchange Online only, this time using Graph API!

IMPORTANT:

• To analyze a particular meeting room, specify one or more primary SMTP addresses in the format: "room1@domain.com, room2@domain.com". Alternatively, analyze all meeting rooms by using the "-All" switch;
• You will need to have, or create, an 'app registration' in Azure and user digital certificate for authentication (you can update the script to use 'client secret' instead);
• The app registration will need the following API permissions to Graph API: 'User.Read.All', 'Calendars.Read', and 'Place.Read.All', all of type 'Application';
• Maximum range to search is 1825 days (5 years);
• You can enter the dates in the format "22/02/2020", "22/02/2020 15:00", or in ISO 8601 format such as "2020-02-22T15:00:00", or even "2020-02-22T15:00:00-08:00" to specify an offset to UTC (time zone).

The most basic way of running the script is as follows (more examples in the script itself):

.\Get-MeetingRoomStats_GraphAPI.ps1 -All -From "01/01/2020" -To "01/02/2020"

The script gathers and exports the following stats for each meeting room for the given date range:

• RoomName: the display name of the meeting room (when using -All). When using -RoomListSMTP, this will be the room's SMTP address;
• RoomSMTP: the SMTP address of the meeting room;
• From: the start of the date range to search the calendar;
• To: the end of the date range to search the calendar;
• totalMeetings: the total number of meetings;
• totalDuration: the total number of minutes for all meetings;
• totalAttendees: the total number of attendees invited across all meetings;
• totalUniqueOrganizers: the number of unique meeting organizers;
• totalUniqueAttendees: the number of unique attendees;
• totalReqAttendees: the total number of required attendees;
• totalOptAttendees: the total number of optional attendees;
• Top5Organizers: the email address of the top 5 meeting organizers, and how many meetings each scheduled;
• Top5Attendees: the email address of the top 5 meeting attendees, and how many meetings each attended;
• totalAllDay: the total number of 'all-day' meetings;
• totalAM: the total number of meetings that started in the morning (this excludes all-day meetings);
• totalPM: the total number of meetings that started in the afternoon;
• totalRecurring: the total number of recurring meetings;
• totalSingle: the total number of non-recurring meetings (single instance/occurrence).

Empty StorageLimitStatus when running Get-MailboxStatistics in Exchange 2013/2016

The old version of this script, used to gather some statistics out of mailboxes, made use of the StorageLimitStatus attribute of mailboxes (when running the Get-MailboxStatistics cmdlet). However, if you run the script in an Exchange 2013/2016 environment, you will notice that this attribute is always blank while with Exchange 2010 it is not... Unfortunately, this is by design...

 

Unlike versions of the Information Store earlier than the one that comes with Exchange 2013, the Information Store in Exchange 2013 does not cache the values of mailbox quotas. Therefore, the Information Store makes frequent calls to Active Directory (AD) to retrieve the values of mailbox quotas for each mailbox that is specified in the Get-MailboxStatistics cmdlet. Because of the frequent calls to AD, admins may experience poor performance in Exchange. To avoid this, the default Get-MailboxStatistics cmdlet does not retrieve the mailbox quotas and does not display a value in the StorageLimitStatus field...

 

To work around this issue, all we can do is either use the Exchange Admin Console (EAC):

1. Log on to EAC by using a user account that is assigned at least the Mail Recipients role;
2. In the feature pane, click recipients. A list of mailboxes is displayed;
3. Select the mailbox of which you want to verify the quota status and then click the Edit button on the toolbar;
4. Click mailbox usage. The mailbox quota usage is displayed.

Or we can use the Exchange Management Shell and run the following cmdlet:

Get-Mailbox "user" | FT *quota*, *size -AutoSize

The script has now been updated for Exchange 2013/2016 environments to work around this "issue".

Exchange Meeting Room Statistics

Room mailboxes have been available for a very long time now, and most organizations make extensive use of them for all their meeting room bookings. In certain cases, having statistical information about these rooms helps organizations plan or redesign their offices in a more efficient way. This information can show how often certain rooms are utilized, the average meeting duration, who tends to book more meetings, and so on.

In this article we will develop a script to provide us with some of this information and to serve as a stepping stone to gather further information depending on the reader’s particular needs. The end result will look something like this:

 

In this example, I only searched one meeting room called ITD – 16A – Small CF VC. For the month of December we can see that a total of 115 meetings were booked, out of which 55% are recurring meetings. The average meeting duration was 46 minutes, each meeting had an average of 8 attendees and there were slightly more meetings booked in the morning.

To download the script and the read the entire article, please go to MSExchange’s Exchange Meeting Room Statistics article page.

Distribution Groups Statistics

As an Exchange Administrator, have you ever wondered if all those Distribution Groups are actually being used? Organizations running Microsoft Exchange Server are likely to have been running Exchange for a at least a few years and also likely to continue to do so for a while. After all those years, more and more distribution groups get created, some of them stop being used, some are simply forgotten, etc.

Maybe you got asked by the Audit department or by your manager for a list of the 20 most utilized groups, or maybe you are just curious. The good news is that as long as you have Message Tracking Logs enabled, you can easily get this information!

The easiest way to track messages sent to distribution groups is to list all the expansion events. When a user sends an email to a group, Exchange needs to expand that group in order to know who to send the email to. This gets registered with an EventID of EXPAND. Additionally, the RelatedRecipientAddress field in the EXPAND entry contains the PrimarySmtpAddress of the expanded group. And this is pretty much all the information we need.

Using the following cmdlet, we can get a list of all the emails sent to distribution groups on the 1st of January 2016:

Get-MessageTrackingLog -Start 01/01/2016 –End 01/02/2016 -EventID Expand | Select Timestamp, RelatedRecipientAddress, MessageSubject

But what we really want is to know how many emails were sent to which groups during a particular time. Not a problem! All we need to do is tweak the cmdlet above and add Group-Object:

Get-MessageTrackingLog -Start 01/01/2016 –End 01/02/2016 -EventID Expand | Group RelatedRecipientAddress | Sort Count –Descending | Select Count, Name

Do not forget to add Get-TransportService (or Get-TransportServer in Exchange 2007 and 2010) to get the logs across all transport servers if you have more than one!

If you are interested in getting the Top 20 most used groups, for example, this is also very easy:

Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start 01/01/2016 -EventID Expand | Group RelatedRecipientAddress | Sort Count -Descending | Select -First 20 | FT Count, Name -AutoSize

Get-LogonStatistics in Exchange 2013

The Get-LogonStatistics cmdlet is used to retrieve logon statistics about currently active sessions such as user name, logon time, last access time and client version for example.

 

As you can see from the next screenshot, the Get-LogonStatistics cmdlet is still present in Exchange 2013 all the way to the latest CU5:

 

However, if you actually try to use it, you will get an error similar to the following:

 

 

The specified mailbox "DB01" doesn't exist.
  + CategoryInfo          : NotSpecified: (:) [Get-LogonStatistics], ManagementObjectNotFoundException
  + FullyQualifiedErrorId : [Server=EXAIO,RequestId=361f29b3-6474-4aad-a43e-111fe3af86b2,TimeStamp=07/08/2014 14:42:08] [FailureCategory=Cmdlet-ManagementObjectNotFoundException] 83C28CB1,Microsoft.Exchange.Management.MapiTasks.GetLogonStatistics
  + PSComputerName        : exaio.letsexchange.com



Cannot find LogonStatistics objects from the root 'DB01'. Please make sure that you specified the correct search root and that you have the correct permissions to perform the search.
  + CategoryInfo          : NotSpecified: (:) [Get-LogonStatistics], MapiOperationException
  + FullyQualifiedErrorId : [Server=EXAIO,RequestId=ebda72ad-ce19-443e-95bf-6b417cd14d7e,TimeStamp=07/08/2014 14:42:18] [FailureCategory=Cmdlet-MapiOperationException] 3962F04B,Microsoft.Exchange.Management.MapiTasks.GetLogonStatistics
  + PSComputerName        : exaio.letsexchange.com

The reason for this error is that this cmdlet has been deprecated in Exchange 2013 since RTM! According to Microsoft, “Get-LogonStatistics is no longer supported by Exchange 2013. The cmdlet and documentation will eventually be removed.”

 

The documentation mentioned above refers to a couple of TechNet articles that still mentioned the cmdlet. However, these now seem to have been removed.

AssociatedItemCount versus ItemCount in Exchange

When running the Get-MailboxStatistics cmdlet, two of the attributes returned are AssociatedItemCount and ItemCount. But what exactly are the differences between the two?!

 

As one would expect, ItemCount is the total number of e-mails, contacts, etc. items available and visible to the end-user on the mailbox. Basically, everything the users sees.

 

On the other hand, AssociatedItemCount are special messages in the Associated Table of a folder. They are most often rules and other special messages like the MRM FAI message. Basically, things that are “associated” with a folder, usually other file-like objects not visible to users such as IPM.Configuration.RssRule, IPM.Configuration.AutoComplete, Outlook Rules Organizer or IPM.Microsoft.MigrationStatus.

 

These are not visible to the user, only through MFCMapi for example (right-click a folder and select Open Associated Contents table).

 

Folder Associated Information (FAI) is a collection of message objects that are stored in a folder object and are typically hidden from view by e-mail applications. An FAI Message object is used to store a variety of settings and auxiliary data, including forms, views, calendar options, favorites and category lists.

Archive Mailbox Statistics

Although Personal Archives in Exchange 2010 are often in cheaper storage than “normal” mailboxes, we still need to keep an eye on them to check how archives are growing and how (if!) users are using it.

Here’s a simple script to get some statistics regarding all the archive mailboxes in your environment:

$mbcombCollection = @()

$archiveMbxs = Get-Mailbox -Archive -ResultSize Unlimited | Select Identity, ArchiveWarningQuota, ArchiveQuota
ForEach ($mbx in $archiveMbxs)
{
 $mbxStats = Get-MailboxStatistics $mbx.Identity -Archive | Select DisplayName, StorageLimitStatus, TotalItemSize, TotalDeletedItemSize, ItemCount, DeletedItemCount, Database

 $mbcomb = "" | Select "Display Name", StorageLimitStatus, "TotalItemSize (MB)", "TotalDeletedItemSize (MB)", ItemCount, DeletedItemCount, Database, "ArchiveWarningQuota (GB)", "ArchiveQuota (GB)"

 $mbcomb."Display Name" = $mbxStats.DisplayName
 $mbcomb.StorageLimitStatus = $mbxStats.StorageLimitStatus
 $mbcomb."TotalItemSize (MB)" = [math]::round($mbxStats.TotalItemSize.Value.ToMB(), 2)
 $mbcomb."TotalDeletedItemSize (MB)" = [math]::round($mbxStats.TotalDeletedItemSize.Value.ToMB(), 2)
 $mbcomb.ItemCount = $mbxStats.ItemCount
 $mbcomb.DeletedItemCount = $mbxStats.DeletedItemCount
 $mbcomb.Database = $mbxStats.Database
 $mbcomb."ArchiveWarningQuota (GB)" = $mbx.ArchiveWarningQuota.Value.ToGB()
 $mbcomb."ArchiveQuota (GB)" = $mbx.ArchiveWarningQuota.Value.ToGB()

 $mbcombCollection += $mbcomb
}

#$mbcombCollection
$mbcombCollection | Export-Csv D:\Scripts\Reports\"ArchiveStats_$(Get-Date -f 'yyyyMMdd').csv" -NoType

Hope this helps!