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.
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.


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 = ""
$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 "
}
}
}
}
}
}

Here is the database script:

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