Powershell and T-sql

Question

Powershell and T-sql

Sara 441

This is probably a follow up question, I have a PS code to collect and store the status of services in a SQl table. Looking for some help to send an email alert by reading it from the sql tables like if the state column has an entry as "down" , send an email alert with the names from all the 3 tables alert_script.txt

Accepted answer

2 additional answers

Your answer

Answer 1

You need to do a bit more work to get that message body to look the way you'd like it to. Here's one simple way:

[CmdletBinding()]
param(
    [Parameter(Mandatory=$false)]
    [string]$Email = "test.com"
)
Begin {
    $modules = @(
        "secretserver"
        "automation"
    )
    # Load  modules if not present
    if (-not (get-module $modules)) {
        Import-AHModule $modules -ErrorAction Stop
    }
    Import-Module "SqlServer","dbatools","NetScaler" -Verbose:$false -ErrorAction Stop

    # Name of SQL Instance where database is located
    $SqlInstance = "dbserv"

    # Name of the database
    $DbName = "test"

    # Name of the schema in which the above database is in
    $Schema = "dbo"

    # connect to NetScalers using the login nsexsremetrics
    if ($env:Winusername) {
        Write-verbose "$(Get-date): Creating Secret Server Login" -Verbose
        $SecurePassword = ConvertTo-SecureString $env:WinPassword -AsPlainText -Force -ErrorAction Stop
        $SSCred = New-Object System.Management.Automation.PSCredential ($env:WinUserName, $SecurePassword) -ErrorAction Stop
    }
    $NScred = Get-SecretServerCredential -SamAccountName "nsexs" -OperatorCredentials $SSCred -ErrorAction Stop -Verbose:$false

    # Get Primary NetScalers
    $Netscalers = Get-Primary | select-object -ExpandProperty DNSName
    
    $props =    [PSCustomObject]@{Table = "NetScaler_Servicegroup";NSStatType = "servicegroup";NSStatProps = ("name,servicegroupname,effectivestate,servicetype,state" -split ',')},
                [PSCustomObject]@{Table = "NetScaler_service";     NSStatType = "service";     NSStatProps = ("name,primaryipaddress,servicetype,state" -split ',')},
                [PSCustomObject]@{Table = "NetScaler_stat";        NSStatType = "lbvserver";   NSStatProps = ("name,primaryipaddress,type,state" -split ',')}

    $Message = "Check the below Confluence document to start investigating the alert"
    
    [array]$DownServices = @()
}
Process {
    # Collect information from three sets of parameters, for all servers
    ForEach ($p in $props){
        # Connect to each netscaler and insert its stats into the database table
        foreach ($NetScaler in $NetScalers) {
            Connect-NetScaler -Hostname $NetScaler -Credential $NScred
            $s = Get-NSStat -Type $p.NSStatType |
                    Select-Object $p.NSStatProps
            if ($s.name -notlike "*test*"){
                if ($s.state -eq 'down'){
                    $DownServices += $s
                }
                $s | Write-SqlTableData -ServerInstance $SqlInstance -DatabaseName $DbName -SchemaName $Schema -TableName $p.Table -Force
            }
        }
    }
    if ($DownServices.Count -gt 0){
        $mailprops=@{
            From        = "******@domain.com"
            To          = "******@domain.com"
            Subject     = "Services found to be 'down'"
            SmtpServer  = "******@domain.com"
            Port        = 587
            UseSsl      = $true
            Body        = ""
            BodyAsHtml  = $true
        }
        $table = $DownServices | ConvertTo-Html -Fragment
$body = @"
<html>
    <body>
        <p>$Message</p>
        $table
    </body
</html>
"@
        $mailprops.Body = $body
        Send-MailMessage @mailprops
    }
}

Answer 2

You'll have to change the parameters for the Send-MailMessage to match your email system. The message body will probably look ugly, but you can substitute your own HTML formatting if you like.

Give this a try:

[CmdletBinding()]
param(
    [Parameter(Mandatory=$false)]
    [string]$Email = "test.com"
)
Begin {
    $modules = @(
        "secretserver"
        "automation"
    )
    # Load  modules if not present
    if (-not (get-module $modules)) {
        Import-AHModule $modules -ErrorAction Stop
    }
    Import-Module "SqlServer","dbatools","NetScaler" -Verbose:$false -ErrorAction Stop

    # Name of SQL Instance where database is located
    $SqlInstance = "dbserv"

    # Name of the database
    $DbName = "test"

    # Name of the schema in which the above database is in
    $Schema = "dbo"

    # connect to NetScalers using the login nsexsremetrics
    if ($env:Winusername) {
        Write-verbose "$(Get-date): Creating Secret Server Login" -Verbose
        $SecurePassword = ConvertTo-SecureString $env:WinPassword -AsPlainText -Force -ErrorAction Stop
        $SSCred = New-Object System.Management.Automation.PSCredential ($env:WinUserName, $SecurePassword) -ErrorAction Stop
    }
    $NScred = Get-SecretServerCredential -SamAccountName "nsexs" -OperatorCredentials $SSCred -ErrorAction Stop -Verbose:$false

    # Get Primary NetScalers
    $Netscalers = Get-Primary | select-object -ExpandProperty DNSName
    
    $props =    [PSCustomObject]@{Table = "NetScaler_Servicegroup";NSStatType = "servicegroup";NSStatProps = ("name,servicegroupname,effectivestate,servicetype,state" -split ',')},
                [PSCustomObject]@{Table = "NetScaler_service";     NSStatType = "service";     NSStatProps = ("name,primaryipaddress,servicetype,state" -split ',')},
                [PSCustomObject]@{Table = "NetScaler_stat";        NSStatType = "lbvserver";   NSStatProps = ("name,primaryipaddress,type,state" -split ',')}

    $mailprops=@{
        From        = "******@domain.com"
        To          = "******@domain.com"
        Subject     = "Services found to be 'down'"
        SmtpServer  = "******@domain.com"
        Port        = 587
        UseSsl      = $true
        Body        = ""
        BodyAsHtml  = $true
    }
    [array]$DownServices = @()
}
Process {
    # Collect information from three sets of parameters, for all servers
    ForEach ($p in $props){
        # Connect to each netscaler and insert its stats into the database table
        foreach ($NetScaler in $NetScalers) {
            Connect-NetScaler -Hostname $NetScaler -Credential $NScred
            $s = Get-NSStat -Type $p.NSStatType |
                    Select-Object $p.NSStatProps
            if ($s.name -notlike "*test*"){
                if ($s.state -eq 'down'){
                    $DownServices += $s
                }
                $s | Write-SqlTableData -ServerInstance $SqlInstance -DatabaseName $DbName -SchemaName $Schema -TableName $p.Table -Force
            }
        }
    }
    if ($DownServices.Count -gt 0){
        $mailprops.Body = $DownServices
        Send-MailMessage @mailprops
    }
}

Answer 3

Thanks @Rich Matheisen , This was much helpful, it just fails with this error while trying to send a email. I tried modifying the script as below by using out-string but it still fails

               $Message = "Check the below Confluence document to start investigating the alert"
   
   $mailprops=@{
                smtpServer = "hub.corp.test.com"
                From       = "******@test.com"
                To         = "******@test.com"
                Subject    = "service is down"
                BodyAsHTML = $true
                Body       = $message | out-string

User's image

Share via

Powershell and T-sql

2 additional answers

Your answer