List users and licenses in Database

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.

function get-tenantuserlicenses {
 
    [CmdletBinding()]
    param(
    )
    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
        $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 {
        $SQLInstance = "localhost\SQLExpress"
        $SQLDatabase = "Microsoft365"
        
    $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)'" 
        $users = invoke-sqlcmd -query $sqlquery -ServerInstance $SQLInstance -Database $SQLDatabase
        if ($users.count -gt 0) { $f = (((($users[0] | get-member) | Where-Object { $_.membertype -eq "Property" } )).name) | sort-object -desc }
        $users | Select-Object $f 

    }

}

The 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.

Leave a Reply