Category Archives: Azure

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

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 Cegal CSP
  .DESCRIPTION
  List licenses for tenant purchased using Cegal 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
    }
}

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