Azure DevOps Users and Groups Report in Excel

Azure DevOps is a Microsoft product which provides version control, project management, automated builds, lab management, testing and release management,Code repository capabilities. It covers the application life-cycle and enables DevOps capabilities.

Organizations using Azure DevOps allow users to login and perform different job roles including manage repositories, work items, perform build and release management etc. Every user logging in to it consumes a license. Available services varies based on licensing type assigned to the user. It is also available as Free-tier up to 5 users. Refer to Microsoft document to know more about Azure DevOps and Licensing.

It is an important task for an administrator to manage DevOps users , groups, permissions , license etc. for each Organization efficiently. Microsoft provides excellent user friendly GUI to manage all these aspects of administration. However, it will be great if we can automate these tasks and save our valuable time and effort.

In this post I am going to share a script which will generate a report of all Users and Groups in an Organization in Excel format. If you have multiple Organizations, it will be a small change in the script to loop through each Organization(You need to have Personal Access Token(PA Token) generated for each Organization with necessary permissions). I encourage you to update this script and use it based on your requirements. I personally maintain a JSON file with Organization names and PA Tokens and loop through each element in the file to generate a complete report.

As pre-requisite, you need Microsoft Excel installed on the system from where you will run this script.In addition, you must have PA Tokens set up as mentioned above.

Once you run this script, it will generate an Excel file as shown in below screenshots.

I have made Azure DevOps REST API calls to retrieve those information. You may refer Microsoft documents to learn more on this. Make sure you select the correct API version while referring those documents.

Here is the complete script!

#region - declare variables and assign values
$options = "displayName Asc"
$adoPAToken = "[Enter Your PA Token Here]"
$orgName = "[Your ADO Organization Name]"
$oFile =  "${env:Temp}\AzureDevOpsUsersAndGroups.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:allADOGroups = @()
$Global:allADOUsers = @()
#endregion

#region - Function to list all ADO Group details for an Organization
Function Get-ADOGroups($oName,$paToken){
    $adoGroupUri = "https://vssps.dev.azure.com/$($oName)/_apis/graph/groups?api-version=6.0-preview.1"
    $adoHeader = @{Authorization=("Basic {0}" -f [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f "",$paToken))))}
    $response= Invoke-RestMethod -Uri $adoGroupUri -ContentType "application/son" -headers $adoHeader -Method GET
    foreach($r in $response.value){
        $groupName = $description = $domain = $projectName = ""
        $orgLevel = $projectLevel = "FALSE"
        $groupName = $r.displayName
        $description = $r.description.replace(",","\")
        $domain = $r.domain
        $origin = $r.origin
        $projectName = $r.principalName.Split("\")[0] -replace "[\[ | \]]",""
        if($projectName -eq $orgName){
            $orgLevel = "TRUE"
            $projectName = ""
        }
        else{
            $projectLevel = "TRUE"
        }
        $objGroup = New-Object psObject
        $objGroup | Add-Member OrgName $orgName
        $objGroup | Add-Member ProjName $projectName
        $objGroup | Add-Member GrpName $groupName
        $objGroup | Add-Member OrgLevel $orgLevel
        $objGroup | Add-Member ProjLevel $projectLevel
        $objGroup | Add-Member Description $description
        $objGroup | Add-Member Domain $domain
        $objGroup | Add-Member Origin $origin   
        $Global:allADOGroups += $objGroup
    }
}
#endregion
#region - Function to list 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")
        $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-ADOGroups $orgName $adoPAToken
Get-ADOUsers $orgName $adoPAToken
#endregion

#region - Generate Excel Files with multiple 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
$usrWksheet = $workbook.Sheets.Add()
$usrWksheet.Name = "ADO Users"
$row = $col = 1
$usrWksheet.Cells.Item($row,$col) = "User Name"
$col++
$usrWksheet.Cells.Item($row,$col) = "Mail Address"
$col++
$usrWksheet.Cells.Item($row,$col) = "User Type"
$col++
$usrWksheet.Cells.Item($row,$col) = "Domain"
$col++
$usrWksheet.Cells.Item($row,$col) = "Directory Alias"
$col++
$usrWksheet.Cells.Item($row,$col) = "Origin"
$col++
$usrWksheet.Cells.Item($row,$col) = "Organization Name"
$col++
$usrWksheet.Cells.Item($row,$col) = "Status"
$col++
$usrWksheet.Cells.Item($row,$col) = "Creation Date"
$col++
$usrWksheet.Cells.Item($row,$col) = "Last Access Date"
$col++
$usrWksheet.Cells.Item($row,$col) = "License Name"
$col++
$usrWksheet.Cells.Item($row,$col) = "Groups"
$row++  
#region - reading through each array element and update worksheet with it      
$Global:allADOUsers | ForEach-Object{
    $col = 1
    $usrWksheet.Cells.Item($row,$col) = $_.UserName
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.MailAddress
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.UserType
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.Domain
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.DirAlias
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.Origin
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.OrgName
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.Status
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.CreateDate
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.LastAccessDate
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.LicDisplayName
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.Group
    $row++
}
#endregion
$tabColorIndex = 43
Decorate-Worksheet $usrWksheet $col $tabColorIndex

$dataRange = $usrWksheet.UsedRange						
$dataRange.EntireColumn.AutoFit() | Out-Null
#endregion
#region - Creating ADO Groups Worksheet in the Excel File
$grpWksheet = $workbook.Sheets.Add()
$grpWksheet.Name = "ADO Groups"
$row = $col = 1
$grpWksheet.Cells.Item($row,$col) = "Group Name"
$col++
$grpWksheet.Cells.Item($row,$col) = "Description"
$col++
$grpWksheet.Cells.Item($row,$col) = "Origin"
$col++
$grpWksheet.Cells.Item($row,$col) = "Organization Level?"
$col++
$grpWksheet.Cells.Item($row,$col) = "Project Level?"
$col++
$grpWksheet.Cells.Item($row,$col) = "Organization Name"
$col++
$grpWksheet.Cells.Item($row,$col) = "Project Name"
$row++    
#region - reading through each array element and update worksheet with it      
$Global:allADOGroups | ForEach-Object{
    $col = 1
    $grpWksheet.Cells.Item($row,$col) = $_.GrpName
    $col++
    $grpWksheet.Cells.Item($row,$col) = $_.Description
    $col++
    $grpWksheet.Cells.Item($row,$col) = $_.Origin
    $col++
    $grpWksheet.Cells.Item($row,$col) = $_.OrgLevel
    $col++
    $grpWksheet.Cells.Item($row,$col) = $_.ProjLevel
    $col++
    $grpWksheet.Cells.Item($row,$col) = $_.OrgName
    $col++
    $grpWksheet.Cells.Item($row,$col) = $_.ProjName
    $row++
}
#endregion
$tabColorIndex = 45
Decorate-Worksheet $grpWksheet $col $tabColorIndex

$dataRange = $grpWksheet.UsedRange						
$dataRange.EntireColumn.AutoFit() | Out-Null
#endregion

#Stop alert window to overwrite excel file
$excel.DisplayAlerts = $false
#Delete unwanted worksheet
$Workbook.Worksheets.Item("Sheet1").Delete()

#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 Desktop
Start-Process $oFile

That’s it! Hope this script will help you managing your Azure DevOps environment. Let me know if you face any issue!