Azure DevOps Audit report in Excel

Posted on
This image has an empty alt attribute; its file name is image-3.png

In my last few posts I have discussed about Microsoft Azure DevOps Users, Groups, License audit etc. Periodically, we run those audits to perform access review and license true up. Refer to my previous posts here:

In this post, we will understand User entitlements on different Azure DevOps(ADO) projects in an Organization. Through this process, we will identify different entitlements for an user on ADO Projects and find out one user’s effective permissions.

Some common entitlements could be Project Reader,Contributor, Administrator etc.We can set up Custom permissions for a user. It will be reported as “Custom” in the entitlement list.

We will write a script which will perform audit on all Organizations, Projects and users and generate an Excel report.To run this script we will use Personal Access Token(PA Token) with required permissions on every Organization we scan. I will use a JSON file containing all Organizations and corresponding PA Tokens. Following is a sample JSON file. Update it with information from your environment and save it as ADOOrgDetails.json. We will use this file in the script provided by the end of the post.

ADOOrgDetails.json 

[
    {
      "OrgName": "org1",
      "PAToken": "org1secretpatokensample"
    },
    {
      "OrgName": "org2",
      "PAToken": "org2secretpatokensample"
    }
]

Note, storing PA Token in plain text file is not a good security practice. Use secret stores like Azure Key Vault or some other security store and retrieve PA Token from there.

With that, let’s start scripting!

We will use Azure DevOps REST API to collect information about all users in an Organization and to identify entitlements associated with user account. You may refer to Microsoft Documents to learn more about APIs.

So, before we access those APIs, let’s define authentication using PA Token retrieved from SON file:

$adoHeader = @{Authorization=("Basic {0}" -f [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f "",$pacToken))))}

Here are those two API Endpoint urls we will be using:

https://vsaex.dev.azure.com/<ORG NAME>/_apis/userentitlements?top=10000&sortOption=<OPTIONS>&api-version=5.1-preview.2
https://vsaex.dev.azure.com/<ORG NAME>/_apis/userentitlements/<USERID>?api-version=5.1-preview.2

Here <ORG NAME> is the name of the Organization and <OPTIONS> is a static query setting in the query to display user names in Ascending Order. Refer to the full script to understand how we are using it. <USERID> refers to the User GUID retrieved from the first API call. Also, note, I am using a limit of 10000 records in the query to limit the number of records the API query result will return.

Finally, once we have collected all required information including entitlements we will run a script block to generate an Excel file as output. If you have gone through my previous scripts in ADO you might already know how it works. If not, it is a simple process of creating an Excel workbook using Powershell and then creating an Worksheet with all required Column names we are collecting from ADO for each user and data for each user. As a last step, I am formatting the Worksheet to make it presentable and displaying on screen.

As a perquisite, you will need Microsoft Excel installed on your system. If you don’t have it, you can output everything to a csv file.

Here is the final script. Keep both the script .ps1 file and the JSON file in the same directory.

$options = "displayName Asc"
$oFile =  "${env:Temp}\AzureDevOpsUserEntitlements.xlsx"
if(Test-Path $oFile){Remove-Item $oFile -Force}
New-Item -Path $oFile -ItemType File

#region - Import Azure DevOps Credential JSON file
$allOrgs = Get-Content .\ADOOrgDetails.json | ConvertFrom-json
#endregion

#region - all arrays to store result data
$Global:allADOUsers = @()
#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 - Function to all User Details for an Organization
Function List-ADOUsers($orgName,$pacToken){
    $adoUserUri =  "https://vsaex.dev.azure.com/$($orgName)/_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 "",$pacToken))))}
    $response= Invoke-RestMethod -Uri $adoUserUri -ContentType "application/json" -headers $adoHeader -Method GET
    $response.members | ForEach-Object{
        $userName = $dirAlias = $mailAddress = ""
        $licDisplayName = $status = $createDate = $lastAccessDate = ""
        $userName = $_.user.displayName
        $dirAlias = $_.user.directoryAlias
        $mailAddress = $_.user.mailAddress    
        $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")
        $usrEntitlementUri = "https://vsaex.dev.azure.com/$($orgName)/_apis/userentitlements/$($_.id)?api-version=5.1-preview.2"
        $usrEntlData = Invoke-RestMethod -Uri $usrEntitlementUri -ContentType "application/json" -headers $adoHeader -Method GET      
        foreach($ent in $usrEntlData.projectEntitlements){
            $objUser = New-Object psObject
            $objUser | Add-Member OrgName $orgName
            $objUser | Add-Member UserName $userName
            $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 Project $ent.projectRef.name
            $objUser | Add-Member EntitlementLevel $ent.group.displayName
            $objUser | Add-Member Inherited $ent.projectPermissionInherited
            $Global:allADOUsers += $objUser 
        }       
    }
}
#endregion

#region - Parse through the Json File and retrieve Orginzation Name and PA Token

foreach($o in $allOrgs){
    List-ADOUsers $o.OrgName $o.PAToken
}

#endregion

#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
$usrWksheet = $workbook.Sheets.Add()
$usrWksheet.Name = "ADO User Entitlement"
$row = $col = 1
$usrWksheet.Cells.Item($row,$col) = "Organization Name"
$col++
$usrWksheet.Cells.Item($row,$col) = "User Name"
$col++
$usrWksheet.Cells.Item($row,$col) = "Directory Alias"
$col++
$usrWksheet.Cells.Item($row,$col) = "EMail Address"
$col++
$usrWksheet.Cells.Item($row,$col) = "License 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) = "Project Name"
$col++
$usrWksheet.Cells.Item($row,$col) = "Entitlements"
$col++
$usrWksheet.Cells.Item($row,$col) = "Permission Inherited?"
$row++  
#region - reading through each array element and update worksheet with it      
$Global:allADOUsers | ForEach-Object{
    $col = 1
    $usrWksheet.Cells.Item($row,$col) = $_.OrgName
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.UserName
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.DirAlias
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.MailAddress
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.LicDisplayName
    $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) = $_.Project
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.EntitlementLevel
    $col++
    $usrWksheet.Cells.Item($row,$col) = $_.Inherited
    $row++
}
#endregion
$tabColorIndex = 43
Decorate-Worksheet $usrWksheet $col $tabColorIndex

$dataRange = $usrWksheet.UsedRange						
$dataRange.EntireColumn.AutoFit() | Out-Null

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

Once you execute this script, you should get a file like following screenshot :

Excel Result