SQLDBATools

0.0.12

Dependencies/Read-Me.ps1

                                # Cleanup

Get-Variable Sdt* | Remove-Variable

Remove-Module SQLDBATools

cls

Get-SdtServers -Verbose

#$servers = @('SqlDr1','SqlProd1')

$servers = $SdtFriendlyNameList

Alert-SdtDiskSpace -ComputerName $servers -WarningThresholdPercent 65 -CriticalThresholdPercent 80 -DelayMinutes 1 -Verbose -Debug

# Copy files from b/w directories. Ensure not to add '\' at end of path

cls

$srcPath = "C:\Users\$($env:USERNAME)\Documents\WindowsPowerShell\Modules\SQLDBATools"

#$dstPath = "C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.7"

#$srcPath = "C:\Users\Public\Documents\GitHub\SQLDBATools"

#$dstPath = "C:\Users\$($env:USERNAME)\Documents\WindowsPowerShell\Modules\SQLDBATools"

#$dstPath = "C:\Users\Public\Documents\WindowsPowerShell\Modules\SQLDBATools"

$dstPath = "C:\Users\adwivedi\Documents\SQLDBATools"

robocopy $srcPath $dstPath /e /is /it /MT:4 /XD Private Logs

# Import module by manual path specification

Import-Module SQLDBATools -DisableNameChecking

Import-Module "C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.7\SQLDBATools" -DisableNameChecking

Import-Module "C:\Users\$($env:USERNAME)\Documents\WindowsPowerShell\Modules\SQLDBATools" -DisableNameChecking

# Unblock files if getting untrusted non signed warnings

Get-ChildItem -Recurse | Unblock-File

# Test function

Get-SdtServers -Verbose

cls

& 'C:\Users\Public\Documents\GitHub\SQLDBATools\Wrapper\Wrapper-SdtTestCommand.ps1' -ComputerName 'SqlProd2' -Verbose

cls

& 'C:\Users\Public\Documents\GitHub\SQLDBATools\Wrapper\Wrapper-SdtDiskSpace.ps1' `

        -DelayMinutes 2 -WarningThresholdPercent 50 -CriticalThresholdPercent 85 `

        -Verbose -Debug

cls

Get-SdtServers -Verbose

$servers = @('SqlDr1','SqlProd1')

#$servers = $SdtFriendlyNameList

& "C:\Users\$($env:USERNAME)\Documents\WindowsPowerShell\Modules\SQLDBATools\Wrapper\Wrapper-SdtDiskSpace.ps1" `

        -DelayMinutes 1 -WarningThresholdPercent 65 -CriticalThresholdPercent 85 `

        -ComputerName $servers -Verbose `

        -Debug

cls

Get-SdtServers -Verbose

$servers = @('SqlDr1','SqlProd1')

Alert-SdtDiskSpace -ComputerName $servers -WarningThresholdPercent 60 -CriticalThresholdPercent 85 -DelayMinutes 1 -Verbose -Debug

# CmdExec Step Type with below format of Script Call. Try both of these methods in command prompt first

powershell.exe -executionpolicy bypass -Noninteractive C:\Program` Files\WindowsPowerShell\Modules\SQLDBATools\0.0.8\Wrapper\Wrapper-SdtDiskSpace.ps1 -WarningThresholdPercent 30 -CriticalThresholdPercent 50 -DelayMinutes 2

powershell.exe -executionpolicy bypass C:\Program` Files\WindowsPowerShell\Modules\SQLDBATools\0.0.8\Wrapper\Wrapper-SdtTestCommand.ps1 -ComputerName 'SqlProd1'

# Powershell Step Type with below format of Script Call => Working

Invoke-Command -ScriptBlock { & "C:\Users\Public\Documents\WindowsPowerShell\Modules\SQLDBATools\Wrapper\Wrapper-SdtTestCommand.ps1" -ComputerName 'SqlProd1'}

Invoke-Command -ScriptBlock { & 'C:\Program Files\WindowsPowerShell\Modules\SQLDBATools\0.0.8\Wrapper\Wrapper-SdtDiskSpace.ps1' -WarningThresholdPercent 30 -CriticalThresholdPercent 50 -DelayMinutes 5 }

<#

use DBA

go

select GETDATE() as srv_time, GETUTCDATE() as utc_time, *

from dbo.sdt_server_inventory

go

select DATEDIFF(minute,last_notified_date_utc,GETUTCDATE()) as last_notified_minutes, 

        [is_suppressed_valid] = case when state = 'Suppressed' and (GETUTCDATE() between a.suppress_start_date_utc and a.suppress_end_date_utc) then 1 else 0 end,

        *

--update a set [state] = 'Suppressed', suppress_start_date_utc = GETUTCDATE(), suppress_end_date_utc = DATEADD(minute,20,GETUTCDATE())

--update a set [state] = 'Suppressed', suppress_end_date_utc = DATEADD(minute,2,suppress_start_date_utc)

--delete a

from dbo.sdt_alert a with (nolock)

--where alert_key = 'Alert-SdtDiskSpace'

order by created_date_utc desc

-- truncate table dbo.sdt_alert

go

select *

from dbo.sdt_alert_rules ar

go

/*

insert dbo.sdt_alert_rules (alert_key, server_friendly_name, severity, alert_receiver, alert_receiver_name, reference_request)

select 'Alert-SdtDiskSpace','SqlProd1',NULL,'ajay.dwivedi2007@gmail.com','Ajay','Testing'

union all

select 'Alert-SdtDiskSpace','SqlDr1',NULL,'ajay.dwivedi2007@gmail.com','Ajay','Testing'

*/

/*

if object_id('tempdb..#sdt_alert_rules_by_server') is not null

    drop table #sdt_alert_rules_by_server;

if object_id('tempdb..#sdt_alert_rules_by_owner') is not null

    drop table #sdt_alert_rules_by_owner;

select ar.rule_id, ar.alert_key, ar.server_friendly_name, i.server_owner, ar.alert_receiver

into #sdt_alert_rules_by_server

from dbo.sdt_alert_rules ar left join dbo.sdt_server_inventory i on i.friendly_name = ar.server_friendly_name

where ar.alert_key = 'Alert-SdtDiskSpace'

and ar.server_friendly_name in ('SqlProd1','SqlDr1','SqlProd2','Sqldr2','SqlProd3','SqlDr3')

select ar.rule_id, ar.alert_key, ar.server_friendly_name, i.server_owner, ar.alert_receiver

into #sdt_alert_rules_by_owner

from dbo.sdt_server_inventory i left join dbo.sdt_alert_rules ar on i.server_owner = ar.server_owner

where ar.alert_key = 'Alert-SdtDiskSpace'

and i.friendly_name in ('SqlProd1','SqlDr1','SqlProd2','Sqldr2','SqlProd3','SqlDr3')

select *

from #sdt_alert_rules_by_server s

select *

from #sdt_alert_rules_by_owner o

*/

#>