Mobile device inventory and statistics PowerShell-based report

In preparation for the upcoming “Optimizing PowerShell for large Office 365 tenants” webinar, we prepared a sample script to generate an inventory of all mobile devices in the company. In addition, the report will also include some device statistics as well as information about the mailbox. 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 being foolproof solution that will address all possible scenarios. Let’s dig in.

What to include in a mobile device report

There are several approaches one 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 way faster compared to running it against each individual device, or each mailbox. And 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 want to gather a report only for a subset of the devices, at which point using 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 only cover specific users/mailboxes, a different approach might seem more useful, namely preparing the (filtered) list of mailboxes and running the Get-MobileDevice cmdlet against each of them individually. This will almost certainly take longer time to execute compared to 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. Namely, just 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.

So, the script will use the Get-MobileDevice cmdlet to fetch all devices in the company, and 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 then get the full list of User mailboxes in the tenant. As usual, we try to optimize this part as well, by using a server-side filter to only return User mailboxes, and by taking advantage of Invoke-Command to only return 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. Thus, you might already have a process built around this, so there is no need to query that same data every time you want to include some mailbox details in another report. Third-party reporting tools such as Radar utilize 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 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 pass it to another helper function in order to build the hash table.

Using hash tables for faster lookups

Which 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. They are one of the things we will be covering in our upcoming webinar.

Anyway, back to the task at hand. 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 that big of 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-Mailbox cmdlet (line 13), as needed. Otherwise, you will again have to execute additional cmdlet per each device, further slowing down the script execution. And, 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 poor-man’s anti-throttling code. A better solution would be to dynamically handle throttling and in general, 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 on how to take advantage of different concepts to optimize the execution of scripts against large number of Office 365 mailboxes, and we will have more of these on our webinar next month, so make sure to tune in.

Lastly, some statistics for the script – a test run against 25000+ devices took a little above 2 hours, which averages at almost 3 devices per second. Your mileage will vary greatly here, as running the same cmdlets against the service can be times faster depending on the time of day. But more importantly, the script completed with zero throttling. Still, for more complex solutions I would definitely recommend you use the approach described here in order to properly handle throttling and session reconnects.