Back to blog

PowerShell Script for Office 365: Mobile Device Inventory and Statistics

Jan 30, 2018 by Vasil Michev

Like all the recent scripts we have published, the script aims to illustrate some common concepts that should help you minimize the time it takes to obtain the report, rather than a foolproof solution that will address all possible scenarios. Let’s dig in.

What to include in a mobile device report

There are several approaches you can take when generating a mobile device inventory report, depending on the level of detail required. If you are interested in getting all the data for all devices, the fastest method is to simply execute the Get-MobileDevice cmdlet once. While the cmdlet can take some time to execute in large environments, this method will still be much faster than running it against each individual device, or each mailbox. Although, that’s a general rule – make sure to always run the minimum required number of cmdlets, especially in large environments. But we will talk more about this in a moment.

In some cases, you might only want to gather a report for a subset of the devices, at which point the server-side filtering capabilities come in handy. There are switch parameters built-in for filtering by protocol (so –OWAforDevices, –UniversalOutlook, –ActiveSync or –RestApi). And, you can use the –Filter parameter against any of the other filterable properties (you can find the list here). If you want the report to cover specific users/mailboxes, a different approach might seem more useful, such as preparing the (filtered) list of mailboxes and running the Get-MobileDevice cmdlet against each of them individually. In all likelihood, this will take a longer time to execute than simply getting the full list of devices for all mailboxes and then filtering the unnecessary ones out – a task that is easily done by generating a hash table with the relevant mailboxes and (some of their) properties.

Another common problem with generating a mobile devices report is that the output of the Get-MobileDevice cmdlet only includes a minimal set of information about the mailbox to which the device belongs. Normally, this information is limited to the UserDisplayName, which is certainly not enough to uniquely identify the user in large tenants. You can however construct the DistinguishedName of the corresponding mailbox based on the device’s DistinguishedName and use this information to fetch additional details via other cmdlets, as needed.

Our script will use the Get-MobileDevice cmdlet to fetch all devices in the company. To further optimize the execution speed, we are again taking advantage of Invoke-Command and running the cmdlet directly in the remote session in order to minimize the data returned. In other words, the core of the script is this single cmdlet:
Invoke-Command -Session $session -ScriptBlock { Get-MobileDevice -ResultSize Unlimited | Select-Object FriendlyName,UserDisplayName,DeviceId,DeviceOS,DeviceType,DeviceUserAgent,DeviceModel,DistinguishedName,FirstSyncTime,DeviceAccessState,DeviceAccessStateReason,DeviceAccessControlRule,ClientType }
To make sure that we can include mailbox properties such as the UserPrincipalName or PrimarySMTPAddress, we will then get the full list of User mailboxes in the tenant. As usual, we try to optimize this part too by using a server-side filter to return User mailboxes only, and by taking advantage of Invoke-Command to return just the properties we care about:
Invoke-Command -Session $session -ScriptBlock { Get-Mailbox -ResultSize Unlimited -RecipientTypeDetails UserMailbox | Select-Object DisplayName,Alias,UserPrincipalName,DistinguishedName,PrimarySmtpAddress }
Other types of important information might still be missing from the output, so in some cases you will also want to run additional cmdlets such as Get-MobileDeviceStatistics, Get-User, Get-MsolUser and so on. More on this later.

Reusing data

In large tenants it can take a considerable amount of time to gather all that data, so we also tried to illustrate the use of another concept in this script – reuse of data you have already obtained via other means. For example, it’s common for many companies to generate a “mailbox inventory” report on a monthly or even weekly basis, so you might already have a process built around this. This means there’s no need to query that same data every time you want to include some mailbox details in another report. Our Office 365 reporting software utilizes this approach to allow you to quickly generate custom reports or pivot the data. But you can also make this available in your own script – simply construct a function that will import the data from an existing CSV file.

In the current script, this process is handled via the Load-MailboxMatchInputFile function. The code 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, the data from this file will be used. Otherwise, the Get-Mailbox cmdlet sample above will be used to generate a new file, and as the next step, it will pass it to another helper function in order to build the hash table.

Using hash tables for faster lookups

This brings us to the next important concept – using Hash tables to quickly filter out data. And I mean quickly – as in less than a millisecond (even in array of tens of thousands of entries). In case you have not worked with hash tables yet, I would strongly recommend you start familiarizing yourself with them, for example by reviewing the about hash tables help item.

Anyway, back to the task. After we get the mailbox data, we 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. And this is pretty much all we need for the script.

Putting it all together

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. For our script, we selected to include some additional properties which are only obtainable via the Get-MobileDeviceStatistics cmdlet. For example, the LastSuccessSync property. 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. If you don’t need those properties, make sure to comment out the corresponding lines –  the script will run much, much faster.

The addition of mailbox properties doesn’t bring a big 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 which will be used as input for the hash table, or add the properties to the Get-Mailbox cmdlet (line 13), as needed. If you don’t do this, you will have to execute additional cmdlets per each device again, further slowing down the script execution. Not only that, you might run into throttling issues, which the script does not handle – only a simple 1s delay is added after each 100 iterations (lines 70-73).

For the same reason, we have not included any Licensing information in the script, as handling that requires additional cmdlets, slowing the script and making it more prone to throttling issues. Actually, that is not entirely true – you can execute a single run of the Get-MsolUser cmdlet to get the licensing data for all users and then use the hash table method as we did with mailbox properties – which is a good homework assignment for you folks! ????

Without further ado, here’s the link to the full script. Again, don’t treat this as a complete solution – some parts such as connecting to Exchange Online Remote PowerShell are not handled in the script. Others are hardcoded, such as the input/output CSV file names, or the basic anti-throttling code. A better solution would be to dynamically handle throttling and provide convenient parameters to use when calling the script, so feel free to make any changes you see fit. The idea behind the script is to give you an example of how to take advantage of different concepts to optimize the execution of scripts against large number of Office 365 mailboxes.

Your run time may vary for a number of reasons – running the same cmdlets against the service can be much faster depending on the time of day. This script completed with zero throttling, however for more complex solutions I would still recommend you use the approach described here in order to properly handle throttling and session reconnects.