Tag Archives: Sql

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.

Powershell to get DirectAccess connection history.

This is a simple powershell to get data from the DirectAccess database. It reguired some serious Bing’ing (and google) to get the time field. You will have to configure reporting database in DirectAccess config. I used windows internal database.

$server="\\.\pipe\MICROSOFT##WID\tsql\query"
$database="RaAcctDb"
#$table="connectiontable"
$table="sessiontable"
#$table="endpointsaccessedtable"
#$table="serverendpointtable"

$cs="server=$server;database=$database;Integrated Security=True;"
$connection=New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString=$cs
$connection.Open()

#$query="Select * from $table"
#$query="Select * from $database.INFORMATION_SCHEMA.TABLES"
$query="declare @start bigint=131277336299720000;select dateadd(mi,datediff(mi,getutcdate(),getdate()),([sessionstarttime]/864000000000.0-109207)) AS DATO,* from sessiontable join connectiontable on sessiontable.connectionid=connectiontable.connectionid where sessionstarttime >=@start"

$command=$connection.CreateCommand()
$command.CommandText=$query
$result=$command.ExecuteReader()

$resulttable=New-Object System.Data.DataTable
$resulttable.Load($result)
$resulttable | Out-GridView
$connection.Close()