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 <string>
  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 <string>
  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
    }
}