Avatar of Mark Galvin
Mark Galvin
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Scheduled Report on Active 365 Users

We have a client that has around 290 users in their 365/Active Directory. We bill them for their 365 licenses but will also bill them a set fee for IT Support. It is a Per User Per Month fixed fee. 


What we need to have scheduled is a report on a particular day each month detailing the 'active; users. The client is happy that the total number of billable users is the figure taken on that particular day. 


We have been looking into the simplest way to automate this. We have been using manual exports showing the User Last Login date but not finding that to be accurate. 


Any other way to report on this and to automate the report? 

Microsoft 365Azure

Avatar of undefined
Last Comment
Arun Kumar V

8/22/2022 - Mon
Arun Kumar V

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

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

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mark Galvin

ASKER
Sorry, yes. If the user has logged in, in the last 30 days we charge for support. 
ASKER CERTIFIED SOLUTION
Arun Kumar V

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.