Let’s imagine for a moment, that you work for a big multinational company, and need to produce reports of allocated Office 365 licenses; organised per user, per product and distributed across different business units for cost management, on a monthly basis.
You might say it’s pretty easy to do. “Just export the list of active users from Office 365 Admin portal, make it pretty and send to interested parties!” we hear you cry - and you’d almost be right. Almost.
Unless, a business unit that a user actually belongs to is defined by a user’s company attribute in Active Directory and (as funny as it sounds,) that attribute is not available in the exported csv from Office 365. Now you have the user and license info, but no way of distributing costs. So now what happens?
You have to fetch the attribute from AD using another export with PowerShell (or “old school” CSVDE), convert to table and, if you’re good with Excel, use VLOOKUP or similar formulae to find and match appropriate values from both exports.
Then make it all look pretty with pivot tables and send to the appropriate people. There goes half a day of one person’s life that they’re never getting back (and that’s being optimistic on time spent). Every month.
So, let’s see how PowerShell and Azure automation can help us with this time-consuming task, and save you some man-hours.
In short, we want reporting to happen on the first day of every month automatically - without any user intervention. We want the reports to contain licensing info for every user, plus the total number of licenses assigned per company, per product.
To make things simple, we need to get usernames, company info and license info in one singular file. If users are being synced from on-prem AD to Azure AD, it’s logical to assume that company attributes should be available as well, and accessible using PowerShell.
Thankfully it is; you just have to use Azure Active Directory PowerShell for Graph (cmdlets include AzureAD in their name) instead of Microsoft Azure Active Directory Module for Windows PowerShell (cmdlets include MSol in their name). The following command will fetch all the info we need for the report:
$licensedUsers = Get-AzureADuser -All $true | where {$_.AssignedLicenses.skuid} | select DisplayName, @{name="AssignedLicenses";expression={$_.AssignedLicenses.skuid}}, CompanyName
First, we can replace SkuId with product name in the PowerShell itself, before exporting:
foreach($user in $licensedUsers)
{
$user.AssignedLicenses = $user.AssignedLicenses.Replace("18181a46-0d4e-45cd-891e-60aabd171b4e","OFFICE 365 ENTERPRISE E1")
$user.AssignedLicenses = $user.AssignedLicenses.Replace("6fd2c87f-b296-42f0-b197-1e91e994b900","OFFICE 365 ENTERPRISE E3")
$user.AssignedLicenses = $user.AssignedLicenses.Replace("c5928f49-12ba-48f7-ada3-0d743a3601d5","VISIO Online Plan 2")
$user.AssignedLicenses = $user.AssignedLicenses.Replace("f8a1db68-be16-40ed-86d5-cb42ce701560","POWER BI PRO")
$user.AssignedLicenses = $user.AssignedLicenses.Replace("09015f9f-377f-4538-bbb5-f75ceb09358a","PROJECT ONLINE PREMIUM")
$user.AssignedLicenses = $user.AssignedLicenses.Replace("53818b1b-4a27-454b-8896-0dba576410e6","PROJECT ONLINE PROFESSIONAL")
$user.AssignedLicenses = $user.AssignedLicenses.Replace("a403ebcc-fae0-4ca2-8c8c-7a907fd6c235","POWER BI STANDARD")
}
Then, there’s a brilliant PowerShell module for importing/exporting and manipulating Excel spreadsheets, that will not only export your data to Excel, but create tables, Pivot tables and even pie charts, all via a single command:
$Params = @{
Path = 'C:\Temp\O365license.xlsx'
IncludePivotTable = $true
PivotRows = 'CompanyName'
PivotData = @{CompanyName='Count'}
PivotColumns = 'AssignedLicenses'
WorksheetName = 'O365license'
Show = $true
}
$licensedUsers | Export-Excel @Params
Finally, we need to email the report, which we can also do via the script:
$mailParams = @{
To =$To
Body ="Please find attached monthly Office 365 license report."
Subject =$Subject
SmtpServer ='smtp.office365.com'
From =$From
BodyAsHtml =$True
UseSsl =$True
Port =587
Credential =$EmailCredentials
Attachments='C:\Temp\O365license.xlsx'
}
Send-MailMessage @mailParams
And there you go, our report is done in just a few minutes, down from half a day’s work. The power of automation!
We still need a machine (virtual or physical) to run this script, create a report and email it. This is where Azure Automation comes into play. All we need to do is create PowerShell runbook that will execute our PowerShell code in Azure, without the need to create a VM (serverless) and run on a monthly schedule that we define.
Now that our manual work is effectively reduced to zero, we can spend our time doing something more productive, like sipping cocktails on the beach (we’re kidding, we know you have plenty still to do).
If we want to take it a step further, we can also integrate PowerShell with Power BI which would help us send data from PowerShell into Power BI and visualise that data with powerful reports.
This is just one small example of how PowerShell and Azure Automation can help you save time and resources. We hope it helps in your day-to-day operations, and be sure to watch this space for more cool Azure tips, tricks, and helpful guides.
Microsoft Infrastructure and Cloud Subject Matter Expert
Enabling digital transformation using the best of Microsoft and Citrix technologies with strong focus on Cloud.