Log tenants from office 365 to local Db

Hi,
I like to keep control of how many licenses our cutomers use versus how many  have been purchased.
Here is 1st part  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 tenants in the csp. You will also need an service principal in the tenant.
To create a SPN in CSP check this :CSP access to tenants using powershell. Part 1
I am also using the storedcredential -> Get Secretserver secret

function Update-TenantListinDB {

# Set all customers in database where active is < 10 to inactive
import-module partnercenter
$tid = "Partnercenter Tenant ID"
$appid = "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 -Credential $app -Tenant $tid -Scopes 'https://api.partnercenter.microsoft.com/user_impersonation' -ServicePrincipal -ApplicationId $appid # -Resource "https://api.partnercenter.microsoft.com"
$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 = ""

$customers = Get-PartnerCustomer

$deactivated = Invoke-Sqlcmd -Query "update tenants set active='0' where active < '10'" -ServerInstance $SQLInstance -Database $SQLDatabase
Write-Host "Parsing $(($customers).count) tenants."
foreach ($customer in $customers) {
# Check if exists
$select = "select * from tenants where tenantid like '$($customer.customerid)'"
$found = invoke-sqlcmd -query $select -ServerInstance $SQLInstance -Database $SQLDatabase
if ($found -eq $null) {
Write-Host "Inserting tenant:" $customer.Name
$SQLQuery1 = "insert into tenants (tenantid,tenantname,displayname,active) values ('$($customer.CustomerId)','$($customer.Domain)','$($customer.Name)','1')"
invoke-sqlcmd -query $SQLQuery1 -ServerInstance $SQLInstance -Database $SQLDatabase # -Username $SQLUsername -Password $SQLPassword
}
else {
Invoke-Sqlcmd -Query "update tenants set active='1' where tenantID like '$($customer.CustomerId)' and active <'10'" -ServerInstance $SQLInstance -Database $SQLDatabase
}
}
}

I use this code to populat my database (using integrated authentication).
Sql for tabel (Database named Microsoft365):

USE [Microsoft365]
GO

/****** Object: Table [dbo].[tenants] Script Date: 03.01.2021 20:18:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tenants](
[tenantID] [VARCHAR](255) NOT NULL,
[tenantname] [VARCHAR](255) NOT NULL,
[displayname] [VARCHAR](255) NULL,
[active] [INT] NOT NULL,
CONSTRAINT [PK_tenants] PRIMARY KEY CLUSTERED
(
[tenantID] 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

Leave a Reply