Category Archives: Azure

Add tenant licenses from csp to database.

This is a followup from previus post. In this post I will populate the database with what licenses a tenant has aquired.
This will add records of what aquired skus and ‘usage’. For me this is how many license are bought versus how many are assigned. This does not account for senarios other than CSP.
In this function I am only adding the overview of license. How many has been purchased(from us) and versus how many has been assigned. There is no information about what licens a specific user are assigened ( This is for a later article).

function update-tenantlicsinDB {
    <#
  .SYNOPSIS
  Update tenant subscriptions/licenses from Cegal CSP
  .DESCRIPTION
  Update licenses from tenant.
  Tenant refer to the display name of the tenant "Super Company 1"
  Tenant is a dynamic parameter, autocomplete list.

  DYNAMIC PARAMTERS
 
  PARAMETER tenant
  -tenant <string>
  tenant refers to the display name of the tenant "Super Company 1"
  If omitted all tenants are returned. Autocomplete list of all tenants in csp

  .PARAMETER force
  Will force update of skus in database. Normaly updated once a day.
 
  .EXAMPLE
  update-tenantlicsindb -tenant 'Tenant name' -force

  .EXAMPLE
  update-tenantlicsindb

 #>
    [CmdletBinding()]
    param(
        [switch]$force,
        [switch]$updateusers
    )
    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]"
        $customers = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
        #$arrSet = Get-WmiObject Win32_Service -ComputerName $computername | select -ExpandProperty Name
        $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]  
    }
    process {

        import-module partnercenter
        $tid = "<CSP tenant ID>"
        $appid = "<App ID>"
   
        $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 -Resource "https://api.partnercenter.microsoft.com" -Credential $app -TenantId $tid
        $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"
        $partneraccesstoken
        #$connected = Connect-PartnerCenter -AccessToken $partneraccesstoken.AccessToken -ApplicationId $app.username -TenantId $tid -Environment AzureCloud -ServicePrincipal
        $connected = Connect-PartnerCenter -ApplicationId $appid -Credential $app -RefreshToken $token
        if (-not $connected) { throw "Error connecting to partnercenter.." }
        update-storedcredential -user cspuser -secret ($partneraccesstoken.RefreshToken | ConvertTo-SecureString -AsPlainText -Force)

        $SQLInstance = "localhost\SQLExpress"
        $SQLDatabase = "Microsoft365"
        #  $SQLUsername = ""
        #  $SQLPassword = ""

        $today = Get-date
        $dayofyear = "$($today.Year)-$($today.DayOfYear)"
        if (-not $kunde) {
            try {
                $customers = Get-PartnerCustomer
            }
            catch { Write-Host "Error getting tenants from partnercenter." }
        }
        else {

            $customers = Get-PartnerCustomer | Where-Object { $_.domain -eq $kunde }
        }

        # Add users to DB. (not #ext# or contacts)
        # $customer=$customers[9]
        foreach ($customer in $customers ) {
            #$customer=$customers[2]
            Write-Host "Processing tenant: $($customer.Name) ($($customer.domain))"
            # Add Sku to Skus table
            $customerskus = Get-PartnerCustomerSubscribedSku -CustomerId $customer.CustomerId
            if ($customerskus) {
                foreach ($sku in $customerskus) {
                    try {
                        $sqlsku = "insert into skus (SkuID,SkuPartNumber,ProductName) values ('$($sku.SkuID)','$($sku.SkuPartNumber)','$($sku.ProductName)')"
                        invoke-sqlcmd -query $sqlsku -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword
                        Write-Output "SkuID ($($sku.ProductName)) added to database"
                    }
                    catch {
                        $sqlsku = "insert into skus (SkuID,SkuPartNumber,ProductName) values ()"
                        #  Write-Output "SkuID ($($sku.ProductName)) already exists in database"
                    }
                    # Add licensed2licensedskus table
                    $sqlls = "select * from licensedskus where DayOfYear='$($dayofyear)' and tenantid='$($customer.CustomerId)' and skuid='$($sku.SkuID)'"
                    $ls = invoke-sqlcmd -query $sqlls -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword
                    if (-not $ls ) {
                        # Not registered for today -> insert
                        $sqlls = "insert into licensedskus (DayOfYear,Date,TenantID,SkuID,AvailableUnits,ActiveUnits,CapabilityStatus,ConsumedUnits,LicenseGroupId,SuspendedUnits,TargetType,TotalUnits,WarningUnits) values ('$($dayofyear)','$($today)','$($customer.CustomerId)','$($sku.SkuID)','$($sku.AvailableUnits)','$($sku.ActiveUnits)','$($sku.CapabilityStatus)','$($sku.ConsumedUnits)','$($sku.LicenseGroupId)','$($sku.SuspendedUnits)','$($sku.TargetType)','$($sku.TotalUnits)','$($sku.WarningUnits)')"  #.ToUniversalTime()
                        $ls = invoke-sqlcmd -query $sqlls -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword
                    }
                    else {
                        if ($force) {
                            $sqlls = "update licensedskus set DayOfYear='$($dayofyear)',Date='$($today)',TenantID='$($customer.CustomerId)',SkuID='$($sku.SkuID)',AvailableUnits='$($sku.AvailableUnits)',ActiveUnits='$($sku.ActiveUnits)',CapabilityStatus='$($sku.CapabilityStatus)',ConsumedUnits='$($sku.ConsumedUnits)',LicenseGroupId='$($sku.LicenseGroupId)',SuspendedUnits='$($sku.SuspendedUnits)',TargetType='$($sku.TargetType)',TotalUnits='$($sku.TotalUnits)',WarningUnits='$($sku.WarningUnits)' where DayOfYear='$($dayofyear)' and tenantid='$($customer.CustomerId)' and skuid='$($sku.SkuID)'"
                            $ls = invoke-sqlcmd -query $sqlls -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword    
                            Write-output " Updating sku ($($sku.Productname))"
                        }
                    }
                }
            }

          }
       
    }
}

This PS script inserts data into “skus” table and “licensedskus” table. Here are the sql management studio scripts to create those.
“SKSU” tabler

USE [Microsoft365]
GO

/****** Object:  Table [dbo].[Skus]    Script Date: 17.01.2021 21:34:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Skus](
    [SkuID] [varchar](255) NOT NULL,
    [SkuPartNumber] [varchar](255) NULL,
    [ProductName] [varchar](255) NULL,
    [FriendlyName] [varchar](255) NULL,
 CONSTRAINT [PK_Skus] PRIMARY KEY CLUSTERED
(
    [SkuID] 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

And lucky you here are the sql MS to create the othe table:

USE [Microsoft365]
GO

/****** Object:  Table [dbo].[LicensedSkus]    Script Date: 17.01.2021 21:38:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LicensedSkus](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DayOfYear] [varchar](10) NULL,
    [Date] [datetime] NULL,
    [TenantID] [varchar](50) NULL,
    [SkuID] [varchar](50) NULL,
    [AvailableUnits] [int] NULL,
    [ActiveUnits] [int] NULL,
    [CapabilityStatus] [varchar](50) NULL,
    [ConsumedUnits] [int] NULL,
    [LicenseGroupId] [varchar](50) NULL,
    [SuspendedUnits] [int] NULL,
    [TargetType] [varchar](50) NULL,
    [TotalUnits] [int] NULL,
    [WarningUnits] [int] NULL,
 CONSTRAINT [PK_LicensedSkus] PRIMARY KEY CLUSTERED
(
    [ID] 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

Get-tenantlistindb (from previous post)

Now that we have our tenants listed in a database table, listing them is quite easy.
Added a switch that allow you to also list deleted/removed tenants.

function get-tenantlistindb {
    param(
        [switch]$all
    )
    $SQLInstance = "localhost\SQLExpress"
    $SQLDatabase = "Microsoft365"
    $sqlqr = "select * from [Microsoft365].[dbo].[tenants]"
    if (-not $all) {
        $sqlqr += " where active='1'"
    }
    $tenants = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
    $tenants
}

Log tenants from office 365 to local Db

Hi,
I like to keep control of how many licenses our cutomers use versus how many  have been purchased.
Here is 1st part  my PS script to copy the info from csp to the DB. I will start creating a database and table to keep a list of all the tenants in the csp. You will also need an service principal in the tenant.
To create a SPN in CSP check this :CSP access to tenants using powershell. Part 1
I am also using the storedcredential -> Get Secretserver secret

function Update-TenantListinDB {

# Set all customers in database where active is &lt; 10 to inactive
import-module partnercenter
$tid = "Partnercenter Tenant ID"
$appid = "AppID"
$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"
$connected = Connect-PartnerCenter -ApplicationId $appid -Credential $app -RefreshToken $token
if (-not $connected) { throw "Error connecting to partnercenter.." }
update-storedcredential -user cspuser -secret ($partneraccesstoken.RefreshToken | ConvertTo-SecureString -AsPlainText -Force)

$SQLInstance = "localhost\SQLExpress"
$SQLDatabase = "Microsoft365"
$SQLUsername = ""
$SQLPassword = ""

$customers = Get-PartnerCustomer

$deactivated = Invoke-Sqlcmd -Query "update tenants set active='0' where active &lt; '10'" -ServerInstance $SQLInstance -Database $SQLDatabase
Write-Host "Parsing $(($customers).count) tenants."
foreach ($customer in $customers) {
# Check if exists
$select = "select * from tenants where tenantid like '$($customer.customerid)'"
$found = invoke-sqlcmd -query $select -ServerInstance $SQLInstance -Database $SQLDatabase
if ($found -eq $null) {
Write-Host "Inserting tenant:" $customer.Name
$SQLQuery1 = "insert into tenants (tenantid,tenantname,displayname,active) values ('$($customer.CustomerId)','$($customer.Domain)','$($customer.Name)','1')"
invoke-sqlcmd -query $SQLQuery1 -ServerInstance $SQLInstance -Database $SQLDatabase # -Username $SQLUsername -Password $SQLPassword
}
else {
Invoke-Sqlcmd -Query "update tenants set active='1' where tenantID like '$($customer.CustomerId)' and active &lt;'10'" -ServerInstance $SQLInstance -Database $SQLDatabase
}
}
}

I use this code to populat my database (using integrated authentication).
Sql for tabel (Database named Microsoft365):

USE [Microsoft365]
GO

/****** Object: Table [dbo].[tenants] Script Date: 03.01.2021 20:18:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tenants](
[tenantID] [VARCHAR](255) NOT NULL,
[tenantname] [VARCHAR](255) NOT NULL,
[displayname] [VARCHAR](255) NULL,
[active] [INT] NOT NULL,
CONSTRAINT [PK_tenants] PRIMARY KEY CLUSTERED
(
[tenantID] 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

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
$refreshtoken=$rt.GetNetworkCredential().Password
$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
    $counter=0
    foreach($cust in $customers){
        Write-Host "$($counter) - $($cust.Name) - $($cust.domain)"
        $counter++
    }
    $custid=Read-Host "Enter customer #"
    $customer=$customers[$custid]
    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.