function get-tenantlics {
\n <#
\n .SYNOPSIS
\n List tenant licenses from your CSP
\n .DESCRIPTION
\n List licenses for tenant purchased using your CSP
\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 ProductName
\n -ProductName <string>
\n Poductname refers to Microsoft product name , "Office 365 E3" , this is a autocomplete field from all products in database-
\n .EXAMPLE
\n get-tenantlics -tenant 'Tenant name' -productname 'Office 365 E3' -lastndays 2
\n
\n .PARAMETER lastndays
\n Return license report for last N days, default is last reported.
\n
\n #>
\n [CmdletBinding()]
\n param(
\n [int]$lastndays = 1
\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 $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 process {
\n $lastndays--
\n if ($lastndays -lt 0) { $lastndays = 0 }
\n $SQLInstance = "localhost\\SQLExpress"
\n $SQLDatabase = "Microsoft365"
\n $today = get-date -Hour 0 -Minute 0 -Second 0
\n $fromdate = $today.AddDays(($lastndays * -1) )
\n $sqlqr = "select Date,Tenantname,Displayname,ProductName,ActiveUnits,ConsumedUnits,AvailableUnits,SuspendedUnits,TotalUnits,CapabilityStatus from [Microsoft365].[dbo].[skuscustomer] where tenantname like '$($kunde)' and Date >='$($fromdate)' and Productname like '$($product)'"
\n $Skus = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
\n if (-not $skus) {
\n $fromdate = $today.AddDays(($lastndays * -1) - 1)
\n $sqlqr = "select Date,Tenantname,Displayname,ProductName,ActiveUnits,ConsumedUnits,AvailableUnits,SuspendedUnits,TotalUnits,CapabilityStatus from [Microsoft365].[dbo].[skuscustomer] where tenantname like '$($kunde)' and Date >='$($fromdate)' and Productname like '$($product)'"
\n $Skus = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
\n }
\n $skus
\n }
\n}<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"
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. function get-tenantlics { <# .SYNOPSIS List tenant licenses from your CSP .DESCRIPTION … Continue reading Use get-tenantlicsindb after syncing them to our 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":"Use get-tenantlicsindb after syncing them to our 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,73,74,2,48,10],"tags":[],"class_list":["post-1018","post","type-post","status-publish","format-standard","hentry","category-azure","category-azure-uncategorized","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":1018,"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":899,"url":"https:\/\/www.vatland.no\/index.php\/csp-access-to-tenants-using-powershell-part-2\/","url_meta":{"origin":1018,"position":1},"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":804,"url":"https:\/\/www.vatland.no\/index.php\/use-powershell-to-get-leakedcredentials-from-azure-using-graph\/","url_meta":{"origin":1018,"position":2},"title":"Use Powershell to get LeakedCredentials from Azure using Graph","author":"Atle","date":"November 1, 2018","format":false,"excerpt":"Leaked credentials listed from Azure using powershell and Microsoft Graph\u00a0We need one Azure AD Premium X license to get this log. Would it be nice to list all leakedcredentials using powershell?(or riskysignins or identiyriskevents). All of this could be achieved using powershell and REST api at Microsoft Graph. I have\u2026","rel":"","context":"In \"Powershell\"","block_context":{"text":"Powershell","link":"https:\/\/www.vatland.no\/index.php\/tag\/powershell\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.vatland.no\/wp-content\/uploads\/2018\/11\/Leaked.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.vatland.no\/wp-content\/uploads\/2018\/11\/Leaked.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.vatland.no\/wp-content\/uploads\/2018\/11\/Leaked.jpg?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":902,"url":"https:\/\/www.vatland.no\/index.php\/csp-access-to-tenants-using-powershell-part-3\/","url_meta":{"origin":1018,"position":3},"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":1018,"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":1018,"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\/1018","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=1018"}],"version-history":[{"count":3,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/1018\/revisions"}],"predecessor-version":[{"id":1038,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/posts\/1018\/revisions\/1038"}],"wp:attachment":[{"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/media?parent=1018"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/categories?post=1018"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vatland.no\/index.php\/wp-json\/wp\/v2\/tags?post=1018"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}