Import user licenses from CSP using CSP api and graph.

January 31, 2021 Atle Leave a comment

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.

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 = "<csp tenant id>"
$appid = "<csp 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 "
}
}
}
}
}
}

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.

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

Azure, Azure, CSP, Development, Powershell, Windows

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

January 31, 2021 Atle Leave a comment

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 Cegal CSP
.DESCRIPTION
List licenses for tenant purchased using Cegal 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
}
}

Azure, Azure, CSP, Development, Powershell, Windows

Add tenant licenses from csp to database.

January 17, 2021 Atle Leave a comment

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

Azure, CSP, Development, Powershell

Get-tenantlistindb (from previous post)

January 8, 2021 Atle Leave a comment

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
}

Azure, CSP, Development, Powershell

Log tenants from office 365 to local Db

January 3, 2021 Atle Leave a comment

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

Development, Powershell

Powershell 2.0 Json to Hash

November 11, 2020 Atle Leave a comment

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
}

CSP, Powershell

Hash tables in powershell

March 23, 2020 Atle Leave a comment

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.

Powershell

SecretServer(Thycotic) plugin for powershell SecretsManagement module.

February 18, 2020 Atle Leave a comment

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

Powershell

Simple Powershell MRU list

December 23, 2019 Atle Leave a comment

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

Development, Powershell, Windows

Get Secretserver secret

October 3, 2019 Atle Leave a comment

Since we are using SecretServer as our credential store it is of great help to be able to get credentials directly from powershell. This is a small function that connects to secretserver webservices and retrieve a secret based on secred ID. The function will connect to the webservice as the signedin user or by a supplied credential or lastly by a predefined stored credential. To use stored credential I’am using functions from https://github.com/cunninghamp/PowerShell-Stored-Credentials .

Usually you would use the PS credential object directly. To get the password as text you could use it from the PSobject referring to the get networkcredential().


$cred=get-secretid -secretID 2007

$password_As_text=$cred.GetNetworkCredential().Password

Or if you need the password in clear text, displayed on screen, you could specify that as a an argument.

The function is made for my usage, so there is definitive roomfor improvement .


function Get-SecretID

{

param(

[parameter(ValueFromPipeline=$True)]

[int] $secretID,

[pscredential]$sscred,

[switch]$Cleartext

)



$where = 'https://secretserverdnsname/secretserver/winauthwebservices/sswinauthwebservice.asmx'



if($sscred -ne $null){

    $ws = New-WebServiceProxy -uri $where -Credential $sscred 

}else{



  try{

    $ws = New-WebServiceProxy -uri $where -UseDefaultCredential -ErrorAction SilentlyContinue

    if($ws -eq $null){

      if (!(Test-Path Variable:\ssuser)){

        throw {

          Write-Host "No secretserver user specified or variable 'ssuser' defined.`nThis is to be used by 'get-storedcredential'"

        }

      }

        $credacc=Get-StoredCredential -UserName $ssuser

        $ws = New-WebServiceProxy -uri $where -Credential $credacc -ErrorAction SilentlyContinue

        if($ws -eq $null){throw{Write-host "Unable to connect to SecretServer"}}

    }

  }

  catch{



  }

}



$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()

  }

return $Cred

}

}

Azure, CSP, Powershell

CSP access to tenants using powershell. Part 4

September 24, 2019 Atle Leave a comment

This is a small script that connects to partnercenter list all customers tenants and let you select one. When one is selected it connects to azuread and az for that customer.

All my credentials are stored in SecretServer . I use a web service request to get those credentials. I will show these modules in a later post.


import-module partnercenter



#$app = AppID + AppKey

$app=Get-SecretID -secretID xxxx

#rt = refreshtoken.

$rt=Get-SecretID -secretID yyyy

$refreshtoken=$rt.GetNetworkCredential().Password

$tid="'csp tenant directory id'"



   

   

#Connect PartnerCenter

$pcToken = New-PartnerAccessToken -RefreshToken $refreshToken -Resource https://api.partnercenter.microsoft.com -Credential $app -TenantId $tid

Connect-PartnerCenter -AccessToken $pcToken.AccessToken -ApplicationId $app.username -TenantId $tid 



    $customers=get-partnercustomer | sort-object -Property name

    $counter=0

    foreach($cust in $customers){

        Write-Host "$($counter) - $($cust.Name) - $($cust.domain)"

        $counter++

    }

    $custid=Read-Host "Enter customer #"

    $customer=$customers[$custid]

    Write-host " Targeting : $($customer.name) - $($customer.Domain)"



$azureToken = New-PartnerAccessToken -Resource "https://graph.microsoft.com/" -Credential $app -RefreshToken $refreshtoken -TenantId $customer.CustomerId 

$graphToken = New-PartnerAccessToken -RefreshToken $refreshToken -Resource "https://graph.windows.net/" -Credential $app -TenantId $customer.CustomerId  



Connect-Azuread -aadAccessToken $graphToken.AccessToken -msAccessToken $azureToken.AccessToken -TenantId $customer.CustomerId  -AccountId $app.username





$azure2Token = New-PartnerAccessToken -Resource https://management.azure.com/ -Credential $app -RefreshToken $refreshtoken -TenantId $customer.CustomerId 

$graph2Token = New-PartnerAccessToken -RefreshToken $refreshToken -Resource https://graph.windows.net/ -Credential $app -TenantId $customer.CustomerId  



 Connect-AzAccount -AccessToken $azure2Token.AccessToken -GraphAccessToken $graph2Token.AccessToken -TenantId $customer.CustomerId  -AccountId $app.username

Development, Powershell

Use powershell to get external IP address

September 23, 2019 Atle Leave a comment

How can you get your external IP address from powershell? I use a simple script to query an external public web service. The service I’m using is hosted by ipinfo.io . I have created a small function that is placed in my powershell library. All my modules are loaded by using powershell profiles.

This simple function uses rest. Usage is simple : get-mypublicip . Could easily be uses in script (get-mypublicip).ip .


function get-mypublicip{

    Write-Verbose "Resolving external IP"

    try { 

        $ipaddr = Invoke-RestMethod http://ipinfo.io/json #| Select-Object -ExpandProperty ip 

        }

    catch { 

        throw "Can't get external IP Address. Quitting." 

        }

    if ($ipaddr -eq $null) { throw "Can't get external IP Address. Quitting." }

    Write-Verbose "External IP is $ipaddr"

    return $ipaddr

}

Azure, CSP, Office365, Powershell

CSP access to tenants using powershell. Part 3

September 23, 2019 Atle Leave a comment

In this part 3 of CSP and powershell I will show how you can connect to azureAD of a customer tenant using your CSP app credentials and refreshtoken. This is almost the same procedure as we use to connect to az. We will start with the same variables as in part 2. Remember to keep your credential and secure, as it will give access to all your tenants.


$app=get-credential # Get AppID and Key for out partnecenter app. (created in part 1)

$refreshtoken = 'refreshtoken' # From part 1 or whenever we get a new one.

$CustomerTenantID= 'Azure directory object id'

Struggled for a while to get this to work. The important thing is the endpoints and when to use the customer tenant ID.


$azureToken = New-PartnerAccessToken -Resource "https://graph.microsoft.com/" -Credential $app -RefreshToken $refreshtoken -TenantId $CustomerTenantID 

$graphToken = New-PartnerAccessToken -RefreshToken $refreshToken -Resource "https://graph.windows.net/" -Credential $app -TenantId $CustomerTenantID  



Connect-Azuread -aadAccessToken $graphToken.AccessToken -msAccessToken $azureToken.AccessToken -TenantId $CustomerTenantID  -AccountId $app.username

So now you can use get-azureaduser to get users from this customer tenant.

You could also use the MS online module msol to query for users, this module requires you to use tenantid as an argument.

In part 4 I will wrap this up in a simple script allowing you to select customer tenant.

Azure, CSP, Powershell

CSP access to tenants using powershell. Part 2

September 20, 2019 Atle Leave a comment

In part 1 we created the Azure Enterprise App for Partnercenter and used this information to connect using powershell and connect-partnercenter. Now we will use this to connect to one of our customers tenants. First we will use AZ module and connect-azaccount. We will use the AZ module and the partnercenter module. So if those at not installed please install :


install-module az

install-module partnercenter

I will use the partnercenter module to request an accesstoken for azure.


$app=get-credential # Get AppID and Key for out partnecenter app. (created in part 1)

$refreshtoken = 'refreshtoken' # From part 1 or whenever we get a new one.

$CustomerTenantID= 'Azure directory object id'

Now we have all the required info to connect. The credentials should be stored securely!!!!


$azureToken = New-PartnerAccessToken -Resource https://management.azure.com/ -Credential $app -RefreshToken $refreshtoken -TenantId $CustomerTenantID 



$grapToken = New-PartnerAccessToken -RefreshToken $refreshToken -Resource https://graph.windows.net/ -Credential $app -TenantId $CustomerTenantID  



Connect-AzAccount -AccessToken $azureToken.AccessToken -GraphAccessToken $graphToken.AccessToken -TenantId $CustomerTenantID  -AccountId $app.username

There. We are now connected to our azure of our customer. In next part we will connect to azureAD

Azure, CSP, Powershell

CSP access to tenants using powershell. Part 1

September 18, 2019 Atle Leave a comment

A short explanation of how to access customer tenant using a CSP tenant SPN credential connectiong to AzureAD and AZ. Have been struggling for a while to manage all our customers tenants using powershell scripts. It can be complicated to organize all the credentials, tenant domain, tenant id’s password expiry.

First step is to be able to use powershell in the CSP tenants and access the partnercenter module. To get this started Microsoft has published a script to create the SPN required for this. https://docs.microsoft.com/en-us/powershell/partnercenter/secure-app-model?view=partnercenterps-1.5 This script will help you create the SPN . When using the SPN for the first time you will have to consent it using an admin account. The “ConfigurePreconsent” argument adds the spn to the adminagents group, this result in the account being a global admin in the customer tenants. Next:


$credential = Get-Credential

$token = New-PartnerAccessToken -Consent -Credential $credential -Resource https://api.partnercenter.microsoft.com -TenantId 'Your Tenant Id'

This is to consent the spn and get the refresh token we will in further logins. TenantId is the ID of your partner tenant. First it asks for the credential of the newly created spn (appID and key), next it will require you to login and consent using a service account . In return you get a token. Remember to store the refresh token part in a secure place as this will be used in our next login.


$refreshToken = 'Enter the refresh token value here'

$credential = Get-Credential

$tenantId = 'Your Tenant Id'

$pcToken = New-PartnerAccessToken -RefreshToken $refreshToken -Resource https://api.partnercenter.microsoft.com -Credential $credential -TenantId $tenantId

Connect-PartnerCenter -AccessToken $pcToken.AccessToken -ApplicationId $appId -TenantId $tenantId

Here we connect to partnercenter. We got the $refreshtoken in the previous step. $credential is our appid and key returned from the script. $tenantid is the tenantid of the partner tenant. Returned from the connection is a new $pcToken. This $pcToken includes a new refresh token that we could store and use at next login, but the one we already got would still last for a default value of 90 days. We’ve had some issues it the MFA settings in the tenant allow the user to “not be asked for credentials in xx days” (So we always uncheck this box).

Part 2 will for the AZ connection to customer tenant.

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

-MS Stuff-