Retrieve Office 365 users with a specific SKU using PowerShell

Its common that every Office 365 Administrator/Engineer is asked to provide user license stats from sales department for billing or reviewing purposes with the client. This quick read will demonstrate how you can easily get the User license count for a specific SKU via a simple PowerShell cmdlet.

Instead of logging in to Office 365 Admin Center and going through the admin UI to find and export these stats, we can easily do that same using a PowerShell Script. Before you start, ensure that you have latest PowerShell version and the MSOLSERVICE module installed on top of it.

Connect to Office 365 via PowerShell using this command

Connect-MsolService

Once connected, it should simply show as below and no errors or warnings

Now, let’s run Get-MsolAccountSku to get a list of the current licenses in your Office 365 tenant. Make a note of the AccountSkuId value for the license you want to filter on.

Get-MsolAccountSku

Next, you can edit this script to get the users matching that license. In this case, we’re getting users with the EnterprisePremium license.

Get-MsolUser | Where-Object {($_.licenses).AccountSkuId -match "EnterprisePremium"}

Replace EnterprisePremium with the AccountSkuID you’re trying to filter by. As we use the -match operator here we don’t need to type the entire AccountSkuID, we can just type enough of it to ensure that we’re only retrieving that specific SKU. You can change the SKU to meet your specific needs there.

Now we can export this result using this PowerShell line. Change the path to match your target location and file name.

Get-MsolUser | Where-Object {($_.licenses).AccountSkuId -match "EnterprisePremium"} | Out-file C:\Official\ENTPREMUsers.csv

Advertisement

Setup PowerShell to connect Microsoft Teams Administration

Error “Connect-MicrosoftTeams : The term ‘Connect-MicrosoftTeams’ is not recognized as the name of a cmdlet”

Root cause – Obviously because of the PowerShell module availability in the machine you are trying to connect from. Every module needs its presence in the local machine to be able to work with PowerShell properly so, installing it and keeping it up to date is vital for commands to execute against the target workload (like Teams, SharePoint or Exchange etc..)

Let’s get through the basics of installing Microsoft Teams PowerShell module. Ensure you launch the native PowerShell from your machine, not Exchange Online PowerShell or SharePoint ones.

Type PowerShell in the start menu and –> right click on the native PowerShell tool and run as Admin

Type this in PowerShell and Enter

Find-Module -Name MicrosoftTeams

Now let’s install this. Type this in PowerShell and Enter

Find-Module -Name MicrosoftTeams | Install-Module

Type “Y” to continue

Now let’s run the connect command to interact with the Office 365 tenant

Connect-MicrosoftTeams

And this should prompt you for authentication where you will provide the Admin credentials to connect

Once done, you will get connected to the tenant as below

Now, let’s just verify the functionality by running a simple Teams command. If this succeeds, you have installed PowerShell module for Microsoft Teams properly!

Get-Team

Change Exchange Room Meeting Title Settings

If you are reading this post, probably you needed to change that title appears in your O365 meeting room . Meeting rooms in Office 365 offer some basic settings in the GUI but the advance settings like this one has to be dealt via PowerShell. The following commands will help you to adjust the title for O365 meeting room. You can either do this individually or set for all existing room mailboxes.

Refer to this Microsoft reference for detailed list of available commands for creating and managing mailboxes in Office 365

Run this to get the list of room mailboxes which has the default setting (shows Organizer in every meeting subject)

Get-Mailbox -ResultSize unlimited -Filter "RecipientTypeDetails -eq 'RoomMailbox'" | Get-CalendarProcessing | Format-List Identity,AddOrganizerToSubject 

Now, that shows what are the room with default setting and the ones have changed to display the Organizer name in the title. To change the behavior, below command can be used

Change for individual room mailboxes (Affect to the targeted specific room mailbox)

Get-Mailbox -Identity <Replace With MeetingRoomName> | Set-CalendarProcessing -AddOrganizerToSubject $True

Change for all existing room mailboxes (Affect to all current room mailboxes)

Get-Mailbox -ResultSize unlimited -Filter "RecipientTypeDetails -eq 'RoomMailbox'" | Set-CalendarProcessing -AddOrganizerToSubject $True 

Excel unable to access SharePoint Online files, fails with an error “sorry we couldn’t open”

This happened to few of my clients time to time in SharePoint online environments. You may have seen it but weirdly for some users only? You are not alone.

One of the errors is ‘Sorry we couldn’t open https://mantoso.sharepoint.com/DocumentLibrary/excelfile.xlsx’

And, the other error is – ‘Microsoft Excel cannot access the file ‘https://mantoso.sharepoint.com/DocumentLibrary/excelfile.xlsx’. There are several possible reasons:

• The file name or path does not exist.
  
• The file is being used by another program.
  
• The workbook you are trying to save has the same name as a currently open workbook.’

This issue in my perspective, can be caused by Office Document Cache of your Office Desktop application. Here’s how I managed to get rid of it.

Open Windows Explorer and copy and paste one of the following locations into the address bar:

Clearing Office Document Cache for Office 2016

%localappdata%\Microsoft\Office\16.0\OfficeFileCache

Clearing Office Document Cache for Office 2013

%localappdata%\Microsoft\Office\15.0\OfficeFileCache

Select all files beginning with ‘FS’

And delete those files. Restart the Excel application and you should now be able to open files from SharePoint.