Alert configuration

  • 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 .

  • 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.

     

  • if any links provided is helpful  to me

  • 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 .

     

     

  • What does your script do?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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 .

  • 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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 8 posts - 1 through 8 (of 8 total)

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