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 {
<#
.SYNOPSIS
List tenant licenses from your CSP
.DESCRIPTION
List licenses for tenant purchased using your CSP
Tenant refer to the display name of the tenant "Super Company 1"
Tenant is a dynamic parameter, autocomplete list.
DYNAMIC PARAMTERS
PARAMETER tenant
-tenant
tenant refers to the display name of the tenant “Super Company 1”
If omitted all tenants are returned. Autocomplete list of all tenants in csp
PARAMETER ProductName
-ProductName
Poductname refers to Microsoft product name , “Office 365 E3” , this is a autocomplete field from all products in database-
.EXAMPLE
get-tenantlics -tenant ‘Tenant name’ -productname ‘Office 365 E3′ -lastndays 2
.PARAMETER lastndays
Return license report for last N days, default is last reported.
#>
[CmdletBinding()]
param(
[int]$lastndays = 1
)
DynamicParam {
$ParameterName = “tenant”
$RuntimeParameterDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
$AttributeCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]
# Create and set the parameters’ attributes
$ParameterAttribute = New-Object System.Management.Automation.ParameterAttribute
$ParameterAttribute.Mandatory = $false
$ParameterAttribute.Position = 1
# Add the attributes to the attributes collection
$AttributeCollection.Add($ParameterAttribute)
# Generate and set the ValidateSet
# Add the ValidateSet to the attributes collection
$SQLInstance = “localhost\SQLExpress”
$SQLDatabase = “Microsoft365”
$sqlqr = “select * from [Microsoft365].[dbo].[tenants]”
$customers = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
#$arrSet = Get-WmiObject Win32_Service -ComputerName $computername | select -ExpandProperty Name
$ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($Customers | Select-Object -ExpandProperty tenantname)
$AttributeCollection.Add($ValidateSetAttribute)
$Parameter2Name = “ProductName”
$Parameter2Attribute = New-Object System.Management.Automation.ParameterAttribute
$Parameter2Attribute.Mandatory = $false
$Parameter2Attribute.Position = 2
$Attribute2Collection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]
$Attribute2Collection.Add($Parameter2Attribute)
$sqlqr2 = “select * from [Microsoft365].[dbo].[skus]”
$skus = invoke-sqlcmd -query $sqlqr2 -ServerInstance $SQLInstance -Database $SQLDatabase
#$arrSet = Get-WmiObject Win32_Service -ComputerName $computername | select -ExpandProperty Name
$ValidateSet2Attribute = New-Object System.Management.Automation.ValidateSetAttribute($skus | Select-Object -ExpandProperty productname)
$Attribute2Collection.Add($ValidateSet2Attribute)
$RuntimeParameter2 = New-Object System.Management.Automation.RuntimeDefinedParameter($Parameter2Name, [string], $Attribute2Collection)
# Create and return the dynamic parameter
$RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)
$RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)
$RuntimeParameterDictionary.Add($Parameter2Name, $RuntimeParameter2)
return $RuntimeParameterDictionary
}
begin {
# Bind the parameter to a friendly variable
$Kunde = $PsBoundParameters[$ParameterName]
if ( -not $kunde) {
$kunde = “%”
}
else {
}
$Product = $PsBoundParameters[$Parameter2Name]
if ( -not $Product) {
$Product = “%”
}
else {
}
}
process {
$lastndays–
if ($lastndays -lt 0) { $lastndays = 0 }
$SQLInstance = “localhost\SQLExpress”
$SQLDatabase = “Microsoft365”
$today = get-date -Hour 0 -Minute 0 -Second 0
$fromdate = $today.AddDays(($lastndays * -1) )
$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)'”
$Skus = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
if (-not $skus) {
$fromdate = $today.AddDays(($lastndays * -1) – 1)
$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)'”
$Skus = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
}
$skus
}
}
[/cc]
