Reporting

Reporting on Office 365 Online Users with Services and Licensing Status

Posted on

For those Online Administrators who need to account for Online Users and the Services they are assigned, along with enabled services, this email is for you!  The below is provided as-is and should be placed into a .ps1 PowerShell file, so you can run these commands against the Microsoft Online Services.  The end result will be a .csv (spreadsheet) file that outputs all the relevant information:

…special thanks to Mauricio O. for the following information!

Steps to Run

Notes:

  1. Make sure you have installed the following prerequisites:
    1. Sign-In Assistant – Note: Even though the download states BETA, it is the proper SIA: http://www.microsoft.com/en-us/download/details.aspx?id=39267
    2. Windows Azure Active Directory Module for Windows PowerShell: https://portal.office.com/default.aspx#@/IdentityFederation/IdentityFederation.aspx
      1. clip_image001
  2. Start –> Run: Notepad
    1. Copy the text below into NotePad
      1. Replace the <user> with the location you want to save the output .csv spreadsheet file!

     

    1. Connect-MsolService -Credential $UserCredential

      write-host “Getting a list of users with their assigned licenses. Can take a while”

      $withlicense=get-msoluser -all | where {$_.islicensed}

      write-host “Tenant contains “$withlicense.count” licensed users. Generating report in c:\users\<user>\desktop\report.csv”

      ”UPN,Product,Status” | out-file “c:\users\<user>\desktop\report.csv” -Append

      foreach ($usr in $withlicense) {

          $status=$usr.licenses.servicestatus

          $status | %{

              $licstatus=$usr.userprincipalname+”,”+$_.serviceplan.servicename+”,”+$_.provisioningstatus

              $licstatus | out-file “c:\users\<user>\desktop\report.csv” -Append

          }

      }

  3. Save-As and set the File Type to All and place a .ps1 file extension to the file name
  4. clip_image003
  5. Open PowerShell and run the command, such as: c:\users\<user>\Desktop> .\OnlineuserReport.ps1

     

    Notes

  6. The output is all placed into a single column, so the best option here is to open the .csv file via Excel with File –> Open to review and massage the data!

  7. Launch Excel

  8. File –> Open and open the file

  9. Select Delimited à Next

  10. clip_image009

  1. Uncheck Tab and select Comma as the Delimiter –> Next

  2. clip_image011 

  3. Finish

  4. clip_image013 

  5. This will open the spreadsheet with the different data in different columns making it easier to read and review, filter, etc

  6. clip_image015

 

Legend of Column #3

  1. Pending Input = Needs attention from Admin to assign license
  2. Disabled = Disabled
  3. Success = Activated and enabled with Service, service listed in 2nd column