Solved: Scheduled Report on Active 365 Users

Arun Kumar V

12/22/2021

Hi Mark,

This script will fetch active counts, consumed units, and available units for Office 365 E1, Office 365 E3, Microsoft E3, and PowerBI Pro

1. Replace Contso with the companyname: Get the details from when connected to azure (Connect-MsolService)
2. Save details of the account - How to save credentials in powershell for automation, encrypted
3. Script lists is an example which provides Office 365 E1,Office 365 E3, Microsoft E3 and PowerBI you can include other details to get a complete report on License usage
4. Update the From and To address
5. Enter the SMTP IP address, this will send email to recipient with the table as mentioned in output
6. Add this to a schedule task and set to run on that specific date of the month or daily How to schedule powershell script from task manager

<#

  This is an autmomated report to provide O365 License Usage
  Author: Arun Kumar V
  
#>
$credential = Import-CliXml -Path 'C:\Scripts\O365.xml'
 Connect-MsolService -Credential $credential
 $licE3 = Get-MsolAccountSku | Where-Object{$_.accountskuid -match "Contso:ENTERPRISEPACK"}
 $licE1 = Get-MsolAccountSku | Where-Object{$_.accountskuid -match "Contso:STANDARDPACK"}
 $licM3 = Get-MsolAccountSku | Where-Object{$_.accountskuid -match "ContsoSPE_E3"}
 $PowerBIPro = Get-MsolAccountSku | Where-Object{$_.accountskuid -match "ContsoPOWER_BI_PRO"}
 $activeE3 = $licE3.activeunits
 $consumedE3 = $licE3.ConsumedUnits
 $activeE1 = $licE1.activeunits
 $consumedE1 = $licE1.ConsumedUnits
 $remaininglicE3= $licE3.ActiveUnits-$licE3.ConsumedUnits
 $remaininglicE1= $licE1.ActiveUnits-$licE1.ConsumedUnits
 $activeM3 = $licM3.activeunits
 $consumedM3 = $licM3.ConsumedUnits
 $PowerBIProactive = $PowerBIPro.activeunits
 $PowerBIProconsumed = $PowerBIPro.ConsumedUnits
 $spareE1 = $activeE1-$consumedE1
 $spareE3 = $activeE3-$consumedE3
 $spareM3 = $activeM3-$consumedM3
 $sparepowerbi=$PowerBIProactive-$PowerBIProconsumed
 $htmlbody2 = @{
 Subject = "Report: Office-365 License Counts"
    Body = "<b>Office 365 Licensing Report</b><br></P>
    <p>
 
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
}
</style>
</head>
<body>
<P>

<table>
  <tr>
    <th>Subscription</th>
    <th>Active Units</th>
    <th>Consumed Units</th>
    <th>Available Units</th>
  </tr>
  <tr>
    <td>Office 365 E1</td>
    <td>$activeE1</td>
    <td>$consumedE1</td>
    <td>$spareE1</td>
  </tr>
  <tr>
    <td>Office 365 E3</td>
    <td>$activeE3</td>
    <td>$consumedE3</td>
    <td>$spareE3</td>
     </tr>
  <tr>
    <td>Microsoft 365 E3</td>
    <td>$activeM3</td>
    <td>$consumedM3</td>
    <td>$spareM3</td>
  <tr>
    <td>PowerBI Pro</td>
    <td>$PowerBIProactive</td>
    <td>$PowerBIProconsumed</td>
    <td>$sparepowerbi</td>
    
  </tr>
</table>
<P>
Note: This is an automated report <br> <br><P>
Thank you, <br>
IT."


From = "no-reply@Contoso.com"
To = "Email ID of the recipent"
 
    SmtpServer = "192.168.1.1"
}
Send-MailMessage @htmlbody2

Open in new window

Output:

Mark Galvin

12/22/2021

ASKER

Thanks.

So this will show us total number of the licenses, how many are assigned and how many is free.

But it will not show us if the users are active, which is what we need.

Arun Kumar V

12/22/2021

Hi,

Can you give use case of active users, example are you expecting if the user is logged in last 30 days is that user considered as active ?

Arun

Mark Galvin

12/22/2021

ASKER

Sorry, yes. If the user has logged in, in the last 30 days we charge for support.