Tag Archives: CSP

List users and licenses in Database

Now that we have a database containing user, tenant and license it would be nice to have a function/command to list this. Took me a while to figure it out but thanks to Bing, I prefer Bing before google. I sync my database on a schedule once a day. This way my data is new. Based on this data you could create a graph indicating office 365 license usage.

function get-tenantuserlicenses {
    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
        # Generate and set the ValidateSet
        # Add the ValidateSet to the attributes collection   
        $SQLInstance = "localhost\SQLExpress"
        $SQLDatabase = "Microsoft365"
        $sqlqr = "select * from [Microsoft365].[dbo].[tenants]"
        $customers = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
        $ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($Customers | Select-Object -ExpandProperty tenantname)


        $Parameter2Name = "ProductName"
        $Parameter2Attribute = New-Object System.Management.Automation.ParameterAttribute
        $Parameter2Attribute.Mandatory = $false
        $Parameter2Attribute.Position = 2
        $Attribute2Collection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]
        $sqlqr2 = "select * from [Microsoft365].[dbo].[skus]"
        $skus = invoke-sqlcmd -query $sqlqr2 -ServerInstance $SQLInstance -Database $SQLDatabase
        #$arrSet = Get-WmiObject Win32_Service -ComputerName $computername | select -ExpandProperty Name
        $ValidateSet2Attribute = New-Object System.Management.Automation.ValidateSetAttribute($skus | Select-Object -ExpandProperty productname)

        $RuntimeParameter2 = New-Object System.Management.Automation.RuntimeDefinedParameter($Parameter2Name, [string], $Attribute2Collection)

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

        # Bind the parameter to a friendly variable
        $Kunde = $PsBoundParameters[$ParameterName]
        if ( -not $kunde) {
            $kunde = "%"
        else {
        $Product = $PsBoundParameters[$Parameter2Name]
        if ( -not $Product) {
            $Product = "%"
        else {

    process {
        $SQLInstance = "localhost\SQLExpress"
        $SQLDatabase = "Microsoft365"
    $sqlquery = "SELECT dbo.usersextended.displayname as userdisplayname,userprincipalname,givenname,surname,dbo.usersextended.active as activeuser,tenantname,dbo.tenants.displayname as tenantdisplayname,Productname,usagelocation FROM usersextended Cross apply string_split(usersextended.skus,',') INNER JOIN dbo.tenants ON dbo.usersextended.tenantid = dbo.tenants.tenantID inner join dbo.skus on dbo.Skus.skuid like '%'+value+'%' where usersextended.skus like '%-%' and tenantname like '$($kunde)' and usersextended.userprincipalname not like '%#EXT#%' and productname like '$($product)'" 
        $users = invoke-sqlcmd -query $sqlquery -ServerInstance $SQLInstance -Database $SQLDatabase
        if ($users.count -gt 0) { $f = (((($users[0] | get-member) | Where-Object { $_.membertype -eq "Property" } )).name) | sort-object -desc }
        $users | Select-Object $f 



The important part is the T-Sql. This will join the usersextended (previously users) table and tenant table to generate a list of users and licenses. If used in combination with export-excel this is one of my favorite tools.

Hash tables in powershell

We all have the need to store data in some kind of arrays. I use hashtables a lot. Preferred use is as a lookup table, I can use ‘contains’ instead of looping through each item or reference an object by name instead of index number. Lookup table for licenses is where I use it the most at the time being. A table of license ID versus license name. One place for this is Microsoft licenses in partnercenter, and show licenses assigned to users. Using powershell to connect to Microsoft partnercenter to get all available skus by ID , name and displayname. When I have collected this – how do you ask?.
Here you go:

 Import-module partnercenter
    $lic = @{ }
    $prodid = @{ }
    $customerstenants = Get-PartnerCustomer
    ForEach ($customer in $customerstenants) {
        $message = "Customer :$($customer.name)"
        write-verbose -Message "$message"
        $custskus = Get-PartnerCustomerSubscribedSku -CustomerId $customer.customerid
        foreach ($custsku in $custskus) {
            if ($lic.ContainsKey($custsku.SkuPartNumber)) {
            else {
                $lic.Add($custsku.skupartnumber, $custsku.productname)
                $prodid.Add($custsku.SkuId, $custsku.ProductName)


Connect-companypartnercenter is a custom function. Here is how to connect to Office partnercenter.
The script above will (hopefully , no try-catch) end up in 2 hash tables. One “skuid” and productname:

"WACONEDRIVEENTERPRISE","OneDrive for Business (Plan 2)"
"O365_BUSINESS_ESSENTIALS","Office 365 Business Essentials"
"MCOSTANDARD","Skype for Business Online (Plan 2)"
"Win10_E3_Local","Windows 10 Enterprise E3 (local only)"
"WACONEDRIVESTANDARD","OneDrive for Business (Plan 1)"

As long as I’m using this in the same script it is ok. Exporting it to files is not that straight forward, even tried ‘convertto-json’ –not working (convertfrom-json returns an array)
So I do this to crate a csv file. Creating correct columns.

       $csvobject = ($lic.GetEnumerator() | Select-Object @{expression = { $_.name }; label = "SkuID" }, @{expression = { $_.value }; label = "DisplayName" } | convertto-csv -Delimiter ',' -NoTypeInformation )
        $csvobject | Out-File -Encoding unicode $ExportSkuIDFile

        $csvobject = ($prodid.GetEnumerator() | Select-Object @{expression = { $_.name }; label = "ProductID" }, @{expression = { $_.value }; label = "DisplayName" } | convertto-csv -Delimiter ',' -NoTypeInformation )
        $csvobject | Out-File -Encoding unicode $ExportProductIDFile

Now that we have the file it would be great to be able to use them 🙂
And this is how it is imported in to a hashtable.

$filecontent=import-csv -Path $importfile -Encoding Unicode -Header "column1","column2"
    write-verbose -message "$_.column1 , $_.column2"

Read the file line by line and build our hash-table. Now we can use : $hashtable[“MCOSTANDARD”] , this will return the full name ‘Skype for Business Online (Plan 2)’
I have this as functions in my default module, the module I import in all my powershell sessions. (Surely you know about powershell profiles?)
Hope this helped.

CSP access to tenants using powershell. Part 4

This is a small script that connects to partnercenter list all customers tenants and let you select one. When one is selected it connects to azuread and az for that customer.

All my credentials are stored in SecretServer . I use a web service request to get those credentials. I will show these modules in a later post.

import-module partnercenter

#$app = AppID + AppKey
$app=Get-SecretID -secretID xxxx
#rt = refreshtoken.
$rt=Get-SecretID -secretID yyyy
$tid="'csp tenant directory id'"

#Connect PartnerCenter
$pcToken = New-PartnerAccessToken -RefreshToken $refreshToken -Resource https://api.partnercenter.microsoft.com -Credential $app -TenantId $tid
Connect-PartnerCenter -AccessToken $pcToken.AccessToken -ApplicationId $app.username -TenantId $tid

    $customers=get-partnercustomer | sort-object -Property name
    foreach($cust in $customers){
        Write-Host "$($counter) - $($cust.Name) - $($cust.domain)"
    $custid=Read-Host "Enter customer #"
    Write-host " Targeting : $($customer.name) - $($customer.Domain)"

$azureToken = New-PartnerAccessToken -Resource "https://graph.microsoft.com/" -Credential $app -RefreshToken $refreshtoken -TenantId $customer.CustomerId
$graphToken = New-PartnerAccessToken -RefreshToken $refreshToken -Resource "https://graph.windows.net/" -Credential $app -TenantId $customer.CustomerId  

Connect-Azuread -aadAccessToken $graphToken.AccessToken -msAccessToken $azureToken.AccessToken -TenantId $customer.CustomerId  -AccountId $app.username

$azure2Token = New-PartnerAccessToken -Resource https://management.azure.com/ -Credential $app -RefreshToken $refreshtoken -TenantId $customer.CustomerId
$graph2Token = New-PartnerAccessToken -RefreshToken $refreshToken -Resource https://graph.windows.net/ -Credential $app -TenantId $customer.CustomerId  

 Connect-AzAccount -AccessToken $azure2Token.AccessToken -GraphAccessToken $graph2Token.AccessToken -TenantId $customer.CustomerId  -AccountId $app.username

CSP access to tenants using powershell. Part 3

In this part 3 of CSP and powershell I will show how you can connect to azureAD of a customer tenant using your CSP app credentials and refreshtoken. This is almost the same procedure as we use to connect to az. We will start with the same variables as in part 2. Remember to keep your credential and secure, as it will give access to all your tenants.

$app=get-credential # Get AppID and Key for out partnecenter app. (created in part 1)
$refreshtoken = 'refreshtoken' # From part 1 or whenever we get a new one.
$CustomerTenantID= 'Azure directory object id'

Struggled for a while to get this to work. The important thing is the endpoints and when to use the customer tenant ID.

$azureToken = New-PartnerAccessToken -Resource "https://graph.microsoft.com/" -Credential $app -RefreshToken $refreshtoken -TenantId $CustomerTenantID
$graphToken = New-PartnerAccessToken -RefreshToken $refreshToken -Resource "https://graph.windows.net/" -Credential $app -TenantId $CustomerTenantID  

Connect-Azuread -aadAccessToken $graphToken.AccessToken -msAccessToken $azureToken.AccessToken -TenantId $CustomerTenantID  -AccountId $app.username

So now you can use get-azureaduser to get users from this customer tenant.

You could also use the MS online module msol to query for users, this module requires you to use tenantid as an argument.

In part 4 I will wrap this up in a simple script allowing you to select customer tenant.

CSP access to tenants using powershell. Part 2

In part 1 we created the Azure Enterprise App for Partnercenter and used this information to connect using powershell and connect-partnercenter. Now we will use this to connect to one of our customers tenants. First we will use AZ module and connect-azaccount. We will use the AZ module and the partnercenter module. So if those at not installed please install :

install-module az
install-module partnercenter

I will use the partnercenter module to request an accesstoken for azure.

$app=get-credential # Get AppID and Key for out partnecenter app. (created in part 1)
$refreshtoken = 'refreshtoken' # From part 1 or whenever we get a new one.
$CustomerTenantID= 'Azure directory object id'

Now we have all the required info to connect. The credentials should be stored securely!!!!

$azureToken = New-PartnerAccessToken -Resource https://management.azure.com/ -Credential $app -RefreshToken $refreshtoken -TenantId $CustomerTenantID

$grapToken = New-PartnerAccessToken -RefreshToken $refreshToken -Resource https://graph.windows.net/ -Credential $app -TenantId $CustomerTenantID  

Connect-AzAccount -AccessToken $azureToken.AccessToken -GraphAccessToken $graphToken.AccessToken -TenantId $CustomerTenantID  -AccountId $app.username

There. We are now connected to our azure of our customer. In next part we will connect to azureAD

CSP access to tenants using powershell. Part 1

A short explanation of how to access customer tenant using a CSP tenant SPN credential connectiong to AzureAD and AZ. Have been struggling for a while to manage all our customers tenants using powershell scripts. It can be complicated to organize all the credentials, tenant domain, tenant id’s password expiry.

First step is to be able to use powershell in the CSP tenants and access the partnercenter module. To get this started Microsoft has published a script to create the SPN required for this. https://docs.microsoft.com/en-us/powershell/partnercenter/secure-app-model?view=partnercenterps-1.5 This script will help you create the SPN . When using the SPN for the first time you will have to consent it using an admin account. The “ConfigurePreconsent” argument adds the spn to the adminagents group, this result in the account being a global admin in the customer tenants. Next:

$credential = Get-Credential
$token = New-PartnerAccessToken -Consent -Credential $credential -Resource https://api.partnercenter.microsoft.com -TenantId 'Your Tenant Id'

This is to consent the spn and get the refresh token we will in further logins. TenantId is the ID of your partner tenant. First it asks for the credential of the newly created spn (appID and key), next it will require you to login and consent using a service account . In return you get a token. Remember to store the refresh token part in a secure place as this will be used in our next login.

$refreshToken = 'Enter the refresh token value here'
$credential = Get-Credential
$tenantId = 'Your Tenant Id'
$pcToken = New-PartnerAccessToken -RefreshToken $refreshToken -Resource https://api.partnercenter.microsoft.com -Credential $credential -TenantId $tenantId
Connect-PartnerCenter -AccessToken $pcToken.AccessToken -ApplicationId $appId -TenantId $tenantId

Here we connect to partnercenter. We got the $refreshtoken in the previous step. $credential is our appid and key returned from the script. $tenantid is the tenantid of the partner tenant. Returned from the connection is a new $pcToken. This $pcToken includes a new refresh token that we could store and use at next login, but the one we already got would still last for a default value of 90 days. We’ve had some issues it the MFA settings in the tenant allow the user to “not be asked for credentials in xx days” (So we always uncheck this box).

Part 2 will for the AZ connection to customer tenant.