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))"
}
}
}
}
}
}
}
