173 lines
6.4 KiB
PowerShell
173 lines
6.4 KiB
PowerShell
function Get-FullTenantListFromServer {
|
|
<#
|
|
|
|
.SYNOPSIS
|
|
This function retrieves a full list of tenants from a given server identified by connection string
|
|
|
|
.DESCRIPTION
|
|
This function retrieves a full list of tenants from a given server identified by connection string
|
|
|
|
.PARAMETER connectionString
|
|
[string] Used to identify the connection to insert tenants to
|
|
|
|
.INPUTS
|
|
Connection string to connect to the server.
|
|
|
|
.OUTPUTS
|
|
A list of tenants. This is an array of hashmap objects. The objects should look like this example for developer tenant:
|
|
@{
|
|
Name = 'Developer Dynamic';
|
|
BankGuid = '78554577-9DE6-43CD-9085-5868977156D1';
|
|
Signature = 'developer.dev.alkamitech.com';
|
|
AdminSignature = 'admin-developer.dev.alkamitech.com';
|
|
DataSource = 'localhost';
|
|
Catalog = 'DeveloperDynamic';
|
|
Version = '';
|
|
ConnectionString = 'data source=localhost;Integrated Security=SSPI; Database=DeveloperDynamic;Max Pool Size=500;Pooling=true;MultipleActiveResultSets=true;';
|
|
GlobalIdentifier = '08bc7221-ba19-478b-889f-316c43324af2'
|
|
};
|
|
|
|
.EXAMPLE
|
|
Get-FullTenantListFromServer ConnectionString
|
|
|
|
Get-FullTenantListFromServer 'data source=localhost;Integrated Security=SSPI; Database=AlkamiMaster;Max Pool Size=500;Pooling=true;MultipleActiveResultSets=true;'
|
|
|
|
@(@{
|
|
Name = 'Developer Dynamic';
|
|
BankGuid = '78554577-9DE6-43CD-9085-5868977156D1';
|
|
Signature = 'developer.dev.alkamitech.com';
|
|
AdminSignature = 'admin-developer.dev.alkamitech.com';
|
|
DataSource = 'localhost';
|
|
Catalog = 'DeveloperDynamic';
|
|
Version = '';
|
|
ConnectionString = 'data source=localhost;Integrated Security=SSPI; Database=DeveloperDynamic;Max Pool Size=500;Pooling=true;MultipleActiveResultSets=true;';
|
|
GlobalIdentifier = '08bc7221-ba19-478b-889f-316c43324af2'
|
|
})
|
|
|
|
.NOTES
|
|
Will return GlobalIdentifier from Tenant table if column exist, else will return NULL for that column.
|
|
#>
|
|
[CmdletBinding()]
|
|
param(
|
|
[Parameter(Mandatory = $false, Position = 0)]
|
|
[string]$connectionString
|
|
)
|
|
process {
|
|
$logLead = Get-LogLeadName
|
|
|
|
if ([string]::IsNullOrEmpty($connectionString)) {
|
|
## If there is no passed in connection string, use the current computer connection string.
|
|
$connectionString = Get-MasterConnectionString
|
|
}
|
|
|
|
if (!(Confirm-DatabaseAccess $connectionString)) {
|
|
throw "$logLead : could not connect to the database!"
|
|
}
|
|
|
|
try {
|
|
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString
|
|
$sqlConnection.Open()
|
|
|
|
$queryBankinstanceidentifierStatus = @"
|
|
IF COL_LENGTH('[Tenant]','bankinstanceidentifier') IS NOT NULL
|
|
SELECT '1' AS [Status];
|
|
ELSE
|
|
SELECT '0' AS [Status];
|
|
"@
|
|
|
|
$queryTenantFull = @"
|
|
SELECT
|
|
Name,
|
|
BankIdentifiers,
|
|
BankUrlSignatures,
|
|
BankAdminUrlSignatures,
|
|
DataSource,
|
|
Catalog,
|
|
Version,
|
|
ConnectionString,
|
|
BankInstanceIdentifier
|
|
FROM
|
|
tenant;
|
|
"@
|
|
$queryTenantPartial = @"
|
|
SELECT
|
|
Name,
|
|
BankIdentifiers,
|
|
BankUrlSignatures,
|
|
BankAdminUrlSignatures,
|
|
DataSource,
|
|
Catalog,
|
|
Version,
|
|
ConnectionString,
|
|
NULL AS BankInstanceIdentifier
|
|
FROM
|
|
tenant;
|
|
"@
|
|
|
|
$data = @()
|
|
|
|
try {
|
|
[System.Data.SqlClient.SqlCommand]$command = $sqlConnection.CreateCommand()
|
|
|
|
# Run query to determine if column exists.
|
|
$command.CommandText = $queryBankinstanceidentifierStatus
|
|
|
|
Write-Verbose "$logLead : Running query to determine if Tenant.BankInstanceIdentifier exists."
|
|
[System.Data.SqlClient.SqlDataReader]$reader = $command.ExecuteReader()
|
|
|
|
# Find if the column exists.
|
|
while ($reader.Read()) {
|
|
$hasGLOBIDBit = $reader[0]
|
|
}
|
|
|
|
$reader.Close()
|
|
|
|
Write-Verbose "$logLead : GlobIdBit = $hasGLOBIDBit"
|
|
|
|
if($hasGLOBIDBit -eq "1") {
|
|
Write-Verbose "$logLead : Tenant table has BankInstanceIdentifier column. Using queryTenantFull"
|
|
$command.CommandText = $queryTenantFull
|
|
} else {
|
|
Write-Verbose "$logLead : Tenant table does not have BankInstanceIdentifier column. Using queryTenantPartial"
|
|
$command.CommandText = $queryTenantPartial
|
|
}
|
|
|
|
# Run the actual tenant query.
|
|
Write-Verbose "$logLead : Running tenant query."
|
|
$reader = $command.ExecuteReader()
|
|
|
|
while ($reader.Read()) {
|
|
$data += @{
|
|
Name = $reader[0];
|
|
BankGuid = $reader[1];
|
|
Signature = $reader[2];
|
|
AdminSignature = $reader[3];
|
|
DataSource = $reader[4];
|
|
Catalog = $reader[5];
|
|
Version = $reader[6];
|
|
ConnectionString = $reader[7];
|
|
Bankinstanceidentifier = $reader[8];
|
|
};
|
|
}
|
|
} catch {
|
|
Write-Host $_.Exception.Message
|
|
throw "$logLead : An error occurred getting tenant information from the database."
|
|
} finally {
|
|
Write-Verbose "$logLead : Closing and disposing DataReader and SqlConnection"
|
|
$reader.Dispose()
|
|
$sqlConnection.Close()
|
|
}
|
|
|
|
foreach ($record in $data) {
|
|
if ([string]::IsNullOrWhiteSpace($record.ConnectionString)) {
|
|
$record.ConnectionString = Get-FormattedConnectionString -ServerName $record.DataSource -DatabaseName $record.Catalog
|
|
}
|
|
}
|
|
|
|
return $data
|
|
} catch {
|
|
Write-Host $_.Exception.Message
|
|
throw "$logLead : An error occurred getting the tenant list from the server."
|
|
}
|
|
}
|
|
} |