{"id":975,"date":"2021-01-03T20:43:57","date_gmt":"2021-01-03T19:43:57","guid":{"rendered":"http:\/\/www.vatland.no\/?p=975"},"modified":"2021-01-07T12:25:57","modified_gmt":"2021-01-07T11:25:57","slug":"log-tenants-from-office-365-to-local-db","status":"publish","type":"post","link":"https:\/\/www.vatland.no\/index.php\/log-tenants-from-office-365-to-local-db\/","title":{"rendered":"Log tenants from office 365 to local Db"},"content":{"rendered":"

Hi,
\nI like to keep control of how many licenses our cutomers use versus how many\u00a0 have been purchased.
\nHere 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 tenants in the csp. You will also need an service principal in the tenant.
\nTo create a SPN in CSP check this :CSP access to tenants using powershell. Part 1<\/a>
\nI am also using the storedcredential -> Get Secretserver secret<\/a><\/p>\n

function<\/span> Update-<\/span>TenantListinDB {<\/span>
\n
\n# Set all customers in database where active is &lt; 10 to inactive<\/span>
\nimport-<\/span>module partnercenter
\n$tid<\/span> =<\/span> "Partnercenter Tenant ID"<\/span>
\n$appid<\/span> =<\/span> "AppID"<\/span>
\n$k<\/span> =<\/span> (<\/span>get-<\/span>storedcredential -<\/span>user $appid<\/span>)<\/span>.password
\n$token<\/span> =<\/span> (<\/span>get-<\/span>storedcredential -<\/span>user cspuser)<\/span>.getnetworkcredential(<\/span>)<\/span>.password
\n$app<\/span> =<\/span> New-Object<\/span> System.Management.Automation.PSCredential -ArgumentList<\/span> $appid<\/span>,<\/span> $k<\/span>
\n$partneraccesstoken<\/span> =<\/span> New-<\/span>PartnerAccessToken -<\/span>RefreshToken $token<\/span> -Credential<\/span> $app<\/span> -<\/span>Tenant $tid<\/span> -<\/span>Scopes 'https:\/\/api.partnercenter.microsoft.com\/user_impersonation'<\/span> -<\/span>ServicePrincipal -<\/span>ApplicationId $appid<\/span> # -Resource "https:\/\/api.partnercenter.microsoft.com"<\/span>
\n$connected<\/span> =<\/span> Connect-<\/span>PartnerCenter -<\/span>ApplicationId $appid<\/span> -Credential<\/span> $app<\/span> -<\/span>RefreshToken $token<\/span>
\nif<\/span> (<\/span>-not<\/span> $connected<\/span>)<\/span> {<\/span> throw<\/span> "Error connecting to partnercenter.."<\/span> }<\/span>
\nupdate-<\/span>storedcredential -<\/span>user cspuser -<\/span>secret (<\/span>$partneraccesstoken<\/span>.RefreshToken |<\/span> ConvertTo-SecureString<\/span> -AsPlainText<\/span> -Force<\/span>)<\/span>
\n
\n$SQLInstance<\/span> =<\/span> "localhost\\SQLExpress"<\/span>
\n$SQLDatabase<\/span> =<\/span> "Microsoft365"<\/span>
\n$SQLUsername<\/span> =<\/span> ""<\/span>
\n$SQLPassword<\/span> =<\/span> ""<\/span>
\n
\n$customers<\/span> =<\/span> Get-<\/span>PartnerCustomer
\n
\n$deactivated<\/span> =<\/span> Invoke-<\/span>Sqlcmd -Query<\/span> "update tenants set active='0' where active &lt; '10'"<\/span> -<\/span>ServerInstance $SQLInstance<\/span> -<\/span>Database $SQLDatabase<\/span>
\nWrite-Host<\/span> "Parsing $(($customers).count) tenants."<\/span>
\nforeach<\/span> (<\/span>$customer<\/span> in<\/span> $customers<\/span>)<\/span> {<\/span>
\n# Check if exists<\/span>
\n$select<\/span> =<\/span> "select * from tenants where tenantid like '$($customer.customerid)'"<\/span>
\n$found<\/span> =<\/span> invoke-<\/span>sqlcmd -query<\/span> $select<\/span> -<\/span>ServerInstance $SQLInstance<\/span> -<\/span>Database $SQLDatabase<\/span>
\nif<\/span> (<\/span>$found<\/span> -eq<\/span> $null<\/span>)<\/span> {<\/span>
\nWrite-Host<\/span> "Inserting tenant:"<\/span> $customer<\/span>.Name
\n$SQLQuery1<\/span> =<\/span> "insert into tenants (tenantid,tenantname,displayname,active) values ('$($customer.CustomerId)','$($customer.Domain)','$($customer.Name)','1')"<\/span>
\ninvoke-<\/span>sqlcmd -query<\/span> $SQLQuery1<\/span> -<\/span>ServerInstance $SQLInstance<\/span> -<\/span>Database $SQLDatabase<\/span> # -Username $SQLUsername -Password $SQLPassword<\/span>
\n}<\/span>
\nelse<\/span> {<\/span>
\nInvoke-<\/span>Sqlcmd -Query<\/span> "update tenants set active='1' where tenantID like '$($customer.CustomerId)' and active &lt;'10'"<\/span> -<\/span>ServerInstance $SQLInstance<\/span> -<\/span>Database $SQLDatabase<\/span>
\n}<\/span>
\n}<\/span>
\n}<\/span><\/div><\/div>\n

I use this code to populat my database (using integrated authentication).
\nSql for tabel (Database named Microsoft365):<\/p>\n

USE<\/span> [<\/span>Microsoft365]<\/span>
\nGO<\/span>
\n
\n\/****** Object: Table [dbo].[tenants] Script Date: 03.01.2021 20:18:23 ******\/<\/span>
\nSET<\/span> ANSI_NULLS ON<\/span>
\nGO<\/span>
\n
\nSET<\/span> QUOTED_IDENTIFIER ON<\/span>
\nGO<\/span>
\n
\nCREATE<\/span> TABLE<\/span> [<\/span>dbo]<\/span>.<\/span>[<\/span>tenants]<\/span>(<\/span>
\n[<\/span>tenantID]<\/span> [<\/span>VARCHAR<\/span>]<\/span>(<\/span>255<\/span>)<\/span> NOT<\/span> NULL<\/span>,<\/span>
\n[<\/span>tenantname]<\/span> [<\/span>VARCHAR<\/span>]<\/span>(<\/span>255<\/span>)<\/span> NOT<\/span> NULL<\/span>,<\/span>
\n[<\/span>displayname]<\/span> [<\/span>VARCHAR<\/span>]<\/span>(<\/span>255<\/span>)<\/span> NULL<\/span>,<\/span>
\n[<\/span>active]<\/span> [<\/span>INT<\/span>]<\/span> NOT<\/span> NULL<\/span>,<\/span>
\nCONSTRAINT<\/span> [<\/span>PK_tenants]<\/span> PRIMARY<\/span> KEY<\/span> CLUSTERED
\n(<\/span>
\n[<\/span>tenantID]<\/span> ASC<\/span>
\n)<\/span>WITH<\/span> (<\/span>PAD_INDEX =<\/span> OFF,<\/span> STATISTICS_NORECOMPUTE =<\/span> OFF,<\/span> IGNORE_DUP_KEY =<\/span> OFF,<\/span> ALLOW_ROW_LOCKS =<\/span> ON<\/span>,<\/span> ALLOW_PAGE_LOCKS =<\/span> ON<\/span>)<\/span> ON<\/span> [<\/span>PRIMARY<\/span>]<\/span>
\n)<\/span> ON<\/span> [<\/span>PRIMARY<\/span>]<\/span>
\nGO<\/span><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

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 tenants in the csp. You … Continue reading Log tenants from office 365 to local Db<\/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":"Log tenants from office 365 to local Db","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":[72,74,2,48],"tags":[],"class_list":["post-975","post","type-post","status-publish","format-standard","hentry","category-azure","category-csp","category-development","category-powershell"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"jetpack-related-posts":[{"id":1010,"url":"https:\/\/www.vatland.no\/index.php\/add-tenant-licenses-from-csp-to-database\/","url_meta":{"origin":975,"position":0},"title":"Add tenant licenses from csp to database.","author":"Atle","date":"January 17, 2021","format":false,"excerpt":"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\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":975,"position":1},"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":914,"url":"https:\/\/www.vatland.no\/index.php\/csp-access-to-tenants-using-powershell-part-4\/","url_meta":{"origin":975,"position":2},"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":899,"url":"https:\/\/www.vatland.no\/index.php\/csp-access-to-tenants-using-powershell-part-2\/","url_meta":{"origin":975,"position":3},"title":"CSP access to tenants using powershell. Part 2","author":"Atle","date":"September 20, 2019","format":false,"excerpt":"In part 1 we created the Azure Enterprise App for Partnercenter and used this information to connect using powershell and connect-partnercenter. Now we will use this to connect to one of our customers tenants. First we will use AZ module and connect-azaccount. We will use the AZ module and 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":975,"position":4},"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":1020,"url":"https:\/\/www.vatland.no\/index.php\/import-user-licenses-from-csp-using-csp-api-and-graph\/","url_meta":{"origin":975,"position":5},"title":"Import user licenses from CSP using CSP api and graph.","author":"Atle","date":"January 31, 2021","format":false,"excerpt":"Hi,there.This is also quiet simple. I want to get all users and what licenses they have been assigned. All this should do, is retrieve information using a csp token.After some attempts this is my best one. The \"goodlooking\" argument will not work when run from ISE.I'am still using the same\u2026","rel":"","context":"Similar post","block_context":{"text":"Similar post","link":""},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/975","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=975"}],"version-history":[{"count":14,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/975\/revisions"}],"predecessor-version":[{"id":989,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/975\/revisions\/989"}],"wp:attachment":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/media?parent=975"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/categories?post=975"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/tags?post=975"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}