AzureAssess

0.1.23

Resolve-AzureAssessPrivilegedIdentities.ps1

<#
.SYNOPSIS
    Resolve the list of identities with privileged access to resources, subscriptions and management groups.
    All identities will be saved to a csv with an indication of the source ($null = direct assignemend; guid = group providing this access).
.EXAMPLE
    Resolve-AzureAssessPrivilegedIdentities.ps1
#>

function Resolve-AzureAssessPrivilegedIdentities {
    # load the role assignements
    $roleassignmentscsv = Join-Path "." -ChildPath "roleassignments.csv"

    # path where to export identities
    $principalscsv = Join-Path -Path "." -ChildPath "principals.csv"
    $membershipscsv = Join-Path -Path "." -ChildPath "memberhips.csv"
    $ownershipscsv = Join-Path -Path "." -ChildPath "owners.csv"
    "principalId,principalType,displayName" | Out-File -FilePath $principalscsv -Force
    "Id,GroupId" | Out-File -FilePath $membershipscsv -Force
    "Id,OwnerId" | Out-File -FilePath $ownershipscsv -Force

    # get PrivilegedRoleAssignements from csvs
    $roleAssignements = get-content -Path $roleassignmentscsv | ConvertFrom-Csv

    # gather identities from role assignements
    $privIdentities = @{}
    foreach($roleAssignment in $roleAssignements) {
        $privIdentities[$roleAssignment.principalId] = $roleAssignment | Select-Object principalId,principalType,@{N="displayName";E={""}}
    }


    # get access token for graph
    $graphToken = Get-AzAccessToken -ResourceTypeName MSGraph 

    # expand groups
    # table to retain memberships
    $memberships = @()
    $queries = new-object -TypeName System.Collections.Stack
    $privIdentities.Values | where-object { $_.principalType -ieq "Group"} | Select-Object @{N="id";E={$_.principalId}},@{N="url";E={"/groups/$($_.principalId)/transitiveMembers?`$top=999&`$select=id"}} | ForEach-Object { $queries.Push($_) }
    # get token for ms graph
    $reqtoken = $graphToken.Token | ConvertTo-SecureString -AsPlainText -Force
    $count = 0
    while($queries.Count -gt 0) {
        $requests = @()
        while($requests.Count -lt 20 -and $queries.Count -gt 0) {
            $q = $queries.Pop()
            $requests += "" | select-object @{N="id"; E={$q.id}},@{N="method";E={"GET"}},@{N="url"; E={$q.url}}
        }
        $batchreq = "" | select-object @{N="requests";E={@(,$requests)}}
        $resp = Invoke-RestMethod -ContentType "application/json" -Authentication Bearer -Token $reqtoken -Method Post -Body ($batchreq | ConvertTo-Json -Compress -Depth 2) -Uri "https://graph.microsoft.com/v1.0/`$batch"
        # check for throttling
        if (($resp.responses | Where-Object {$_.status -eq 429}).Count -gt 0) {
            Start-Sleep -Seconds 60
        } else {
            Start-Sleep -Seconds 5
        }
        # requeue get failed requests
        $failedresps = $resp.responses | Where-Object { [int]($_.status/100) -ne 2 -and $_.status -ne 404} 
        foreach($failedresp in $failedresps) {
            # get corresponding request
            $req = $requests | Where-Object { $_.id -eq $failedresp.id} | Select-Object id,url
            # requeue request
            $queries.Push($req)
        }
        # parse successfull responses
        $successresps = $resp.responses | Where-Object { [int]($_.status/100) -eq 2 }
        # fill in responses
        foreach($response in $successresps) {
            foreach ($value in $response.body.value) {
                $type = "unknown"
                if ($value."@odata.type" -ieq "#microsoft.graph.user") {
                    $type = "User"
                } elseif ($value."@odata.type" -ieq "#microsoft.graph.group") {
                    $type = "Group"
                } elseif ($value."@odata.type" -ieq "#microsoft.graph.servicePrincipal") {
                    $type = "ServicePrincipal"
                }
                $memberships += "" | Select-Object @{N="Id";E={$value.id}},@{N="GroupId";E={$response.Id}}
                if ($value.id -notin $privIdentities.Keys) {
                    $privIdentities[$value.id] = "" | Select-Object @{N="principalId";E={$value.id}},@{N="principalType";E={$type}},@{N="displayName";E={""}}
                }
            } 
            if ($response."@odata.nextLink") {
                # create request for nextlink
                $req = "" | Select-Object @{N="id";E={$response.id}},@{N="url";E={$response."@odata.nextLink"}}
                # enqueue request
                $queries.Push($req)
            }
            $count += 1
        }
        Write-Progress -Activity "Getting Memberships" -Status "$count of $($count + $queries.Count)" -PercentComplete ($count * 100 / ($count + $queries.Count))
    }

    # list of ownerships
    $ownerships = @()
    # check owners of groups (has only users and serviceprincipals)
    $queries = New-Object -TypeName System.Collections.Stack
    $privIdentities.Values | where-object { $_.principalType -ieq "Group"} | ForEach-Object { $queries.Push($_.principalId) }
    # get token for ms graph
    $reqtoken = $graphToken.Token | ConvertTo-SecureString -AsPlainText -Force
    $count = 0
    while($queries.Count -gt 0) {
        $requests = @()
        while($requests.Count -lt 20 -and $queries.Count -gt 0) {
            $requests += $queries.Pop() | select-object @{N="id"; E={$_}},@{N="method";E={"GET"}},@{N="url"; E={"/groups/$($_)/owners?`$top=999&`$select=id"}}
        }
        $batchreq = "" | select-object @{N="requests";E={@(,$requests)}}
        $resp = Invoke-RestMethod -ContentType "application/json" -Authentication Bearer -Token $reqtoken -Method Post -Body ($batchreq | ConvertTo-Json -Compress -Depth 2) -Uri "https://graph.microsoft.com/v1.0/`$batch"
        # check for throttling
        if (($resp.responses | Where-Object {$_.status -eq 429}).Count -gt 0) {
            Start-Sleep -Seconds 60
        } else {
            Start-Sleep -Seconds 5
        }
        # requeue get failed requests
        $resp.responses | Where-Object { [int]($_.status/100) -ne 2 -and $_.status -ne 404} | ForEach-Object { $queries.Push($_.id) }
        # parse successfull responses
        $successresps = $resp.responses | Where-Object { [int]($_.status/100) -eq 2 }
        # fill in responses
        foreach($response in $successresps) {
            foreach ($value in $response.body.value) {
                $type = "unknown"
                if ($value."@odata.type" -ieq "#microsoft.graph.user") {
                    $type = "User"
                } elseif ($value."@odata.type" -ieq "#microsoft.graph.servicePrincipal") {
                    $type = "ServicePrincipal"
                }
                $ownerships += "" | Select-Object @{N="Id";E={$response.id}},@{N="OwnerId";E={$value.Id}}
                if ($value.id -notin $privIdentities.Keys) {
                    $privIdentities[$value.id] = "" | Select-Object @{N="principalId";E={$value.id}},@{N="principalType";E={$type}},@{N="displayName";E={""}}
                }
            } 
            if ($response."@odata.nextLink") {
                # create request for nextlink
                $req = "" | Select-Object @{N="id";E={$response.id}},@{N="url";E={$response."@odata.nextLink"}}
                # enqueue request
                $queries.Push($req)
            }
            $count += 1
        }
        Write-Progress -Activity "Getting Group Owners" -Status "$count of $($count + $queries.Count)" -PercentComplete ($count * 100 / ($count + $queries.Count))
    }


    # owner check for serviceprincipals
    # TODO - handle potential of long nesting
    $queries = New-Object -TypeName System.Collections.Stack
    $privIdentities.Values | Where-Object { $_.principalType -eq "ServicePrincipal" } | ForEach-Object { $queries.Push($_.principalId)}
    $count = 0
    while ($queries.Count -gt 0) {
        $requests = @()
        while($requests.Count -lt 20 -and $queries.Count -gt 0) {
            $requests += $queries.Pop() | select-object @{N="id"; E={$_}},@{N="method";E={"GET"}},@{N="url"; E={"/servicePrincipals/$($_)/owners?`$top=999&`$select=id"}}
        }
        $batchreq = "" | select-object @{N="requests";E={@(,$requests)}}
        $resp = Invoke-RestMethod -ContentType "application/json" -Authentication Bearer -Token $reqtoken -Method Post -Body ($batchreq | ConvertTo-Json -Compress -Depth 2) -Uri "https://graph.microsoft.com/v1.0/`$batch"
        # check for throttling
        if (($resp.responses | Where-Object {$_.status -eq 429}).Count -gt 0) {
            Start-Sleep -Seconds 60
        } else {
            Start-Sleep -Seconds 5
        }
        # add requests havint returned not found to the counter
        $count += ($resp.responses | Where-Object { $_.status -eq 404 }).Count
        # requeue get failed requests
        $resp.responses | Where-Object { [int]($_.status/100) -ne 2 -and $_.status -ne 404} | ForEach-Object { $queries.Push($_.id) }
        # parse successfull responses
        $successresps = $resp.responses | Where-Object { [int]($_.status/100) -eq 2 }
        # fill in responses
        foreach($response in $successresps) {
            foreach ($value in $response.body.value) {
                $type = "unknown"
                if ($value."@odata.type" -ieq "#microsoft.graph.user") {
                    $type = "User"
                } elseif ($value."@odata.type" -ieq "#microsoft.graph.servicePrincipal") {
                    $type = "ServicePrincipal"
                    if ($value.id -ne $response.id -and $value.id -notin $privIdentities.Keys) {
                        $queries.Push($value.id)
                    }
                }
                $ownerships += "" | Select-Object @{N="Id";E={$response.id}},@{N="OwnerId";E={$value.Id}}
                if ($value.id -notin $privIdentities.Keys) {
                    $privIdentities[$value.id] = "" | Select-Object @{N="principalId";E={$value.id}},@{N="principalType";E={$type}},@{N="displayName";E={""}}
                }
                $count += 1
            } 
            if ($response."@odata.nextLink") {
                # create request for nextlink
                $req = "" | Select-Object @{N="id";E={$response.id}},@{N="url";E={$response."@odata.nextLink"}}
                # enqueue request
                $queries.Push($req)
            }
        }
        Write-Progress -Activity "Getting ServicePrincipals Owners" -Status "$count of $($count + $queries.Count)" -PercentComplete ($count * 100 / ($count + $queries.Count))
    }

    # resolve privilegedIdentities displaynames
    $directoryobjects = @{
        ids = @($privIdentities.Keys)
        types = @(
            "user"
            "group"
            "serviceprincipal"
        )
    } | ConvertTo-Json -Depth 2 -Compress
    $res = Invoke-RestMethod -ContentType "application/json" -Authentication Bearer -Token $reqtoken -Method Post -Body $directoryobjects -Uri "https://graph.microsoft.com/v1.0/directoryObjects/getByIds?`$select=id,displayName"
    Start-Sleep -Seconds 5
    foreach($value in $res.value) {
        $privIdentities[$value.id].displayName = $value.displayName
    }
    foreach($id in $privIdentities.Keys) {
        if ($privIdentities[$id].displayName -eq "") {
            $privIdentities[$id].displayName = $id
        }
    }

    # save extracted information
    $privIdentities.Values | Export-Csv -Path $principalscsv -NoTypeInformation -Append
    $memberships | Export-Csv -Path $membershipscsv -NoTypeInformation -Append
    $ownerships | Export-Csv -Path $ownershipscsv -NoTypeInformation -Append

    # path to export user details
    $userscsv = Join-Path -Path "." -ChildPath "users.csv"
    # path to export group details
    $groupscsv = Join-Path -Path "." -ChildPath "groups.csv"
    # path to export service principal details
    $serviceprincipalscsv = Join-Path -Path "." -ChildPath "serviceprincipals.csv"

    # check group details
    # create file and headers
    "id,displayName,mailEnabled,securityEnabled,onPremisesSyncEnabled" | Out-File -FilePath $groupscsv -Force
    # get groups informations
    $queries = New-Object -TypeName System.Collections.Stack
    $privIdentities.Values | Where-Object { $_.principalType -eq "Group" } | ForEach-Object { $queries.Push($_.principalId) }
    $count = 0
    while($queries.Count -gt 0) {
        $requests = @()
        while($requests.Count -lt 20 -and $queries.Count -gt 0) {
            $groupid = $queries.Pop()
            $requests += "" | select-object @{N="id"; E={$groupid}},@{N="method";E={"GET"}},@{N="url"; E={"/groups/$($groupid)?`$select=id,displayName,mailEnabled,securityEnabled,onPremisesSyncEnabled"}}
        }
        $batchreq = "" | select-object @{N="requests";E={@(,$requests)}}
        $resp = Invoke-RestMethod -ContentType "application/json" -Authentication Bearer -Token $reqtoken -Method Post -Body ($batchreq | ConvertTo-Json -Compress -Depth 2) -Uri "https://graph.microsoft.com/v1.0/`$batch"
        # check for throttling
        if (($resp.responses | Where-Object {$_.status -eq 429}).Count -gt 0) {
            Start-Sleep -Seconds 60
        } else {
            Start-Sleep -Seconds 5
        }
        # requeue get failed requests
        $resp.responses | Where-Object { [int]($_.status/100) -ne 2 -and $_.status -ne 404} | ForEach-Object { $queries.Push($_.id) } 
        # fill in responses
        $resp.responses | Where-Object { [int]($_.status/100) -eq 2 } | ForEach-Object{ $count += 1; $_.body } | Select-Object id,displayName,mailEnabled,securityEnabled,onPremisesSyncEnabled | Export-Csv -Path $groupscsv -NoTypeInformation -Append
        Write-Progress -Activity "Getting Groups" -Status "$count of $($count + $queries.Count)" -PercentComplete ($count * 100 / ($count + $queries.Count))
    }

    # check service principals details
    $validitydate = get-date
    $appqueries = New-Object -TypeName System.Collections.Stack
    # create file and headers
    "id,appId,displayName,servicePrincipalType,expiredPasswordCredentials,validPasswordCredentials,expiredKeyCredentials,validKeyCredentials,federatedIdentityCredentials,accountEnabled,lastSignIn,lastAzureSignIn" | Out-File -FilePath $serviceprincipalscsv -Force
    $serviceprincipals = @{}
    # get service principals informations
    $queries = New-Object -TypeName System.Collections.Stack
    $privIdentities.Values | Where-Object { $_.principalType -eq "ServicePrincipal" } | ForEach-Object { $queries.Push($_.principalId) }
    $count = 0
    while($queries.Count -gt 0) {
        $requests = @()
        while($requests.Count -lt 20 -and $queries.Count -gt 0) {
            $spid = $queries.Pop()
            $requests += "" | select-object @{N="id"; E={$spid}},@{N="method";E={"GET"}},@{N="url"; E={"/servicePrincipals/$($spid)?`$select=id,appId,displayName,servicePrincipalType,passwordCredentials,keyCredentials,federatedIdentityCredentials,accountEnabled"}}
        }
        $batchreq = "" | select-object @{N="requests";E={@(,$requests)}}
        $resp = Invoke-RestMethod -ContentType "application/json" -Authentication Bearer -Token $reqtoken -Method Post -Body ($batchreq | ConvertTo-Json -Compress -Depth 2) -Uri "https://graph.microsoft.com/v1.0/`$batch"
        # check for throttling
        if (($resp.responses | Where-Object {$_.status -eq 429}).Count -gt 0) {
            Start-Sleep -Seconds 60
        } else {
            Start-Sleep -Seconds 5
        }
        # requeue get failed requests
        $resp.responses | Where-Object { [int]($_.status/100) -ne 2 -and $_.status -ne 404 } | ForEach-Object { $queries.Push($_.id) }
        # fill app queries
        $resp.responses | Where-Object { [int]($_.status/100) -eq 2 } | ForEach-Object{ $appqueries.Push($_.body.appId) }
        # fill in responses
        $resp.responses | Where-Object { [int]($_.status/100) -eq 2 } | ForEach-Object{ $count += 1; $_.body } `
            | Select-Object `
                id, `
                appId, `
                displayName, `
                servicePrincipalType, `
                @{N="expiredPasswordCredentials";E={@($_.passwordCredentials | Where-Object {$_.endDateTime -le $validitydate}).Count}}, `
                @{N="validPasswordCredentials";E={@($_.passwordCredentials | Where-Object {$_.endDateTime -gt $validitydate}).Count}}, `
                @{N="expiredKeyCredentials";E={@($_.keyCredentials | Where-Object {$_.endDateTime -le $validitydate}).Count}}, `
                @{N="validKeyCredentials";E={@($_.keyCredentials | Where-Object {$_.endDateTime -le $validitydate}).Count}}, `
                @{N="federatedIdentityCredentials";E={$_.federatedIdentityCredentials.Count}},accountEnabled, `
                @{N="lastAzureSignIn";E={$null}}, `
                @{N="lastSignIn";E={$null}} `
            | ForEach-Object { $serviceprincipals[$_.appId] = $_}
        Write-Progress -Activity "Getting ServicePrincipals" -Status "$count of $($count + $queries.Count)" -PercentComplete ($count * 100 / ($count + $queries.Count))
    }

    # get app infromations
    $count = 0
    while($appqueries.Count -gt 0) {
        $requests = @()
        while($requests.Count -lt 20 -and $appqueries.Count -gt 0) {
            $appid = $appqueries.Pop()
            $requests += "" | select-object @{N="id"; E={$appid}},@{N="method";E={"GET"}},@{N="url"; E={"/applications?`$filter=appId+eq+'$appid'&`$select=id,appId,displayName,keyCredentials,passwordCredentials,federatedIdentityCredentials"}}
        }
        $batchreq = "" | select-object @{N="requests";E={@(,$requests)}}
        $resp = Invoke-RestMethod -ContentType "application/json" -Authentication Bearer -Token $reqtoken -Method Post -Body ($batchreq | ConvertTo-Json -Compress -Depth 2) -Uri "https://graph.microsoft.com/v1.0/`$batch"
        # check for throttling
        if (($resp.responses | Where-Object {$_.status -eq 429}).Count -gt 0) {
            Start-Sleep -Seconds 60
        } else {
            Start-Sleep -Seconds 5
        }
        # requeue get failed requests
        $resp.responses | Where-Object { [int]($_.status/100) -ne 2 -and $_.status -ne 404} | ForEach-Object { $queries.Push($_.id) }
        # udpate service principals
        $resp.responses | Where-Object { [int]($_.status/100) -eq 2 } | ForEach-Object{ $_.body.value } `
            | Select-Object id,appId,displayName,@{N="expiredPasswordCredentials";E={@($_.passwordCredentials | Where-Object {$_.endDateTime -le $validitydate}).Count}},@{N="validPasswordCredentials";E={@($_.passwordCredentials | Where-Object {$_.endDateTime -gt $validitydate}).Count}},@{N="expiredKeyCredentials";E={@($_.keyCredentials | Where-Object {$_.endDateTime -le $validitydate}).Count}},@{N="validKeyCredentials";E={@($_.keyCredentials | Where-Object {$_.endDateTime -le $validitydate}).Count}},@{N="federatedIdentityCredentials";E={$_.federatedIdentityCredentials.Count}} `
            | ForEach-Object {
                $count += 1 
                $serviceprincipals[$_.appId].expiredKeyCredentials += $_.expiredKeyCredentials
                $serviceprincipals[$_.appId].validKeyCredentials += $_.validKeyCredentials
                $serviceprincipals[$_.appId].expiredPasswordCredentials += $_.expiredPasswordCredentials
                $serviceprincipals[$_.appId].validPasswordCredentials += $_.validPasswordCredentials
                $serviceprincipals[$_.appId].federatedIdentityCredentials += $_.federatedIdentityCredentials
            }
        Write-Progress -Activity "Getting Applications" -Status "$count of $($count + $appqueries.Count)" -PercentComplete ($count * 100 / ($count + $appqueries.Count))
    }
    
    # check user details
    # create file and headers
    "id,displayName,userPrincipalName,mail,accountEnabled,department,userType,onPremisesSyncEnabled,lastSignIn,lastAzureSignIn" | Out-File -FilePath $userscsv -Force
    # get user informations
    $queries = New-Object -TypeName System.Collections.Stack
    $privIdentities.Values | Where-Object { $_.principalType -eq "User" } | ForEach-Object { $queries.Push($_.principalId) }
    $count = 0
    $users = @()
    while($queries.Count -gt 0) {
        $requests = @()
        while($requests.Count -lt 20 -and $queries.Count -gt 0) {
            $userid = $queries.Pop()
            $requests += "" | select-object @{N="id"; E={$userid}},@{N="method";E={"GET"}},@{N="url"; E={"/users/$($userid)?`$select=id,displayName,userPrincipalName,mail,accountEnabled,department,userType,signInActivity,onPremisesSyncEnabled"}}
        }
        $batchreq = "" | select-object @{N="requests";E={@(,$requests)}}
        $resp = Invoke-RestMethod -ContentType "application/json" -Authentication Bearer -Token $reqtoken -Method Post -Body ($batchreq | ConvertTo-Json -Compress -Depth 2) -Uri "https://graph.microsoft.com/beta/`$batch"
        # check for throttling
        if (($resp.responses | Where-Object {$_.status -eq 429}).Count -gt 0) {
            Start-Sleep -Seconds 60
        } else {
            Start-Sleep -Seconds 5
        }
        # requeue get failed requests
        $resp.responses | Where-Object { [int]($_.status/100) -ne 2 -and $_.status -ne 404} | ForEach-Object { $queries.Push($_.id) } 
        # fill in responses
        $users += $resp.responses | Where-Object { [int]($_.status/100) -eq 2 } | ForEach-Object{ $count += 1; $_.body } | Select-Object `
            id, `
            displayName, `
            userPrincipalName, `
            mail,`
            accountEnabled,`
            department,`
            userType,`
            @{N="onPremisesSyncEnabled";E={$_.onPremisesSyncEnabled -eq $true}}, `
            @{N="lastSignIn";E={$_.signInActivity.lastSuccessfulSignInDateTime}}, `
            @{N="lastAzureSignIn";E={$_.signInActivity.lastSuccessfulSignInDateTime}}
        Write-Progress -Activity "Getting Users" -Status "$count of $($count + $queries.Count)" -PercentComplete ($count * 100 / ($count + $queries.Count))
    }


    # determine if there is a log analytics to get details
    # looking at Azure AD diagnostic settings
    $res = Invoke-AzRestMethod -Method "GET" -path "/providers/microsoft.aadiam/diagnosticSettings/?api-version=2017-04-01"
    $aaddiagsettings = @()
    if ($res.StatusCode -eq 200) {
        $aaddiagsettings += ($res.Content | convertfrom-json).value | Select-Object -ExpandProperty properties `
            | Where-Object { 
                ![string]::IsNullorEmpty($_.workspaceId) -and
                ($_.logs | Where-Object { $_.category -eq "SignInLogs" -and $_.enabled }).count -gt 0 -and # User SignIn logs
                ($_.logs | Where-Object { $_.category -eq "NonInteractiveUserSignInLogs" -and $_.enabled }).count -gt 0 # User Non Interactive SignIn logs
            } `
            | Select-Object workspaceId,@{N="kind";E={"user"}},@{N="retention";E={($_.logs | Where-Object { $_.category -eq "SignInLogs" -and $_.enabled }).retentionPolicy.days}}
        $aaddiagsettings += ($res.Content | convertfrom-json).value | Select-Object -ExpandProperty properties `
            | Where-Object { 
                ![string]::IsNullorEmpty($_.workspaceId) -and
                ($_.logs | Where-Object { $_.category -eq "ServicePrincipalSignInLogs" -and $_.enabled }).count -gt 0 -and # SPN SignIn logs
                ($_.logs | Where-Object { $_.category -eq "ManagedIdentitySignInLogs" -and $_.enabled }).count -gt 0 # MI SignIn logs
            } `
            | Select-Object workspaceId,@{N="kind";E={"spn"}},@{N="retention";E={($_.logs | Where-Object { $_.category -eq "ServicePrincipalSignInLogs" -and $_.enabled }).retentionPolicy.days}}
    }
    $userworkspaceId = $aaddiagsettings | Where-Object { $_.kind -eq "user" } | Sort-Object -Property retention -Descending | Select-Object -First 1 -ExpandProperty workspaceId
    $spnworkspaceId = $aaddiagsettings | Where-Object { $_.kind -eq "spn" } | Sort-Object -Property retention -Descending | Select-Object -First 1 -ExpandProperty workspaceId

    # get user last signin to Azure with a maxspan of last six months
    if (![string]::IsNullorEmpty($userworkspaceId)) {
        $sub = ($userworkspaceId -split "/")[2]
        $rg = ($userworkspaceId -split "/")[4]
        $name = ($userworkspaceId -split "/")[-1]
        $kql = "
        SigninLogs
        | where ResourceDisplayName == 'Windows Azure Service Management API' and TimeGenerated > ago(6*30d) and ResultType == 0
        | project TimeGenerated,UserId
        | summarize TimeGenerated=max(TimeGenerated) by UserId
        | union (
            AADNonInteractiveUserSignInLogs
            | where ResourceDisplayName == 'Windows Azure Service Management API' and TimeGenerated > ago(6*30d) and ResultType == 0
            | project TimeGenerated,UserId
            | summarize TimeGenerated=max(TimeGenerated) by UserId
        )
        | summarize TimeGenerated=max(TimeGenerated) by UserId
        "
        $count = 0
        Write-Progress -Activity "Check Users last signing to Azure" -Status "$count of $($users.Count)" -PercentComplete ($count * 100 / $users.Count)
        Set-AzContext -Subscription $sub | Out-Null
        $ws = Get-AzOperationalInsightsWorkspace -Name $name -ResourceGroupName $rg
        $query = Invoke-AzOperationalInsightsQuery -WorkspaceId $ws.CustomerId -Query $kql
        foreach($user in $users) {
            $timegenerated = ($query.Results | Where-Object { $_.UserId -eq $user.id }).TimeGenerated
            if (![string]::IsNullorEmpty($timegenerated)) {
                $user.lastAzureSignIn = get-date -date $timegenerated
            }
            $count += 1
            Write-Progress -Activity "Check Users last signing to Azure" -Status "$count of $($users.Count)" -PercentComplete ($count * 100 / $users.Count)
        }
    }

    # get serviceprincipal last signin to Azure with a maxspan of last six months
    if (![string]::IsNullorEmpty($userworkspaceId)) {
        $sub = ($spnworkspaceId -split "/")[2]
        $rg = ($spnworkspaceId -split "/")[4]
        $name = ($spnworkspaceId -split "/")[-1]
        $kql = "
        AADServicePrincipalSignInLogs
        | where TimeGenerated > ago(6*30d) and ResultType == 0
        | project TimeGenerated,ResourceDisplayName,AppId
        | summarize LastAzureSignIn=maxif(TimeGenerated,ResourceDisplayName == 'Windows Azure Service Management API'),LastSignIn=max(TimeGenerated) by AppId
        | union (
            AADManagedIdentitySignInLogs
            | where TimeGenerated > ago(6*30d) and ResultType == 0
            | project TimeGenerated,ResourceDisplayName,AppId
        | summarize LastAzureSignIn=maxif(TimeGenerated,ResourceDisplayName == 'Windows Azure Service Management API'),LastSignIn=max(TimeGenerated) by AppId
        )
        | summarize LastAzureSignIn=max(LastAzureSignIn),LastSignIn=max(LastSignIn) by AppId
        "
        $count = 0
        Write-Progress -Activity "Check ServicePrincipal last signing to Azure" -Status "$count of $($serviceprincipals.Keys.Count)" -PercentComplete ($count * 100 / $serviceprincipals.Keys.Count)
        Set-AzContext -Subscription $sub | Out-Null
        $ws = Get-AzOperationalInsightsWorkspace -Name $name -ResourceGroupName $rg
        $query = Invoke-AzOperationalInsightsQuery -WorkspaceId $ws.CustomerId -Query $kql
        foreach($appid in $serviceprincipals.Keys) {
            $spninfo = $query.Results | Where-Object { $_.AppId -eq $appid }
            $lastsignin = $spninfo.LastSignIn
            $lastazuresignin = $spninfo.LastAzureSignIn
            if (![string]::IsNullorEmpty($lastazuresignin)) {
                $serviceprincipals[$appid].lastAzureSignIn = get-date -date $lastazuresignin
            }
            $lastazuresignin = $spninfo.LastSignIn
            if (![string]::IsNullorEmpty($lastsignin)) {
                $serviceprincipals[$appid].lastSignIn = get-date -date $lastsignin
            }
            $count += 1
            Write-Progress -Activity "Check ServicePrincipal last signing to Azure" -Status "$count of $($serviceprincipals.Keys.Count)" -PercentComplete ($count * 100 / $serviceprincipals.Keys.Count)
        }
    }

    # export users to csv
    $users |  Export-Csv -Path $userscsv -NoTypeInformation -Append
    # save service principals
    $serviceprincipals.Values | Export-Csv -Path $serviceprincipalscsv -NoTypeInformation -Append

    # do not extend if memberships are already found
    if (($roleAssignements | Where-Object { $_.source -eq "Group"}).Count -eq 0) {
        # extend roleassignments with memberships
        $count = 0
        foreach($membership in $memberships) {
            $roleAssignements += @($roleAssignements | Where-Object {$_.principalId -eq $membership.GroupId}) `
                | Select-Object `
                    "roleId", `
                    "role", `
                    @{N="principalId";E={$membership.Id}}, `
                    @{N="principalType";E={$privIdentities[$membership.Id].principalType}}, `
                    "scope", `
                    @{N="source";E={"Group"}},`
                    "resourceType", `
                    "resourceName", `
                    "link"
            $count += 1
            Write-Progress -Activity "Expand Role Assignment with Memberships" -Status "$count of $($memberships.Count)" -PercentComplete ($count * 100 / $memberships.Count)
        }
    }

    # do not extend if ownerhips are already found
    if (($roleAssignements | Where-Object { $_.source -eq "Owner"}).Count -eq 0) {
        $count = 0
        # extend roleassignments with ownerships
        foreach($ownership in $ownerships) {            
            $roleAssignements += @($roleAssignements | Where-Object {$_.principalId -eq $ownership.Id}) `
                | Select-Object `
                    "roleId", `
                    "role", `
                    @{N="principalId";E={$ownership.OwnerId}}, `
                    @{N="principalType";E={$privIdentities[$ownership.OwnerId].principalType}}, `
                    "scope", `
                    @{N="source";E={"Owner"}},`
                    "resourceType", `
                    "resourceName", `
                    "link"
            $count += 1
            Write-Progress -Activity "Expand Role Assignment with Ownerships" -Status "$count of $($ownerships.Count)" -PercentComplete ($count * 100 / $ownerships.Count)
        }
    }

    $roleAssignements | export-csv -Path $roleassignmentscsv -NoTypeInformation -Force
}
# SIG # Begin signature block
# MIIRWAYJKoZIhvcNAQcCoIIRSTCCEUUCAQExDzANBglghkgBZQMEAgEFADB5Bgor
# BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG
# KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCCpjihNGISVYTjN
# r5F/RbuGzClKTPYs6LHiFsQ7e3yMUKCCDZIwgga5MIIEoaADAgECAhEAmaOACiZV
# O2Wr3G6EprPqOTANBgkqhkiG9w0BAQwFADCBgDELMAkGA1UEBhMCUEwxIjAgBgNV
# BAoTGVVuaXpldG8gVGVjaG5vbG9naWVzIFMuQS4xJzAlBgNVBAsTHkNlcnR1bSBD
# ZXJ0aWZpY2F0aW9uIEF1dGhvcml0eTEkMCIGA1UEAxMbQ2VydHVtIFRydXN0ZWQg
# TmV0d29yayBDQSAyMB4XDTIxMDUxOTA1MzIxOFoXDTM2MDUxODA1MzIxOFowVjEL
# MAkGA1UEBhMCUEwxITAfBgNVBAoTGEFzc2VjbyBEYXRhIFN5c3RlbXMgUy5BLjEk
# MCIGA1UEAxMbQ2VydHVtIENvZGUgU2lnbmluZyAyMDIxIENBMIICIjANBgkqhkiG
# 9w0BAQEFAAOCAg8AMIICCgKCAgEAnSPPBDAjO8FGLOczcz5jXXp1ur5cTbq96y34
# vuTmflN4mSAfgLKTvggv24/rWiVGzGxT9YEASVMw1Aj8ewTS4IndU8s7VS5+djSo
# McbvIKck6+hI1shsylP4JyLvmxwLHtSworV9wmjhNd627h27a8RdrT1PH9ud0IF+
# njvMk2xqbNTIPsnWtw3E7DmDoUmDQiYi/ucJ42fcHqBkbbxYDB7SYOouu9Tj1yHI
# ohzuC8KNqfcYf7Z4/iZgkBJ+UFNDcc6zokZ2uJIxWgPWXMEmhu1gMXgv8aGUsRda
# CtVD2bSlbfsq7BiqljjaCun+RJgTgFRCtsuAEw0pG9+FA+yQN9n/kZtMLK+Wo837
# Q4QOZgYqVWQ4x6cM7/G0yswg1ElLlJj6NYKLw9EcBXE7TF3HybZtYvj9lDV2nT8m
# FSkcSkAExzd4prHwYjUXTeZIlVXqj+eaYqoMTpMrfh5MCAOIG5knN4Q/JHuurfTI
# 5XDYO962WZayx7ACFf5ydJpoEowSP07YaBiQ8nXpDkNrUA9g7qf/rCkKbWpQ5bou
# fUnq1UiYPIAHlezf4muJqxqIns/kqld6JVX8cixbd6PzkDpwZo4SlADaCi2JSplK
# ShBSND36E/ENVv8urPS0yOnpG4tIoBGxVCARPCg1BnyMJ4rBJAcOSnAWd18Jx5n8
# 58JSqPECAwEAAaOCAVUwggFRMA8GA1UdEwEB/wQFMAMBAf8wHQYDVR0OBBYEFN10
# XUwA23ufoHTKsW73PMAywHDNMB8GA1UdIwQYMBaAFLahVDkCw6A/joq8+tT4HKbR
# Og79MA4GA1UdDwEB/wQEAwIBBjATBgNVHSUEDDAKBggrBgEFBQcDAzAwBgNVHR8E
# KTAnMCWgI6Ahhh9odHRwOi8vY3JsLmNlcnR1bS5wbC9jdG5jYTIuY3JsMGwGCCsG
# AQUFBwEBBGAwXjAoBggrBgEFBQcwAYYcaHR0cDovL3N1YmNhLm9jc3AtY2VydHVt
# LmNvbTAyBggrBgEFBQcwAoYmaHR0cDovL3JlcG9zaXRvcnkuY2VydHVtLnBsL2N0
# bmNhMi5jZXIwOQYDVR0gBDIwMDAuBgRVHSAAMCYwJAYIKwYBBQUHAgEWGGh0dHA6
# Ly93d3cuY2VydHVtLnBsL0NQUzANBgkqhkiG9w0BAQwFAAOCAgEAdYhYD+WPUCia
# U58Q7EP89DttyZqGYn2XRDhJkL6P+/T0IPZyxfxiXumYlARMgwRzLRUStJl490L9
# 4C9LGF3vjzzH8Jq3iR74BRlkO18J3zIdmCKQa5LyZ48IfICJTZVJeChDUyuQy6rG
# DxLUUAsO0eqeLNhLVsgw6/zOfImNlARKn1FP7o0fTbj8ipNGxHBIutiRsWrhWM2f
# 8pXdd3x2mbJCKKtl2s42g9KUJHEIiLni9ByoqIUul4GblLQigO0ugh7bWRLDm0Cd
# Y9rNLqyA3ahe8WlxVWkxyrQLjH8ItI17RdySaYayX3PhRSC4Am1/7mATwZWwSD+B
# 7eMcZNhpn8zJ+6MTyE6YoEBSRVrs0zFFIHUR08Wk0ikSf+lIe5Iv6RY3/bFAEloM
# U+vUBfSouCReZwSLo8WdrDlPXtR0gicDnytO7eZ5827NS2x7gCBibESYkOh1/w1t
# VxTpV2Na3PR7nxYVlPu1JPoRZCbH86gc96UTvuWiOruWmyOEMLOGGniR+x+zPF/2
# DaGgK2W1eEJfo2qyrBNPvF7wuAyQfiFXLwvWHamoYtPZo0LHuH8X3n9C+xN4YaNj
# t2ywzOr+tKyEVAotnyU9vyEVOaIYMk3IeBrmFnn0gbKeTTyYeEEUz/Qwt4HOUBCr
# W602NCmvO1nm+/80nLy5r0AZvCQxaQ4wggbRMIIEuaADAgECAhBNn7NsFkgeJAAl
# SKxTsSjWMA0GCSqGSIb3DQEBCwUAMFYxCzAJBgNVBAYTAlBMMSEwHwYDVQQKExhB
# c3NlY28gRGF0YSBTeXN0ZW1zIFMuQS4xJDAiBgNVBAMTG0NlcnR1bSBDb2RlIFNp
# Z25pbmcgMjAyMSBDQTAeFw0yNDAxMTYxMjAwMzRaFw0yNTAxMTUxMjAwMzNaMHcx
# CzAJBgNVBAYTAkJFMRgwFgYDVQQIDA9XYWxsb29uIEJyYWJhbnQxHjAcBgNVBAoM
# FU9wZW4gU291cmNlIERldmVsb3BlcjEuMCwGA1UEAwwlT3BlbiBTb3VyY2UgRGV2
# ZWxvcGVyLCBDZWRyaWMgQmxvbWFydDCCAiIwDQYJKoZIhvcNAQEBBQADggIPADCC
# AgoCggIBALp1WzPLBGmSjvJ2se9K+HWIzxlPIh9vmddaj0Sc6Yz3jJizVN7WphR7
# 1kR4KFEfCFL6eleZlIkqDMkNfwBTpfVCx7MYR85YSceHo8ieEIV5MmWSZ1CR36/A
# wXrUvXzlX85Rf/RuotfluSmdhTRoBbCN9aIsSm50pEZixvUOcipTNZbuY29NvjXP
# JAH1RX0od6QYCGt/v+5C+hBippk+QohxmKQYLilh4+58fdZvnXaKPvEuEpbhXCeh
# /HbASrvTbZXXbh8fMbHOq+xnCemK/6aqjfqgRzkXn9unjl6wj6KT7MoEp1tCn+9z
# cmi/zW3KeI3Alld5B6IEvVfZVmaCrvSsRBNFnVpVg5eGQNRyan0xfblEwKLrsoNk
# WhUtJY3SD53kvz8OHBKRY1quaq70UefcIDuPq4+76CVovjbpT/wnmP6PFnHtNrDj
# KzMAnUWOeseIcDhp7ETzVyoh2rWofTpXUr5aEau5mkXs0MclqjimMeWf0r3NyBa5
# E3k0rG/xtpmTFB9e4w7b0VQUxNUcVdmocGL7Rj4ouWLjUDUkiE2DctxMTaPmqKJE
# nx8cFsBCrDmgNYxGOZ03ykPmPbwc3SooZeS5rRmR4v2GTVrbsmbiiggtYDjhJddo
# sC/TtJ+65JL48oTHDK1KVMtSjUBr/Q16Nzg0JaXfiZ4JS+MX45ylAgMBAAGjggF4
# MIIBdDAMBgNVHRMBAf8EAjAAMD0GA1UdHwQ2MDQwMqAwoC6GLGh0dHA6Ly9jY3Nj
# YTIwMjEuY3JsLmNlcnR1bS5wbC9jY3NjYTIwMjEuY3JsMHMGCCsGAQUFBwEBBGcw
# ZTAsBggrBgEFBQcwAYYgaHR0cDovL2Njc2NhMjAyMS5vY3NwLWNlcnR1bS5jb20w
# NQYIKwYBBQUHMAKGKWh0dHA6Ly9yZXBvc2l0b3J5LmNlcnR1bS5wbC9jY3NjYTIw
# MjEuY2VyMB8GA1UdIwQYMBaAFN10XUwA23ufoHTKsW73PMAywHDNMB0GA1UdDgQW
# BBQ2dBi+EnTsl++Xmrax5h7dyzC12TBLBgNVHSAERDBCMAgGBmeBDAEEATA2Bgsq
# hGgBhvZ3AgUBBDAnMCUGCCsGAQUFBwIBFhlodHRwczovL3d3dy5jZXJ0dW0ucGwv
# Q1BTMBMGA1UdJQQMMAoGCCsGAQUFBwMDMA4GA1UdDwEB/wQEAwIHgDANBgkqhkiG
# 9w0BAQsFAAOCAgEAZm3wL05QNFh25ll601jtST553L2VVS4ugvlHmX90PSOkjmX3
# jQ2ERwdz9MHWhoOvW1EdOrBlm31YRt2GmCz4/82vq9fTTNwLSzk3csHEIgHuAbN1
# INCVgz0l7Ya7mkxFLHoZ4BJ9LluS4p2NjvU9r7OGnBCgfFaLriC+qRY8QxCeMQOl
# l1BTTaUBMp0pgxh3XOOxOQfNeFCkXg0q4QULkaRuApJFZi/pY+PMA6p8bjOTP4YI
# 9VNsoc7ReBYMDL588oupc1CTiDaf0e6YaN80z6WoIGuNja3VPDEG4VmWZG+Of9gO
# XUNllvR8n1IXXsvWEuHRTCV9jjK89NHGaNroeXHr1C6eERnoNUPbEuIb/parUnGB
# n5MKWL+2TlL9Z34lSFz6e1Efi4oJeDznUojNZKJzOMvBS+JJyr0aVuW4lDoqbqkl
# 9vFLjHi1oGM7mBrlL8AHEC3iUNEOwgPcbsIYAqV85RyXYNQqLu1ik1MmmpKy/Tma
# Mmi/AiXxtjC6RrnrkN6tIHR7Nky9b5jWfMxCCG2b119gr7SkJ8qVOH8I9oBxMDPR
# IjBi5rEjSbDOCpRhZiqmx7K7gW2Gyp1OCIffVyiqmeZh+YLxpdP0tymxtqg/HVqV
# If3xn0Sgl7HcUpcRkY3EgTPUqNaVzOSO5h8KyzA9nVtUX2XwSx9C4vb6uqcxggMc
# MIIDGAIBATBqMFYxCzAJBgNVBAYTAlBMMSEwHwYDVQQKExhBc3NlY28gRGF0YSBT
# eXN0ZW1zIFMuQS4xJDAiBgNVBAMTG0NlcnR1bSBDb2RlIFNpZ25pbmcgMjAyMSBD
# QQIQTZ+zbBZIHiQAJUisU7Eo1jANBglghkgBZQMEAgEFAKCBhDAYBgorBgEEAYI3
# AgEMMQowCKACgAChAoAAMBkGCSqGSIb3DQEJAzEMBgorBgEEAYI3AgEEMBwGCisG
# AQQBgjcCAQsxDjAMBgorBgEEAYI3AgEVMC8GCSqGSIb3DQEJBDEiBCDTfTR9rMiL
# vQ7KLebvohR5E9rwit3sa9C92iKQFGy88DANBgkqhkiG9w0BAQEFAASCAgCJVWev
# 6vXYLrfCaV/ieFvn8aAiiZwXDqozKv6jWc44AZV21KNHIdQKwiJKp0uzdn6BDjnR
# yo0d3Rr5jS3F/QzhuF2+1o+Q9CWGJiwcaCkZvpT9c/gAaIgr3NcKWpk3zYf8EFSA
# 8seNuiYN9pKNLn9FJpqDPatI1F8F9ukwkyZHYxBlsRs9px6zALZp+Dp8SIfYbryq
# e8p0DzjB/i0wSgGvWO3jAEHy9ePqPqQ6YJ9jqr71faKz645iGLPOq3Rudp05aWRo
# giugUvjEgt43XGKPRhWgfqe/sNsD8jtwtFlY/5izGZLBuIWaBKOfUlmE0GU0+Q0j
# ENsGhK/14G7cRKU03nSeZyvP6gBMxa8StO7NYhPWRPx1X2iLkc4SsvPL0KEkGBug
# Y3x4WRVrNbXfN+IPn3il4RP6pvqW3BBbGiRqZvL7i/DQjfAMxQ7dqa4gB0jJhVbk
# MJcdY4QQtVTZJASbaHTnxIVU2KVEijPTiOl+AG4/LaS0uBgY9ASJOl6NC7uQoDNG
# X/g5izKn7YfLCDVIdAxcb4yvxVR2+1pXNgcsCgmWxxR/iLB5pm4/lVn2j3ttodIB
# QxmlrQmgats3kR33t9KbjTW0XtA1h256NcJrvsExLqqGRe8aVzJA9PyjQ4UlBQSM
# 5tZ3+oIaHuUbLlD/gDau2b6+xuhh9Q+SJtz1AA==
# SIG # End signature block