February 9, 2021 at 3:09 pm
How to configure alerts regarding the status of Sql agent , sql server , SSAS,SSRS, SSIS . I need to get the alert when the service is stopped /started on multiple servers .
February 9, 2021 at 7:41 pm
If a Windows Service stops, you can configure actions in the "Recovery" tab for that service. One option is run a program. You would need to run a program that alerts you. There are plenty of examples on the Internet of PoSh, VBScript, etc. to send emails or post an alert.
Otherwise, purchase a monitoring system/service that does this. This isn't built into SQL Server or Windows, which is why various vendors sell this software or service.
February 10, 2021 at 1:59 am
if any links provided is helpful to me
February 23, 2021 at 3:43 pm
Thanks for the reply i used the below script
Function Get-ServiceSQLAlert
{
param(
[String]$ComputerList,[String[]]$includeService,[String]$To,[String]$From,[string]$SMTPMail
)
$script:list = $ComputerList
#Make sure to check write acess on c:\ drive. if not, change the path
$ServiceFileName= "c:\ServiceFileName.htm"
New-Item -ItemType file $ServiceFilename -Force
# Function to write the HTML Header to the file
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString('yyyy/MM/dd')
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title>Service Status Report </title>'
add-content $fileName '<STYLE TYPE="text/css">'
add-content $fileName "<!--"
add-content $fileName "td {"
add-content $fileName "font-family: Tahoma;"
add-content $fileName "font-size: 11px;"
add-content $fileName "border-top: 1px solid #999999;"
add-content $fileName "border-right: 1px solid #999999;"
add-content $fileName "border-bottom: 1px solid #999999;"
add-content $fileName "border-left: 1px solid #999999;"
add-content $fileName "padding-top: 0px;"
add-content $fileName "padding-right: 0px;"
add-content $fileName "padding-bottom: 0px;"
add-content $fileName "padding-left: 0px;"
add-content $fileName "}"
add-content $fileName "body {"
add-content $fileName "margin-left: 5px;"
add-content $fileName "margin-top: 5px;"
add-content $fileName "margin-right: 0px;"
add-content $fileName "margin-bottom: 10px;"
add-content $fileName ""
add-content $fileName "table {"
add-content $fileName "border: thin solid #000000;"
add-content $fileName "}"
add-content $fileName "-->"
add-content $fileName "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"
add-content $fileName "<table width='100%'>"
add-content $fileName "<tr bgcolor='#CCCCCC'>"
add-content $fileName "<td colspan='4' height='25' align='center'>"
add-content $fileName "<font face='tahoma' color='#003399' size='4'>Service Stauts Alert - $date</font>"
add-content $fileName "</td>"
add-content $fileName "</tr>"
add-content $fileName "</table>"
}
# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)
Add-Content $fileName "<tr bgcolor=#CCCCCC>"
Add-Content $fileName "<td width='10%' align='center'>ServerName</td>"
Add-Content $fileName "<td width='50%' align='center'>Service Name</td>"
Add-Content $fileName "<td width='10%' align='center'>status</td>"
Add-Content $fileName "</tr>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function writeDiskInfo
{
param($filename,$Servername,$name,$Status)
if( $status -eq "Stopped")
{
increment $global:a
Add-Content $fileName "<tr>"
Add-Content $fileName "<td bgcolor='#FF0000' align=left ><b>$servername</td>"
Add-Content $fileName "<td bgcolor='#FF0000' align=left ><b>$name</td>"
Add-Content $fileName "<td bgcolor='#FF0000' align=left ><b>$Status</td>"
Add-Content $fileName "</tr>"
}
}
$global:a=0
function increment {
$global:a++
}
writeHtmlHeader $ServiceFileName
Add-Content $ServiceFileName "<table width='100%'><tbody>"
Add-Content $ServiceFileName "<tr bgcolor='#CCCCCC'>"
Add-Content $ServiceFileName "<td width='100%' align='center' colSpan=3><font face='tahoma' color='#003399' size='2'> Service Details</font></td>"
Add-Content $ServiceFileName "</tr>"
writeTableHeader $ServiceFileName
#Change value of the following parameter as needed
$InlcudeArray=@()
#List of programs to exclude
#$InlcudeArray = $inlcudeService
Foreach($ServerName in (Get-Content $script:list))
{
if(Test-Connection -ComputerName $ServerName -Count 1 -ea 0) {
$service = Get-Service -ComputerName $ServerName
if ($Service -ne $NULL)
{
foreach ($item in $service)
{
#$item.DisplayName
Foreach($include in $includeService)
{
write-host $inlcude
if(($item.serviceName).Contains($include) -eq $TRUE)
{
Write-Host $item.MachineName $item.name $item.Status
writeDiskInfo $ServiceFileName $item.MachineName $item.name $item.Status
}
}
}
}
}
}
Add-Content $ServiceFileName "</table>"
writeHtmlFooter $ServiceFileName
Function sendEmail
{
param($from,$to,$subject,$smtphost,$htmlFileName)
[string]$receipients="$to"
$body = Get-Content $htmlFileName
$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtphost)
$smtp.Send($body)
}
$date = ( get-date ).ToString('yyyy/MM/dd')
if ($global:a -ge 1)
{
$date = ( get-date ).ToString('yyyy/MM/dd')
sendEmail -from $From -to $to -subject "Service Status - $Date" -smtphost $SMTPMail -htmlfilename $ServiceFilename
}
}
when i execute the script data came where the services are in stopped state by default . i need to exclude the services which are in stopped state by default and need to raise the alert when the running services came to stopped state .
can any one help on this and modify the script in excluding the services which are in stopped state .
February 23, 2021 at 3:47 pm
What does your script do?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 23, 2021 at 4:09 pm
It will give report when specific service is in stopped state ,
Get-ServiceSQLAlert -ComputerList C:\ file .txt -includeService "", -To -From -SMTPMail
also it will not give correct data for cluster servers . i request how to modify in exlcuding the services which are in stopped state and get the services data in cluster environment .
February 23, 2021 at 4:16 pm
You can filter out services which are not set to start automatically by referencing the ServiceStartMode enum. That might be the closest you can get to 'in stopped state by default'.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply