Import user licenses from CSP using CSP api and graph.

Hi,there.
This is also quiet simple. I want to get all users and what licenses they have been assigned. All this should do, is retrieve information using a csp token.
After some attempts this is my best one. The “goodlooking” argument will not work when run from ISE.

function update-tenantusersindb {
 
    [CmdletBinding()]
    param(
        [switch]$includeexternal,
        [switch]$goodlooking
    )
    DynamicParam {
        $ParameterName = "tenant"
        $RuntimeParameterDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
        $AttributeCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]

        # Create and set the parameters' attributes
        $ParameterAttribute = New-Object System.Management.Automation.ParameterAttribute
        $ParameterAttribute.Mandatory = $false
        $ParameterAttribute.Position = 1

        # Add the attributes to the attributes collection
        $AttributeCollection.Add($ParameterAttribute)
        # Generate and set the ValidateSet
        # Add the ValidateSet to the attributes collection  
        $SQLInstance = "localhost\SQLExpress"
        $SQLDatabase = "Microsoft365"
        $sqlqr = "select * from [Microsoft365].[dbo].[tenants] where active='1'"
        $customers = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
        $ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($Customers | Select-Object -ExpandProperty tenantname)

        $AttributeCollection.Add($ValidateSetAttribute)

        # Create and return the dynamic parameter
        $RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)
        $RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)
        return $RuntimeParameterDictionary
    }
    begin {

        # Bind the parameter to a friendly variable
        $Kunde = $PsBoundParameters[$ParameterName]
        if ( -not $kunde) {
            $filter = ''
        }
        else {
            $filter = '?Size=0&filter={"Field":"Domain","Value":"' + $($kunde) + '","Operator":"starts_with"}'    
        }
    }
    process {
        if ($goodlooking) {
            Clear-Host
            $ui = (get-host).ui
            $rui = $ui.RawUI
            $xx = [math]::round(($rui.MaxWindowSize.Width)/2)

        }
        $tid = "<csp tenant id>"
        $appid = "<csp appid>"
        $SQLInstance = "localhost\SQLExpress"
        $SQLDatabase = "Microsoft365"
        $SQLUsername = ""
        $SQLPassword = ""
        $tokentime = get-date
        $k = (get-storedcredential -user $appid).password
        $token = (get-storedcredential -user cspuser).getnetworkcredential().password
        $app = New-Object System.Management.Automation.PSCredential -ArgumentList $appid, $k
        $partneraccesstoken = New-PartnerAccessToken -RefreshToken $token -Credential $app -Tenant $tid -Scopes 'https://api.partnercenter.microsoft.com/user_impersonation' -ServicePrincipal -ApplicationId $appid  #  -Resource "https://api.partnercenter.microsoft.com"
        if ($partneraccesstoken) {
            Write-Output "Got accesstoken"
            update-storedcredential -user cspuser -secret ($partneraccesstoken.RefreshToken | ConvertTo-SecureString -AsPlainText -Force)
            $tokentime = get-date
        }
        else { throw "Error getting accesstoken" }
        $baseurl = "https://api.partnercenter.microsoft.com/"
        $apiversion = "v1"
        $endpoint = "/customers"
        $url = $baseurl + $apiversion + $endpoint + $filter
        $tenants = Invoke-RestMethod -Headers @{Authorization = "Bearer $($partneraccesstoken.AccessToken)" } -Uri $url -Method Get

        $cb = $tenants.indexof('{')  # Find first occurance of '{'
        $tenants = $tenants.Substring($cb) | convertfrom-json  # Trim start, remove garble and convert from json
        $filter = ''
        foreach ($tenant in $tenants.items) {
           
            $users = $()
            $customerTenant = $tenant.id #$customer.customerid    
            $customerAccessTokenUri = "https://login.windows.net/$customerTenant/oauth2/token"
           
            $params = @{
                resource      = "https://graph.microsoft.com";
                grant_type    = "refresh_token";
                client_secret = $app.GetNetworkCredential().password;
                client_id     = $appid;
                scope         = "openid";
                refresh_token = $token
            }
            $graphAccess = Invoke-RestMethod -Uri $customerAccessTokenUri -Method POST -Body $params
            $url = "https://graph.microsoft.com/beta/users"
            $result = $null
            $users = $null
            do {
                try {
                    $result = Invoke-RestMethod -Uri $url  -Headers @{Authorization = "Bearer " + $graphAccess.access_token }
                    $url = $result.'@odata.nextLink'
                    $users += $result.value
                }
                catch {
                    $url = $null
                    $users = $null
                }
            } while ($url)        
         
            $totalusers = ($users | Where-Object { $_.userprincipalname -notlike "*#EXT#*" }).count
            if ($includeexternal) { $totalusers += ($users | Where-Object { $_.userprincipalname -like "*#EXT#*" }).count }        
            $usersdone = 0
            $totaltime = 0
            if ($goodlooking) { [console]::SetCursorPosition(0, 1) }
            if ($goodlooking) {
                Write-Host "Tenant : " -NoNewline
                Write-host -ForegroundColor Yellow "$($tenant.companyprofile.Domain),$($tenant.companyprofile.companyName),$($tenant.id) - User count : $($totalusers)"
            }
            else {
                Write-Host "Tenant : $($tenant.companyprofile.Domain),$($tenant.companyprofile.companyName),$($tenant.id) - User count : $($totalusers)              "
            }#set users as inactive in database.
            Invoke-Sqlcmd -Query "update usersextended set active='0' where active < '10' and tenantid like '$($tenant.id)'" -ServerInstance $SQLInstance -Database $SQLDatabase

            foreach ($user in $users <#.items #>) {
                if ($user.userPrincipalName -like "*#EXT#*" -and (-not $includeexternal)) {
                    # Skip external user if not specified
                    #  Write-host "Skipping external user : $($user.userPrincipalName)"
                }
                else {
                    $t = Measure-Command {
                        $assignedskus = $user.assignedlicenses | join-string -property skuid -separator ','
                        # Write-Output "$($user.displayname) - sku : $($assignedskus)"
                        if ($goodlooking) {
                            [console]::SetCursorPosition(0, 2)
                            write-host " "
                            [console]::SetCursorPosition(0, 2)
                            Write-host -ForegroundColor Green "Inserting user($($usersdone+1)):$($user.Displayname)    " -NoNewline
                        }
                        else {
                            Write-host "Inserting user($($usersdone+1)):$($user.Displayname)    " -NoNewline

                        }
                        $displayname = if ($user.displayname -ne $null) { $user.displayname.Replace("'", "''") }
                        $givenname = if ($user.givenname -ne $null) { $user.givenname.Replace("'", "''") }
                        $surname = if ($user.surname -ne $null) { $user.surname.Replace("'", "''") }
                        $userUPN = if ($user.userprincipalname -ne $null) { $user.userprincipalname.Replace("'", "''") }


                        switch ($user.accountenabled) {
                            'Active' { $userstate = 1 }
                            default { $userstate = 0 }
                        }
   
                        $insertsql = "insert into usersextended (tenantid,userid,displayname,userprincipalname,givenname,surname,skus,usagelocation,accountEnabled,active,city,companyname,streetaddress,country) values ('$($tenant.id)','$($user.ID)','$($DisplayName)','$($userUPN)','$($givenname)','$($surname)','$($assignedskus)','$($user.usagelocation)','$($userstate)','1','$($user.city)','$($user.companyName)','$($user.streetAddress)','$($user.country)')"
                        $er = Invoke-Sqlcmd -Query $insertsql -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorVariable ierr -ErrorAction SilentlyContinue
                        #Write-Host "$($ierr)"
                        if ($ierr) {
                            # User exists -> update.
                            if ($goodlooking) {
                                Write-host -ForegroundColor DarkGreen "User exists.... updating.                       " -NoNewline
                            }
                            else {
                                Write-host "User exists.... updating.                       " -NoNewline
                            }
                            $updatesql = "update usersextended set skus='$($assignedskus)',usagelocation='$($user.usagelocation)',tenantid='$($tenant.id)',active='1',companyname='$($user.companyName)',city='$($user.city)',streetaddress='$($user.streetAddress)',country='$($user.country)' where userid = '$($user.id)'"
                            Invoke-Sqlcmd -Query $updatesql -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorVariable updterr
                            if ($updterr) {
                                Write-host "$($updatesql)"
                            }
                        }
                    }
                    $usersdone++
                    $totaltime += $t
                    if ($goodlooking) {
                        [console]::SetCursorPosition(80, 2)
                        Write-Host -ForegroundColor Gray "Time left ~ $((($totaltime.totalSeconds/$usersdone)*($totalusers-$usersdone)).tostring("#.#")) seconds                  "
                        # Last user took:$(($t.totalseconds).tostring("#.#")) -
                    }
                    else {
                        Write-Host "Last user took:$(($t.totalseconds).tostring("#.#")) - Time left ~ $((($totaltime.totalSeconds/$usersdone)*($totalusers-$usersdone)).tostring("#.#")) seconds                  "
                    }
                }
            }
        }
    }
}

I’am still using the same database. The table is called usersextended and you will be given the sql command to create it in a while.
This function basically fetch a user and all the attributes from M365, licenses skus are joined to a csv and all are added to as a record in the table.
PLEASE remember I am so lacy so almost all fields are varchar.

USE [Microsoft365]
GO

/****** Object:  Table [dbo].[usersextended]  
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[usersextended](
    [tenantid] [varchar](255) NOT NULL,
    [userid] [varchar](255) NOT NULL,
    [accountEnabled] [int] NULL,
    [ageGroup] [varchar](255) NULL,
    [assignedlicenses] [varchar](255) NULL,
    [assignedplans] [varchar](255) NULL,
    [businessPhones] [varchar](255) NULL,
    [city] [varchar](255) NULL,
    [companyName] [varchar](255) NULL,
    [consentProviderForMinor] [varchar](255) NULL,
    [country] [varchar](255) NULL,
    [createddatetime] [datetime] NULL,
    [creationtype] [varchar](255) NULL,
    [deleteddatetime] [datetime] NULL,
    [department] [varchar](255) NULL,
    [devicekeys] [varchar](255) NULL,
    [displayname] [varchar](255) NULL,
    [employeehiredate] [datetime] NULL,
    [employeeid] [varchar](255) NULL,
    [employeeorgdata] [varchar](255) NULL,
    [employeetype] [varchar](255) NULL,
    [externaluserstate] [varchar](255) NULL,
    [externaluserstatechangedatetime] [datetime] NULL,
    [faxnumber] [varchar](255) NULL,
    [givenname] [varchar](255) NULL,
    [id] [varchar](255) NULL,
    [identities] [varchar](255) NULL,
    [imaddresses] [varchar](255) NULL,
    [infocatalogs] [varchar](255) NULL,
    [ismanagementrestricted] [varchar](255) NULL,
    [isresourceaccount] [varchar](255) NULL,
    [jobtitle] [varchar](255) NULL,
    [legalagegroupclassification] [varchar](255) NULL,
    [mail] [varchar](255) NULL,
    [mailnickname] [varchar](255) NULL,
    [mobilephone] [varchar](255) NULL,
    [officelocation] [varchar](255) NULL,
    [onpremisesDistinguishedname] [varchar](255) NULL,
    [onpremisesedomainname] [varchar](255) NULL,
    [onpremisesextensionattributes] [varchar](255) NULL,
    [onpremisesimmutableid] [varchar](255) NULL,
    [onpremiseslastsyncdatetime] [datetime] NULL,
    [onpremisesprovisioningerrors] [varchar](255) NULL,
    [onpremisessamaccountname] [varchar](255) NULL,
    [onpremisessecurityidentifier] [varchar](255) NULL,
    [onpremisessyncenabled] [int] NULL,
    [onpremisesuserprincipalname] [varchar](255) NULL,
    [othermails] [varchar](255) NULL,
    [passwordpolicies] [varchar](255) NULL,
    [passwordprofile] [varchar](255) NULL,
    [postalcode] [varchar](255) NULL,
    [preferreddatalocation] [varchar](255) NULL,
    [preferredlanguage] [varchar](255) NULL,
    [provisionedplans] [varchar](255) NULL,
    [proxyaddresses] [varchar](255) NULL,
    [refreshtokenvalidfromdaterime] [datetime] NULL,
    [showinaddresslist] [varchar](255) NULL,
    [signinsessionsvalidfromdatetime] [datetime] NULL,
    [state] [varchar](255) NULL,
    [streetaddress] [varchar](255) NULL,
    [surname] [varchar](255) NULL,
    [usagelocation] [varchar](255) NULL,
    [userprincipalname] [varchar](255) NULL,
    [usertype] [varchar](255) NULL,
    [skus] [varchar](512) NULL,
    [active] [int] NULL,
 CONSTRAINT [PK_usersextended] PRIMARY KEY CLUSTERED
(
    [userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Leave a Reply