Category Archives: Uncategorized

Add tenant licenses from csp to database.

This is a followup from previus post. In this post I will populate the database with what licenses a tenant has aquired.
This will add records of what aquired skus and ‘usage’. For me this is how many license are bought versus how many are assigned. This does not account for senarios other than CSP.
In this function I am only adding the overview of license. How many has been purchased(from us) and versus how many has been assigned. There is no information about what licens a specific user are assigened ( This is for a later article).

function update-tenantlicsinDB {
  Update tenant subscriptions/licenses from Cegal CSP
  Update licenses from tenant.
  Tenant refer to the display name of the tenant "Super Company 1"
  Tenant is a dynamic parameter, autocomplete list.

  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 force
  Will force update of skus in database. Normaly updated once a day.
  update-tenantlicsindb -tenant 'Tenant name' -force


    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

        # 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)


        # Create and return the dynamic parameter
        $RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)
        $RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)
        return $RuntimeParameterDictionary
    begin {

        # Bind the parameter to a friendly variable
        $Kunde = $PsBoundParameters[$ParameterName]  
    process {

        import-module partnercenter
        $tid = "<CSP tenant ID>"
        $appid = "<App ID>"
        $k = (get-storedcredential -user $appid).password
        $token = (get-storedcredential -user cspuser).getnetworkcredential().password
        $app = New-Object System.Management.Automation.PSCredential -ArgumentList $appid, $k
        #        $partneraccesstoken = New-PartnerAccessToken -RefreshToken $token -Resource "" -Credential $app -TenantId $tid
        $partneraccesstoken = New-PartnerAccessToken -RefreshToken $token -Credential $app -Tenant $tid -Scopes '' -ServicePrincipal -ApplicationId $appid  #  -Resource ""
        #$connected = Connect-PartnerCenter -AccessToken $partneraccesstoken.AccessToken -ApplicationId $app.username -TenantId $tid -Environment AzureCloud -ServicePrincipal
        $connected = Connect-PartnerCenter -ApplicationId $appid -Credential $app -RefreshToken $token
        if (-not $connected) { throw "Error connecting to partnercenter.." }
        update-storedcredential -user cspuser -secret ($partneraccesstoken.RefreshToken | ConvertTo-SecureString -AsPlainText -Force)

        $SQLInstance = "localhost\SQLExpress"
        $SQLDatabase = "Microsoft365"
        #  $SQLUsername = ""
        #  $SQLPassword = ""

        $today = Get-date
        $dayofyear = "$($today.Year)-$($today.DayOfYear)"
        if (-not $kunde) {
            try {
                $customers = Get-PartnerCustomer
            catch { Write-Host "Error getting tenants from partnercenter." }
        else {

            $customers = Get-PartnerCustomer | Where-Object { $_.domain -eq $kunde }

        # Add users to DB. (not #ext# or contacts)
        # $customer=$customers[9]
        foreach ($customer in $customers ) {
            Write-Host "Processing tenant: $($customer.Name) ($($customer.domain))"
            # Add Sku to Skus table
            $customerskus = Get-PartnerCustomerSubscribedSku -CustomerId $customer.CustomerId
            if ($customerskus) {
                foreach ($sku in $customerskus) {
                    try {
                        $sqlsku = "insert into skus (SkuID,SkuPartNumber,ProductName) values ('$($sku.SkuID)','$($sku.SkuPartNumber)','$($sku.ProductName)')"
                        invoke-sqlcmd -query $sqlsku -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword
                        Write-Output "SkuID ($($sku.ProductName)) added to database"
                    catch {
                        $sqlsku = "insert into skus (SkuID,SkuPartNumber,ProductName) values ()"
                        #  Write-Output "SkuID ($($sku.ProductName)) already exists in database"
                    # Add licensed2licensedskus table
                    $sqlls = "select * from licensedskus where DayOfYear='$($dayofyear)' and tenantid='$($customer.CustomerId)' and skuid='$($sku.SkuID)'"
                    $ls = invoke-sqlcmd -query $sqlls -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword
                    if (-not $ls ) {
                        # Not registered for today -> insert
                        $sqlls = "insert into licensedskus (DayOfYear,Date,TenantID,SkuID,AvailableUnits,ActiveUnits,CapabilityStatus,ConsumedUnits,LicenseGroupId,SuspendedUnits,TargetType,TotalUnits,WarningUnits) values ('$($dayofyear)','$($today)','$($customer.CustomerId)','$($sku.SkuID)','$($sku.AvailableUnits)','$($sku.ActiveUnits)','$($sku.CapabilityStatus)','$($sku.ConsumedUnits)','$($sku.LicenseGroupId)','$($sku.SuspendedUnits)','$($sku.TargetType)','$($sku.TotalUnits)','$($sku.WarningUnits)')"  #.ToUniversalTime()
                        $ls = invoke-sqlcmd -query $sqlls -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword
                    else {
                        if ($force) {
                            $sqlls = "update licensedskus set DayOfYear='$($dayofyear)',Date='$($today)',TenantID='$($customer.CustomerId)',SkuID='$($sku.SkuID)',AvailableUnits='$($sku.AvailableUnits)',ActiveUnits='$($sku.ActiveUnits)',CapabilityStatus='$($sku.CapabilityStatus)',ConsumedUnits='$($sku.ConsumedUnits)',LicenseGroupId='$($sku.LicenseGroupId)',SuspendedUnits='$($sku.SuspendedUnits)',TargetType='$($sku.TargetType)',TotalUnits='$($sku.TotalUnits)',WarningUnits='$($sku.WarningUnits)' where DayOfYear='$($dayofyear)' and tenantid='$($customer.CustomerId)' and skuid='$($sku.SkuID)'"
                            $ls = invoke-sqlcmd -query $sqlls -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorAction Stop # -Username $SQLUsername -Password $SQLPassword    
                            Write-output " Updating sku ($($sku.Productname))"


This PS script inserts data into “skus” table and “licensedskus” table. Here are the sql management studio scripts to create those.
“SKSU” tabler

USE [Microsoft365]

/****** Object:  Table [dbo].[Skus]    Script Date: 17.01.2021 21:34:11 ******/


CREATE TABLE [dbo].[Skus](
    [SkuID] [varchar](255) NOT NULL,
    [SkuPartNumber] [varchar](255) NULL,
    [ProductName] [varchar](255) NULL,
    [FriendlyName] [varchar](255) NULL,
    [SkuID] ASC

And lucky you here are the sql MS to create the othe table:

USE [Microsoft365]

/****** Object:  Table [dbo].[LicensedSkus]    Script Date: 17.01.2021 21:38:41 ******/


CREATE TABLE [dbo].[LicensedSkus](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DayOfYear] [varchar](10) NULL,
    [Date] [datetime] NULL,
    [TenantID] [varchar](50) NULL,
    [SkuID] [varchar](50) NULL,
    [AvailableUnits] [int] NULL,
    [ActiveUnits] [int] NULL,
    [CapabilityStatus] [varchar](50) NULL,
    [ConsumedUnits] [int] NULL,
    [LicenseGroupId] [varchar](50) NULL,
    [SuspendedUnits] [int] NULL,
    [TargetType] [varchar](50) NULL,
    [TotalUnits] [int] NULL,
    [WarningUnits] [int] NULL,
    [ID] ASC

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.


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

#$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 &amp;gt;=@start"


$resulttable=New-Object System.Data.DataTable
$resulttable | Out-GridView

Use Powershell to get LeakedCredentials from Azure using Graph

Leaked credentials listed from Azure using powershell and Microsoft Graph 
We 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 a scheduled task running to get this reports. Using a appilcation in Azure. All credentials are stored in SecretServer. First we need an Application Registration in Azure.

Application Registration list

The registered application. The home page URL can be any url, it is not used.

After we have created the AppReg. Add a password, app key. Combined with the application id this is our username and password.

Now it is time to give this app the required permissions from microsoft we can identify witch permissions are needed to run this query. 

Permission required.
Some of the permissions set in Azure.
Remeber to click “Grant Permissions” after they are added.

Next would be to set the enterprise application to “user assignment required” and “Enabled for users to sign-in.” also “Hide it from users.

Settings of the Enterprise application.

Now we are ready to start with our powershell script.

$l_ClientID ="&lt;APPID&gt;"
$l_ClientSecret="&lt;APP password Key&gt;"
    $body= @{grant_type="client_credentials";
$oauth=Invoke-RestMethod -Method Post -Uri $loginURL/$l_tenantdomain/oauth2/token?api-version=1.0 -Body $body
if ($oauth.access_token -ne $null)
      $headerParams = @{'Authorization'="$($oauth.token_type) $($oauth.access_token)"
$url = ""
$myReport = (Invoke-WebRequest -UseBasicParsing -Headers $headerParams -Uri $url)
} else {
Write-Host "ERROR: No Access Token"
($myReport.Content | ConvertFrom-Json).value |where-object {$_.riskeventstatus -eq "active"} | ft risk&lt;em&gt;,user

SfB : Server startup is being delayed because fabric pool manager is initializing.

Ran into a issue where Skype for  Business frontend service refused to start. It remained in starting for ages before giving up. In the event viewer the statement was : Server startup is being delayed because fabric pool manager is initializing. This event seemed to have something to do regarding pool, but this was a standardedition Skype for Business setup containing one frontend and one edge server.

Server startup is being delayed because fabric pool manager is initializing.

Many articles on Bing and Google explained how this could be a issue with the certificates on the server, but in our case the frontend server and edge server was happily replicating the topology. We started by trying to do as the event told us: 

Reset-CsPoolRegistrarState -poolfqdn &lt;ourpool> -ResetType QuorumLossRecovery

But this also failed. For me it looked like there was something wrong with WindowsFabric. Compared with another SfB server and in taskmanager I could see fabric.exe running, but not on on the server with the issue.  Looking in eventviewer Microsoft/WindowsFabric Admin:

Windows Fabric Admin log

At first I tried to install Windows Fabric from SfB install media. But same error. Then we tried to uninstall and reinstall. This resulted in a more serious error. Now the server has lost its connections to the Fabric. So how do we fix this. My solution was to uninstall SfB frontend server module and then run the Deployment wizard to reinstall it with config from the management store. This worked perfect. The front end service started immediately. 

Office 365 applications and high disk IO

After we installed office 365 on our pc’s we discovered high disk IO, especially on our terminal servers. Running tools from sysinternals this turned out to be something in Office installation called Telemetry, When we started office apps some file, in the profile folder structure, called OTELE was constantly updated. Not one file, but several.Telemetry3

After som time of investigation we found one registry key that seem interesting “DisableTelemetry”. The obvious thing to do was to set this value to 1 (binary enabled). But that did not help at all. When we started Oulook the value was set to “0”. Searching the internet gave us the answer from Microsoft (second hand 🙂 ) That this could not be disabled. But after a support case : It would have taken us forever to find the value. The answer is 170000 , telemetry2

Set the value to 170000 and all disk IO to OTelemetry stopped. Now our servers are back to normal, only a subset of files are created.Telemetry1Thanks to Jan Ove Aarnes for his findings.