function get-tenantlistindb {
\n param(
\n [switch]$all
\n )
\n $SQLInstance = "localhost\\SQLExpress"
\n $SQLDatabase = "Microsoft365"
\n $sqlqr = "select * from [Microsoft365].[dbo].[tenants]"
\n if (-not $all) {
\n $sqlqr += " where active='1'"
\n }
\n $tenants = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
\n $tenants
\n}<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"
Now that we have our tenants listed in a database table, listing them is quite easy. Added a switch that allow you to also list deleted\/removed tenants. function get-tenantlistindb { param( [switch]$all ) $SQLInstance = "localhost\\SQLExpress" $SQLDatabase = "Microsoft365" $sqlqr = … Continue reading Get-tenantlistindb (from previous post)<\/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_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":"Get-tenantlistindb (from previous post)","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},"jetpack_post_was_ever_published":false},"categories":[72,74,2,48],"tags":[],"class_list":["post-990","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":1018,"url":"https:\/\/www.vatland.no\/index.php\/use-get-tenantlicsindb-after-syncing-them-to-our-local-db\/","url_meta":{"origin":990,"position":0},"title":"Use get-tenantlicsindb after syncing them to our local db.","author":"Atle","date":"January 31, 2021","format":false,"excerpt":"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. [cc language=powershell] function get-tenantlics { [CmdletBinding()] param( [int]$lastndays = 1 ) DynamicParam\u2026","rel":"","context":"In "Azure"","block_context":{"text":"Azure","link":"https:\/\/www.vatland.no\/index.php\/category\/uncategorized\/azure-uncategorized\/"},"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":990,"position":1},"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":1010,"url":"https:\/\/www.vatland.no\/index.php\/add-tenant-licenses-from-csp-to-database\/","url_meta":{"origin":990,"position":2},"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\/uncategorized\/azure-uncategorized\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":975,"url":"https:\/\/www.vatland.no\/index.php\/log-tenants-from-office-365-to-local-db\/","url_meta":{"origin":990,"position":3},"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":1020,"url":"https:\/\/www.vatland.no\/index.php\/import-user-licenses-from-csp-using-csp-api-and-graph\/","url_meta":{"origin":990,"position":4},"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":[]},{"id":875,"url":"https:\/\/www.vatland.no\/index.php\/csp-access-to-tenants-using-powershell-part-1\/","url_meta":{"origin":990,"position":5},"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":[]}],"_links":{"self":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/990","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=990"}],"version-history":[{"count":4,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/990\/revisions"}],"predecessor-version":[{"id":994,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/990\/revisions\/994"}],"wp:attachment":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/media?parent=990"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/categories?post=990"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/tags?post=990"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}