function update-tenantlicsinDB {
\n <#
\n .SYNOPSIS
\n Update tenant subscriptions\/licenses from Cegal CSP
\n .DESCRIPTION
\n Update licenses from tenant.
\n Tenant refer to the display name of the tenant "Super Company 1"
\n Tenant is a dynamic parameter, autocomplete list.
\n
\n DYNAMIC PARAMTERS
\n
\n PARAMETER tenant
\n -tenant <string>
\n tenant refers to the display name of the tenant "Super Company 1"
\n If omitted all tenants are returned. Autocomplete list of all tenants in csp
\n
\n .PARAMETER force
\n Will force update of skus in database. Normaly updated once a day.
\n
\n .EXAMPLE
\n update-tenantlicsindb -tenant 'Tenant name' -force
\n
\n .EXAMPLE
\n update-tenantlicsindb
\n
\n #>
\n [CmdletBinding()]
\n param(
\n [switch]$force,
\n [switch]$updateusers
\n )
\n DynamicParam {
\n $ParameterName = "tenant"
\n $RuntimeParameterDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
\n $AttributeCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]
\n
\n # Create and set the parameters' attributes
\n $ParameterAttribute = New-Object System.Management.Automation.ParameterAttribute
\n $ParameterAttribute.Mandatory = $false
\n $ParameterAttribute.Position = 1
\n
\n # Add the attributes to the attributes collection
\n $AttributeCollection.Add($ParameterAttribute)
\n
\n # Generate and set the ValidateSet
\n
\n # Add the ValidateSet to the attributes collection
\n $SQLInstance = "localhost\\SQLExpress"
\n $SQLDatabase = "Microsoft365"
\n $sqlqr = "select * from [Microsoft365].[dbo].[tenants]"
\n $customers = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
\n #$arrSet = Get-WmiObject Win32_Service -ComputerName $computername | select -ExpandProperty Name
\n $ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($Customers | Select-Object -ExpandProperty tenantname)
\n
\n $AttributeCollection.Add($ValidateSetAttribute)
\n
\n # Create and return the dynamic parameter
\n $RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)
\n $RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)
\n return $RuntimeParameterDictionary
\n }
\n begin {
\n
\n # Bind the parameter to a friendly variable
\n $Kunde = $PsBoundParameters[$ParameterName]
\n }
\n process {
\n
\n import-module partnercenter
\n $tid = "<CSP tenant ID>"
\n $appid = "<App ID>"
\n
\n $k = (get-storedcredential -user $appid).password
\n $token = (get-storedcredential -user cspuser).getnetworkcredential().password
\n $app = New-Object System.Management.Automation.PSCredential -ArgumentList $appid, $k
\n # $partneraccesstoken = New-PartnerAccessToken -RefreshToken $token -Resource "https:\/\/api.partnercenter.microsoft.com" -Credential $app -TenantId $tid
\n $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"
\n $partneraccesstoken
\n #$connected = Connect-PartnerCenter -AccessToken $partneraccesstoken.AccessToken -ApplicationId $app.username -TenantId $tid -Environment AzureCloud -ServicePrincipal
\n $connected = Connect-PartnerCenter -ApplicationId $appid -Credential $app -RefreshToken $token
\n if (-not $connected) { throw "Error connecting to partnercenter.." }
\n update-storedcredential -user cspuser -secret ($partneraccesstoken.RefreshToken | ConvertTo-SecureString -AsPlainText -Force)
\n
\n $SQLInstance = "localhost\\SQLExpress"
\n $SQLDatabase = "Microsoft365"
\n # $SQLUsername = ""
\n # $SQLPassword = ""
\n
\n $today = Get-date
\n $dayofyear = "$($today.Year)-$($today.DayOfYear)"
\n if (-not $kunde) {
\n try {
\n $customers = Get-PartnerCustomer
\n }
\n catch { Write-Host "Error getting tenants from partnercenter." }
\n }
\n else {
\n
\n $customers = Get-PartnerCustomer | Where-Object { $_.domain -eq $kunde }
\n }
\n
\n # Add users to DB. (not #ext# or contacts)
\n # $customer=$customers[9]
\n foreach ($customer in $customers ) {
\n #$customer=$customers[2]
\n Write-Host "Processing tenant: $($customer.Name) ($($customer.domain))"
\n # Add Sku to Skus table
\n $customerskus = Get-PartnerCustomerSubscribedSku -CustomerId $customer.CustomerId
\n if ($customerskus) {
\n foreach ($sku in $customerskus) {
\n try {
\n $sqlsku = "insert into skus (SkuID,SkuPartNumber,ProductName) values ('$($sku.SkuID)','$($sku.SkuPartNumber)','$($sku.ProductName)')"
\n invoke-sqlcmd -query $sqlsku -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword
\n Write-Output "SkuID ($($sku.ProductName)) added to database"
\n }
\n catch {
\n $sqlsku = "insert into skus (SkuID,SkuPartNumber,ProductName) values ()"
\n # Write-Output "SkuID ($($sku.ProductName)) already exists in database"
\n }
\n # Add licensed2licensedskus table
\n $sqlls = "select * from licensedskus where DayOfYear='$($dayofyear)' and tenantid='$($customer.CustomerId)' and skuid='$($sku.SkuID)'"
\n $ls = invoke-sqlcmd -query $sqlls -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword
\n if (-not $ls ) {
\n # Not registered for today -> insert
\n $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()
\n $ls = invoke-sqlcmd -query $sqlls -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword
\n }
\n else {
\n if ($force) {
\n $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)'"
\n $ls = invoke-sqlcmd -query $sqlls -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword
\n Write-output " Updating sku ($($sku.Productname))"
\n }
\n }
\n }
\n }
\n
\n }
\n
\n }
\n}<\/div><\/div>\n
This PS script inserts data into “skus” table and “licensedskus” table. Here are the sql management studio scripts to create those.
\n“SKSU” tabler<\/p>\n
USE [Microsoft365]
\nGO
\n
\n\/****** Object: Table [dbo].[Skus] Script Date: 17.01.2021 21:34:11 ******\/
\nSET ANSI_NULLS ON
\nGO
\n
\nSET QUOTED_IDENTIFIER ON
\nGO
\n
\nCREATE TABLE [dbo].[Skus](
\n [SkuID] [varchar](255) NOT NULL,
\n [SkuPartNumber] [varchar](255) NULL,
\n [ProductName] [varchar](255) NULL,
\n [FriendlyName] [varchar](255) NULL,
\n CONSTRAINT [PK_Skus] PRIMARY KEY CLUSTERED
\n(
\n [SkuID] ASC
\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
\n) ON [PRIMARY]
\nGO<\/div><\/div>\n
And lucky you here are the sql MS to create the othe table:<\/p>\n
USE [Microsoft365]
\nGO
\n
\n\/****** Object: Table [dbo].[LicensedSkus] Script Date: 17.01.2021 21:38:41 ******\/
\nSET ANSI_NULLS ON
\nGO
\n
\nSET QUOTED_IDENTIFIER ON
\nGO
\n
\nCREATE TABLE [dbo].[LicensedSkus](
\n [ID] [int] IDENTITY(1,1) NOT NULL,
\n [DayOfYear] [varchar](10) NULL,
\n [Date] [datetime] NULL,
\n [TenantID] [varchar](50) NULL,
\n [SkuID] [varchar](50) NULL,
\n [AvailableUnits] [int] NULL,
\n [ActiveUnits] [int] NULL,
\n [CapabilityStatus] [varchar](50) NULL,
\n [ConsumedUnits] [int] NULL,
\n [LicenseGroupId] [varchar](50) NULL,
\n [SuspendedUnits] [int] NULL,
\n [TargetType] [varchar](50) NULL,
\n [TotalUnits] [int] NULL,
\n [WarningUnits] [int] NULL,
\n CONSTRAINT [PK_LicensedSkus] PRIMARY KEY CLUSTERED
\n(
\n [ID] ASC
\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
\n) ON [PRIMARY]
\nGO<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"
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. … Continue reading Add tenant licenses from csp to database.<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"Add tenant licenses from csp to database.","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[73,72,74,2,48,10],"tags":[],"class_list":["post-1010","post","type-post","status-publish","format-standard","hentry","category-azure-uncategorized","category-azure","category-csp","category-development","category-powershell","category-windows"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"jetpack-related-posts":[{"id":975,"url":"https:\/\/www.vatland.no\/index.php\/log-tenants-from-office-365-to-local-db\/","url_meta":{"origin":1010,"position":0},"title":"Log tenants from office 365 to local Db","author":"Atle","date":"January 3, 2021","format":false,"excerpt":"Hi, I like to keep control of how many licenses our cutomers use versus how many\u00a0 have been purchased. Here is 1st part\u00a0 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\u2026","rel":"","context":"In "Azure"","block_context":{"text":"Azure","link":"https:\/\/www.vatland.no\/index.php\/category\/azure\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":902,"url":"https:\/\/www.vatland.no\/index.php\/csp-access-to-tenants-using-powershell-part-3\/","url_meta":{"origin":1010,"position":1},"title":"CSP access to tenants using powershell. Part 3","author":"Atle","date":"September 23, 2019","format":false,"excerpt":"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\u2026","rel":"","context":"In "Azure"","block_context":{"text":"Azure","link":"https:\/\/www.vatland.no\/index.php\/category\/azure\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":875,"url":"https:\/\/www.vatland.no\/index.php\/csp-access-to-tenants-using-powershell-part-1\/","url_meta":{"origin":1010,"position":2},"title":"CSP access to tenants using powershell. Part 1","author":"Atle","date":"September 18, 2019","format":false,"excerpt":"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.\u2026","rel":"","context":"In "Azure"","block_context":{"text":"Azure","link":"https:\/\/www.vatland.no\/index.php\/category\/azure\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1043,"url":"https:\/\/www.vatland.no\/index.php\/list-user-and-license-in-database\/","url_meta":{"origin":1010,"position":3},"title":"List users and licenses in Database","author":"Atle","date":"March 26, 2021","format":false,"excerpt":"Now that we have a database containing user, tenant and license it would be nice to have a function\/command to list this. Took me a while to figure it out but thanks to Bing, I prefer Bing before google. I sync my database on a schedule once a day. This\u2026","rel":"","context":"In "CSP"","block_context":{"text":"CSP","link":"https:\/\/www.vatland.no\/index.php\/category\/csp\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":914,"url":"https:\/\/www.vatland.no\/index.php\/csp-access-to-tenants-using-powershell-part-4\/","url_meta":{"origin":1010,"position":4},"title":"CSP access to tenants using powershell. Part 4","author":"Atle","date":"September 24, 2019","format":false,"excerpt":"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\u2026","rel":"","context":"In "Azure"","block_context":{"text":"Azure","link":"https:\/\/www.vatland.no\/index.php\/category\/azure\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":957,"url":"https:\/\/www.vatland.no\/index.php\/hash-tables-in-powershell\/","url_meta":{"origin":1010,"position":5},"title":"Hash tables in powershell","author":"Atle","date":"March 23, 2020","format":false,"excerpt":"We all have the need to store data in some kind of arrays. I use hashtables a lot. Preferred use is as a lookup table, I can use 'contains' instead of looping through each item or reference an object by name instead of index number. Lookup table for licenses is\u2026","rel":"","context":"In "CSP"","block_context":{"text":"CSP","link":"https:\/\/www.vatland.no\/index.php\/category\/csp\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/1010","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/comments?post=1010"}],"version-history":[{"count":6,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/1010\/revisions"}],"predecessor-version":[{"id":1015,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/1010\/revisions\/1015"}],"wp:attachment":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/media?parent=1010"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/categories?post=1010"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/tags?post=1010"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}