CloudAssert.Hybr

2023.5.23.0

Hybr.psm1

                                
# Instal Pre-Req Modules

if ((Get-InstalledModule -Name "CloudAssert.Common" -ErrorAction SilentlyContinue) -eq $null) {

    #-MinimumVersion 5.0 # Optionally specify minimum version to have

    Install-Module -Name CloudAssert.Common

} else {

    Update-Module -Name CloudAssert.Common -Force

}

# For Adding a Job to Purge the Hybr Tables on a weekly basis

function AddOrUpdate-SqlHybrTablesCleanUpJob {

    param($connectionName)

    if($connectionName -eq $null) {

        $connectionName =  Read-Input -message "Enter SQL connection name"

    }

    $sqlDbConn = Get-SqlDbConnection $connectionName

    $decoded = Invoke-Decode $sqlDbConn.Password

    $hybrDb = "Hybr";

    $dbServerName = $sqlDbConn.DbServerName;

    $backupScriptBlobUrl = '"https://cadownloads.blob.core.windows.net/plugins/BackupEventNotifications.ps1"';

    if(!(Get-YesOrNoUserResponse -message "Is Hybr database name - $hybrDb ?")) {

        $hybrDb = Read-Input -message "Enter Hybr database name"

    }

    $oldRecDuration = Read-Input -message "Enter how much old data need to be purged in months"

    $backupPath = $null;

    do

    {

        $backupPath = Read-Input -message "Enter Path to Backup EventNotifications(Eg: C:\EventsBackup)"

        if(($backupPath -replace "[^\s]").length -gt 0)

        {

            Write-Host "Backup path cannot contain path name with spaces inbetween. Enter Path name with no spaces"

        }

    }until(($backupPath -replace "[^\s]").length -eq 0)

    $proxyName = Read-Input -message "Enter Proxy Name created for powershell execution"

    $backUpScriptPath = '"' + $backupPath + "\Assets_DonotDelete" + '"';

    $backUpScriptFileName = '"' + $backupPath + "\Assets_DonotDelete\BackupEventNotifications.ps1" + '"';

    if(!(Test-Path -PathType container $backupPath))

    {

        New-Item -ItemType Directory -Path $backupPath

    }

    $endDate = (Get-Date).AddMonths(-$oldRecDuration).ToString('yyyy-MM-dd');

    $query = "  USE msdb ;

                GO

                IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name='Weekly_Hybr_Tables_Cleanup')

                BEGIN

                    EXEC sp_delete_jobschedule @name = N'Weekly_Hybr_Cleanup_Schedule', @job_name = N'Weekly_Hybr_Tables_Cleanup'

                    EXEC msdb.dbo.sp_delete_job  @job_name = N'Weekly_Hybr_Tables_Cleanup', @delete_unused_schedule = 1

                END

                IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name='Weekly_Hybr_Tables_Cleanup')

                BEGIN

                    EXEC dbo.sp_add_job  @job_name = N'Weekly_Hybr_Tables_Cleanup';

                END

                EXEC sp_add_jobstep

                    @job_name = N'Weekly_Hybr_Tables_Cleanup',

                    @step_name = N'Hybr Logs Table Cleanup',

                    @subsystem = N'TSQL',

                    @command = N'Declare @enddate date = DATEADD(MONTH, -$oldRecDuration, GETDATE())

                                WHILE 1 = 1

                                BEGIN

                                    DELETE TOP (1000)

                                    FROM Logs 

                                    WHERE CreatedDate <= @endDate;

                                    IF @@rowcount < 1000

                                        BREAK;

                                END',

                    @retry_attempts = 5,

                    @retry_interval = 5,

                    @on_success_action = 3,

                    @on_fail_action = 3,

                    @database_name = '$hybrDb';

                GO

                EXEC sp_add_jobstep

                    @job_name = N'Weekly_Hybr_Tables_Cleanup',

                    @step_name = N'Hybr Notifications Table Cleanup',

                    @subsystem = N'TSQL',

                    @command = N'Declare @enddate date = DATEADD(MONTH, -$oldRecDuration, GETDATE())

                                WHILE 1 = 1

                                BEGIN

                                    DELETE TOP (1000)

                                    FROM Notifications 

                                    WHERE CompletedDate <= @endDate;

                                    IF @@rowcount < 1000

                                        BREAK;

                                END',

                    @retry_attempts = 5,

                    @retry_interval = 5,

                    @on_success_action = 3,

                    @on_fail_action = 3,

                    @database_name = '$hybrDb';

                GO

                EXEC sp_add_jobstep

                    @job_name = N'Weekly_Hybr_Tables_Cleanup',

                    @step_name = N'Hybr EventNotificationStateChanges Table Cleanup',

                    @subsystem = N'TSQL',

                    @command = N'Declare @enddate date = DATEADD(MONTH, -$oldRecDuration, GETDATE())

                                WHILE 1 = 1

                                BEGIN

                                    DELETE TOP (1000)

                                    FROM EventNotificationStateChanges 

                                    WHERE Timestamp <= @endDate;

                                    IF @@rowcount < 1000

                                        BREAK;

                                END',

                    @retry_attempts = 5,

                    @retry_interval = 5,

                    @on_success_action = 3,

                    @on_fail_action = 3,

                    @database_name = '$hybrDb';

                GO

                EXEC sp_add_jobstep

                    @job_name = N'Weekly_Hybr_Tables_Cleanup',

                    @step_name = N'Hybr Trace Table Cleanup',

                    @subsystem = N'TSQL',

                    @command = N'Declare @enddate date = DATEADD(MONTH, -$oldRecDuration, GETDATE())

                                WHILE 1 = 1

                                BEGIN

                                    DELETE TOP (1000)

                                    FROM Trace 

                                    WHERE CreatedDate <= @endDate;

                                    IF @@rowcount < 1000

                                        BREAK;

                                END',

                    @retry_attempts = 5,

                    @retry_interval = 5,

                    @on_success_action = 3,

                    @on_fail_action = 3,

                    @database_name = '$hybrDb';

                GO

                EXEC sp_add_jobstep

                    @job_name = N'Weekly_Hybr_Tables_Cleanup',

                    @step_name = N'Hybr Events Table Cleanup',

                    @subsystem = N'TSQL',

                    @command = N'Declare @enddate date = DATEADD(MONTH, -$oldRecDuration, GETDATE())

                                WHILE 1 = 1

                                BEGIN

                                    DELETE TOP (1000)

                                    FROM Events 

                                    WHERE CreatedTimestamp <= @endDate;

                                    IF @@rowcount < 1000

                                        BREAK;

                                END',

                    @retry_attempts = 5,

                    @retry_interval = 5,

                    @on_success_action = 3,

                    @on_fail_action = 3,

                    @database_name = '$hybrDb';

                GO

                EXEC sp_add_jobstep

                    @job_name = N'Weekly_Hybr_Tables_Cleanup',

                    @step_name = N'Copy Event Notification backup script from blob if not exists',

                    @subsystem = N'PowerShell',

                    @command = 'if(!(Test-Path -PathType container $backUpScriptPath))

                    {

                        New-Item -ItemType Directory -Path $backUpScriptPath

                    }

                    if(Test-Path($backUpScriptFileName))

                    {

                        return

                    }

                    Invoke-WebRequest -URI $backupScriptBlobUrl -OutFile $backUpScriptFileName',

                    @retry_attempts = 5,

                    @on_success_action = 3,

                    @on_fail_action = 1,

                    @proxy_name= '$proxyName',

                    @database_name = '$hybrDb';

                GO

                EXEC sp_add_jobstep

                    @job_name = N'Weekly_Hybr_Tables_Cleanup',

                    @step_name = N'Hybr EventNotifications Table Backup',

                    @subsystem = N'PowerShell',

                    @command = N'powershell $backUpScriptFileName $dbServerName $hybrDb $oldRecDuration $backupPath',

                    @retry_attempts = 5,

                    @on_success_action = 3,

                    @on_fail_action = 1,

                    @database_name = '$hybrDb';

                GO

                EXEC sp_add_jobstep

                    @job_name = N'Weekly_Hybr_Tables_Cleanup',

                    @step_name = N'Hybr EventNotifications Table Cleanup',

                    @subsystem = N'TSQL',

                    @command = N'Declare @enddate date = DATEADD(MONTH, -$oldRecDuration, GETDATE())

                                WHILE 1 = 1

                                BEGIN

                                    DELETE TOP (1000)

                                    FROM EventNotifications 

                                    WHERE CreatedTimestamp <= @endDate 

                                    AND NotificationState <> 0;

                                    IF @@rowcount < 1000

                                        BREAK;

                                END',

                    @retry_attempts = 5,

                    @retry_interval = 5,

                    @on_success_action = 1,

                    @database_name = '$hybrDb';

                GO

                EXEC dbo.sp_add_schedule

                    @schedule_name = N'Weekly_Hybr_Cleanup_Schedule',

                    @freq_type = 8,

                    @freq_interval = 64,

                    @freq_recurrence_factor = 1,

                    @active_start_time = 200000 ;

                EXEC sp_attach_schedule

                    @job_name = N'Weekly_Hybr_Tables_Cleanup',

                    @schedule_name = N'Weekly_Hybr_Cleanup_Schedule';

                GO  

                EXEC dbo.sp_add_jobserver

                    @job_name = N'Weekly_Hybr_Tables_Cleanup';"

    $dbName = 'master'

    Invoke-Sqlcmd -ServerInstance $sqlDbConn.DbServerName -Database $dbName -Username $sqlDbConn.User -Password "$decoded" -Query $query

 }

 function Delete-SqlHybrTablesCleanUpJob {

    param($connectionName)

    if($connectionName -eq $null) {

        $connectionName =  Read-Input -message "Enter SQL connection name"

    }

    $sqlDbConn = Get-SqlDbConnection $connectionName

    $decoded = Invoke-Decode $sqlDbConn.Password

    $query = "  USE msdb ;

                GO

                IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name='Weekly_Hybr_Tables_Cleanup')

                BEGIN

                    EXEC sp_delete_jobschedule @name = N'Weekly_Hybr_Cleanup_Schedule', @job_name = N'Weekly_Hybr_Tables_Cleanup'

                    EXEC msdb.dbo.sp_delete_job  @job_name = N'Weekly_Hybr_Tables_Cleanup', @delete_unused_schedule = 1

                END "

    $dbName = 'master'

    Invoke-Sqlcmd -ServerInstance $sqlDbConn.DbServerName -Database $dbName -Username $sqlDbConn.User -Password "$decoded" -Query $query

 }

# SIG # Begin signature block

# MIIQQAYJKoZIhvcNAQcCoIIQMTCCEC0CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB

# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR

# AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUnSflUBOIvd3Rhs++V7+DRaJ5

# uSygggz8MIIGcjCCBFqgAwIBAgIIZDNR08c4nwgwDQYJKoZIhvcNAQELBQAwfDEL

# 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

# BgorBgEEAYI3AgELMQ4wDAYKKwYBBAGCNwIBFTAjBgkqhkiG9w0BCQQxFgQUIOWU

# puKWcVlUkRZ4gbOWHvmP64AwDQYJKoZIhvcNAQEBBQAEggGAGK1dobd2Owvmc0tl

# KppX7fUrPSA7Y76zk6Ja0+0DYI9ul96w7g9M6+dmh/2jCXK3W9GyJN9AQVkG8dup

# kgl1vlLs/686PKwBDxlY90FhVrmrBNDoiZWtdNELGyj+C1btpWQbGmDa6KWG+Tsk

# ATMhJGMpeK9BqOcvwYV2r3QBHVn00J6EF/rSt/IkKmuwOwgTaq5FCtOGUafRoAVw

# yWB4NJYFb0qRIexUlgSeRWbRd9N56X+YKAjf7c7aTMRZAn32kgc0+WXJQxPlU1g4

# VV21TmM65AZky2h/3EdbQb27XHouUJT5fALbyD1UbjCsZ2/U5nNqLqJyz/Is3aIq

# gfYeY/IEFtO7vn1HyhF3bVGL1FFU5hAMiecRrEL1VGMORO19CHrTpjvL9+XVwsjk

# 0JePff9QDFM43m+xIBxBISgpghj0shxpaMEyocal1rI/tPSejbyNuNcApRTJZ1+g

# TkSpRErlfvYqB3VDvhVxZNYltMOxj+q1w/IFHI1+x1CUboXw

# SIG # End signature block