Azure | Vatland

Ok, Now that we have syncronized our customers licenes to a local DB, we have this new commandlet to list them. This is a easy one, no advanced tokens setup to connect to Microsoft endpoint, only local database.

function get-tenantlics {
<#
.SYNOPSIS
List tenant licenses from your CSP
.DESCRIPTION
List licenses for tenant purchased using your CSP
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 ProductName
-ProductName <string>
Poductname refers to Microsoft product name , "Office 365 E3" , this is a autocomplete field from all products in database-
.EXAMPLE
get-tenantlics -tenant 'Tenant name' -productname 'Office 365 E3' -lastndays 2

.PARAMETER lastndays
Return license report for last N days, default is last reported.

#>
[CmdletBinding()]
param(
[int]$lastndays = 1
)
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)

$Parameter2Name = "ProductName"
$Parameter2Attribute = New-Object System.Management.Automation.ParameterAttribute
$Parameter2Attribute.Mandatory = $false
$Parameter2Attribute.Position = 2
$Attribute2Collection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]
$Attribute2Collection.Add($Parameter2Attribute)
$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)

$Attribute2Collection.Add($ValidateSet2Attribute)
$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 {
$lastndays--
if ($lastndays -lt 0) { $lastndays = 0 }
$SQLInstance = "localhost\SQLExpress"
$SQLDatabase = "Microsoft365"
$today = get-date -Hour 0 -Minute 0 -Second 0
$fromdate = $today.AddDays(($lastndays * -1) )
$sqlqr = "select Date,Tenantname,Displayname,ProductName,ActiveUnits,ConsumedUnits,AvailableUnits,SuspendedUnits,TotalUnits,CapabilityStatus from [Microsoft365].[dbo].[skuscustomer] where tenantname like '$($kunde)' and Date >='$($fromdate)' and Productname like '$($product)'"
$Skus = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
if (-not $skus) {
$fromdate = $today.AddDays(($lastndays * -1) - 1)
$sqlqr = "select Date,Tenantname,Displayname,ProductName,ActiveUnits,ConsumedUnits,AvailableUnits,SuspendedUnits,TotalUnits,CapabilityStatus from [Microsoft365].[dbo].[skuscustomer] where tenantname like '$($kunde)' and Date >='$($fromdate)' and Productname like '$($product)'"
$Skus = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
}
$skus
}
}

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

Vatland

Category Archives: Azure

Use get-tenantlicsindb after syncing them to our local db.

Add tenant licenses from csp to database.

-MS Stuff-

Share this:

Share this:

-MS Stuff-