Link to home
Create AccountLog in
Avatar of Mark Galvin
Mark GalvinFlag 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? 

Avatar of Arun Kumar V
Arun Kumar V
Flag of India image

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:
User generated image
User generated image
Avatar of 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. 
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
Sorry, yes. If the user has logged in, in the last 30 days we charge for support. 
ASKER CERTIFIED SOLUTION
Avatar of Arun Kumar V
Arun Kumar V
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account