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 way my data is new. Based on this data you could create a graph indicating office 365 license usage. <\/p>\n\n\n
\nfunction get-tenantuserlicenses {\n \n [CmdletBinding()]\n param(\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 # Generate and set the ValidateSet\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 $ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($Customers | Select-Object -ExpandProperty tenantname)\n\n $AttributeCollection.Add($ValidateSetAttribute)\n\n $Parameter2Name = "ProductName"\n $Parameter2Attribute = New-Object System.Management.Automation.ParameterAttribute\n $Parameter2Attribute.Mandatory = $false\n $Parameter2Attribute.Position = 2\n $Attribute2Collection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]\n $Attribute2Collection.Add($Parameter2Attribute)\n $sqlqr2 = "select * from [Microsoft365].[dbo].[skus]"\n $skus = invoke-sqlcmd -query $sqlqr2 -ServerInstance $SQLInstance -Database $SQLDatabase\n #$arrSet = Get-WmiObject Win32_Service -ComputerName $computername | select -ExpandProperty Name\n $ValidateSet2Attribute = New-Object System.Management.Automation.ValidateSetAttribute($skus | Select-Object -ExpandProperty productname)\n\n $Attribute2Collection.Add($ValidateSet2Attribute)\n $RuntimeParameter2 = New-Object System.Management.Automation.RuntimeDefinedParameter($Parameter2Name, [string], $Attribute2Collection)\n\n\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 $RuntimeParameterDictionary.Add($Parameter2Name, $RuntimeParameter2)\n return $RuntimeParameterDictionary\n }\n begin {\n\n # Bind the parameter to a friendly variable\n $Kunde = $PsBoundParameters[$ParameterName]\n if ( -not $kunde) {\n $kunde = "%"\n }\n else {\n \n }\n $Product = $PsBoundParameters[$Parameter2Name]\n if ( -not $Product) {\n $Product = "%"\n }\n else {\n \n }\n\n\n }\n process {\n $SQLInstance = "localhost\\SQLExpress"\n $SQLDatabase = "Microsoft365"\n \n $sqlquery = "SELECT dbo.usersextended.displayname as userdisplayname,userprincipalname,givenname,surname,dbo.usersextended.active as activeuser,tenantname,dbo.tenants.displayname as tenantdisplayname,Productname,usagelocation FROM usersextended Cross apply string_split(usersextended.skus,',') INNER JOIN dbo.tenants ON dbo.usersextended.tenantid = dbo.tenants.tenantID inner join dbo.skus on dbo.Skus.skuid like '%'+value+'%' where usersextended.skus like '%-%' and tenantname like '$($kunde)' and usersextended.userprincipalname not like '%#EXT#%' and productname like '$($product)'" \n $users = invoke-sqlcmd -query $sqlquery -ServerInstance $SQLInstance -Database $SQLDatabase\n if ($users.count -gt 0) { $f = (((($users[0] | get-member) | Where-Object { $_.membertype -eq "Property" } )).name) | sort-object -desc }\n $users | Select-Object $f \n\n }\n\n}\n<\/pre><\/div>\n\n\nThe important part is the T-Sql. This will join the usersextended (previously users) table and tenant table to generate a list of users and licenses. If used in combination with export-excel this is one of my favorite tools.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 way my data is new. … Continue reading List users and licenses in 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":"","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":[74,48],"tags":[65,82,59,62],"class_list":["post-1043","post","type-post","status-publish","format-standard","hentry","category-csp","category-powershell","tag-csp","tag-graph-api","tag-powershell","tag-sql"],"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":1043,"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\/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":1043,"position":1},"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":1043,"position":2},"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":1018,"url":"https:\/\/www.vatland.no\/index.php\/use-get-tenantlicsindb-after-syncing-them-to-our-local-db\/","url_meta":{"origin":1043,"position":3},"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":875,"url":"https:\/\/www.vatland.no\/index.php\/csp-access-to-tenants-using-powershell-part-1\/","url_meta":{"origin":1043,"position":4},"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":1043,"position":5},"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":[]}],"_links":{"self":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/1043","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=1043"}],"version-history":[{"count":7,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/1043\/revisions"}],"predecessor-version":[{"id":1063,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/1043\/revisions\/1063"}],"wp:attachment":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/media?parent=1043"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/categories?post=1043"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/tags?post=1043"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}