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, [Parameter(Mandatory = $false, Position = 2)] $outputFileName, [Parameter(Mandatory = $false, Position = 3)] $outputFilePath, [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 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 = $false, Position = 3)] $dbName, [Parameter(Mandatory = $false, Position = 4)] $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 DELETE FROM [dbo].[BillingInvoiceSendInfos] DELETE FROM [dbo].[BillingInvoiceLineItems] WHERE StartTime >= @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 ---------CSP License------- UPDATE [dbo].[UsageProcessingStates] SET [LastProcessedTime] = @lastprocessedtime WHERE StateName = 'CspLicenseLogProcessor' 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 belo 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 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 RecordId >= @lastAzsHubCostRecordId DECLARE @lastCspAzureCostRecordId INT SELECT TOP 1 @lastCspAzureCostRecordId = RecordId FROM [$costManagementDBName].Source.CspAzureInvoiceLineItems WHERE ChargeStartDate >= @lastProcessedTime ORDER BY RecordId DELETE from [dbo].[CspCostRecordProcessingStatus] WHERE RecordId >= @lastCspAzureCostRecordId DECLARE @lastCspLicenseCostRecordId INT SELECT TOP 1 @lastCspLicenseCostRecordId = RecordId FROM [$costManagementDBName].Source.CspLicenseInvoiceLineItems WHERE ChargeStartDate >= @lastProcessedTime ORDER BY RecordId DELETE from [dbo].CspLicenseCostRecordProcessingStatus WHERE RecordId >= @lastCspAzureCostRecordId DECLARE @lastCspEstimateCostRecordId INT SELECT TOP 1 @lastCspEstimateCostRecordId = RecordId FROM [$costManagementDBName].[Source].[CspAzureUnBilledLineItems] WHERE ChargeStartDate >= @lastProcessedTime ORDER BY RecordId DELETE from [dbo].CspCostEstimateRecordProcessingStatus WHERE 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 } } # SIG # Begin signature block # MIIQQAYJKoZIhvcNAQcCoIIQMTCCEC0CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB # gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR # AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUz+XQN7Sp3TW1nSFQQ8LK5tm7 # fiCgggz8MIIGcjCCBFqgAwIBAgIIZDNR08c4nwgwDQYJKoZIhvcNAQELBQAwfDEL # 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 # BgorBgEEAYI3AgELMQ4wDAYKKwYBBAGCNwIBFTAjBgkqhkiG9w0BCQQxFgQUSQSY # xfym03Iko1qJ1EVk8jEcDF4wDQYJKoZIhvcNAQEBBQAEggGAfJbu4fk1zizWiWur # s/XyLbD7oznTvXWRct6KuVp+/I+xu3Mh3UzdBAJXnbAFlSZ4CQFACdP3Z8a8Z0ld # CGAoAcE1xqdVXgzrZCLji9ILXaZnuyWlobflXSAC3vxHZ6kMs/dsSV2TC98C+YN9 # EieZAYTFhC0rEWgHpL6jTfilr8ZcMAZL++8npJqRZG9FDXpxpx+lxTmxZXzsE7f7 # DyVYbPI2dWvDtf+N3Nh3T0LZ/PL+Otuu06FdC+ZKfcUvQ2PBLZp8/Tk/xy8CHGEv # ALmcUHd5HEapI4mqSVUCadYC/92cqdybRGotXtP8f6SMuvYazZWyxmc/aYSZ0Q1l # BYqyuRRUy8HXhMsCWI4j0+Xs6v6gzcfXsUMBuxSlRlFzb93o08jvuAbQKHeDjhyK # p0XlvKI0HHiA9vtjCvQGBSl6VPP5XGcuEAY8a0IGzlUrZW7o7WlIG67ltJ3Iqevu # DCVI+NrQLo/M63JAWeIY9iOXjJ1l9qrD6dYcqZTyntIzgdoH # SIG # End signature block |