Export a Licence reconciliation report from Office 365 using Powershell

As more and more companies move to Microsoft Office 365, IT Managers need to find a way to reconcile their cloud based licences with their actual user numbers.  No-one wants to pay for licences that they aren’t using!

 
 
 
 
 
4.5 Star
(45)
30,445 times
Add to favorites
Office 365
10/7/2016
E-mail Twitter del.icio.us Digg Facebook
Sign in to ask a question


  • Script with AzureAD module
    1 Posts | Last post November 06, 2018
    • Hi everybody,
      
      is there an update plant with Azure AD Powershell module instead of MSOnline?
  • Primary SMTP
    1 Posts | Last post May 10, 2018
    • can you please edit the script to add the primary SMTP address aswell in the results ? As i couldn't manage to get it working with ;
      
      Select DisplayName ,islicensed, @{Name="PrimaryEmailAddress";Expression={$_.ProxyAddresses | 
      ?{$_ -cmatch '^SMTP\:.*'}}} |
  • Specific OU?
    1 Posts | Last post October 27, 2017
    • Is it possible to run this script for users in a specific AD OU? We have a hybrid 2013/365 environment and need to report on licenses for users in a specific OU.
  • incorrect column headers when there are different license type
    4 Posts | Last post November 17, 2016
    • Great script BUT the output is not correct, when there are several different licenses in use, which use a different set of service plans. Output should be so, that different licensetypes go to different output file, which each have the correct column headers. I have at this moment not a working solution for this. 
    • Fixed. 
      
      Also the speed is very slow, because all users are fetched (Get-MsolUser -All) for every License Type. Fetching all users is a very costly command in terms of execution time. This is now also fixed in my copy of the script. 
      
      I don't know how to publish new version of the script here, so you can find it here: 
      http://sikkepitje.blogspot.nl/2016/10/get-msoluserlicense.html 
    • Thank you Sikkepitje! I've updated the description to link to your improved version.
    • With this version, I can't export users correctly.
      I mean, Excel files are created correctly when the script terminated, but if I open any of these, I can find only the first row (titles), nothing else.
      
      DisplayName	UserPrincipalName	JobTitle	Office	AccountSku	TEAMS1	PROJECTWORKMANAGEMENT	SWAY	INTUNE_O365	YAMMER_ENTERPRISE	RMS_S_ENTERPRISE	OFFICESUBSCRIPTION	MCOSTANDARD	SHAREPOINTWAC	SHAREPOINTENTERPRISE	EXCHANGE_S_ENTERPRISE
      				ENTERPRISEPACK											
      We have about 1000 users on our tenant.
  • Formatting
    5 Posts | Last post October 06, 2016
    • Love this script, but having a problem with output into .csv.
      
      My user's display names are LastName, FirstName,
      
      When I open the csv file, most columns are shifted to the right by one.  Any way to have the output of the "display name" contained to one column?
    • Hi mclementin,
      
      Assuming you are opening the CSV file with Excel, the issue you are experiencing is more related to how Excel opens CSV files. You can get around this by using Excel's data import from text feature.
      
      Let me know if you need any further assistance regarding the above explanation.
    • Ken, sorry I missed your post.  Could you explain your suggestion?  I've tried the "text feature", but not seeing any options to overcome this issue.
    • I changed it from output using comma's to semicolon as the Display Name using LastName, FirstName contains a comma which doesn't format the csv correctly when directly opening via Excel. Opening Excel workbook first and then File, Open the csv imports using the Text Import and have to select SemiColon as the separator and it displays correctly.
    • I have hade the same problem. Chaning the separator character to ";" is one solution. You could also replace the line where $datastring is built by:
      
       $datastring = (($user.displayname -replace ","," ") + "," + $user.userprincipalname + "," + $license.SkuPartNumber)
      
      So as to replace all commas in de DisplayName by a space, would also fix this.
  • Script fails to run with large amount of users.
    1 Posts | Last post September 26, 2016
    • I mean a on a different tenant it worked.
  • Script fails to run
    1 Posts | Last post September 26, 2016
    • Hi I ran your script against a large enterprise and did not produce any results. It was ran for more than 30 minutes and when I checked nothing was writing to the CSV.  If I run it against a small number of users on a different it worked.  Any ideas on why this might be the case?
  • Licenses not being reported
    7 Posts | Last post September 15, 2016
    • I recently ran this script.   I find the results to be inaccurate.   For example, we have 42 users with Visio 365 (VISIO_CLIENT_SUBSCRIPTION) but it reports only 5 users having it.   If I compare manually or to what the portal reports, the script is definitely wrong.   With over 50k users, its tough to know whats accurate and not with the script.
      
      I saw some suggestions throughout these comments, has anyone put a more recent version together?
      
      Thanks.
    • Same same on myside. I assume the script was good some time ago. 
      Now we have a lot more SKUs und the Skus contain a lot more Services.
      
      would be very helpful if a crack could update the script. 
      Im happy to help and Tests the script. habe several tenants to Admin and several 10'000 users
      
    • If you look at the code it only returns back the first item in an array ([0] in the code) . I've amended this script to deal with this as our company have 12 possible SKU's available to us (a few which aren't in the code at all) and a user could have a few assigned to them e.g. VISIO, PROJECT and E5, and also quite a few "Service Names" have been added for the new SKU's. I can't see a way of uploading any code changes so if Alan wants my updated script i'm more than happy to share it with him to replace this one.
    • Hi Michael Gibson - any way you can share your results elsewhere?   Try as I might, I'm really struggling to get valid results out of this script.   Maybe even cut & paste like a few others have?    Thanks!
    • Hello Michael,
      
      Is there a way you can share your version of the script? I'm dying to get this to work, but my limited PS skills are not really helping out ;-)
      
      Regards,
      
      Stijn
    • Hello Everyone,
      
      I apologise for the long silence - I've now had time to test and update this script.
      
      Can you please run it again and let me know if it works?
      
      Regards,
      
      Alan
    • Alan,
      
      The script now works perfectly for me!
      
      Thanks a lot!
      
      Regards,
      
      Stijn
  • Not Getting All Licenses
    2 Posts | Last post September 15, 2016
    • Hello,
      Love the script!
      One issue, I can see by the license count on the O365 admin panel that we have 1278 licenses. However, the script returns 1241 licenses. Has anyone else run into this? We only have E1 and E4 licenses. 
      Thanks!
    • Sorry for that - it seems a lot of these emails were blocked by quarantine and I didn't know there was an issue with the script.
      
      I have now updated this to resolve this issue.
  • Script update
    2 Posts | Last post September 15, 2016
    • Alan Byrne/Michael Gibson Hertel/JohnDL1143 - is the script Michael tweaked available anywhere? There is a section on technet to upload contributions. It simply does not seem to be posted here. Can you help a brother out!?
    • Hello Roland,
      
      I apologise for the long silence - I've now had time to test and update this script.
      
      Can you please run it again and let me know if it works?
      
      Regards,
      
      Alan
1 - 10 of 28 Items