$orgName = '-------' # Name of the DevOps organisation
$api = '7.1-preview.4' # The version of the API to utilize

$personalAccessToken = '----------' #Create a Personal access Token in DevOps

# Install the AzDevOps module if required
if (-not(Get-Module -ListAvailable -Name AzDevOps)) {
    Install-Module AzDevOps
} 

# Connect to Azure DevOps & Azure
Connect-AdoOrganization -Orgname $orgName -PAT $personalAccessToken -ApiVersion $api
Connect-MgGraph -Scopes 'Application.Read.All'

# Get all available projects from ADO
$AdoProjects = Get-AdoProject

# Retrieve the Service Endpoints (Service connections) of all projects
$Result = @()
for($x=0; $x -lt $AdoProjects.length; $x++) {
    $requestUri = "https://dev.azure.com/$orgName/$($AdoProjects[$x].name)/_apis/serviceendpoint/endpoints?api-version=$api"
    $Result += (Invoke-AdoEndpoint -Uri ([System.Uri]::new($requestUri)) -Method Get -Headers $Global:azDevOpsHeader).value
    Write-Host "Retrieved $($Result.count) results total"
}

# Filter down to the Azure RM Service Endpoints
$azureEndpoints = $Result | Where-Object {$_.type -eq 'azurerm'}

$output = @()
$ErrorActionPreference = "Stop"
$output = for($i=0; $i -lt $azureEndPoints.count; $i++) {
    $record = $azureEndpoints[$i]
    # Get the usage history of each Service Endpoint, hardcoded api-version 7.0 otherwise it returns an error
    $historyResult = Invoke-AdoEndpoint -Uri ([System.Uri]::new("https://dev.azure.com/$orgName/$project/_apis/serviceendpoint/$($record.id)/executionhistory?api-version=7.0")) -Method Get -Headers $Global:azDevOpsHeader
    Write-Host "Retrieved $($historyResult.count) Usage records for $($record.name)"

    if($historyResult.count -gt 0) {
        $lastUsed = $historyResult[0].value.data[0].finishTime.Substring(0, 10)
    } else {
        $lastUsed = "Never"
    }
            
    # Get the Enterprise App name
    try {
        if($record.data.appObjectId -ne $null) {
            $enterpriseApp = Get-MgApplication -ApplicationId $record.data.appObjectId -ErrorAction Stop
            $registeredApp = Get-MgServicePrincipal -ServicePrincipalId $record.data.spnObjectId -ErrorAction Stop | select *
        } else {
            $enterpriseApp = "appObjectId is blank for $($record.name)"
            $registeredApp = "appObjectId is blank for $($record.name)"
        }

    } catch [System.Exception] {
        $enterpriseApp = "Enterprise Application not found"
        $registeredApp = "Registered Application not found"
    } catch {
        $message = $_.Exception.Message + " - " + $_.Exception.GetType().FullName
        Write-Error $message
        Write-Host "at $i"
    }

    $credentialExpiry = if($enterpriseApp.PasswordCredentials -ne $null) {($enterpriseApp.PasswordCredentials | sort-object EndDateTime -Descending | select EndDateTime -First 1).EndDateTime} else {$enterpriseApp}
    [pscustomobject]@{
        connectionName = $record.name
        lastUsed = $lastused
        clientExpiry = $credentialExpiry
        clientExpired = if($credentialExpiry -lt (Get-Date) -or $credentialExpiry.GetType().Name -ne 'DateTime') {$true} else {$false}
        createdBy = $record.createdBy.displayName
        createdByEmail = $record.createdBy.uniqueName
        description = $record.description
        authorization = $record.authorization.scheme
        isShared = $record.isShared
        isReady = $record.isReady
        subscriptionName = $record.data.subscriptionName
        enterpriseAppName = if($enterpriseApp.DisplayName -ne $null) {$enterpriseApp.DisplayName} else {$enterpriseApp}
        
        connectionId = $record.id
        subscriptionId = $record.data.subscriptionId
        enterpriseAppId = $record.data.appObjectId
        registeredAppId = if($registeredApp.AppId -ne $null) {$registeredApp.AppId} else {$registeredApp}
    }
}

$date = Get-Date
$output | export-csv -NoClobber -NoTypeInformation "C:\temp\report-$($date.Year)$($date.Month)$($date.Day)$($date.Millisecond).csv"

Disconnect-MgGraph