Category Archives: Uncategorized

List NordPool electric prices using powershell.

This is a small script using nordpoolgroup api to get todays electric prices for Kr.sand – Norway. This is the same API as their webpage uses. All the urls and parameters can be found using developermode in the browser. This code is only to demonstrate that it can be done. It all started when i first saw the Homeassistant integration on github .https://github.com/custom-components/nordpool

$today=Get-Date -Format "d-M-yyyy"
$url="https://www.nordpoolgroup.com/api/marketdata/page/23" #23 = Hourly ,24 = Daily, 25 = Weekly, 26 = Monthly, 27 = Yearly

$url+="?currency=,NOK,NOK,EUR&endDate=$today"
$response=Invoke-RestMethod -Uri $url
$prices=@()
foreach ($row in $response.data.rows){ 
    $localrow= $row.Columns | Where-Object {($_.name -like "Kr.sand")} 
 if($localrow.IsValid){
  [float]$cost=((($localrow[0].value).tostring()).Replace(' ','')).replace(',','.')
  [datetime]$datetime=$row.StartTime
    Write-Output "$($datetime) - øre/kWh ink mva $([math]::round(($cost/10 * 1.25),2))"
    $prices+=[math]::round(($cost * 1.25),2)
    }
}

$prices|Measure-Object -Average -Maximum -Minimum

Import user licenses from CSP using CSP api and graph.

Hi,there.
This is also quiet simple. I want to get all users and what licenses they have been assigned. All this should do, is retrieve information using a csp token.
After some attempts this is my best one. The “goodlooking” argument will not work when run from ISE.
I’am still using the same database. The table is called usersextended and you will be given the sql command to create it in a while.
This function basically fetch a user and all the attributes from M365, licenses skus are joined to a csv and all are added to as a record in the table.
PLEASE remember I am so lacy so almost all fields are varchar.


function update-tenantusersindb {

[CmdletBinding()]
param(
[switch]$includeexternal,
[switch]$goodlooking
)
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] where active='1'"
$customers = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
$ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($Customers | Select-Object -ExpandProperty tenantname)

$AttributeCollection.Add($ValidateSetAttribute)

# 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]
if ( -not $kunde) {
$filter = ''
}
else {
$filter = '?Size=0&filter={"Field":"Domain","Value":"' + $($kunde) + '","Operator":"starts_with"}'
}
}
process {
if ($goodlooking) {
Clear-Host
$ui = (get-host).ui
$rui = $ui.RawUI
$xx = [math]::round(($rui.MaxWindowSize.Width)/2)

}
$tid = ""
$appid = ""
$SQLInstance = "localhost\SQLExpress"
$SQLDatabase = "Microsoft365"
$SQLUsername = ""
$SQLPassword = ""
$tokentime = get-date
$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 -Credential $app -Tenant $tid -Scopes 'https://api.partnercenter.microsoft.com/user_impersonation' -ServicePrincipal -ApplicationId $appid # -Resource "https://api.partnercenter.microsoft.com"
if ($partneraccesstoken) {
Write-Output "Got accesstoken"
update-storedcredential -user cspuser -secret ($partneraccesstoken.RefreshToken | ConvertTo-SecureString -AsPlainText -Force)
$tokentime = get-date
}
else { throw "Error getting accesstoken" }
$baseurl = "https://api.partnercenter.microsoft.com/"
$apiversion = "v1"
$endpoint = "/customers"
$url = $baseurl + $apiversion + $endpoint + $filter
$tenants = Invoke-RestMethod -Headers @{Authorization = "Bearer $($partneraccesstoken.AccessToken)" } -Uri $url -Method Get

$cb = $tenants.indexof('{') # Find first occurance of '{'
$tenants = $tenants.Substring($cb) | convertfrom-json # Trim start, remove garble and convert from json
$filter = ''
foreach ($tenant in $tenants.items) {

$users = $()
$customerTenant = $tenant.id #$customer.customerid
$customerAccessTokenUri = "https://login.windows.net/$customerTenant/oauth2/token"

$params = @{
resource = "https://graph.microsoft.com";
grant_type = "refresh_token";
client_secret = $app.GetNetworkCredential().password;
client_id = $appid;
scope = "openid";
refresh_token = $token
}
$graphAccess = Invoke-RestMethod -Uri $customerAccessTokenUri -Method POST -Body $params
$url = "https://graph.microsoft.com/beta/users"
$result = $null
$users = $null
do {
try {
$result = Invoke-RestMethod -Uri $url -Headers @{Authorization = "Bearer " + $graphAccess.access_token }
$url = $result.'@odata.nextLink'
$users += $result.value
}
catch {
$url = $null
$users = $null
}
} while ($url)

$totalusers = ($users | Where-Object { $_.userprincipalname -notlike "*#EXT#*" }).count
if ($includeexternal) { $totalusers += ($users | Where-Object { $_.userprincipalname -like "*#EXT#*" }).count }
$usersdone = 0
$totaltime = 0
if ($goodlooking) { [console]::SetCursorPosition(0, 1) }
if ($goodlooking) {
Write-Host "Tenant : " -NoNewline
Write-host -ForegroundColor Yellow "$($tenant.companyprofile.Domain),$($tenant.companyprofile.companyName),$($tenant.id) - User count : $($totalusers)"
}
else {
Write-Host "Tenant : $($tenant.companyprofile.Domain),$($tenant.companyprofile.companyName),$($tenant.id) - User count : $($totalusers) "
}#set users as inactive in database.
Invoke-Sqlcmd -Query "update usersextended set active='0' where active < '10' and tenantid like '$($tenant.id)'" -ServerInstance $SQLInstance -Database $SQLDatabase foreach ($user in $users <#.items #>) {
if ($user.userPrincipalName -like "*#EXT#*" -and (-not $includeexternal)) {
# Skip external user if not specified
# Write-host "Skipping external user : $($user.userPrincipalName)"
}
else {
$t = Measure-Command {
$assignedskus = $user.assignedlicenses | join-string -property skuid -separator ','
# Write-Output "$($user.displayname) - sku : $($assignedskus)"
if ($goodlooking) {
[console]::SetCursorPosition(0, 2)
write-host " "
[console]::SetCursorPosition(0, 2)
Write-host -ForegroundColor Green "Inserting user($($usersdone+1)):$($user.Displayname) " -NoNewline
}
else {
Write-host "Inserting user($($usersdone+1)):$($user.Displayname) " -NoNewline

}
$displayname = if ($user.displayname -ne $null) { $user.displayname.Replace("'", "''") }
$givenname = if ($user.givenname -ne $null) { $user.givenname.Replace("'", "''") }
$surname = if ($user.surname -ne $null) { $user.surname.Replace("'", "''") }
$userUPN = if ($user.userprincipalname -ne $null) { $user.userprincipalname.Replace("'", "''") }

switch ($user.accountenabled) {
'Active' { $userstate = 1 }
default { $userstate = 0 }
}

$insertsql = "insert into usersextended (tenantid,userid,displayname,userprincipalname,givenname,surname,skus,usagelocation,accountEnabled,active,city,companyname,streetaddress,country) values ('$($tenant.id)','$($user.ID)','$($DisplayName)','$($userUPN)','$($givenname)','$($surname)','$($assignedskus)','$($user.usagelocation)','$($userstate)','1','$($user.city)','$($user.companyName)','$($user.streetAddress)','$($user.country)')"
$er = Invoke-Sqlcmd -Query $insertsql -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorVariable ierr -ErrorAction SilentlyContinue
#Write-Host "$($ierr)"
if ($ierr) {
# User exists -> update.
if ($goodlooking) {
Write-host -ForegroundColor DarkGreen "User exists.... updating. " -NoNewline
}
else {
Write-host "User exists.... updating. " -NoNewline
}
$updatesql = "update usersextended set skus='$($assignedskus)',usagelocation='$($user.usagelocation)',tenantid='$($tenant.id)',active='1',companyname='$($user.companyName)',city='$($user.city)',streetaddress='$($user.streetAddress)',country='$($user.country)' where userid = '$($user.id)'"
Invoke-Sqlcmd -Query $updatesql -ServerInstance $SQLInstance -Database $SQLDatabase -ErrorVariable updterr
if ($updterr) {
Write-host "$($updatesql)"
}
}
}
$usersdone++
$totaltime += $t
if ($goodlooking) {
[console]::SetCursorPosition(80, 2)
Write-Host -ForegroundColor Gray "Time left ~ $((($totaltime.totalSeconds/$usersdone)*($totalusers-$usersdone)).tostring("#.#")) seconds "
# Last user took:$(($t.totalseconds).tostring("#.#")) -
}
else {
Write-Host "Last user took:$(($t.totalseconds).tostring("#.#")) - Time left ~ $((($totaltime.totalSeconds/$usersdone)*($totalusers-$usersdone)).tostring("#.#")) seconds "
}
}
}
}
}
}

Here is the database script:

USE [Microsoft365]
GO

/****** Object: Table [dbo].[usersextended]
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[usersextended](
[tenantid] [varchar](255) NOT NULL,
[userid] [varchar](255) NOT NULL,
[accountEnabled] [int] NULL,
[ageGroup] [varchar](255) NULL,
[assignedlicenses] [varchar](255) NULL,
[assignedplans] [varchar](255) NULL,
[businessPhones] [varchar](255) NULL,
[city] [varchar](255) NULL,
[companyName] [varchar](255) NULL,
[consentProviderForMinor] [varchar](255) NULL,
[country] [varchar](255) NULL,
[createddatetime] [datetime] NULL,
[creationtype] [varchar](255) NULL,
[deleteddatetime] [datetime] NULL,
[department] [varchar](255) NULL,
[devicekeys] [varchar](255) NULL,
[displayname] [varchar](255) NULL,
[employeehiredate] [datetime] NULL,
[employeeid] [varchar](255) NULL,
[employeeorgdata] [varchar](255) NULL,
[employeetype] [varchar](255) NULL,
[externaluserstate] [varchar](255) NULL,
[externaluserstatechangedatetime] [datetime] NULL,
[faxnumber] [varchar](255) NULL,
[givenname] [varchar](255) NULL,
[id] [varchar](255) NULL,
[identities] [varchar](255) NULL,
[imaddresses] [varchar](255) NULL,
[infocatalogs] [varchar](255) NULL,
[ismanagementrestricted] [varchar](255) NULL,
[isresourceaccount] [varchar](255) NULL,
[jobtitle] [varchar](255) NULL,
[legalagegroupclassification] [varchar](255) NULL,
[mail] [varchar](255) NULL,
[mailnickname] [varchar](255) NULL,
[mobilephone] [varchar](255) NULL,
[officelocation] [varchar](255) NULL,
[onpremisesDistinguishedname] [varchar](255) NULL,
[onpremisesedomainname] [varchar](255) NULL,
[onpremisesextensionattributes] [varchar](255) NULL,
[onpremisesimmutableid] [varchar](255) NULL,
[onpremiseslastsyncdatetime] [datetime] NULL,
[onpremisesprovisioningerrors] [varchar](255) NULL,
[onpremisessamaccountname] [varchar](255) NULL,
[onpremisessecurityidentifier] [varchar](255) NULL,
[onpremisessyncenabled] [int] NULL,
[onpremisesuserprincipalname] [varchar](255) NULL,
[othermails] [varchar](255) NULL,
[passwordpolicies] [varchar](255) NULL,
[passwordprofile] [varchar](255) NULL,
[postalcode] [varchar](255) NULL,
[preferreddatalocation] [varchar](255) NULL,
[preferredlanguage] [varchar](255) NULL,
[provisionedplans] [varchar](255) NULL,
[proxyaddresses] [varchar](255) NULL,
[refreshtokenvalidfromdaterime] [datetime] NULL,
[showinaddresslist] [varchar](255) NULL,
[signinsessionsvalidfromdatetime] [datetime] NULL,
[state] [varchar](255) NULL,
[streetaddress] [varchar](255) NULL,
[surname] [varchar](255) NULL,
[usagelocation] [varchar](255) NULL,
[userprincipalname] [varchar](255) NULL,
[usertype] [varchar](255) NULL,
[skus] [varchar](512) NULL,
[active] [int] NULL,
CONSTRAINT [PK_usersextended] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Use get-tenantlicsindb after syncing them to our local db.

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.

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

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 {
    <#
  .SYNOPSIS
  Update tenant subscriptions/licenses from Cegal CSP
  .DESCRIPTION
  Update licenses from tenant.
  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 force
  Will force update of skus in database. Normaly updated once a day.
 
  .EXAMPLE
  update-tenantlicsindb -tenant 'Tenant name' -force

  .EXAMPLE
  update-tenantlicsindb

 #>
    [CmdletBinding()]
    param(
        [switch]$force,
        [switch]$updateusers
    )
    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)

        # 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 "https://api.partnercenter.microsoft.com" -Credential $app -TenantId $tid
        $partneraccesstoken = New-PartnerAccessToken -RefreshToken $token -Credential $app -Tenant $tid -Scopes 'https://api.partnercenter.microsoft.com/user_impersonation' -ServicePrincipal -ApplicationId $appid  #  -Resource "https://api.partnercenter.microsoft.com"
        $partneraccesstoken
        #$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 ) {
            #$customer=$customers[2]
            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]
GO

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

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Skus](
    [SkuID] [varchar](255) NOT NULL,
    [SkuPartNumber] [varchar](255) NULL,
    [ProductName] [varchar](255) NULL,
    [FriendlyName] [varchar](255) NULL,
 CONSTRAINT [PK_Skus] PRIMARY KEY CLUSTERED
(
    [SkuID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

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

USE [Microsoft365]
GO

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

SET QUOTED_IDENTIFIER ON
GO

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,
 CONSTRAINT [PK_LicensedSkus] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

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

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

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

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. https://developer.microsoft.com/en-us/graph/docs/api-reference/beta/api/leakedcredentialsriskevent_get 

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.

$loginURL="https://login.microsoft.com"
$resource="https://graph.microsoft.com"
$l_tenantdomain="&lt;domain&gt;.onmicrosoft.com"
$l_ClientID ="&lt;APPID&gt;"
$l_ClientSecret="&lt;APP password Key&gt;"
    $body= @{grant_type="client_credentials";
    resource=$resource;
    client_id=$l_ClientID;
    client_secret=$l_ClientSecret
}
$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)"
      }
 # https://developer.microsoft.com/en-us/graph/docs/api-reference/beta/api/leakedcredentialsriskevent_get
$url = "https://graph.microsoft.com/beta/leakedCredentialsRiskEvents"
$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.

Exchange server quarantined a mailbox.

exchangeOne user tried to run an attachment in a newly received email  . After this his mailbox became inaccessible. When he tried to access it using OWA the familiar message appeared : Something went wrong.

mailboxerror

My first thought was that the database was dismounted, but other mailboxes on the same database still worked. What’s next? Continue reading Exchange server quarantined a mailbox.