How to send an email for me ? (my email vpmaciel@live.com)

  • Function Get-ServiceInfo {

    Param(

    [String]$Server,

    [String]$SQLInstance,

    [String]$SQLService,

    [String]$SMTP,

    [String]$To

    )

    # Get the current datetime

    $logdate = (Get-Date).ToString('MM/dd/yyyy hh:mm:ss')

    #Function created to send email

    Function sendEmail

    {

    param($from,$to,$subject,$smtp,$body)

    [string]$receipients="$to"

    $body = $body

    $body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body

    $smtpServer = $smtp

    $smtp = new-object Net.Mail.SmtpClient($smtpServer)

    $smtp.Send($body)

    }

    #Test the server connection

    if((test-connection -ComputerName $Server -count 1 -ErrorAction SilentlyContinue))

    {

    #Check the SQL Service

    $service = Get-WmiObject Win32_Service -ComputerName $server |where-object {$_.name -eq "$SQLService" -and $_.State -eq 'Running'}

    #Check for the instance availability

    if ($Service -ne $NULL)

    {

    #Query the tempdb database creation time

    $result = Invoke-Sqlcmd -Query "SELECT create_date FROM sys.databases WHERE NAME = 'tempdb'" -ServerInstance $SQLInstance

    if ($result)

    {

    Write-Host "tempdb creation time is $($result.create_date) SQL connection to $SQLInstance"

    $crdate=$($result.create_date)

    $props += New-Object PSObject -Property @{

    Servername = $server

    name = $service.name

    tempdbCreationTime=$crdate.ToString('MM/dd/yyyy hh:mm:ss')

    Status = $service.Status

    startmode = $service.startmode

    state = $service.state

    serviceaccount=$service.startname

    DisplayName =$service.displayname

    LogDate=$logdate

    }

    }

    }

    else

    {

    $props += [pscustomobject]@{

    Servername = $server

    tempdbCreationTime='NA'

    name = 'MSSQLServer'

    Status = 'Not Running'

    startmode = 'NA'

    state = 'NA'

    serviceaccount='NA'

    DisplayName ='SQL Server'

    LogDate=$logdate}

    $status = "Critical"

    $priority = "HIGH"

    $body = @"

    This is to Notify that SQL Server service is not running!

    On the $server, the SQL $SQLInstance instance, the servic$SQLService service is Stopped. Please assign an $priority priority ticket to the Respective DBA team.

    -This is an auto generated email generated by the PowerShell script. Please do not reply!!

    "@

    sendEmail pjayaram@sqlShack.com "$to" "SQL Service On $server is not running!" $SMTP $body

    }

    }

    else

    {

    $props += [pscustomobject]@{

    Servername = $server

    tempdbCreationTime='NA'

    name = 'SQL Server'

    Status = 'Server Not Responding'

    startmode = 'NA'

    state = 'NA'

    serviceaccount='NA'

    DisplayName ='NA'

    LogDate=$logdate}

    $status = "Critical"

    $priority = "HIGH"

    $body = @"

    This is to Notify that SQL Server is not responding!

    The $server is not responding . Please assign an $priority priority ticket to the Respective DBA team.

    -This is an auto generated email generated by the PowerShell script. Please do not reply!!

    "@

    sendEmail pjayaram@appvion.com "$to" "SQL Service On $server is not running!" $SMTP $body

    }

    #maintain the output of columns in a specific order

    $data=$props|select-object servername,name,tempdbCreationTime,status,startmode,serviceaccount,Displayname,logdate

    # Build trusted Connection to the SQL Server and a database

    $conn = New-Object System.Data.SqlClient.SqlConnection

    $conn.ConnectionString = "Server=ComputerName;Database=sql_server_monitor;trusted_connection=true;"

    #Open the connection

    $Conn.Open()

    # prepare the SQL command. This is something like building a dynamic SQL

    $Command = New-Object System.Data.SQLClient.SQLCommand

    $Command.Connection = $Conn

    $data | foreach {

    $Command.CommandText = "INSERT INTO tbl_serveravailability (Servername, TempdbCreationTime, Name, status, startmode, serviceaccount, DisplayName, LogDate) VALUES ('$($data.servername)','$($data.TempdbCreationTime)','$($data.Name)','$($data.status)','$($data.startmode)','$($data.serviceaccount)','$($data.DisplayName)','$($data.LogDate)')"

    #Insert the prepared SQL statement to the Central repository

    $Command.ExecuteNonQuery() | out-null

    }

    }

    #Input file - lists all the SQL Servers

    $filepath='\\ComputerName\c$\Server\serverlist.csv'

    # Import the CSV file and Loop through each server

    Import-CSV $filepath | Foreach-Object{

    write-host $_.ServerName $_.InstanceName $_.SQLServiceName

    Get-ServiceInfo -server $_.ServerName -SQLInstance $_.InstanceName -SQLService $_.SQLServiceName -SMTP 'mail.sqlshackdemo.com' -to 'vpmaciel@live.com'

    }

  • I found this Sources from: https://solutioncenter.apexsql.com/multi-server-script-to-automatically-monitor-sql-server-availability/

    I run this script in power shell

  • From looking at it, you need to put in the right SMTP server for whatever you have on your environment that can relay mail.  My guess is that your mail server is not called "mail.sqlshackdemo.com", I also suspect you need to change the value being passed to the $to variable, I doubt you want to send to/from appvion.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply