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).
[cc language=”powershell”]
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
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 = “”
$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 -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))”
}
}
}
}
}
}
}
[/cc]
This PS script inserts data into “skus” table and “licensedskus” table. Here are the sql management studio scripts to create those.
“SKSU” tabler
[cc language=”powershell”]
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
[/cc]
And lucky you here are the sql MS to create the othe table:
[cc language=”powershell”]
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
[/cc]
