February 7, 2020 at 12:42 pm
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'
}
February 7, 2020 at 12:44 pm
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
February 7, 2020 at 1:27 pm
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