Inventorying mobile devices remains one of the more challenging tasks in Microsoft 365, even with the latest improvements in the service. In this article, we will examine an improved version of the old mobile devices inventory and statistics script, which I last updated 5 years ago.
As before, we want to avoid iterating over each mailbox, as there are likely to be at least few mailboxes with no mobile device configured. Instead, we can use the Get-MobileDevice cmdlet to return the full set of mobile devices within the organizations. As the cmdlet exposes a limited set of properties though, we then loop over each device in order to “enrich” the output with details, such as the UserPrincipalName and PrimarySmtpAddress of the device owner. Herein, we can reuse an already obtained mailbox inventory report file, generated via your preferred script or exported directly from the Exchange Admin Center UI.
In the current version of the script this is handled via the Load-MailboxMatchInputFile helper function. The function will check for the existence of any CSV files in the script directory that have names ending with “*MailboxReport”. If any such CSV file is found, and its last modified date is less than 30 days ago, the data from said file will be used. Otherwise, the Get-ExOMailbox cmdlet will be used to generate a new report.
Get-EXOMailbox -ResultSize Unlimited -RecipientTypeDetails UserMailbox | Select-Object DisplayName,Alias,UserPrincipalName,DistinguishedName,PrimarySmtpAddress
Note that we are only including User mailboxes in the output, in order to minimize the time it takes to run the script. In some organizations, you can find mobile devices configured for other mailbox types, such as shared mailbox. If this is a scenario you want to cover, consider updating the filter statement (line 36 in the script). Similarly, we only gather a handful of properties – if more are needed, make sure to amend the list.
After we get the mailbox data, another helper function is used to “store” it a hash table, using the DistinguishedName value as key. Getting the DistinguishedName value for each mobile device allows us to get the DistinguishedName value for the corresponding mailbox as well, which in turn makes obtaining the mailbox properties very fast, thanks to the hash table lookup. This is all enabled via the arrayToHash and Get-MailboxMatch helper functions.
Once we have all the mobile devices and mailboxes data, we simply prepare the output by iterating against each mobile device and inserting the additional properties. Apart from information about the device owner, in most cases you will likely want to include some of the properties exposed via the Get-EXOMobileDeviceStatistics cmdlet, such as when did the device last sync with the server. This unfortunately means the script execution will slow down considerably, as the cmdlet will be run against each device – there is no way to obtain this data in bulk for all devices.
Compared to the previous version of the script, the Get-EXOMobileDeviceStatistics should bring some improvements in terms of reliability and speed. At least that’s the theory. In practice, accommodating the script to use the RESTful cmdlet turned to be a bit more complicated than expected, due to some changes in the input/output. Without boring you with too many details, using the device’s Guid property seems to be a bad option, as it slows the cmdlet execution considerably. In addition, the DistinguishedName property cannot be used as input, and throws an error. And, the Identity property uses a different format/value, compared to the output of the Get-MobileDevice cmdlet.
At the end, all these annoyances can be worked around. To craft a “suitable” value for the –Identity parameter, we use the OrganizationId and Identity values as reported by Get-MobileDevice, and perform some basic string manipulation operations. Once a suitable value is crafted, we use the Get-EXOMobileDeviceStatistics cmdlet to include four additional properties in the output: LastSuccessSync, Status, DevicePolicyApplied and DevicePolicyApplicationStatus. If you don’t need those properties, make sure to comment out the corresponding lines (92-103) – the script will run much, much faster.
Speaking of additional properties, the addition of mailbox properties doesn’t bring a penalty because of the method we use, so you can include as many of those in the output as needed. Make sure to have the corresponding properties in the CSV file used as input for the hash table, or add the properties to the Get-ExOMailbox cmdlet (line 36), as needed. However, if you want to include properties from other cmdlets, perhaps the licensing data as obtained via the Graph SDK for PowerShell or the good old MSOnline module, the script runtime will greatly increase. An example on how to do that is included in line 114.
That about covers the script’s logic. Go and downloaded it from my GitHub repo. There are no parameters you need to pass, though it might be a good idea to add one to handle other mailbox types. If you want to speed up the execution a bit, make sure to place your mailbox report CSV file within the script directory. Oh, and do make sure you use a recent version of the Exchange Online module, as the script leverages some of the REST cmdlets. Here’s an example on how to run the script:
The script will output the report to a CSV file in the current directory. Here’s how the output will look like (with some columns hidden):