BillingSqlDb.psm1
|
# Instal Pre-Req Modules Invoke-InstallOrUpdateModule 'SqlServer' function Get-StagingTableRecordsCount { param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Switch]$exportCsv) $query = "select min(starttime) as MinStartTime, count(1) as RecordsCount, 'Staging Hourly' as TableName from Staging_BillingHourlyRecords with (nolock) select min(starttime) as MinStartTime, count(1) as RecordsCount, 'Staging Daily' as TableName from Staging_BillingDailyRecords with (nolock) select min(starttime) as MinStartTime, count(1) as RecordsCount, 'Staging Monthly' as TableName from Staging_BillingMonthlyRecords with (nolock) select min(starttime) as MinStartTime, count(1) as RecordsCount, 'Staging Monthly Resource Summary' as TableName from Staging_BillingMonthlyResourceSummaries with (nolock) select min(starttime) as MinStartTime, count(1) as RecordsCount, 'Staging Monthly Running Summary' as TableName from Staging_BillingMonthlyRunningSummary with (nolock)" try { $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole Write-Host "Staging table data result: " $result.Tables if($exportCsv) { $dateTimeTicks = (Get-Date).Ticks $outFileName = 'StagingResults_' + $dateTimeTicks + '.csv' Write-Host "Writing to File: $outFileName" foreach($table in $result.Tables) { $table | Export-Csv -Path $outFileName -NoTypeInformation –Append } } } catch { $exception = $_ Write-Host "Error getting staging table details" Write-Error $exception } } function Get-BillingAggregationRecordsStatus { param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Switch]$exportCsv) $query = "select min(starttime) as MinStartTime, max(starttime) as MaxStartTime, 'Hourly' as TableName from BillingHourlyRecords with (nolock) select min(starttime) as MinStartTime, max(starttime) as MaxStartTime, 'Daily' as TableName from BillingDailyRecords with (nolock) select min(starttime) as MinStartTime, max(starttime) as MaxStartTime, 'Monthly' as TableName from BillingMonthlyRecords with (nolock) select min(starttime) as MinStartTime, max(starttime) as MaxStartTime, 'Monthly Resource Summary' as TableName from BillingMonthlyResourceSummaries with (nolock) select min(starttime) as MinStartTime, max(starttime) as MaxStartTime, 'Monthly Running Summary' as TableName from BillingMonthlyRunningSummary with (nolock)" try { $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole Write-Host "Aggregation table status data result: " $result.Tables if($exportCsv) { $dateTimeTicks = (Get-Date).Ticks $outFileName = 'AggregationTableStatusResults_' + $dateTimeTicks + '.csv' Write-Host "Writing to File: $outFileName" foreach($table in $result.Tables) { $table | Export-Csv -Path $outFileName -NoTypeInformation –Append } } } catch { $exception = $_ Write-Host "Error getting Aggregation table status details" Write-Error $exception } } function ResetBillingUsages { param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $true, Position = 1, HelpMessage = "Specifiy start time from when the reset has to be done")] $startDateTime, [Parameter(Mandatory = $true, Position = 2)] $costManagementDBName, [Parameter(Mandatory = $true, Position = 3)] $partitionKey1, [Parameter(Mandatory = $false, Position = 4)] $dbName, [Parameter(Mandatory = $false, Position = 5)] $timeOut, [Switch]$exportCsv) $query = "DECLARE @lastprocessedtime NVARCHAR(30) SET @lastprocessedtime = '$startDateTime' UPDATE [dbo].[UsageProcessingStates] SET [LastProcessedTime] = @lastprocessedtime WHERE StateName = 'BillingMonthlySummaryAggregator' UPDATE [dbo].[UsageProcessingStates] SET [LastProcessedTime] = @lastProcessedTime, [LastSeenTime] = @lastProcessedTime WHERE StateName = 'DailyTenantCostReportGenerator' UPDATE [dbo].[BillingMonthlyResourceSummaries] SET IsProcessed = 0 WHERE ( CreatedTime > (SELECT InvoiceCreatedTime FROM BillingInvoices WHERE InvoiceStartTime = @lastprocessedtime) AND StartTime < @lastprocessedtime ) DELETE FROM [dbo].[RawUsageRecordProcessingStatus] WHERE RecordId IN (SELECT ID FROM [dbo].[RawUsageRecords] WHERE StartTime >= @lastprocessedtime) DELETE FROM [dbo].[Staging_BillingHourlyRecords] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[Staging_BillingDailyRecords] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[Staging_BillingMonthlyRecords] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[Staging_BillingMonthlyResourceSummaries] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[Staging_BillingMonthlyRunningSummary] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[BillingHourlyRecords] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[BillingDailyRecords] WHERE StartTime >= @lastProcessedTime DROP TABLE IF EXISTS #invoiceIds SELECT Id into #invoiceIds from [dbo].[BillingInvoices] WHERE InvoiceStartTime = @lastProcessedTime DELETE FROM [dbo].[BillingInvoiceSendInfos] DELETE FROM [dbo].[BillingInvoiceLineItems] WHERE InvoiceId IN (SELECT Id FROM [dbo].BillingInvoices WHERE InvoiceStartTime >= @lastProcessedTime) DELETE FROM [dbo].[BillingInvoices] WHERE InvoiceStartTime >= @lastProcessedTime DELETE FROM [dbo].[BillingUserInvoiceLineItems] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[BillingUserInvoices] WHERE InvoiceStartTime >= @lastProcessedTime DELETE FROM [dbo].[BillingMonthlyRecords] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[BillingMonthlyResourceSummaries] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[BillingMonthlyRunningSummary] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[BillingHistory] WHERE StartTime >= @lastProcessedTime DELETE FROM[dbo].[DailyTenantCostReport] WHERE StartTime >= @lastProcessedTime DELETE FROM[dbo].[DashboardDailyCostSummary] WHERE StartTime >= @lastProcessedTime DELETE FROM [dbo].[CustomReports] WHERE BillingCycle >= @lastProcessedTime UPDATE [dbo].BillingMonthlyResourceSummaries set InvoiceId=null, IsProcessed=1,IsError=1, ErrorText = NULL where InvoiceId in (select id from #invoiceIds) ---------CSP License------- UPDATE [dbo].[UsageProcessingStates] SET [LastProcessedTime] = @lastprocessedtime WHERE StateName = 'CspLicenseLogProcessor' UPDATE [dbo].[ReportDefinition] SET [LastProcessedTime] = @lastprocessedtime DELETE from [dbo].[CspLicensePurchaseLogRecordProcessingStatus] WHERE UsageStartTime >= @lastprocessedtime --------------- --DELETE WRT to lastProcessedTime, get the external record id from Cost Management from which reset must be performed. --Update the Cost Management database name in below queries. DECLARE @lastAwsCostRecordId INT SELECT TOP 1 @lastAwsCostRecordId = RecordId FROM [$costManagementDBName].Source.AwsCurSourceData WHERE LineItem_UsageStartDate >= @lastProcessedTime ORDER BY RecordId DELETE from [dbo].[AwsCostRecordProcessingStatus] WHERE (PartitionKey1 is null or PartitionKey1 = '$partitionKey1' ) and RecordId >= @lastAwsCostRecordId DECLARE @lastAzsHubCostRecordId INT SELECT TOP 1 @lastAzsHubCostRecordId = RecordId FROM [$costManagementDBName].Source.AzsHubUsageData WHERE UsageStartTime >= @lastProcessedTime ORDER BY RecordId DELETE from [dbo].[AzsHubUsageRecordProcessingStatus] WHERE (PartitionKey1 is null or PartitionKey1 = '$partitionKey1' ) and RecordId >= @lastAzsHubCostRecordId DELETE from [dbo].[CspCostRecordProcessingStatus] WHERE (PartitionKey1 is null or PartitionKey1 = '$partitionKey1' ) and RecordId IN( SELECT RecordId FROM [$costManagementDBName].Source.CspAzureInvoiceLineItems Where InvoiceId IN( SELECT InvoiceId FROM [$costManagementDBName].Source.CspInvoices Where BillingPeriodStartDate >= @lastProcessedTime)) DECLARE @lastCspLicenseCostRecordId INT SELECT TOP 1 @lastCspLicenseCostRecordId = RecordId FROM [$costManagementDBName].Source.CspLicenseInvoiceLineItems WHERE ChargeStartDate >= @lastProcessedTime ORDER BY RecordId DELETE from [dbo].CspLicenseCostRecordProcessingStatus WHERE (PartitionKey1 is null or PartitionKey1 = '$partitionKey1' ) and RecordId >= @lastCspLicenseCostRecordId DECLARE @lastCspEstimateCostRecordId INT SELECT TOP 1 @lastCspEstimateCostRecordId = RecordId FROM [$costManagementDBName].[Source].[CspAzureUnBilledLineItems] WHERE ChargeStartDate >= @lastProcessedTime ORDER BY RecordId DELETE from [dbo].CspCostEstimateRecordProcessingStatus WHERE (PartitionKey1 is null or PartitionKey1 = '$partitionKey1' ) and RecordId >= @lastCspEstimateCostRecordId" try { $sqlDbConn = Get-SqlDbConnection $connectionName $decoded = Invoke-Decode $sqlDbConn.Password if($dbName -eq $null) { $dbName = $sqlDbConn.DbName } if($timeOut -eq $null) { $timeOut = 1800 } Write-Host "Executing reset query.." $data = Invoke-Sqlcmd -ServerInstance $sqlDbConn.DbServerName -Database $dbName -Username $sqlDbConn.User -Password "$decoded" -Query $query -OutputAs DataSet -QueryTimeout $timeOut Write-Host "Usage Reset completed. Validating if there are any monthly data after $startDateTime" $validateMonthlyRecords = "select count(1) from BillingMonthlyRecords with (nolock) where [StartTime] >= '$startDateTime'" $validationResult = Invoke-SqlDbQuery -name $connectionName -query $validateMonthlyRecords -dbName $dbName -printToConsole if ($validationResult.Column1 -eq 0) { Write-Host "Reset successful. There are no data found after $startDateTime" } else { Write-Host "Reset failed. There are "$validationResult.Column1" records in monthly table after reset for the specified period. Kindly execute reset again." } } catch { $exception = $_ Write-Host "Error resetting usage" Write-Error $exception } } function Invoke-ClearStagingTable { param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1, HelpMessage = "Records of specified and previous months will be cleared. If no date is provided, current month records will not be cleared")] $resetTillDate, [Parameter(Mandatory = $false, Position = 2)] $dbName, [Switch]$exportResult) if (!$resetTillDate) { $resetTillDate = (Get-Date).AddMonths(-1).tostring("yyyy-MM-01") } $query = "DELETE from Staging_BillingHourlyRecords where [StartTime] <= '$resetTillDate'; SELECT @@ROWCOUNT AS DeletedHourlyRows DELETE from Staging_BillingDailyRecords where [StartTime] <= '$resetTillDate'; SELECT @@ROWCOUNT AS DeletedDailyRows DELETE from Staging_BillingMonthlyRecords where [StartTime] <= '$resetTillDate'; SELECT @@ROWCOUNT AS DeletedMonthlyRows DELETE from Staging_BillingMonthlyResourceSummaries where [StartTime] <= '$resetTillDate'; SELECT @@ROWCOUNT AS DeletedMonthlyResourceSummaryRows DELETE from Staging_BillingMonthlyRunningSummary where [StartTime] <= '$resetTillDate'; SELECT @@ROWCOUNT AS DeletedMonthlyRunningSummaryRows" try { Write-Host "Stopping Billing Agent Service..." net stop BillingAgentService Write-Host "`nClearing staging table records..." $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole Write-Host "`nSuccessfully cleared staging records" foreach($table in $result.Tables) { $outData = $outData + "`n" + $table.Columns.Caption + ": " + $table.ItemArray } Write-Host "`nResults:" $outData if($exportResult) { $dateTimeTicks = (Get-Date).Ticks $outFileName = '.\ClearStagingResults_' + $dateTimeTicks + '.txt' Write-Host "Writing to File: $outFileName" $outData | Out-File -FilePath $outFileName } } catch { $exception = $_ Write-Host "Error clearing staging table records" Write-Error $exception } finally { Write-Host "`nStarting Billing Agent Service..." net start BillingAgentService } } function Get-UsageQuotaData { param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "DACM db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Parameter(Mandatory = $false, Position = 2)] $numberOfRecords) try { if(!$numberOfRecords) { $numberOfRecords = 100 } $aggregationTableQueries = "select MAX(StartTime) as MaxStartTime, 'Hourly' as TableName from Source.AzsHubQuotaHourlyRecords with (nolock) select MAX(StartTime) as MaxStartTime, 'Daily' as TableName from Source.AzsHubQuotaDailyRecords with (nolock) select MAX(StartTime) as MaxStartTime, 'Monthly' as TableName from Source.AzsHubQuotaMonthlyRecords with (nolock)" $hourlyStatus = "select top $numberOfRecords * from Pipeline.ProcessorStatus with (nolock) where [Processor] = 'AzsHubQuotaCollector' order by [LastUpdatedTime] desc" $dailyStatus = "select top $numberOfRecords * from Pipeline.ProcessorStatus with (nolock) where [Processor] = 'AzsHubQuotaDailyAggregator' order by [LastUpdatedTime] desc" $monthlyStatus = "select top $numberOfRecords * from Pipeline.ProcessorStatus with (nolock) where [Processor] = 'AzsHubQuotaMonthlyAggregator' order by [LastUpdatedTime] desc" $subscriptionStatus = "select * from Pipeline.SubscriptionProcessingStatus with (nolock) where [PlatformType] = 'AzsHubQuota'" $dateTimeTicks = (Get-Date).Ticks $directoryName = 'UsageQuotaData' If(!(test-path $directoryName)) { New-Item -ItemType Directory -Force -Path $directoryName } Write-Host "Getting Quota Aggregation tables data..." $result = Invoke-SqlDbQuery -name $connectionName -query $aggregationTableQueries -dbName $dbName -printToConsole $outFileName = $directoryName +'\AggregationTableResults_' + $dateTimeTicks + '.csv' $result.Tables Write-ResultDataToCSV -result $result.Tables -outFileName $outFileName Write-Host "Getting Quota Hourly Status data..." $result = Invoke-SqlDbQuery -name $connectionName -query $hourlyStatus -dbName $dbName -printToConsole $outFileName = $directoryName +'\HourlyStatusTableResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName Write-Host "Getting Quota Daily Status data..." $result = Invoke-SqlDbQuery -name $connectionName -query $dailyStatus -dbName $dbName -printToConsole $outFileName = $directoryName +'\DailyStatusTableResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName Write-Host "Getting Quota Monthly Status data..." $result = Invoke-SqlDbQuery -name $connectionName -query $monthlyStatus -dbName $dbName -printToConsole $outFileName = $directoryName +'\MonthlyStatusTableResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName Write-Host "Getting Subscription Status data..." $result = Invoke-SqlDbQuery -name $connectionName -query $subscriptionStatus -dbName $dbName -printToConsole $outFileName = $directoryName +'\SubscriptionStatusResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName } catch { $exception = $_ Write-Host "Error getting Usage and Quota table details" Write-Error $exception } } function Get-UsageQuotaFilterData{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "DACM db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName) try { $syncEnabledSubscriptions = "SELECT [ConnectionId], [SubscriptionId], Connections.[Name] AS ConnectionName, [Owner] FROM Source.AzsHubSubscriptions AS Subscriptions INNER JOIN Config.Connections AS Connections ON Subscriptions.[ConnectionId] = Connections.[Id] WHERE [IsSyncEnabled] = 1 AND [AdditionalInfoJson] IS NOT NULL" $distinctResources = "Select distinct [ResourceDisplayName] as Resource, [ServiceProvider] from Source.AzsHubQuotaMonthlyRecords with (nolock) where [StartTime] >= (select max(StartTime) from source.AzsHubQuotaMonthlyRecords with (nolock))" $dateTimeTicks = (Get-Date).Ticks $directoryName = 'UsageQuotaData' If(!(test-path $directoryName)) { New-Item -ItemType Directory -Force -Path $directoryName } Write-Host "Getting sync enabled subscription details..." $result = Invoke-SqlDbQuery -name $connectionName -query $syncEnabledSubscriptions -dbName $dbName -printToConsole $outFileName = $directoryName + '\SynSubscriptionResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName Write-Host "Getting distinct providers and respective resource type..." $resourceResult = Invoke-SqlDbQuery -name $connectionName -query $distinctResources -dbName $dbName -printToConsole $outFileName = $directoryName + '\ProviderAndResourceTypeResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $resourceResult -outFileName $outFileName } catch { $exception = $_ Write-Host "Error getting Usage and quota filters" Write-Error $exception } } function Get-AppSettings{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Parameter(Mandatory = $false, Position = 2)] $key, [Parameter(Mandatory = $true, Position = 3)] $group, [Switch]$exportCsv) try { $query = "select * from AppSettings with (nolock) where [Group] = '$group'" if ($key) { $query = $query + " and [Key] = '$key'" } Write-Host "Getting AppSettings..." $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole $result if ($exportCsv) { $dateTimeTicks = (Get-Date).Ticks $outFileName = '.\AppSettingsResult_' + $dateTimeTicks + '.csv' Write-Host "Writing to File: $outFileName" Write-ResultDataToCSV -result $result -outFileName $outFileName } } catch { $exception = $_ Write-Host "Error getting AppSettings" Write-Error $exception } } function Update-AppSettings{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Parameter(Mandatory = $true, Position = 2)] $key, [Parameter(Mandatory = $true, Position = 3)] $group, [Parameter(Mandatory = $true, Position = 3)] $value) try { $query = "update AppSettings set [Value] = '$value' where [Group] = '$group' and [Key] = '$key'; SELECT @@ROWCOUNT AS UpdatedRows" Write-Host "Updating AppSettings..." $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole if ($result.UpdatedRows -gt 1) { Write-Host "More than one row updated." } elif ($result.UpdatedRows -eq 0) { Write-Host "No AppSetting got updated." } else { Write-Host "Successfully update the AppSetting." } } catch { $exception = $_ Write-Host "Error updating AppSettings" Write-Error $exception } } function Get-SubscriptionQuotaStatusReport { param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "DACM db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Parameter(Mandatory = $false, Position = 2)] $year, [Parameter(Mandatory = $false, Position = 3)] $month) try { if (!$year -or !$month) { $startTime = (Get-Date).tostring("yyyy-MM-01 00:00:00.000") $endTime = (Get-Date -Day 01).AddMonths(1).AddDays(-1).tostring("yyyy-MM-dd 23:59:59.997") } else { $startTime = "$year-$month-01 00:00:00.000" $endTime = [datetime]::parseexact("$year-$month-01", 'yyyy-MM-dd', $null).AddMonths(1).AddDays(-1).tostring("yyyy-MM-dd 23:59:59.997") } $dateTimeTicks = (Get-Date).Ticks $directoryName = 'UsageQuotaReportData' If(!(test-path $directoryName)) { New-Item -ItemType Directory -Force -Path $directoryName } $query = "Select SubscriptionId, SubscriptionName, ConnectionName, UserId, OK, Critical, Warning From Pipeline.ServiceResourceReportData with (nolock) CROSS Apply OPENJSON(ReportData) With( SubscriptionId VARCHAR(60), SubscriptionName VARCHAR(50), ConnectionName VARCHAR(50), UserId VARCHAR(50), OK INT, Critical INT, Warning INT ) WHERE StartTime >= '$startTime' AND EndTime <= '$endTime' AND LookupId IN (Select Id From Pipeline.CommonServiceResourceLookups with (nolock) Where ResourceType = 'SubscriptionQuotaStatus')" $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole Write-Host "Number of records for " $startTime " : " $result.Count $outFileName = $directoryName + '\SubscriptionQuotaStatusResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName } catch { $exception = $_ Write-Host "Error getting Usage and Quota Subscription Quota status report" Write-Error $exception } } function Get-QuotaConsumptionReport { param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "DACM db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Parameter(Mandatory = $false, Position = 2)] $year, [Parameter(Mandatory = $false, Position = 3)] $month) try { if (!$year -or !$month) { $startTime = (Get-Date).tostring("yyyy-MM-01 00:00:00.000") $endTime = (Get-Date -Day 01).AddMonths(1).AddDays(-1).tostring("yyyy-MM-dd 23:59:59.997") } else { $startTime = "$year-$month-01 00:00:00.000" $endTime = [datetime]::parseexact("$year-$month-01", 'yyyy-MM-dd', $null).AddMonths(1).AddDays(-1).tostring("yyyy-MM-dd 23:59:59.997") } $dateTimeTicks = (Get-Date).Ticks $directoryName = 'UsageQuotaReportData' If(!(test-path $directoryName)) { New-Item -ItemType Directory -Force -Path $directoryName } $query = "SELECT lookup.ResourceType,lookup.SubscriptionId,report.ReportData FROM Pipeline.ServiceResourceReportData as report with (nolock) inner join Pipeline.CommonServiceResourceLookups as lookup with (nolock) on report.LookupId = lookup.Id WHERE report.StartTime >= '$startTime' AND report.EndTime <= '$endTime' AND lookup.ResourceType in ('StampQuotaConsumption','SubscriptionQuotaConsumption')" $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole Write-Host "Number of records for " $startTime " : " $result.Count $outFileName = $directoryName + '\QuotaConsumptionResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName } catch { $exception = $_ Write-Host "Error getting Usage and Quota consumption report" Write-Error $exception } } function Get-TrendReport { param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "DACM db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Parameter(Mandatory = $false, Position = 2)] $year, [Parameter(Mandatory = $false, Position = 3)] $month) try { if (!$year -or !$month) { $startTime = (Get-Date).tostring("yyyy-MM-01 00:00:00.000") $endTime = (Get-Date -Day 01).AddMonths(1).AddDays(-1).tostring("yyyy-MM-dd 23:59:59.997") } else { $startTime = "$year-$month-01 00:00:00.000" $endTime = [datetime]::parseexact("$year-$month-01", 'yyyy-MM-dd', $null).AddMonths(1).AddDays(-1).tostring("yyyy-MM-dd 23:59:59.997") } $dateTimeTicks = (Get-Date).Ticks $directoryName = 'UsageQuotaReportData' If(!(test-path $directoryName)) { New-Item -ItemType Directory -Force -Path $directoryName } $query = "Select QuotaProvider, QuotaResource, MaxQuota, ConsumedQuota, ConsumptionPercentage, UsageDate From Pipeline.ServiceResourceReportData CROSS Apply OPENJSON(ReportData) With( QuotaProvider VARCHAR(60), QuotaResource VARCHAR(50), UsageDate DATETIME, MaxQuota VARCHAR(100), ConsumedQuota VARCHAR(100), ConsumptionPercentage VARCHAR(100) ) WHERE StartTime >= '$startTime' AND EndTime <= '$endTime'" $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole Write-Host "Number of records for " $startTime " : " $result.Count $outFileName = $directoryName + '\TrendReportResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName } catch { $exception = $_ Write-Host "Error getting Usage and Quota consumption report" Write-Error $exception } } function Get-AzsReportStatus{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "DACM db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Parameter(Mandatory = $false, Position = 2)] $numberOfRecords, [Switch]$exportCsv) try { if($exportCsv) { if(!$numberOfRecords) { $numberOfRecords = 100 } $reportTableQueries = "Select MAX(StartTime) as MaxStartTime, 'StampQuotaConsumption' as ResourceType From Pipeline.ServiceResourceReportData with (nolock) where [LookupId] in (select Id from Pipeline.CommonServiceResourceLookups with (nolock) where [ResourceType] = 'StampQuotaConsumption') Select MAX(StartTime) as MaxStartTime, 'SubscriptionQuotaConsumption' as ResourceType From Pipeline.ServiceResourceReportData with (nolock) where [LookupId] in (select Id from Pipeline.CommonServiceResourceLookups with (nolock) where [ResourceType] = 'SubscriptionQuotaConsumption') Select MAX(StartTime) as MaxStartTime, 'SubscriptionQuotaStatus' as ResourceType From Pipeline.ServiceResourceReportData with (nolock) where [LookupId] in (select Id from Pipeline.CommonServiceResourceLookups with (nolock) where [ResourceType] = 'SubscriptionQuotaStatus') Select MAX(StartTime) as MaxStartTime, 'AllStampsQuotaWeekReport' as ResourceType From Pipeline.ServiceResourceReportData with (nolock) where [LookupId] in (select Id from Pipeline.CommonServiceResourceLookups with (nolock) where [ResourceType] = 'AllStampsQuotaWeekReport') Select MAX(StartTime) as MaxStartTime, 'AllStampsQuotaMidMonthReport' as ResourceType From Pipeline.ServiceResourceReportData with (nolock) where [LookupId] in (select Id from Pipeline.CommonServiceResourceLookups with (nolock) where [ResourceType] = 'AllStampsQuotaMidMonthReport') Select MAX(StartTime) as MaxStartTime, 'AllStampsQuotaMonthReport' as ResourceType From Pipeline.ServiceResourceReportData with (nolock) where [LookupId] in (select Id from Pipeline.CommonServiceResourceLookups with (nolock) where [ResourceType] = 'AllStampsQuotaMonthReport') Select MAX(StartTime) as MaxStartTime, 'SubscriptionQuotaWeekReport' as ResourceType From Pipeline.ServiceResourceReportData with (nolock) where [LookupId] in (select Id from Pipeline.CommonServiceResourceLookups with (nolock) where [ResourceType] = 'SubscriptionQuotaWeekReport') Select MAX(StartTime) as MaxStartTime, 'SubscriptionQuotaMidMonthReport' as ResourceType From Pipeline.ServiceResourceReportData with (nolock) where [LookupId] in (select Id from Pipeline.CommonServiceResourceLookups with (nolock) where [ResourceType] = 'SubscriptionQuotaMidMonthReport') Select MAX(StartTime) as MaxStartTime, 'SubscriptionQuotaMonthReport' as ResourceType From Pipeline.ServiceResourceReportData with (nolock) where [LookupId] in (select Id from Pipeline.CommonServiceResourceLookups with (nolock) where [ResourceType] = 'SubscriptionQuotaMonthReport')" $statusQuries = "select top $numberOfRecords * from Pipeline.ProcessorStatus with (nolock) where [Processor] = 'AzsHubReportProcessor' order by [ProcessingStartTime] desc select top $numberOfRecords * from Pipeline.ProcessorStatus with (nolock) where [Processor] = 'AzsHubReportCsrlLookupProcessor' order by [ProcessingStartTime] desc select top $numberOfRecords * from Pipeline.ProcessorStatus with (nolock) where [Processor] = 'AzsHubSubscriptionQuotaStatusProcessor' order by [ProcessingStartTime] desc select top $numberOfRecords * from Pipeline.ProcessorStatus with (nolock) where [Processor] = 'AzsHubDailyReportProcessor' order by [ProcessingStartTime] desc" $dateTimeTicks = (Get-Date).Ticks $directoryName = 'UsageQuotaReportData' If(!(test-path $directoryName)) { New-Item -ItemType Directory -Force -Path $directoryName } Write-Host "Getting Quota Report tables data..." $result = Invoke-SqlDbQuery -name $connectionName -query $reportTableQueries -dbName $dbName -printToConsole $outFileName = $directoryName +'\ReportTableResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result.Tables -outFileName $outFileName Write-Host "Getting Quota Report Status data..." $result = Invoke-SqlDbQuery -name $connectionName -query $statusQuries -dbName $dbName -printToConsole $outFileName = $directoryName +'\QuotaReportStatusTableResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result.Tables -outFileName $outFileName } Write-Host "Getting latest report status data..." $reportProcessorStatus = "select top 1 State,ProcessingStatus,ProcessingStartTime,Message From Pipeline.ProcessorStatus with (nolock) where [Processor] = 'AzsHubReportProcessor' order by [ProcessingStartTime] desc" $result = Invoke-SqlDbQuery -name $connectionName -query $reportProcessorStatus -dbName $dbName -printToConsole $result } catch { $exception = $_ Write-Host "Error getting Usage and quota report status details" Write-Error $exception } } function Get-AzsHubUsageData{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Parameter(Mandatory = $false, Position = 2)] $numberOfRecords, [Switch]$exportCsv) try { if(!$numberOfRecords) { $numberOfRecords = 100 } $dateTimeTicks = (Get-Date).Ticks $directoryName = 'AzsHubUsageData' If(!(test-path $directoryName)) { New-Item -ItemType Directory -Force -Path $directoryName } if ($exportCsv) { $query = "select top $numberOfRecords * from Source.AzsHubUsageData with (nolock) order by UsageStartTime desc,RecordId desc" Write-Host "Getting latest Azs Hub usage data..." $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole $outFileName = $directoryName +'\AzsHubUsageResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName } else { $query = "select top 1 SubscriptionId, UsageStartTime from Source.AzsHubUsageData with (nolock) order by UsageStartTime desc,RecordId desc" $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole Write-Host "Last available record for subscription " $result.SubscriptionId " at " $result.UsageStartTime } } catch { $exception = $_ Write-Host "Error getting Azs Hub usage details" Write-Error $exception } } function Get-AzsHubUsageReaderStatus{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Parameter(Mandatory = $false, Position = 2)] $numberOfRecords) try { if(!$numberOfRecords) { $numberOfRecords = 500 } $dateTimeTicks = (Get-Date).Ticks $directoryName = 'AzsHubUsageData' If(!(test-path $directoryName)) { New-Item -ItemType Directory -Force -Path $directoryName } $query = "select top $numberOfRecords * from Pipeline.ProcessorStatus with (nolock) where [Processor] = 'AzsHubUsageReader' order by LastUpdatedTime desc" $getErrorStatusQuery = "select top $numberOfRecords * from Pipeline.ProcessorStatus with (nolock) where [Processor] = 'AzsHubUsageReader' and ([ProcessingStatus] = 150 or [ProcessingStatus] = 160) order by LastUpdatedTime desc" Write-Host "Getting Azs Hub usage reader status..." $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole $outFileName = $directoryName +'\AzsHubUsageReaderStatusResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName Write-Host "Getting Azs Hub usage reader error status..." $result = Invoke-SqlDbQuery -name $connectionName -query $getErrorStatusQuery -dbName $dbName -printToConsole $outFileName = $directoryName +'\AzsHubUsageReaderErrorStatusResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName } catch { $exception = $_ Write-Host "Error getting Azs Hub usage reader status details" Write-Error $exception } } function Get-SubscriptionMapper{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Parameter(Mandatory = $false, Position = 2)] $SubscriptionId, [Switch]$exportCsv) try { $dateTimeTicks = (Get-Date).Ticks $directoryName = 'SubscriptionMapper' If(!(test-path $directoryName)) { New-Item -ItemType Directory -Force -Path $directoryName } if ($SubscriptionId) { $query = "select * from SubscriptionMapper with (nolock) where RegisteredSubscriptionId = '$SubscriptionId'" } else{ $query = "select * from SubscriptionMapper with (nolock)" } if ($exportCsv) { Write-Host "Getting Subscription Mapper data..." $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole $outFileName = $directoryName +'\SubscriptionMapperResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $result -outFileName $outFileName } else { $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole Write-Host "Getting Subscription Mapper data..." Write-Output $result | fl * } } catch { $exception = $_ Write-Host "Error getting Subscription Mapper data" Write-Error $exception } } function Clean-EmailDispatchQueue{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $false, Position = 1)] $dbName, [Switch]$isUpdateStatus) try { #deleting/updating the email dispatch queue entries if the status is New/Error if (!$isUpdateStatus) { $query = "DELETE FROM EmailDispatchQueue WHERE [Status] != 20" } else { $query = "UPDATE EmailDispatchQueue SET [Status] = 0, [StatusString] = 'None', [DispatchErrorMessage] = 'Updated from powershell command Clean-EmailDispatchQueue' WHERE [Status] != 20" } $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dbName -printToConsole Write-Host "Successfully cleared Email Dispatch Queue" } catch { $exception = $_ Write-Host "Error cleaning Email Dispatch Queue" Write-Error $exception } } function Get-BillingUsageSources{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $true, Position = 1)] $billingDBName, [Parameter(Mandatory = $false, Position = 2)] $platformType, [Parameter(Mandatory = $false, Position = 3)] $sourceType, [Switch]$exportCsv) try { $query = "SELECT * from UsageSource with (nolock)" if ($platformType -and $sourceType) { $query = $query + " WHERE PlatformType = '$platformType' AND SourceType = '$sourceType'" } elseif ($platformType) { $query = $query + " WHERE PlatformType = '$platformType'" } elseif ($sourceType) { $query = $query + " WHERE SourceType = '$sourceType'" } $billingQueryResult = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $billingDBName -printToConsole Write-Host "Usage Source count: "$billingQueryResult.Count $billingQueryResult if ($exportCsv) { $dateTimeTicks = (Get-Date).Ticks $outFileName = '.\BillingUsageSourceResults_' + $dateTimeTicks + '.csv' Write-ResultDataToCSV -result $billingQueryResult -outFileName $outFileName } } catch { $exception = $_ Write-Host "Error getting Usage sources" Write-Error $exception } } function Get-DACMConnections{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $true, Position = 1)] $dacmDBName, [Parameter(Mandatory = $false, Position = 2)] $connectionType) try { $query = "SELECT Id,Name,ConnectionType,SourceName,SourceRecordId,IsDeleted,CreatedTime,LastUpdatedTime from Config.Connections with (nolock)" if ($connectionType) { $query = $query + " WHERE ConnectionType = '$connectionType'" } $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dacmDBName -printToConsole Write-Host "Azs connections available in DACM: "$result.Count $result } catch { $exception = $_ Write-Host "Error getting Azs DACM connections" Write-Error $exception } } function Delete-AzsBillingConnection{ param( [Parameter(Mandatory = $true, Position = 0, HelpMessage = "Billing db connection name; Please use Add-SqlDbConnection to create new connection.")] $connectionName, [Parameter(Mandatory = $true, Position = 1)] $billingDBName, [Parameter(Mandatory = $true, Position = 2)] $dacmDBName, [Parameter(Mandatory = $false, Position = 3)] $azsDACMConnectionName, [Parameter(Mandatory = $false, Position = 4)] $azsDACMConnectionId) try { #deleting the connection using dacm connection name or id if ($azsDACMConnectionName) { $query = "UPDATE Config.Connections SET [IsDeleted] = 1, LastUpdatedTime = GETUTCDATE() WHERE [Name] = '$azsDACMConnectionName'" $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dacmDBName -printToConsole Write-Host "Successfully cleared Azs connection in DACM" $query = "SELECT Id FROM Config.Connections with (nolock) WHERE [Name] = '$azsDACMConnectionName'" $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dacmDBName -printToConsole $azsDACMConnectionId = $result.Id } else { $query = "UPDATE Config.Connections SET [IsDeleted] = 1, LastUpdatedTime = GETUTCDATE() WHERE [Id] = $azsDACMConnectionId" $result = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $dacmDBName -printToConsole Write-Host "Successfully cleared Azs connection in DACM" } $query = "UPDATE UsageSource SET [IsDeleted] = 1, UpdatedTime = GETUTCDATE() WHERE [SourceConnectionId] = $azsDACMConnectionId AND [PlatformType] = 'AzsHub' AND [SourceType] = 'Dacm'" $billingQueryResult = Invoke-SqlDbQuery -name $connectionName -query $query -dbName $billingDBName -printToConsole Write-Host "Successfully cleared Azs connection in Billing" } catch { $exception = $_ Write-Host "Error cleaning Azs connection in DACM and Billing" Write-Error $exception } } function Write-ResultDataToCSV($result, $outFileName) { if (!$result) { Write-Host "No data to write" return } Write-Host "Writing result to File: $outFileName" foreach($table in $result) { $table | Export-Csv -Path $outFileName -NoTypeInformation –Append } Write-Host "Export csv completed" } # SIG # Begin signature block # MIIQQAYJKoZIhvcNAQcCoIIQMTCCEC0CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB # gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR # AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUKVW2seKrDwcJzXvrVsaeTUTg # wnCgggz8MIIGcjCCBFqgAwIBAgIIZDNR08c4nwgwDQYJKoZIhvcNAQELBQAwfDEL # MAkGA1UEBhMCVVMxDjAMBgNVBAgMBVRleGFzMRAwDgYDVQQHDAdIb3VzdG9uMRgw # FgYDVQQKDA9TU0wgQ29ycG9yYXRpb24xMTAvBgNVBAMMKFNTTC5jb20gUm9vdCBD # ZXJ0aWZpY2F0aW9uIEF1dGhvcml0eSBSU0EwHhcNMTYwNjI0MjA0NDMwWhcNMzEw # NjI0MjA0NDMwWjB4MQswCQYDVQQGEwJVUzEOMAwGA1UECAwFVGV4YXMxEDAOBgNV # BAcMB0hvdXN0b24xETAPBgNVBAoMCFNTTCBDb3JwMTQwMgYDVQQDDCtTU0wuY29t # IENvZGUgU2lnbmluZyBJbnRlcm1lZGlhdGUgQ0EgUlNBIFIxMIICIjANBgkqhkiG # 9w0BAQEFAAOCAg8AMIICCgKCAgEAn4MTc6qwxm0hy9uLeod00HHcjpdymuS7iDS0 # 3YADxi9FpHSavx4PUOqebXjzn/pRJqk9ndGylFc++zmJG5ErVu9ny+YL4w45jMY1 # 9Iw93SXpAawXQn1YFkDc+dUoRB2VZDBhOmTyl9dzTH17IwJt83XrVT1vqi3Er750 # rF3+arb86lx56Q9DnLVSBQ/vPrGxj9BJrabjQhlUP/MvDqHLfP4T+SM52iUcuD4A # SjpvMjA3ZB7HrnUH2FXSGMkOiryjXPB8CqeFgcIOr4+ZXNNgJbyDWmkcJRPNcvXr # nICb3CxnxN3JCZjVc+vEIaPlMo4+L1KYxmA3ZIyyb0pUchjMJ4f6zXWiYyFMtT1k # /Summ1WvJkxgtLlc/qtDva3QE2ZQHwvSiab/14AG8cMRAjMzYRf3Vh+OLzto5xXx # d1ZKKZ4D2sIrJmEyW6BW5UkpjTan9cdSolYDIC84eIC99gauQTTLlEW9m8eJGB8L # uv+prmpAmRPd71DfAbryBNbQMd80OF5XW8g4HlbUrEim7f/5uME77cIkvkRgp3fN # 1T2YWbRD6qpgfc3C5S/x6/XUINWXNG5dBGsFEdLTkowJJ0TtTzUxRn50GQVi7Inj # 6iNwmOTRL9SKExhGk2XlWHPTTD0neiI/w/ijVbf55oeC7EUexW46fLFOuato95tj # 1ZFBvKkCAwEAAaOB+zCB+DAPBgNVHRMBAf8EBTADAQH/MB8GA1UdIwQYMBaAFN0E # CQei9Xp9UlMSkpXuOIAlDaZZMDAGCCsGAQUFBwEBBCQwIjAgBggrBgEFBQcwAYYU # aHR0cDovL29jc3BzLnNzbC5jb20wEQYDVR0gBAowCDAGBgRVHSAAMBMGA1UdJQQM # MAoGCCsGAQUFBwMDMDsGA1UdHwQ0MDIwMKAuoCyGKmh0dHA6Ly9jcmxzLnNzbC5j # b20vc3NsLmNvbS1yc2EtUm9vdENBLmNybDAdBgNVHQ4EFgQUVML+EJUAk81q9efA # 19myS7iPDOMwDgYDVR0PAQH/BAQDAgGGMA0GCSqGSIb3DQEBCwUAA4ICAQD1DyaH # cK+Zosr11snwjWY9OYLTiCPYgr+PVIQnttODB9eeJ4lNhI5U0SDuYEPbV0I8x7CV # 9r7M6qM9jk8GxitZhn/rcxvK5UAm4D1vzPa9ccbNfQ4gQDnWBdKvlAi/f8JRtyu1 # e4Mh8GPa5ZzhaS51HU7LYR71pTPfAp0V2e1pk1e6RkUugLxlvucSPt5H/5CcEK32 # VrKk1PrW/C68lyGzdoPSkfoGUNGxgCiA/tutD2ft+H3c2XBberpotbNKZheP5/Dn # V91p/rxe4dWMnxO7lZoV+3krhdVtPmdHbhsHXPtURQ8WES4Rw7C8tW4cM1eUHv5C # NEaOMVBO2zNXlfo45OYS26tYLkW32SLK9FpHSSwo6E+MQjxkaOnmQ6wZkanHE4Jf # /HEKN7edUHs8XfeiUoI15LXn0wpva/6N+aTX1R1L531iCPjZ16yZSdu1hEEULvYu # YJdTS5r+8Yh6dLqedeng2qfJzCw7e0wKeM+U9zZgtoM8ilTLTg1oKpQRdSYU6iA3 # zOt5F3ZVeHFt4kk4Mzfb5GxZxyNi5rzOLlRL/V4DKsjdHktxRNB1PjFiZYsppu0k # 4XodhDR/pBd8tKx9PzVYy8O/Gt2fVFZtReVT84iKKzGjyj5Q0QA07CcIw2fGXOho # v88uFmW4PGb/O7KVq5qNncyU8O14UH/sZEejnTCCBoIwggRqoAMCAQICEA0SjRWQ # uYT7eM+eDgHqTTMwDQYJKoZIhvcNAQELBQAweDELMAkGA1UEBhMCVVMxDjAMBgNV # BAgMBVRleGFzMRAwDgYDVQQHDAdIb3VzdG9uMREwDwYDVQQKDAhTU0wgQ29ycDE0 # MDIGA1UEAwwrU1NMLmNvbSBDb2RlIFNpZ25pbmcgSW50ZXJtZWRpYXRlIENBIFJT # QSBSMTAeFw0yMTEwMjUyMDQ1NTNaFw0yMzEwMjUyMDQ1NTNaMHcxCzAJBgNVBAYT # AlVTMRMwEQYDVQQIDApXYXNoaW5ndG9uMRAwDgYDVQQHDAdSZWRtb25kMRkwFwYD # VQQKDBBDbG91ZCBBc3NlcnQgTExDMQswCQYDVQQLDAJVUzEZMBcGA1UEAwwQQ2xv # dWQgQXNzZXJ0IExMQzCCAaIwDQYJKoZIhvcNAQEBBQADggGPADCCAYoCggGBAOr+ # k6LFTYXntOaV4dGI/mIyACrEE3JCr4RP5Aur5QgWuraKhL2JSh63eADxOOuk5P4E # KXhNG8F1XW67gDLNfUlQ9ZagD3+xts/Vc8hZOqmwGw57K0/EUy5RoVVVWntMQ7DX # q0VNp2SgMVHBuRWLvB7MX7OGTJ96+IWgEzMITBxx+bToBl+iefkJhOVZi2lCG9oN # M3i5Yrq2T7cV1uCQwl6JNBrsaCJ64vs6pz8LzR0XmhXtg5rLYehFCqcWYCcH4Njm # ZUVharTmBozLOTPdL6y3UReZRM5J1SxvrfRvalFQGWX4hK6OirBey1yPnhzqNHAt # iwCLxn5l+pnTh89LLmtc1Bp8OI2nN7yaiXK13441EQFpIYnBSQJ6e8n0dDpwwoux # OSfxtgX8iila0DBoy9vLCyGTnyXdO1zZYGoll9v8aSbvWOZu4n4gvQPVIhgROU74 # wkfGXI61Ab9ZtltF5W5WQesJoDiRIYgHUxYWU5fsTPzsoQFIXzHyaTqeJKXOtwID # AQABo4IBhzCCAYMwHwYDVR0jBBgwFoAUVML+EJUAk81q9efA19myS7iPDOMwegYI # KwYBBQUHAQEEbjBsMEgGCCsGAQUFBzAChjxodHRwOi8vY2VydC5zc2wuY29tL1NT # TGNvbS1TdWJDQS1Db2RlU2lnbmluZy1SU0EtNDA5Ni1SMS5jZXIwIAYIKwYBBQUH # MAGGFGh0dHA6Ly9vY3Nwcy5zc2wuY29tMFEGA1UdIARKMEgwCAYGZ4EMAQQBMDwG # DCsGAQQBgqkwAQMDATAsMCoGCCsGAQUFBwIBFh5odHRwczovL3d3dy5zc2wuY29t # L3JlcG9zaXRvcnkwEwYDVR0lBAwwCgYIKwYBBQUHAwMwTQYDVR0fBEYwRDBCoECg # PoY8aHR0cDovL2NybHMuc3NsLmNvbS9TU0xjb20tU3ViQ0EtQ29kZVNpZ25pbmct # UlNBLTQwOTYtUjEuY3JsMB0GA1UdDgQWBBT5QOeOXNcPYtBWMGBY9lkdLp4AczAO # BgNVHQ8BAf8EBAMCB4AwDQYJKoZIhvcNAQELBQADggIBAGL909UmLyhbmLPe0AyH # mItkDXXIonmIsCrNrquwtFB5ZFhV2eQEEcFi8N+R1Pw2CGWNQe8EGN83nr1ItDNc # JqweHvadc6i5FF1DVRPKEVHzORKKsKGZ97KyYQkT+YxJLfVCLdFCemCd2QYQuFJQ # 4LdKcR9QZE0LvoiE9qVZ0fv2oO/4Yg/jgFTS4m1znT1IXIfCgnxfK9dr5QwQt/wX # 3ayq554Ptbl7f6g9AGnD3U7cEaDvaPqRX16AGgxWbJU4W740UeNZnsFvdNcBHY/7 # wWxCzR03dzTGivW1aozokn05KeOyF0ZU7vhhXSeKyoaLzJXEr96r7pBUfBlVL9p9 # 6IVsHxsnPGFVZiaaZ0YQFsBWJZLEpVOIXCl2Jb2KX/NshRJGeijK0a6msVYIHKPv # mhLnDruJkadj4RIgk8AQ2wsttUWtjWRKjD072OnAVZatRsCPIPQJsk+8gSKqfDZR # o3DZhnrCd6TfjuoU9aULSXrwJljrOqLNOZHFoBuT7y3dZHPoo596yCmwUs+7dYCR # nBU+hQ0Fca9aWpaYw4lKdxxhXn66EIR00TbaE3HYdHhlOc8koA9VUI/eiWdd1rKL # j67luXYkCEJ37fE6SlyL1Jkhu3dd79+GSYlTINRnH415fH4DwiOMckj8kRbdyRV1 # tT1R5QeVMAdZHzQ80j8shEydMYICrjCCAqoCAQEwgYwweDELMAkGA1UEBhMCVVMx # DjAMBgNVBAgMBVRleGFzMRAwDgYDVQQHDAdIb3VzdG9uMREwDwYDVQQKDAhTU0wg # Q29ycDE0MDIGA1UEAwwrU1NMLmNvbSBDb2RlIFNpZ25pbmcgSW50ZXJtZWRpYXRl # IENBIFJTQSBSMQIQDRKNFZC5hPt4z54OAepNMzAJBgUrDgMCGgUAoHgwGAYKKwYB # BAGCNwIBDDEKMAigAoAAoQKAADAZBgkqhkiG9w0BCQMxDAYKKwYBBAGCNwIBBDAc # BgorBgEEAYI3AgELMQ4wDAYKKwYBBAGCNwIBFTAjBgkqhkiG9w0BCQQxFgQUoi9H # 4hZTERPOJ98ULWqZ75+UZ2kwDQYJKoZIhvcNAQEBBQAEggGAgk1M6PVn8+Lrvg6P # ghOHI7MkOGqWXPSJ8229NMyWIZ4eY8n9VTzFhbx2R8Pc80pAtZAlLpK9ngmYAT7q # ExN4A52O8wmEF3C6Mk7mtwmeIUT5dnw83MrgX2LOQoz3HXZvB8qVf8YoUHcHw48h # iqw806aIlX1bE45Vh7SBmh89G3HEdbKDxsibUN/NY91R1RKcmWILYl+/Nm7bXq1b # E6ZwNrPk6YAzv2bY7K9wsoPQ+5Gg6KZllcrrT4z8uYpJ3XtU5EpvoIRQiOHheSrz # UAa2cKzk2QIgd8O1FpWY+ueoViEIKDmZ+MBx4Y5p/1BqqMsUXON3QV6MCHTIWFlE # tMLNzJxFtYbbCbG3yZIENfoQf6eC1vcHYX103UOnA0ErxSO0JbCISTUdjw/VeB1l # SSQCq08wURO4kJ8rI4eEWoZKW1FsFMDwmbzUfYf+xkBmLmK4/HKTuz+6IFkRbtF1 # Ilw3Wqljir0nCf/eEW+Z6qSc8cpR0dvPYNmOVwz77ERs0YYY # SIG # End signature block |