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.<\/p>\n\n
\n\nfunction update-tenantusersindb {\n\n[CmdletBinding()]\nparam(\n[switch]$includeexternal,\n[switch]$goodlooking\n)\nDynamicParam {\n$ParameterName = "tenant"\n$RuntimeParameterDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary\n$AttributeCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]\n\n# Create and set the parameters' attributes\n$ParameterAttribute = New-Object System.Management.Automation.ParameterAttribute\n$ParameterAttribute.Mandatory = $false\n$ParameterAttribute.Position = 1\n\n# Add the attributes to the attributes collection\n$AttributeCollection.Add($ParameterAttribute)\n# Generate and set the ValidateSet\n# Add the ValidateSet to the attributes collection\n$SQLInstance = "localhost\\SQLExpress"\n$SQLDatabase = "Microsoft365"\n$sqlqr = "select * from [Microsoft365].[dbo].[tenants] where active='1'"\n$customers = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase\n$ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($Customers | Select-Object -ExpandProperty tenantname)\n\n$AttributeCollection.Add($ValidateSetAttribute)\n\n# Create and return the dynamic parameter\n$RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)\n$RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)\nreturn $RuntimeParameterDictionary\n}\nbegin {\n\n# Bind the parameter to a friendly variable\n$Kunde = $PsBoundParameters[$ParameterName]\nif ( -not $kunde) {\n$filter = ''\n}\nelse {\n$filter = '?Size=0&filter={"Field":"Domain","Value":"' + $($kunde) + '","Operator":"starts_with"}'\n}\n}\nprocess {\nif ($goodlooking) {\nClear-Host\n$ui = (get-host).ui\n$rui = $ui.RawUI\n$xx = [math]::round(($rui.MaxWindowSize.Width)\/2)\n\n}\n$tid = ""\n$appid = ""\n$SQLInstance = "localhost\\SQLExpress"\n$SQLDatabase = "Microsoft365"\n$SQLUsername = ""\n$SQLPassword = ""\n$tokentime = get-date\n$k = (get-storedcredential -user $appid).password\n$token = (get-storedcredential -user cspuser).getnetworkcredential().password\n$app = New-Object System.Management.Automation.PSCredential -ArgumentList $appid, $k\n$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"\nif ($partneraccesstoken) {\nWrite-Output "Got accesstoken"\nupdate-storedcredential -user cspuser -secret ($partneraccesstoken.RefreshToken | ConvertTo-SecureString -AsPlainText -Force)\n$tokentime = get-date\n}\nelse { throw "Error getting accesstoken" }\n$baseurl = "https:\/\/api.partnercenter.microsoft.com\/"\n$apiversion = "v1"\n$endpoint = "\/customers"\n$url = $baseurl + $apiversion + $endpoint + $filter\n$tenants = Invoke-RestMethod -Headers @{Authorization = "Bearer $($partneraccesstoken.AccessToken)" } -Uri $url -Method Get\n\n$cb = $tenants.indexof('{') # Find first occurance of '{'\n$tenants = $tenants.Substring($cb) | convertfrom-json # Trim start, remove garble and convert from json\n$filter = ''\nforeach ($tenant in $tenants.items) {\n\n$users = $()\n$customerTenant = $tenant.id #$customer.customerid\n$customerAccessTokenUri = "https:\/\/login.windows.net\/$customerTenant\/oauth2\/token"\n\n$params = @{\nresource = "https:\/\/graph.microsoft.com";\ngrant_type = "refresh_token";\nclient_secret = $app.GetNetworkCredential().password;\nclient_id = $appid;\nscope = "openid";\nrefresh_token = $token\n}\n$graphAccess = Invoke-RestMethod -Uri $customerAccessTokenUri -Method POST -Body $params\n$url = "https:\/\/graph.microsoft.com\/beta\/users"\n$result = $null\n$users = $null\ndo {\ntry {\n$result = Invoke-RestMethod -Uri $url -Headers @{Authorization = "Bearer " + $graphAccess.access_token }\n$url = $result.'@odata.nextLink'\n$users += $result.value\n}\ncatch {\n$url = $null\n$users = $null\n}\n} while ($url)\n\n$totalusers = ($users | Where-Object { $_.userprincipalname -notlike "*#EXT#*" }).count\nif ($includeexternal) { $totalusers += ($users | Where-Object { $_.userprincipalname -like "*#EXT#*" }).count }\n$usersdone = 0\n$totaltime = 0\nif ($goodlooking) { [console]::SetCursorPosition(0, 1) }\nif ($goodlooking) {\nWrite-Host "Tenant : " -NoNewline\nWrite-host -ForegroundColor Yellow "$($tenant.companyprofile.Domain),$($tenant.companyprofile.companyName),$($tenant.id) - User count : $($totalusers)"\n}\nelse {\nWrite-Host "Tenant : $($tenant.companyprofile.Domain),$($tenant.companyprofile.companyName),$($tenant.id) - User count : $($totalusers) "\n}#set users as inactive in database.\nInvoke-Sqlcmd -Query "update usersextended set active='0' where active < '10' and tenantid like '$($tenant.id)'" -ServerInstance $SQLInstance -Database $SQLDatabase foreach ($user in $users <#.items #>) {\nif ($user.userPrincipalName -like "*#EXT#*" -and (-not $includeexternal)) {\n# Skip external user if not specified\n# Write-host "Skipping external user : $($user.userPrincipalName)"\n}\nelse {\n$t = Measure-Command {\n$assignedskus = $user.assignedlicenses | join-string -property skuid -separator ','\n# Write-Output "$($user.displayname) - sku : $($assignedskus)"\nif ($goodlooking) {\n[console]::SetCursorPosition(0, 2)\nwrite-host " "\n[console]::SetCursorPosition(0, 2)\nWrite-host -ForegroundColor Green "Inserting user($($usersdone+1)):$($user.Displayname) " -NoNewline\n}\nelse {\nWrite-host "Inserting user($($usersdone+1)):$($user.Displayname) " -NoNewline\n\n}\n$displayname = if ($user.displayname -ne $null) { $user.displayname.Replace("'", "''") }\n$givenname = if ($user.givenname -ne $null) { $user.givenname.Replace("'", "''") }\n$surname = if ($user.surname -ne $null) { $user.surname.Replace("'", "''") }\n$userUPN = if ($user.userprincipalname -ne $null) { $user.userprincipalname.Replace("'", "''") }\n\nswitch ($user.accountenabled) {\n'Active' { $userstate = 1 }\ndefault { $userstate = 0 }\n}\n\n$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)')"\n$er = Invoke-Sqlcmd -Query $insertsql -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorVariable ierr -ErrorAction SilentlyContinue\n#Write-Host "$($ierr)"\nif ($ierr) {\n# User exists -> update.\nif ($goodlooking) {\nWrite-host -ForegroundColor DarkGreen "User exists.... updating. " -NoNewline\n}\nelse {\nWrite-host "User exists.... updating. " -NoNewline\n}\n$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)'"\nInvoke-Sqlcmd -Query $updatesql -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorVariable updterr\nif ($updterr) {\nWrite-host "$($updatesql)"\n}\n}\n}\n$usersdone++\n$totaltime += $t\nif ($goodlooking) {\n[console]::SetCursorPosition(80, 2)\nWrite-Host -ForegroundColor Gray "Time left ~ $((($totaltime.totalSeconds\/$usersdone)*($totalusers-$usersdone)).tostring("#.#")) seconds "\n# Last user took:$(($t.totalseconds).tostring("#.#")) -\n}\nelse {\nWrite-Host "Last user took:$(($t.totalseconds).tostring("#.#")) - Time left ~ $((($totaltime.totalSeconds\/$usersdone)*($totalusers-$usersdone)).tostring("#.#")) seconds "\n}\n}\n}\n}\n}\n}\n<\/pre><\/div>\n\n\nHere is the database script:<\/p>\n\n\n
\nUSE [Microsoft365]\nGO\n\n\/****** Object: Table [dbo].[usersextended]\nSET ANSI_NULLS ON\nGO\n\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE TABLE [dbo].[usersextended](\n[tenantid] [varchar](255) NOT NULL,\n[userid] [varchar](255) NOT NULL,\n[accountEnabled] [int] NULL,\n[ageGroup] [varchar](255) NULL,\n[assignedlicenses] [varchar](255) NULL,\n[assignedplans] [varchar](255) NULL,\n[businessPhones] [varchar](255) NULL,\n[city] [varchar](255) NULL,\n[companyName] [varchar](255) NULL,\n[consentProviderForMinor] [varchar](255) NULL,\n[country] [varchar](255) NULL,\n[createddatetime] [datetime] NULL,\n[creationtype] [varchar](255) NULL,\n[deleteddatetime] [datetime] NULL,\n[department] [varchar](255) NULL,\n[devicekeys] [varchar](255) NULL,\n[displayname] [varchar](255) NULL,\n[employeehiredate] [datetime] NULL,\n[employeeid] [varchar](255) NULL,\n[employeeorgdata] [varchar](255) NULL,\n[employeetype] [varchar](255) NULL,\n[externaluserstate] [varchar](255) NULL,\n[externaluserstatechangedatetime] [datetime] NULL,\n[faxnumber] [varchar](255) NULL,\n[givenname] [varchar](255) NULL,\n[id] [varchar](255) NULL,\n[identities] [varchar](255) NULL,\n[imaddresses] [varchar](255) NULL,\n[infocatalogs] [varchar](255) NULL,\n[ismanagementrestricted] [varchar](255) NULL,\n[isresourceaccount] [varchar](255) NULL,\n[jobtitle] [varchar](255) NULL,\n[legalagegroupclassification] [varchar](255) NULL,\n[mail] [varchar](255) NULL,\n[mailnickname] [varchar](255) NULL,\n[mobilephone] [varchar](255) NULL,\n[officelocation] [varchar](255) NULL,\n[onpremisesDistinguishedname] [varchar](255) NULL,\n[onpremisesedomainname] [varchar](255) NULL,\n[onpremisesextensionattributes] [varchar](255) NULL,\n[onpremisesimmutableid] [varchar](255) NULL,\n[onpremiseslastsyncdatetime] [datetime] NULL,\n[onpremisesprovisioningerrors] [varchar](255) NULL,\n[onpremisessamaccountname] [varchar](255) NULL,\n[onpremisessecurityidentifier] [varchar](255) NULL,\n[onpremisessyncenabled] [int] NULL,\n[onpremisesuserprincipalname] [varchar](255) NULL,\n[othermails] [varchar](255) NULL,\n[passwordpolicies] [varchar](255) NULL,\n[passwordprofile] [varchar](255) NULL,\n[postalcode] [varchar](255) NULL,\n[preferreddatalocation] [varchar](255) NULL,\n[preferredlanguage] [varchar](255) NULL,\n[provisionedplans] [varchar](255) NULL,\n[proxyaddresses] [varchar](255) NULL,\n[refreshtokenvalidfromdaterime] [datetime] NULL,\n[showinaddresslist] [varchar](255) NULL,\n[signinsessionsvalidfromdatetime] [datetime] NULL,\n[state] [varchar](255) NULL,\n[streetaddress] [varchar](255) NULL,\n[surname] [varchar](255) NULL,\n[usagelocation] [varchar](255) NULL,\n[userprincipalname] [varchar](255) NULL,\n[usertype] [varchar](255) NULL,\n[skus] [varchar](512) NULL,\n[active] [int] NULL,\nCONSTRAINT [PK_usersextended] PRIMARY KEY CLUSTERED\n(\n[userid] ASC\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\n) ON [PRIMARY]\nGO\n<\/pre><\/div>\n\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading Import user licenses from CSP using CSP api and graph.<\/span>