All posts by Atle

My ‘simple’ keg scale

Since Plaato is retiring their Keg scale in 2024/25 I had to find a new solution. Searching the web lead me to GitHub – Callwater/Beerkeg-load-cell. Since I had some ESP and loadcells at my hands it was very quick to setup. Had some wood and a router in my ‘workshop’ so made the base in a short time. Soldering all parts together and gluing them to the base.

The basic hardware and software was quickly up and running.

Added a MQTT server to my HomeAssistant instance to prepare for some data presentation. Since there already was a mqtt client library ready for node-mcu – esp8266 it was nearly a walk in the park.

Code was added using Visual Studio Code.

File : main.cpp

#include <Arduino.h>
#include <DHT.h>
#include <DHT_U.h>
#include <HX711.h>
#include <ESP8266WiFi.h>
#include <PubSubClient.h>
#include <SPI.h>
#include <Wire.h>
#include <Adafruit_GFX.h>
#include <Adafruit_SSD1306.h>

#define SCREEN_WIDTH 128 // OLED display width, in pixels
#define SCREEN_HEIGHT 64 // OLED display height, in pixels

#include "config.h"
#define OLED_RESET -1       // Reset pin # (or -1 if sharing Arduino reset pin)
#define SCREEN_ADDRESS 0x3c ///< See datasheet for Address; 0x3D for 128x64, 0x3C for 128x32
Adafruit_SSD1306 display(SCREEN_WIDTH, SCREEN_HEIGHT, &Wire, OLED_RESET);

DHT dht{DHT_PIN, DHT_TYPE};      // Initiate DHT library
HX711 scale;                     // Initiate HX711 library
WiFiClient wifiClient;           // Initiate WiFi library
PubSubClient client(wifiClient); // Initiate PubSubClient library
int publishcount = 10;           // Do 10 mqtt publishes - then force reconnect
bool displayconnected = true;
float prevmeter = 1000.0;
int screentimeout = 10;
void reconnect();
void callback(char *topic, byte *payload, unsigned int length);
void drawText(float temperature, float volume, float humidity, String Message);
void drawMessage(String message);
void screenoff();
void setup()
{
  Serial.begin(74880);
  Serial.println();
  WiFi.mode(WIFI_STA);
  WiFi.begin(SSID, PASSWORD);
  Serial.print("Connecting...");
  while (WiFi.status() != WL_CONNECTED)
  { // Wait till Wifi connected
    delay(500);
    Serial.print(".");
  }
  Serial.println();

  Serial.print("Connected.IP address:");
  Serial.println(WiFi.localIP()); // Print IP address
  delay(2000);
  // SSD1306_SWITCHCAPVCC = generate display voltage from 3.3V internally
  if (!display.begin(SSD1306_SWITCHCAPVCC, SCREEN_ADDRESS))
  {
    Serial.println(F("SSD1306 allocation failed"));
    // for(;;); // Don't proceed, loop forever
    displayconnected = false;
  }
  else
  {
    Serial.println(F("SSD1306 allocation success"));
    displayconnected = true;
    display.clearDisplay();
    display.display();
  };

  if (displayconnected)
  {
    drawMessage("Connected IP address:");
    delay(2000);
    drawMessage(WiFi.localIP().toString());
    delay(2000);
  }
  client.setServer(MQTT_SERVER, 1883);              // Set MQTT server and port number
  client.setCallback(callback);                     // Set callback address, this is used for remote tare
  scale.begin(LOADCELL_DOUT_PIN, LOADCELL_SCK_PIN); // Start scale on specified pins
  scale.wait_ready();                               // Ensure scale is ready, this is a blocking function
  scale.set_scale();
  Serial.println("Scale Set");
  scale.wait_ready();
  scale.tare(); // Tare scale on startup
  scale.wait_ready();
  Serial.println("Scale Zeroed");

  Serial.println("Start DHT library");
  dht.begin();
}

void loop()
{
  float reading;                       // Float for reading
  float raw;                           // Float for raw value which can be useful
  scale.wait_ready();                  // Wait till scale is ready, this is blocking if your hardware is not connected properly.
  scale.set_scale(calibration_factor); // Sets the calibration factor.
  scale.set_offset(offset_factor);     // Sets offset for empty keg
  if (publishcount < 1)
  {
    // reconnect();
    client.disconnect();
    publishcount = 10;
    // Serial.println("Scheduled reconnect.");
  }
  publishcount--;

  // Ensure we are still connected to MQTT Topics
  if (!client.connected())
  {
    reconnect();
  }

  Serial.print("Reading: "); // Prints weight readings in .2 decimal kg units.
  scale.wait_ready();
  reading = scale.get_units(10); // Read scale in g/Kg
  raw = scale.read_average(5);   // Read raw value from scale too
  Serial.print(reading, 1);
  Serial.println(" L");
  Serial.print("Raw: ");
  Serial.println(raw);
  Serial.print("Calibration factor: "); // Prints calibration factor.
  Serial.println(calibration_factor);

  if (reading < 0)
  {
    reading = 0.00; // Sets reading to 0 if it is a negative value, sometimes loadcells will drift into slightly negative values
  }

  String value_str = String(reading);
  String value_raw_str = String(raw);
  client.publish(STATE_TOPIC, value_str.c_str());         // Publish weight to the STATE topic
  client.publish(STATE_RAW_TOPIC, value_raw_str.c_str()); // Publish raw value to the RAW topic

  client.loop();      // MQTT task loop
  scale.power_down(); // Puts the scale to sleep mode for 3 seconds. I had issues getting readings if I did not do this
  delay(3000);
  scale.power_up();

  // Reading values from the DHT sensor
  float humidity = dht.readHumidity();       // Read humidity
  float temperature = dht.readTemperature(); // Read temperature

  // Check if we recieved a number from dht libraray
  if (isnan(humidity) || isnan(temperature))
  {
    Serial.println("Error while parse values to numbers from dht");
    drawMessage("Errir reading DHT");
    delay(2000);
  }

  client.publish(TEMPERATURE_TOPIC, String(temperature).c_str()); // Publish temperature to the temperature value topic
  client.publish(HUMIDITY_TOPIC, String(humidity).c_str());       // Publish humidity to the humidity value topic
  Serial.print("Temp:");
  Serial.println(temperature);
  Serial.print("Displayconnected:");
  Serial.println(displayconnected);
  Serial.print("Prev:");
  Serial.println(prevmeter);
  Serial.print("Reading2:");
  Serial.println(reading);
    Serial.print("Screentimeout:");
    Serial.println(screentimeout);

  if (displayconnected)
  {
    if ((reading < (prevmeter - 0.1)) || (reading > (prevmeter + 0.1)) ||(prevmeter == 1000))
    {
      prevmeter = reading;
      screentimeout = 10;
    }
          screentimeout--;
      if (screentimeout < 1)
      {
        screentimeout = 1;
        screenoff();
      }
    Serial.print("Minvalue:");
    Serial.println((prevmeter - 0.1));
   
    if (screentimeout > 1)
    {
      Serial.println("Print to display");
      drawText(temperature, reading, humidity, "beer remaining !!!");
    }
  }
}

void reconnect()
{
  while (!client.connected())
  { // Loop until connected to MQTT server
    Serial.print("Attempting MQTT connection...");
    if (client.connect(HOSTNAME, mqtt_username, mqtt_password))
    { // Connect to MQTT server
      Serial.println("connected");
      client.publish(AVAILABILITY_TOPIC, "online"); // Once connected, publish online to the availability topic
      client.subscribe(TARE_TOPIC);                 // Subscribe to tare topic for remote tare
      client.subscribe(OFFSET_TOPIC);
    }
    else
    {
      Serial.print("failed, rc=");
      Serial.print(client.state());
      Serial.println(" try again in 5 seconds");
      delay(5000); // Will attempt connection again in 5 seconds
    }
  }
}

void callback(char *topic, byte *payload, unsigned int length)
{
  if (strcmp(topic, TARE_TOPIC) == 0)
  {
    Serial.println("starting tare...");
    scale.wait_ready();
    scale.set_scale();
    scale.tare(); // Reset scale to zero
    Serial.println("Scale reset to zero");
  }
  if (strcmp(topic, OFFSET_TOPIC) == 0)
  {
    Serial.println("Setting offset...");
    scale.wait_ready();
    scale.set_scale();
    scale.set_offset(offset_factor);
    Serial.print("Scale set to offset ");
    Serial.println(*payload);
  }
}
void drawText(float temperature, float volume, float humidity, String message)
{
  display.clearDisplay();
  display.setCursor(0, 0); // Start at top-left corner
  display.setTextSize(1);  // Draw 2X-scale text
  display.setTextColor(SSD1306_WHITE);
  display.print("T: ");
  display.print(temperature);
  display.print((char)247);
  display.println(F("C"));
  display.print("H: ");
  display.print(humidity);
  display.println(F("%"));
  display.setTextSize(3); // Draw 2X-scale text
  display.print(volume);
  display.println(F("L"));
  display.setTextSize(1); // Draw 2X-scale text
  display.println(message);

  display.display();
  // delay(2000);
}
void drawMessage(String message)
{
  display.clearDisplay();
  display.setCursor(0, 0); // Start at top-left corner
  display.setTextSize(1);  // Draw 2X-scale text
  display.setTextColor(SSD1306_WHITE);
  display.println(message);
  display.display();
  // delay(2000);
}

void screenoff()
{
  display.clearDisplay();
  display.display();
}

File : config.h

// Wifi Settings
#define SSID                          "SSID"
#define PASSWORD                      "WIFIPWD"

// MQTT Settings
#define HOSTNAME                      "HA hostname" //"beer_1"
#define MQTT_SERVER                   "192.168.10.132"
#define STATE_TOPIC                   "beer_1"
#define STATE_RAW_TOPIC               "beer_1/raw"
#define AVAILABILITY_TOPIC            "beer_1/available"
#define TARE_TOPIC                    "beer_1/tare"
#define OFFSET_TOPIC                  "beer_1/offset"
#define TEMPERATURE_TOPIC             "beer_1/temperature"
#define HUMIDITY_TOPIC                "beer_1/humidity"
#define mqtt_username                 "mqttusername"
#define mqtt_password                 "mqttpassword"

// HX711 Pins
const int LOADCELL_DOUT_PIN = 12;  // Remember these are ESP GPIO pins, they are not the physical pins on the board.
const int LOADCELL_SCK_PIN = 13;
int calibration_factor = -22500;  // Defines calibration factor we'll use for calibrating.
int offset_factor = -285746;       // Defines offset factor ; -285746 = Empty Cornelius 19L keg. 

// DHT Settings
constexpr auto DHT_PIN = 14;            // Remember these are ESP GPIO pins, they are not the physical pins on the board. 
#define DHT_TYPE DHT11                 // DHT11 or DHT22
constexpr auto sendDHTDataDelay = 500ul; // Delay between sending data over MQTT

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

Windows 11 update error 0x80070002

Have been struggling with this error for some time. I was stuck with some old build in the developer channel. It was the CU updates that failed, almost all others installed ok. Hoping that a new build would manage to install, but no. I decided to finally to a inplace-upgrade to latest build, Downloaded the ISO and mounted it in explorer. Ran setup.exe. And…. after some time FAILURE and rollback. When I finally could log in to windows a message box stating:

0x80070002 – 0x20007
The installation failed in the SAFE_OS phase with en error during INSTALL_DRIVERS operation

Not much more helpful, but it state that it was a driver install error. This error code was not mentioned in the upgrade error list from Microsoft : Get help with Windows upgrade and installation errors (microsoft.com).But now I could have a look at windows install logs. Log files and resolving upgrade errors – Windows Deployment | Microsoft Docs and det log to look in is “setupact.log” (a rather large file , 74mb in my case). A few lines above the bottom there was an entry : “2021-12-03 09:26:46, Error DISM API: PID=1480 TID=1804 Driver package C:\Windows\System32\DriverStore\FileRepository\realtekapo.inf_amd64_bdd2377ee0ec29f4\realtekapo.inf failed to install.”

This reminded me that I did some messing with the realtek audio drivers some time ago, at about the same time that windows update started to fail.

Delete the whole folder (you have to take ownership and set permissions). And after this the updates have been installing without any issue.

SIlly powershell TXT Fractal animation

Found some c# code drawing a Mandelbrot fractal (I’am so sorry I cant remember where or who) and thought that would be fun to do in powershell. Ended up doing a little animation of the fractal. All done in ASCII. This will not work if you run it in ISE or VS Code terminal.

param(
    [switch]$animate = $false
)
$buffer0 = ""
$MAXCOUNT = 30 
$ui = (get-host).ui
$rui = $ui.rawui

function fractal([float] $left, [float] $top, [float] $xside, [float] $yside, [float]$zoom) { 
    [float]$maxx = $rui.MaxWindowSize.Width
    [float]$maxy = $rui.MaxWindowSize.Height
    [float]$xscale = $xside / $maxx 
    [float]$yscale = $yside / $maxy 
    for ([int]$y = 1; $y -le ($maxy - 1); $y++) { 
        for ([int]$x = 1; $x -le ($maxx - 1); $x++) { 
            [float]$cx = $x * $xscale + $left; 
            [float]$cy = $y * $yscale + $top; 
            [float]$zx = 0; 
            [float]$zy = 0; 
            [int]  $count = 0; 
            while (($zx * $zx + $zy * $zy -lt 4) -and ($count -lt $MAXCOUNT)) { 
                [float]$tempx = $zx * $zx - $zy * $zy + $cx; 
                $zy = $zoom * $zx * $zy + $cy; 
                $zx = $tempx; 
                $count = $count + 1; 
            } 
            $t = $count + 65
            $char = [char]$t
            $global:buffer[$y * $maxx + $x] = $char
        } 
    } 
}

[float] $left = -1.75 
[float] $top = -0.25 
[float] $xside = 0.25 
[float] $yside = 0.45 
1..($rui.MaxWindowSize.Width * $rui.MaxWindowSize.Height) | ForEach-Object { $buffer0 += "#" }
$global:buffer = $buffer0.ToCharArray()
[float]$loop = 5.0
while ($loop -gt 1.0 ) {
    fractal -left $left -top $top -xside $xside -yside $yside -zoom $loop
    [console]::SetCursorPosition(0,0) ;
    [string]$drawscreen = New-Object system.string($global:buffer, 0, $global:buffer.Length)
    [console]::SetCursorPosition(0, 0)
    Write-Host $drawscreen
    # $left, $top, $xside, $yside) 
    $loop -= 0.05
    if (-not $animate) { $loop = -100.0 }
}

PS 2.x convertfrom-json

Powershell 2 does not have a convertfrom-json function and I like to use json files for storing configuration. Searching the web gives almost zero results. Converting a multilevel json structure to hashtable would be very complicated. Since my config files are only one level the easiest way of doing it was using regex. Input is json structure and output is an hashtable.

function convertfrom-json-onelevel {
    Param (
        [string[]] $json
    )

    $hashtable = @{ }
    $t = $json | Select-String -Pattern '(["])(?:(?=(\\?))\2.)*?\1' -AllMatches
    $hashtable = @{ }
    (0..((($t.matches).count - 1) / 2)) | % {
        $key = [regex]::Unescape($t.Matches[$_ * 2].Value)
        $key = $key.TrimEnd('"')
        $key = $key.TrimStart('"')   
        $value = [regex]::Unescape($t.Matches[$_ * 2 + 1].Value)
        $value = $value.TrimEnd('"')
        $value = $value.TrimStart('"')   
        $hashtable.add($key, $value)
    }
    return $hashtable
}

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.

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

Get-tenantlistindb (from previous post)

Now that we have our tenants listed in a database table, listing them is quite easy.
Added a switch that allow you to also list deleted/removed tenants.

function get-tenantlistindb {
    param(
        [switch]$all
    )
    $SQLInstance = "localhost\SQLExpress"
    $SQLDatabase = "Microsoft365"
    $sqlqr = "select * from [Microsoft365].[dbo].[tenants]"
    if (-not $all) {
        $sqlqr += " where active='1'"
    }
    $tenants = invoke-sqlcmd -query $sqlqr -ServerInstance $SQLInstance -Database $SQLDatabase
    $tenants
}

Log tenants from office 365 to local Db

Hi,
I like to keep control of how many licenses our cutomers use versus how many  have been purchased.
Here is 1st part  my PS script to copy the info from csp to the DB. I will start creating a database and table to keep a list of all the tenants in the csp. You will also need an service principal in the tenant.
To create a SPN in CSP check this :CSP access to tenants using powershell. Part 1
I am also using the storedcredential -> Get Secretserver secret

function Update-TenantListinDB {

# Set all customers in database where active is &lt; 10 to inactive
import-module partnercenter
$tid = "Partnercenter Tenant ID"
$appid = "AppID"
$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"
$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 = ""

$customers = Get-PartnerCustomer

$deactivated = Invoke-Sqlcmd -Query "update tenants set active='0' where active &lt; '10'" -ServerInstance $SQLInstance -Database $SQLDatabase
Write-Host "Parsing $(($customers).count) tenants."
foreach ($customer in $customers) {
# Check if exists
$select = "select * from tenants where tenantid like '$($customer.customerid)'"
$found = invoke-sqlcmd -query $select -ServerInstance $SQLInstance -Database $SQLDatabase
if ($found -eq $null) {
Write-Host "Inserting tenant:" $customer.Name
$SQLQuery1 = "insert into tenants (tenantid,tenantname,displayname,active) values ('$($customer.CustomerId)','$($customer.Domain)','$($customer.Name)','1')"
invoke-sqlcmd -query $SQLQuery1 -ServerInstance $SQLInstance -Database $SQLDatabase # -Username $SQLUsername -Password $SQLPassword
}
else {
Invoke-Sqlcmd -Query "update tenants set active='1' where tenantID like '$($customer.CustomerId)' and active &lt;'10'" -ServerInstance $SQLInstance -Database $SQLDatabase
}
}
}

I use this code to populat my database (using integrated authentication).
Sql for tabel (Database named Microsoft365):

USE [Microsoft365]
GO

/****** Object: Table [dbo].[tenants] Script Date: 03.01.2021 20:18:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tenants](
[tenantID] [VARCHAR](255) NOT NULL,
[tenantname] [VARCHAR](255) NOT NULL,
[displayname] [VARCHAR](255) NULL,
[active] [INT] NOT NULL,
CONSTRAINT [PK_tenants] PRIMARY KEY CLUSTERED
(
[tenantID] 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 2.0 Json to Hash

Resonse from webrequests are normally in Json format and converting back and forth is no problem using convertto/from-json. Only this time some computers did run Powershell 2.0 and did not have any “new” .NET framework installed. So I ran to a halt at this Json converting. Since my data did have predefined data structure I thought I could easily do this by some ‘foreach’ and string handling, but it ended up being to complex. Finally I turned my head to regex, this turned out to be the solution.

function convertfrom-json-onelevel {
Param (
[string[]] $json
)
$hashtable = @{ }
$t = $json | Select-String -Pattern '(["])(?:(?=(\\?))\2.)*?\1' -AllMatches
$hashtable = @{ }
(0..((($t.matches).count - 1) / 2)) | % {
$key = [regex]::Unescape($t.Matches[$_ * 2].Value)
$key = $key.TrimEnd('"')
$key = $key.TrimStart('"')
$value = [regex]::Unescape($t.Matches[$_ * 2 + 1].Value)
$value = $value.TrimEnd('"')
$value = $value.TrimStart('"')
$hashtable.add($key, $value)
}
return $hashtable
}

Hash tables in powershell

We all have the need to store data in some kind of arrays. I use hashtables a lot. Preferred use is as a lookup table, I can use ‘contains’ instead of looping through each item or reference an object by name instead of index number. Lookup table for licenses is where I use it the most at the time being. A table of license ID versus license name. One place for this is Microsoft licenses in partnercenter, and show licenses assigned to users. Using powershell to connect to Microsoft partnercenter to get all available skus by ID , name and displayname. When I have collected this – how do you ask?.
Here you go:

 Import-module partnercenter
 Connect-CompanyPartnerCenter
 
    $lic = @{ }
    $prodid = @{ }
    $customerstenants = Get-PartnerCustomer
    ForEach ($customer in $customerstenants) {
        $message = "Customer :$($customer.name)"
        write-verbose -Message "$message"
        $custskus = Get-PartnerCustomerSubscribedSku -CustomerId $customer.customerid
        foreach ($custsku in $custskus) {
            if ($lic.ContainsKey($custsku.SkuPartNumber)) {
               
            }
            else {
                $lic.Add($custsku.skupartnumber, $custsku.productname)
                $prodid.Add($custsku.SkuId, $custsku.ProductName)
            }

        }  
 }

Connect-companypartnercenter is a custom function. Here is how to connect to Office partnercenter.
The script above will (hopefully , no try-catch) end up in 2 hash tables. One “skuid” and productname:

"SkuID","DisplayName"
"WACONEDRIVEENTERPRISE","OneDrive for Business (Plan 2)"
"O365_BUSINESS_ESSENTIALS","Office 365 Business Essentials"
"MCOSTANDARD","Skype for Business Online (Plan 2)"
"Win10_E3_Local","Windows 10 Enterprise E3 (local only)"
"WACONEDRIVESTANDARD","OneDrive for Business (Plan 1)"

As long as I’m using this in the same script it is ok. Exporting it to files is not that straight forward, even tried ‘convertto-json’ –not working (convertfrom-json returns an array)
So I do this to crate a csv file. Creating correct columns.

       $csvobject = ($lic.GetEnumerator() | Select-Object @{expression = { $_.name }; label = "SkuID" }, @{expression = { $_.value }; label = "DisplayName" } | convertto-csv -Delimiter ',' -NoTypeInformation )
        $csvobject | Out-File -Encoding unicode $ExportSkuIDFile

        $csvobject = ($prodid.GetEnumerator() | Select-Object @{expression = { $_.name }; label = "ProductID" }, @{expression = { $_.value }; label = "DisplayName" } | convertto-csv -Delimiter ',' -NoTypeInformation )
        $csvobject | Out-File -Encoding unicode $ExportProductIDFile

Now that we have the file it would be great to be able to use them 🙂
And this is how it is imported in to a hashtable.

$filecontent=import-csv -Path $importfile -Encoding Unicode -Header "column1","column2"
$hashtable=@{}
$filecontent|ForEach-Object{
    write-verbose -message "$_.column1 , $_.column2"
    $hashtable.add($_.column1,$_.column2)
}

Read the file line by line and build our hash-table. Now we can use : $hashtable[“MCOSTANDARD”] , this will return the full name ‘Skype for Business Online (Plan 2)’
I have this as functions in my default module, the module I import in all my powershell sessions. (Surely you know about powershell profiles?)
Hope this helped.

SecretServer(Thycotic) plugin for powershell SecretsManagement module.

With the new powershell module SecretsManagement it is possible to add plugins. The new module is used to get/add/remove credentials. I created a plugin for secretserver to this module.

To get started you will have to install secretsmanagement:
Install-Module -Name Microsoft.Powershell.SecretsManagement -AllowPrerelease
If -AllowPrerelase is not an option you would have to update PowershellGet first. ( install-script powershellget )

After SecretManagement has been installed you can run :

add-secret -Name "Test" -secret (get-credential) -vault -builtinlocalvault 

, followed by

get-secret Test 

.

But I would like to have a uniform cmdlet for all my credentials. So I used Secrets Management Module Vault Extensions to get me started.

If you would like to install my module from powershell gallery run :

install-module -name secretsmanagement.secretserver

A bit more info for those interested

First created a new folder structure below C:\Program Files\WindowsPowerShell\Modules\
Secretsmanagement\0.0.3\SecretsManagementExtension
In the version folder “0.0.3” i added 2 files : Readme.txt (How to register vault) and SecretsManagement.SecretServer.psd1 ( Datafile for the module)

# Module manifest for module 'SecretsManagement.SecretServer'
# Generated by: Atle Vatland
# Generated on: 2/12/2020
@{
# Script module or binary module file associated with this manifest.
# RootModule = ''
# Version number of this module.
ModuleVersion = '0.0.3'
# Supported PSEditions
CompatiblePSEditions = @('Desktop')
# ID used to uniquely identify this module
GUID = 'e25aacec-637f-4935-bbd9-463a75ba46ea'
# Author of this module
Author = 'Atle Vatland'
# Copyright statement for this module
Copyright = '(c) 2020 Atle Vatland. All rights reserved.'
# Description of the functionality provided by this module
Description = 'Proof of concept for Secretserver( by Thycotic ) vault extension for powershell. Remove-secret is not implemented in this test.'
# Modules that must be imported into the global environment prior to importing this module
PowershellVersion = '5.1'
}

The subfolder has to be named “SecretsManagementExtension” also the scripts has to be named SecretsManagementExtension.psm1 and .psd1
What gave me some hassle was that the add-secret cmdlet actually calls set-secret in the extension.

SecretsManagementExtension.psd1:

@{
    ModuleVersion = '1.0'   
   RootModule = '.\SecretsManagementExtension.psm1'
   PowershellVersion = '5.1'
    FunctionsToExport = @('Get-Secret','Set-Secret','Remove-Secret','Get-SecretInfo')
    PrivateData = @{
    PSData = @{
        # A URL to the license for this module.
        LicenseUri = 'https://opensource.org/licenses/MIT'
   } # End of PSData hashtable
  } # End of PrivateData hashtable
}

Have not implemented remove-secret as we do not want to to delete any secrets.
SecretsManagementExtension.psm1:

# Licensed under the MIT License.

function Get-Secret
{
    param (
        [string]$Name,
        [hashtable]$AdditionalParameters
    )
        $where = $AdditionalParameters.secretserveruri
        $ws = New-WebServiceProxy -uri $where -Credential $AdditionalParameters.secretservercredential #-ErrorAction SilentlyContinue
        $wsResult = $ws.GetSecret([int]$name, $false, $null)
        [PSCredential]::new($wsResult.Secret.Items[1].value.ToString(), ($wsResult.Secret.Items[2].value.ToString()|ConvertTo-SecureString -AsPlainText -Force))       
}

function set-secret
{
    param (
        [string] $Name,
        [object] $Secret,
        [hashtable] $AdditionalParameters
    )
   
    $where =  $AdditionalParameters.secretserveruri
    $cred= $AdditionalParameters.secretservercredential
    $secretserverfolder=$AdditionalParameters.secretserverfolderid    
    if($secretserverfolder -eq $null){
        $secretserverfolder=44
    }
    $domain=$Name
    $templatename=$AdditionalParameters.secretservertemplate
    if($templatename -eq $null){
        $templatename= "Windows Account"
    }
        if($Secret -eq $null){
        throw "Secret can not be null."
    }
    elseif($secret -is [string]){
        throw "String is currently not supported"
    }
    elseif($secret -is [hashtable]){
        throw "String is currently not supported"
    }
    elseif ($secret -is [PSCredential]){
        $username = $Secret.username
        $password = $secret.GetNetworkCredential().password
    }    
    $ws = New-WebServiceProxy -uri $where -Credential $cred 
    # Get Template
    $template = $ws.GetSecretTemplates().SecretTemplates | Where {$_.Name -eq $templateName}
    # Set fields info
    $secretName = $domain + "-" + $UserName
    $secretItemFields = (($template.Fields | Where {$_.DisplayName -eq "Machine"}).Id, ($template.Fields | Where {$_.DisplayName -eq "Username"}).Id, ($template.Fields | Where {$_.DisplayName -eq "Password"}).Id, ($template.Fields | Where {$_.DisplayName -eq "Notes"}).Id)
    $secretItemValues=($domain,$UserName,$password, "")
    $folderId = $secretserverfolder
    # Add secret to secretserver.
    $addResult = $ws.AddSecret($template.Id, $secretName, $secretItemFields, $secretItemValues, $folderId)
    if($addResult.Errors.Count -gt 0){
        return $false
    }else{
        return $true
    }
}

function Remove-Secret
{
    param (
        [string] $Name,
        [hashtable] $AdditionalParameters
    )
    throw "Not implemented"
}

function Get-SecretInfo
{
    param(
        [string] $filter,
        [hashtable] $AdditionalParameters
    )
    if ([string]::IsNullOrEmpty($filter)) { $filter = "*" }
    $where =  $AdditionalParameters.secretserveruri
    $ws = New-WebServiceProxy -uri $where -Credential $AdditionalParameters.secretservercredential # -ErrorAction SilentlyContinue
    $hits=$ws.SearchSecrets($filter,$null,$null)
    $result=@()
    foreach($hit in $hits.SecretSummaries){
    $result+= ([pscustomobject] @{
        Name = $($hit.secretid.tostring())                                                                   
        Value  = $($hit.secretName.ToString())
        })
    }
    $result
}

To register this extension for module for SecretsManagement there are some required parameters.
Credentials used to access secretserver, secretserver web service url, default template and default folder.
Credential and parameters are stored encrypted in Credential Manager.

[pscredential]$c=Get-Credential  # Secretserver credential
 
 Register-SecretsVault -Name "VaultName" -ModuleName secretsmanagement.secretserver -VaultParameters @{
    secretservercredential=$c    # Account used to connect to secret server.
    secretserveruri="https://secretserver.domain.local/secretserver/winauthwebservices/sswinauthwebservice.asmx" # Webservice uri
    secretserverfolderid="44"    # New secrets are stored in this folder. Folder id -1 is default if not specified.
    secretservertemplate="Windows Account"  # Template used when creating new secrets. "Windows Account" is default if not specified.
 }
 

Simple Powershell MRU list

When using using my secret server powershell functions I got tired of constantly searching for secret ID’s. I had to do a new search just because I could not remember the ID’s. So I added some kind of MRU to my get-secretID function. This code block creates to classes mruitem and mrulist. The mrulist has three functions. Updatelist: Check if the ID already is in the list , if so update last used time. If it is not in the list , add it. Also remove oldest item if list is longer then max size. Savelist: saves the list to mru file. Loadlist : Load old list from file. Script also uses a global variable name ssmru. The global variable is declared in the powershell profile as $ssmru = “<filepath to mru list>”. I use a file because I want this MRU to stay persistent during a reboot.

# MRU list and item class 
class mruitem{
[string]$ssid
[string]$name
[datetime]$lastuseddate

mruitem([string]$ssid,[string]$name,[datetime]$lastuseddate)
{
$this.ssid=$ssid
$this.lastuseddate=$lastuseddate
$this.name=$name
}
}

class mrulist: System.Collections.ArrayList {

[int]$MaxSize = 15

updatelist([mruitem]$item){
$pos=$null
if($this.count -gt 0){
if($this.ssid.contains($item.ssid)){
$pos=$this.ssid.indexof($item.ssid)
}
else {$pos=$null}

if($pos){
$this[$pos].lastuseddate=$item.lastuseddate
$this[$pos].name=$item.name
}else{ #Add new
$this.add($item)
}
}else {
$this.add($item)
}
if($this.count -gt $this.MaxSize){
#delete oldest
$new = New-Object System.Collections.ArrayList
$new=($this| Sort-Object -Property lastuseddate )
$ssid=$new[0].ssid
$ssid
$pos=$this.ssid.indexof($ssid)
$this.RemoveAt($pos)
$new=$null
}
}

loadlist(){
if(Test-Path $global:ssmru){
$data=(Get-Content -Path $global:ssmru -Encoding Unicode)|ConvertFrom-Json
foreach($mru in $data){
$this.updatelist([mruitem]::new($mru.ssid,$mru.name,$mru.lastuseddate))
}
}
}

savelist(){
$data=$this|ConvertTo-Json
$data|out-file -FilePath $global:ssmru -Encoding unicode
}
}

$mrulist=New-Object -typename mrulist
$mrulist.loadlist()
if($mru){
$mrulist | Sort-Object -Property lastuseddate
return
}

For those interested , here is the updated version of get-secretID

function Get-SecretID
{
param(
[parameter(ValueFromPipeline=$True)]
[int] $secretID,
[pscredential]$sscred,
[switch]$Cleartext,
[switch]$mru
)
if(!($secretID)){$mru=$true}

# MRU list and item class
class mruitem{
[string]$ssid
[string]$name
[datetime]$lastuseddate

mruitem([string]$ssid,[string]$name,[datetime]$lastuseddate)
{
$this.ssid=$ssid
$this.lastuseddate=$lastuseddate
$this.name=$name
}
}

class mrulist: System.Collections.ArrayList {

[int]$MaxSize = 15

updatelist([mruitem]$item){
$pos=$null
if($this.count -gt 0){
if($this.ssid.contains($item.ssid)){
$pos=$this.ssid.indexof($item.ssid)
}
else {$pos=$null}

if($pos){
$this[$pos].lastuseddate=$item.lastuseddate
$this[$pos].name=$item.name
}else{ #Add new
$this.add($item)
}
}else {
$this.add($item)
}
if($this.count -gt $this.MaxSize){
#delete oldest
$new = New-Object System.Collections.ArrayList
$new=($this| Sort-Object -Property lastuseddate )
$ssid=$new[0].ssid
$ssid
$pos=$this.ssid.indexof($ssid)
$this.RemoveAt($pos)
$new=$null
}
}

loadlist(){
if(Test-Path $global:ssmru){
$data=(Get-Content -Path $global:ssmru -Encoding Unicode)|ConvertFrom-Json
foreach($mru in $data){
$this.updatelist([mruitem]::new($mru.ssid,$mru.name,$mru.lastuseddate))
}
}
}

savelist(){
$data=$this|ConvertTo-Json
$data|out-file -FilePath $global:ssmru -Encoding unicode
}
}

$mrulist=New-Object -typename mrulist
$mrulist.loadlist()
if($mru){
$mrulist | Sort-Object -Property lastuseddate
return
}

$where = 'https://<Server FQDN>/secretserver/winauthwebservices/sswinauthwebservice.asmx'

if($sscred -ne $null){
try{
$ws = New-WebServiceProxy -uri $where -Credential $sscred
}
catch{
Write-host "Error: Error connecting to secret server."
return $null
}
}else{

try{
$ws = New-WebServiceProxy -uri $where -UseDefaultCredential -ErrorAction SilentlyContinue
if($ws -eq $null){
if (!(Test-Path Variable:\ssadmin)){
throw {
Write-host "No secretserver admin specified or variable 'ssadmin' defined.`nThis is to be used by 'get-storedcredential'"
}
}
$adminacc=Get-StoredCredential -UserName $ssadmin
$ws = New-WebServiceProxy -uri $where -Credential $adminacc -ErrorAction SilentlyContinue
if($ws -eq $null){throw{Write-host "Unable to connect to SecretServer"}}
}
}
catch{
Write-host "Error connecting to SecretServer"
return $null
}
}

$wsResult = $ws.GetSecret($secretId, $false, $null)
if($wsresult.errors -ne $null){
$Cred=New-Object PSObject
$Cred | add-member -NotePropertyName "Username" -NotePropertyValue $wsresult.errors
$Cred | Add-Member -NotePropertyName "Password" -NotePropertyValue $wsresult.errors

return $Cred
} else {

$u=$wsResult.Secret.Items[1].value.ToString()
$ep = ConvertTo-SecureString $wsResult.Secret.Items[2].value.ToString() -AsPlainText -Force
[pscredential]$Cred = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $u,$ep
if($Cleartext){
[psobject]$Cred=New-Object PSObject
$Cred | add-member -NotePropertyName "Username" -NotePropertyValue $u
$Cred | Add-Member -NotePropertyName "Password" -NotePropertyValue $wsResult.Secret.Items[2].value.ToString()
$Cred | Add-Member -NotePropertyName "Domain" -NotePropertyValue $wsResult.Secret.Items[0].value.ToString()
}
$mrulist.updatelist([mruitem]::new($secretID,$u,(get-date)))
$mrulist.savelist()
return $Cred
}
}