In my previous post I discussed about Azure DevOps and shared a script to generate users and groups report in an Excel spreadsheet. We can periodically generate such report and perform audit on users and groups based on Company requirements.
Today, I am going discuss about license usage and user activity audit. A common use case could be managing license usage and understanding license assignment to end user. Azure DevOps provide different license plans based on access level. Each license plan has different cost. Example, “Stakeholder” license plan is free where as “Basic” and “Basic + Test Plan” license plans are chargeable. You may refer Azure Pricing Calculator to understand license cost for Azure DevOps.
Another use case could be running user activity audit to identify new users added in last ‘n’ days or identify users who did not login to Azure DevOps in last ‘n’ days but still consuming license and incurring cost.
I am sharing a script to do basic license and user audit. This is something I use periodically. This script will generate – an Excel spreadsheet with license usage chart, new user(s) added in last 30 days and user(s) not logged in to Azure DevOps in last 60 days. Feel free to update script this script based on your requirements.
Here are some screenshots of a sample output this script generates :
Azure DevOps License Usage Chart:
Users not logged in last 60 days:
Users added in last 30 days
As pre-requisites you need Excel installed on the system from where you will execute your script. You also need a Personal Access Token(PA Token) ready with necessary access. Refer my previous post if you need any help on creating Token. If you have multiple organizations, update this script to loop through each organization. You can either hard code those or pull all organization name and PA Token from a database or some external source. I use a JSON file along with this script to pull those information. I kept it simple here with one organization just to keep everything simple.
I have used Azure DevOps REST API in this script. Refer Microsoft document to learn more.
#region - declare variables and assign values
$options = "displayName Asc" #This is required.Don't change it
$adoPAToken = "[Enter you Azure DevOps PA Token]"
$orgName = "[Your Organization Name]"
$oFile = "${env:Temp}\AzureDevOpsUsersAuditReport.xlsx"
#endregion
#region - delete old output file if exists
if(Test-Path $oFile){
Remove-Item $oFile -Force -Confirm:$false
}
New-Item -Path $oFile -ItemType File
#endregion
#region - all arrays to store result data
$Global:allADOUsers = @()
$Global:newlyCreatedADOUsers = @()
$Global:leastLoggedInADUsers = @()
$Global:LicenseCountDetails = @()
#endregion
#region - Function to all User Details for an Organization
Function Get-ADOUsers($oName,$paToken){
$adoUserUri = "https://vsaex.dev.azure.com/$($oName)/_apis/userentitlements?top=10000&sortOption=$($options)&api-version=5.1-preview.2"
$adoHeader = @{Authorization=("Basic {0}" -f [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f "",$paToken))))}
$response= Invoke-RestMethod -Uri $adoUserUri -ContentType "application/son" -headers $adoHeader -Method GET
$response.members | ForEach-Object{
$userName = $userType = $domain = $origin = $dirAlias = $mailAddress = $descriptor = $groups = ""
$licDisplayName = $status = $createDate = $lastAccessDate = ""
$userName = $_.user.displayName
$userType = $_.user.metaType
$domain = $_.user.domain
$origin = $_.user.origin
$dirAlias = $_.user.directoryAlias
$mailAddress = $_.user.mailAddress
$descriptor = $_.user.descriptor
$membershipUri = "https://vssps.dev.azure.com/$($orgName)/_apis/graph/Memberships/$($descriptor)?api-version=5.1-preview.1"
$responseMember = Invoke-RestMethod -Uri $membershipUri -ContentType "application/son" -headers $adoHeader -Method GET
if($responseMember.count -gt 0){
foreach($desc in $responseMember.value.containerDescriptor){
$grpUri = "https://vssps.dev.azure.com/$($orgName)/_apis/graph/groups/$($desc)?api-version=5.1-preview.1"
$grpResponse = Invoke-RestMethod -Uri $grpUri -ContentType "application/son" -headers $adoHeader -Method GET
$groups += ($grpResponse.principalName -replace "[\[ | \]]","") + ";"
}
$groups = $groups.TrimEnd(";")
}
$licDisplayName = $_.accessLevel.licenseDisplayName
$status = $_.accessLevel.status
$createDate = (Get-Date $_.dateCreated -UFormat "%m-%d-%Y %H:%M:%S")
$lastAccessDate = (Get-Date $_.lastAccessedDate -UFormat "%m-%d-%Y %H:%M:%S")
if((Get-Date -Date $createDate) - (Get-Date).AddDays(-30) -gt 0){
$objNewUser = New-Object psObject
$objNewUser | Add-Member OrgName $orgName
$objNewUser | Add-Member UserName $userName
$objNewUser | Add-Member Origin $origin
$objNewUser | Add-Member DirAlias $dirAlias
$objNewUser | Add-Member MailAddress $mailAddress
$objNewUser | Add-Member LicDisplayName $licDisplayName
$objNewUser | Add-Member Status $status
$objNewUser | Add-Member CreateDate $createDate
$objNewUser | Add-Member LastAccessDate $lastAccessDate
$objNewUser | Add-Member Groups $groups
$Global:newlyCreatedADOUsers += $objNewUser
}
$tempLastAccessDate = $_.lastAccessedDate
$lastAccessDate = (Get-Date $tempLastAccessDate -UFormat "%m-%d-%Y %H:%M:%S")
if((Get-Date -Date $lastAccessDate) - (Get-Date).AddDays(-60) -lt 0){
$objLeastAccessUser = New-Object psObject
$objLeastAccessUser | Add-Member OrgName $orgName
$objLeastAccessUser | Add-Member UserName $userName
$objLeastAccessUser | Add-Member Origin $origin
$objLeastAccessUser | Add-Member DirAlias $dirAlias
$objLeastAccessUser | Add-Member MailAddress $mailAddress
$objLeastAccessUser | Add-Member LicDisplayName $licDisplayName
$objLeastAccessUser | Add-Member Status $status
$objLeastAccessUser | Add-Member CreateDate $createDate
$objLeastAccessUser | Add-Member LastAccessDate $lastAccessDate
$objLeastAccessUser | Add-Member Groups $groups
$Global:leastLoggedInADUsers += $objLeastAccessUser
}
$objUser = New-Object psObject
$objUser | Add-Member OrgName $orgName
$objUser | Add-Member UserName $userName
$objUser | Add-Member UserType $userType
$objUser | Add-Member Domain $domain
$objUser | Add-Member Origin $origin
$objUser | Add-Member DirAlias $dirAlias
$objUser | Add-Member MailAddress $mailAddress
$objUser | Add-Member LicDisplayName $licDisplayName
$objUser | Add-Member Status $status
$objUser | Add-Member CreateDate $createDate
$objUser | Add-Member LastAccessDate $lastAccessDate
$objUser | Add-Member Groups $groups
$Global:allADOUsers += $objUser
}
}
#endregion
#region - Function Design Worksheet with some nice formatting
Function Decorate-Worksheet($wksht,$col,$tabCol){
For($c = 1; $c -le $col ; $c++){
$wksht.Cells.Item(1,$c).Font.Size = 10
$wksht.Cells.Item(1,$c).Font.Bold=$True
$wksht.Cells.Item(1,$c).Font.Name = "Arial"
$wksht.Cells.Item(1,$c).Interior.ColorIndex = 10
$wksht.Cells.Item(1,$c).Font.ColorIndex = 2
$wksht.Tab.Color = $tabCol;
}
}
#endregion
#region - Call Functions to generate data
Get-ADOUsers $orgName $adoPAToken
#endregion
$Global:LicenseCountDetails = $Global:allADOUsers | Group-Object LicDisplayName | ForEach-Object{$x = $_.Name;$y = $_.Count;[PScustomobject]@{LicenseName=$x;LicenseCount=$y} | Sort-Object LicenseName}
#region - Generate Excel Files with multile worksheets with ADO Users and Groups Data
$excel = New-Object -ComObject excel.application
$excel.visible = $False
$workbook = $excel.Workbooks.Add()
# Creating ADO Users Worksheet in the Excel File
$newUsrWksheet = $workbook.Sheets.Add()
$newUsrWksheet.Name = "Added in last 30 days"
$row = $col = 1
$newUsrWksheet.Cells.Item($row,$col) = "User Name"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "Mail Address"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "User Type"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "Domain"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "Directory Alias"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "Origin"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "Organization Name"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "Status"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "Creation Date"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "Last Access Date"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "License Name"
$col++
$newUsrWksheet.Cells.Item($row,$col) = "Groups"
$row++
#region - reading through each array element and update worksheet with it
$Global:newlyCreatedADOUsers | ForEach-Object{
$col = 1
$newUsrWksheet.Cells.Item($row,$col) = $_.UserName
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.MailAddress
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.UserType
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.Domain
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.DirAlias
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.Origin
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.OrgName
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.Status
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.CreateDate
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.LastAccessDate
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.LicDisplayName
$col++
$newUsrWksheet.Cells.Item($row,$col) = $_.Group
$row++
}
#endregion
$tabColorIndex = 43
Decorate-Worksheet $newUsrWksheet $col $tabColorIndex
$dataRange = $newUsrWksheet.UsedRange
$dataRange.EntireColumn.AutoFit() | Out-Null
#endregion
#region - Creating ADO Groups Worksheet in the Excel File
$inactiveUsrWksheet = $workbook.Sheets.Add()
$inactiveUsrWksheet.Name = "Users not logged in 60 days"
$row = $col = 1
$inactiveUsrWksheet.Cells.Item($row,$col) = "User Name"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "Mail Address"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "User Type"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "Domain"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "Directory Alias"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "Origin"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "Organization Name"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "Status"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "Creation Date"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "Last Access Date"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "License Name"
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = "Groups"
$row++
#region - reading through each array element and update worksheet with it
$Global:leastLoggedInADUsers | ForEach-Object{
$col = 1
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.UserName
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.MailAddress
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.UserType
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.Domain
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.DirAlias
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.Origin
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.OrgName
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.Status
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.CreateDate
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.LastAccessDate
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.LicDisplayName
$col++
$inactiveUsrWksheet.Cells.Item($row,$col) = $_.Group
$row++
}
#endregion
$tabColorIndex = 45
Decorate-Worksheet $inactiveUsrWksheet $col $tabColorIndex
$dataRange = $inactiveUsrWksheet.UsedRange
$dataRange.EntireColumn.AutoFit() | Out-Null
#endregion
# Creating License Summary Chart Worksheet
$tmpWksheet = $workbook.WorkSheets.item('Sheet1')
$row = $col = 1
$tmpWksheet.Cells.Item($row,$col) = "LicenseName"
$col++
$tmpWksheet.Cells.Item($row,$col) = "LicenseCount"
$row++
$Global:LicenseCountDetails | ForEach-Object{
$col = 1
$tmpWksheet.Cells.Item($row,$col) = $_.LicenseName
$col++
$tmpWksheet.Cells.Item($row,$col) = $_.LicenseCount
$row++
}
$sheetData = $tmpWksheet.Range("A1").CurrentRegion
$licWksheet = $workbook.sheets.Add()
$licWksheet.Name = "License Usage Summary"
$row = $col = 1
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$licChart = $licWksheet.Shapes.AddChart().Chart
$licChart.ChartType = $xlChart::xlColumnClustered
$licChart.SetSourceData($sheetData)
$licChart.HasTitle = $true
$licChart.ChartTitle.Text = "Azure DevOps License Usage"
$licWksheet.shapes.item("Chart 1").top = 50
$licWksheet.shapes.item("Chart 1").left = 50
$licWksheet.Tab.Color = 41;
# endregion
#Stop alert window to overwrite excel file
$excel.DisplayAlerts = $false
#Save excel file
$workbook.SaveAs($oFile,51)
$workbook.Saved = $true
$workbook.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
#Open Excel file on Windows Descktop
Start-Process $oFile
That’s it! Run this script and start auditing your Azure DevOps environments. Soon I will be back with more Azure DevOps automation scripts and ideas. Share your feedback or email me if you face any issue!
[…] Azure DevOps basic User and License Audit […]