PowerShell- Monitoring Multiple Service on a Group of Servers

I got a request to monitor multiple services and send an email to intended recipients. This post explains how to monitor a multiple services on a group of servers. The function Get-ServiceStatusReport comprises of various cmdLets and function to monitor services on a list of servers.

  • Get-Service
  • HTML Ouptut
  • Email Address validation

The Function Get-ServiceStatusReport contains five parameters

  1. ComputerList – List of Servers
  2. ServiceName – Name of Services separated by comma
  3. SMTPMail – SMTP mail address
  4. FromID – Valid Email ID
  5. ToID – Valid Email ID

Function call –

Get-ServiceStatusReport -ComputerList C:\server.txt -includeService  "MySQL","MpsSvc","W32Time" -To pjayaram@app.com -From pjayaram@ app.com -SMTPMail  app01. app.com

OR

Get-ServiceStatusReport -ComputerList C:\server.txt -includeService  MySQL,MpsSvc,W32Time -To pjayaram@app.com -From pjayaram@ app.com -SMTPMail  app01. app.com

You can download the code :- MonitorMultiple Services

Output:-

MultipleServicesReport

Code:-

Function Get-ServiceStatusReport 
{ 
param( 
[String]$ComputerList,[String[]]$includeService,[String]$To,[String]$From,[string]$SMTPMail 
) 
$script:list = $ComputerList  
$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'><strong>Service Stauts Report - $date</strong></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") 
{ 
 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>" 
} 
else 
{ 
Add-Content $fileName "<tr>" 
 Add-Content $fileName "<td >$servername</td>" 
 Add-Content $fileName "<td >$name</td>" 
 Add-Content $fileName "<td >$Status</td>" 
Add-Content $fileName "</tr>" 
} 

} 

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'><strong> Service Details</strong></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)) 
{ 
$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 Validate-IsEmail ([string]$Email) 
{ 

                return $Email -match "^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$" 
} 

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) 
$validfrom= Validate-IsEmail $from 
if($validfrom -eq $TRUE) 
{ 
$validTo= Validate-IsEmail $to 
if($validTo -eq $TRUE) 
{ 
$smtp.Send($body) 
write-output "Email Sent!!" 

} 
} 
else 
{ 
write-output "Invalid entries, Try again!!" 
} 
} 

$date = ( get-date ).ToString('yyyy/MM/dd') 

sendEmail -from $From -to $to -subject "Service Status - $Date" -smtphost $SMTPMail -htmlfilename $ServiceFilename 

}

About Prashanth Jayaram

I’m a Database technologist having 10+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell. Bookmark the permalink.

71 Responses to PowerShell- Monitoring Multiple Service on a Group of Servers

  1. Sudhakar says:

    Hi Prashanth , When i am executing this script with modified parameters getting error “Get-Service : A parameter cannot be found that matches parameter name ‘ComputerList’.
    At line:98 char:37
    + $service = Get-Service -ComputerList <<<< $servername| where { $_.displayname -like "*$ServiceName*"}|select Machine
    Name,Name,status
    Exception calling "Send" with "1" argument(s): "The SMTP server requires a secure connection or the client was not auth
    enticated. The server response was: 5.7.0 Must issue a STARTTLS command first. tu3sm50318392pab.1 – gsmtp"
    At line:129 char:11
    + $smtp.Send( <<<< $body)"

    • Hi Sudhakar,

      Can you tell me how you are executing the code?

      Follow these steps

      Download and Create the function using PowerShell-ISE and call the function with the required parameters.

      ps:\>Get-ServiceStatusReport -ComputerList C:\computer.txt -includeService SQL -To pjayaram@appvion.com -From pjayaram@appvion.com -SMTPMail abcd .apple.com

      In your case, it looks like there is some problem with SMTP server. Its not able to authenticate.

      But you can see an output in the following folder c:\ServiceFileName.htm

      -Prashanth

      • Sudhakar says:

        Hi Prashanth,

        Thank you for your prompt responce.

        When i am trying to execute the function with parameters. It is giving error ” A parameter cannot be found that matches parameter name ‘ComputerName’.” I have checked the file c:\ServiceFileName.htm but when i checked the file it is having “Service Stauts Report – 2014-01-23 Service Details ServerName Service Name status”. I cannot see the data in this report. I believe it is not able to resolve the computerName.

      • have you created a file c:\computer.txt and added servername like

        c:\computer.txt

        abc
        def

        -Prashanth

      • Sudhakar says:

        Hi Prashanth ,

        Just to share more details..I am using power shell 2 and gmail as a SMTP server . I want to confirm if the script is compatible to powershell 2..

      • Yes script is compatible with PowerShell 2.0. Can you tell me the steps? I doubt it will work with Gmail SMTP configuration.

        -Prashanth

  2. Lady says:

    How am I going to send the report to multiple recipients?

    • Hello,

      Replace the sendEmail function code by below content
      **********************************************************
      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)
      }

      separate the toID field with comma.

      ps:\>Get-ServiceStatusReport -ComputerList C:\server.txt -includeService MySQL,MpsSvc,W32Time -To “pjayaram@avion.com,test@avion.com” -From pjayaram@avion.com -SMTPMail abc.mail.com

      –Prashanth

    • Sudhakar Paradesi says:

      Hi Prashanth,

      I have tried with the modified script..but no luck.

      Enclosed is the screenshots for your reference.

      On Tue, Jan 28, 2014 at 8:09 PM, Prashanth Jayaram wrote:

      > Lady commented: “How am I going to send the report to multiple > recipients?”

      • code :- Run the below code in Powershell-ISE, the function call is given below

        Function Get-ServiceStatusReport
        {
        param(
        [String]$ComputerList,[String[]]$includeService,[String]$To,[String]$From,[string]$SMTPMail
        )
        $script:list = $ComputerList
        $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 “”
        Add-Content $fileName “”
        Add-Content $fileName “”
        Add-Content $fileName ‘Service Status Report ‘
        add-content $fileName ”
        add-content $fileName “”
        add-content $fileName “”
        Add-Content $fileName “”
        Add-Content $fileName “”

        add-content $fileName “”
        add-content $fileName “”
        add-content $fileName “”
        add-content $fileName “Service Stauts Report – $date
        add-content $fileName “”
        add-content $fileName “”
        add-content $fileName “”

        }

        # Function to write the HTML Header to the file
        Function writeTableHeader
        {
        param($fileName)

        Add-Content $fileName “”
        Add-Content $fileName “ServerName”
        Add-Content $fileName “Service Name”
        Add-Content $fileName “status”
        Add-Content $fileName “”
        }

        Function writeHtmlFooter
        {
        param($fileName)

        Add-Content $fileName “”
        Add-Content $fileName “”
        }

        Function writeDiskInfo
        {
        param($filename,$Servername,$name,$Status)
        if( $status -eq “Stopped”)
        {
        Add-Content $fileName “”
        Add-Content $fileName “$servername”
        Add-Content $fileName “
        $name”
        Add-Content $fileName “
        $Status”
        Add-Content $fileName “”
        }
        else
        {
        Add-Content $fileName “”
        Add-Content $fileName “$servername”
        Add-Content $fileName “$name”
        Add-Content $fileName “$Status”
        Add-Content $fileName “”
        }

        }

        writeHtmlHeader $ServiceFileName
        Add-Content $ServiceFileName “”
        Add-Content $ServiceFileName “”
        Add-Content $ServiceFileName “ Service Details
        Add-Content $ServiceFileName “”

        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))
        {
        $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 “”

        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’)

        sendEmail -from $From -to $to -subject “Service Status – $Date” -smtphost $SMTPMail -htmlfilename $ServiceFilename

        }

        Get-ServiceStatusReport -ComputerList C:\server.txt -includeService MySQL,MpsSvc,W32Time -To “pjayaram@avion.com,test@avion.com” -From pjayaram@avion.com -SMTPMail abc.mail.com

  3. Sudhakar says:

    The Steps follow:
    The computer list is maintained in the file computer.txt(C:\computer.txt) as per mentioned in the blog.
    The script was copied and executed with the help of powrshell-Ise. The arguments were modified such as mail id and smtp address and executed.
    The script is creating the file in the C drive with proper data,but it fails to send the mail with the error like “Exception calling “Send” with “1” argument(s): “The SMTP server requires a secure connection or the client was not authenticated.”

  4. John Bryant says:

    Nice script, going to try and get it to where it only emails if there is a service stopped

    JB

  5. Parveen says:

    Many thanks for the script, it really very good, we can monitor whatever services we want to monitor.
    The script generated the file(ServiceFileName.htm) successfully on C drive but the mail is not sent, I am using gmail SMTP server (smtp.gmail.com,587), it would be great if you please fix this.

    Thanks in Advance.

  6. Parveen says:

    Hi All,

    I changed the sendmail function a bit and make it Gmail SMTP specific and it works for me, so here is what I changed.

    Function sendEmail
    {
    param($subject,$htmlFileName)
    $smtp = New-Object Net.Mail.SmtpClient(‘smtp.gmail.com’, 587)
    $smtp.EnableSsl = $true
    $smtp.Credentials = New-Object System.Net.NetworkCredential(‘GmailAccountName@Gmail.com’, ‘PasswordofTheGmailAccount’)
    $body = New-Object System.Net.Mail.MailMessage
    $body.isBodyhtml = $true
    $body.Subject = $subject
    $body.From = ‘GmailAccountName@Gmail.com’
    $body.To.Add(‘GmailAccountName@Gmail.com’)
    $body.Body = Get-Content $htmlFileName
    $smtp.Send($body)
    }

    If you want to use this for Gmail SMTP replace the sendEmail function code by above content.

    Thanks.

  7. dheeraj says:

    hi Prashanth can u plz explain indetail how to execute the above script.What changes we have to made in script.
    I am trying to run through powershell,file extension with .ps1. i am uable to get mails

    • Hi Dheeraj,
      Are you able to create the Function?. Open Powershell-ISE and copy the code and execute it. Once done you just need to call the function

      Get-ServiceStatusReport -ComputerList C:\server.txt
      -includeService “MySQL”,”MpsSvc”,”W32Time”
      -To pjayaram@app.com
      -From pjayaram@ app.com
      -SMTPMail app01. app.com

      Let me know if you face any difficulty.

  8. Louis says:

    Hi Prashanth, what a great script. I am wondering if it is possible to specify a different set of credentials?

    Any I ideas?

    Thanks

    • Hi Louis,

      Get-service cmdlets donot accept credential parameter.

      In your case, you can try using

      $User = “”
      $Pass = ConvertTo-SecureString “” -AsPlainText -Force
      $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass

      Get-WMIObject Win32_Service -Computername -Credential $credential

      –Prashanth

  9. Sudhakar Paradesi says:

    Hi Prashanth,

    I have tested the script with the help of the console and it is working fine. I just to check how can this script will be scheduled with the help of the SQL Server job. I have checked the console but i cannot see this function saved. Pleas do the needful.

  10. Sudhakar Paradesi says:

    Hi Prashanth,

    Just to know as i have execute the script it is showing all the sql server services, but my requirement is to monitor the agent,ssis,sqlserver,sql server agent. Please help me for this..

  11. Sudhakar Paradesi says:

    Hi Prashanth,

    As we need to monitor the servers across different domains. Can it be possible to monitor by passing the credentials in this script ?

    • Hi Sudhakar,

      Yes, its possible.

      You need to pass the credential information. for example

      I’ve used Get-service cmdlet which will not accept credential parameter hence you need to use win32_Service object.

      I’ve shown the below example. You need to modify the code as per your requirement

      $User = “test”
      $Pass = ConvertTo-SecureString “test#%)” -AsPlainText -Force
      $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass
      $Computers =”
      Get-WMIObject Win32_Service -computer $computers -credential $Credentials

      Let me know in case any issues
      –Prashanth

  12. Sudhakar Paradesi says:

    HI Prashanth,

    I have executed the report and working fine. I need some modifications like for clustered envirnoment it is showing the service down in passive node. Is it possible to show only the active node?

  13. Hari says:

    Hi Prashanth,

    Is it possible to send htm file as attachment instead of as body in mail.

    With Thanks and regards,
    Prasad

    • Hi Hari,

      Its very simple. Can you refer the below link

      https://sqlpowershell.wordpress.com/2014/05/21/powershell-os-inventory-and-disk-info-consolidated-excel-file-email/

      You need to replace the below code and pass the filename as its parameter

      Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath)
      {
      #initate message
      $email = New-Object System.Net.Mail.MailMessage
      $email.From = $emailFrom
      $email.To.Add($emailTo)
      $email.Subject = $subject
      $email.Body = $body
      # initiate email attachment
      $emailAttach = New-Object System.Net.Mail.Attachment $filePath
      $email.Attachments.Add($emailAttach)
      #initiate sending email
      $smtp = new-object Net.Mail.SmtpClient($smtpServer)
      $smtp.Send($email)
      }

      #Call Function

      $message = @”
      Hi Team,

      The Discovery of Windows Server and Disk Space information for all the listed instances.

      Autogenerated Email!!! Please do not reply.

      Thank you,
      XYZ.com

      “@
      $date=get-date

      sendEmail -emailFrom $fromEmail -emailTo $ToEmail -subject “Windows Server Inventory & Disk Details -$($date)” -body $message -smtpServer $SMTPMail -filePath $filename

      Let me know in case of any difficulties

      –Prashanth

  14. hari says:

    Hi Prashanth,

    We have 3 instances on same cluster. I used cluster virtual name for each instance separately. But in report along with instace 1 is displaying as running, but other 2 instances also displaying as stopped along with instance 1. Same with instance 2 along with 1 and 3 so on. Could you please help us.

  15. talderon says:

    Hi there. I know this is an older post, but I am looking for something like this and wanted to know what it would take to remove the email component all together and just have it save an HTML file. We just need to run this manually when we are doing some specific tasks and just want the output to be fight there.

    Thanks!

    • Hi Talderon,

      Here you go…………..

      Function Get-ServiceStatusReport
      {
      param(
      [String]$ComputerList,[String[]]$includeService
      )
      $script:list = $ComputerList
      $ServiceFileName= “F:\PowerSQL\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 “”
      Add-Content $fileName “”
      Add-Content $fileName “”
      Add-Content $fileName ‘Service Status Report ‘
      add-content $fileName ”
      add-content $fileName “”
      add-content $fileName “”
      Add-Content $fileName “”
      Add-Content $fileName “”

      add-content $fileName “”
      add-content $fileName “”
      add-content $fileName “”
      add-content $fileName “Service Stauts Report – $date
      add-content $fileName “”
      add-content $fileName “”
      add-content $fileName “”

      }

      # Function to write the HTML Header to the file
      Function writeTableHeader
      {
      param($fileName)

      Add-Content $fileName “”
      Add-Content $fileName “ServerName”
      Add-Content $fileName “Service Name”
      Add-Content $fileName “status”
      Add-Content $fileName “”
      }

      Function writeHtmlFooter
      {
      param($fileName)

      Add-Content $fileName “”
      Add-Content $fileName “”
      }

      Function writeDiskInfo
      {
      param($filename,$Servername,$name,$Status)
      if( $status -eq “Stopped”)
      {
      Add-Content $fileName “”
      Add-Content $fileName “$servername”
      Add-Content $fileName “
      $name”
      Add-Content $fileName “
      $Status”
      Add-Content $fileName “”
      }
      else
      {
      Add-Content $fileName “”
      Add-Content $fileName “$servername”
      Add-Content $fileName “$name”
      Add-Content $fileName “$Status”
      Add-Content $fileName “”
      }

      }

      writeHtmlHeader $ServiceFileName
      Add-Content $ServiceFileName “”
      Add-Content $ServiceFileName “”
      Add-Content $ServiceFileName “ Service Details
      Add-Content $ServiceFileName “”

      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))
      {
      $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 “”

      writeHtmlFooter $ServiceFileName
      }

      ps:\>Get-ServiceStatusReport -ComputerList F:\PowerSQL\server.txt -includeService “SQL”,”MpsSvc”,”W32Time”

      –Prashanth

  16. talderon says:

    Thanks for the quick response! I found a small syntax issue on line 22 (Missing quote), other than that, looks great!😀 I do have one issue though. When I add the function to my profile and use it, I see the results flash across my screen, but no HTML file is generated. I’ll keep poking around,l but any assistance would be appreciated!

    Thanks!

  17. Waqas says:

    How to send email to multiple recipients ???

    • Hello,

      Replace the sendEmail function code by below content
      **********************************************************
      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)
      }

      separate the toID field with comma.

      ps:\>Get-ServiceStatusReport -ComputerList C:\server.txt -includeService MySQL,MpsSvc,W32Time -To “pjayaram@avion.com,test@avion.com” -From pjayaram@avion.com -SMTPMail abc.mail.com

      –Prashanth

  18. Alberto says:

    Hello,
    It’s posible to filter only the automatic service?.
    For example, I want to check Exchange service, but I want monitoring only the automatics service. How can I do it?
    Thanks

    • Hi Alberto,
      You need to query the WMI. The cmdlet don’t bind the information you have asked for hence you need to query WMI which holds requested information.

      Function call remains same.

      Get-WmiObject Win32_Service -ComputerName $servername|Where-Object { $_.StartMode -eq ‘Auto’}

      Refer the below Powershell script

      Function Get-ServiceStatusReport
      {
      param(
      [String]$ComputerList,[String[]]$includeService,[String]$To,[String]$From,[string]$SMTPMail
      )
      $script:list = $ComputerList
      $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 “”
      Add-Content $fileName “”
      Add-Content $fileName “”
      Add-Content $fileName ‘Service Status Report ‘
      add-content $fileName ”
      add-content $fileName “”
      add-content $fileName “”
      Add-Content $fileName “”
      Add-Content $fileName “”

      add-content $fileName “”
      add-content $fileName “”
      add-content $fileName “”
      add-content $fileName “Service Stauts Report – $date
      add-content $fileName “”
      add-content $fileName “”
      add-content $fileName “”

      }

      # Function to write the HTML Header to the file
      Function writeTableHeader
      {
      param($fileName)

      Add-Content $fileName “”
      Add-Content $fileName “ServerName”
      Add-Content $fileName “Service Name”
      Add-Content $fileName “status”
      Add-Content $fileName “”
      }

      Function writeHtmlFooter
      {
      param($fileName)

      Add-Content $fileName “”
      Add-Content $fileName “”
      }

      Function writeDiskInfo
      {
      param($filename,$Servername,$name,$Status)
      if( $status -eq “Stopped”)
      {
      Add-Content $fileName “”
      Add-Content $fileName “$servername”
      Add-Content $fileName “
      $name”
      Add-Content $fileName “
      $Status”
      Add-Content $fileName “”
      }
      else
      {
      Add-Content $fileName “”
      Add-Content $fileName “$servername”
      Add-Content $fileName “$name”
      Add-Content $fileName “$Status”
      Add-Content $fileName “”
      }

      }

      writeHtmlHeader $ServiceFileName
      Add-Content $ServiceFileName “”
      Add-Content $ServiceFileName “”
      Add-Content $ServiceFileName “ Service Details
      Add-Content $ServiceFileName “”

      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))
      {
      $service = Get-WmiObject Win32_Service -ComputerName $servername |Where-Object { $_.StartMode -eq ‘Auto’}
      if ($Service -ne $NULL)
      {
      foreach ($item in $service)
      {
      #$item.DisplayName
      Foreach($include in $includeService)
      {
      write-host $inlcude
      if(($item.name).Contains($include) -eq $TRUE)
      {
      Write-Host $servername $item.name $item.Status
      writeDiskInfo $ServiceFileName $servername $item.name $item.Status
      }
      }
      }
      }
      }

      Add-Content $ServiceFileName “”

      writeHtmlFooter $ServiceFileName

      function Validate-IsEmail ([string]$Email)
      {

      return $Email -match “^(?(“”)(“”.+?””@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&’\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$"
      }

      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)
      $validfrom= Validate-IsEmail $from
      if($validfrom -eq $TRUE)
      {
      $validTo= Validate-IsEmail $to
      if($validTo -eq $TRUE)
      {
      $smtp.Send($body)
      write-output "Email Sent!!"

      }
      }
      else
      {
      write-output "Invalid entries, Try again!!"
      }
      }

      $date = ( get-date ).ToString('yyyy/MM/dd')

      sendEmail -from $From -to $to -subject "Service Status – $Date" -smtphost $SMTPMail -htmlfilename $ServiceFilename

      }

    • Refer the below post

      sqlpowershell.wordpress.com/2016/01/14/powershell-monitoring-multiple-services-on-multiple-servers-using-wmi-class-win32_service/

  19. RK says:

    Hi Prashanth,

    Please let me know how to configure this script in SQL job. I’m able to do the function.

    Please let me know where need to place below step
    ps:\>Get-ServiceStatusReport -ComputerList C:\server.txt -includeService MySQL,MpsSvc,W32Time -To “pjayaram@avion.com,test@avion.com” -From pjayaram@avion.com -SMTPMail abc.mail.com

    • Hi RK,

      You need to have a proper security configuration to run the code.
      If not, atleast try to setup a proxy account and use the account to run the sql agent job.

      In the job you can run as T-SQL script
      and use the below SQL
      master..xp_cmdshell ‘PowerShell.exe F:\PowerSQL\DBServiceSQL.PS1’

      –Prashanth
      .

      • RK says:

        Hi Prashanth,

        I have configured job in SQL agent,

        Step Name ABC

        Type Powershell

        Run as SQL server Agent Service Account

        After ending function added below command .Job has been completed successfully but out put file came as empty file. Please let me know how to resolve this issue.

        Get-ServiceStatusReport -ComputerList D:\Powershell\server.txt -includeService “MSSQLSERVER”,”SQLSERVERAGENT” -To name@xyz.com -From xyz.com-SMTPMail @xyz.com

        ____________________________________________________________
        Function Get-ServiceStatusReport
        {
        param(
        [String]$ComputerList,[String[]]$includeService,[String]$To,[String]$From,[string]$SMTPMail
        )
        $script:list = $ComputerList
        $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 “”
        Add-Content $fileName “”
        Add-Content $fileName “”
        Add-Content $fileName ‘Service Status Report ‘
        add-content $fileName ”
        add-content $fileName “”
        add-content $fileName “”
        Add-Content $fileName “”
        Add-Content $fileName “”

        add-content $fileName “”
        add-content $fileName “”
        add-content $fileName “”
        add-content $fileName “SPOC Rajesh Service Stauts Report – $date
        add-content $fileName “”
        add-content $fileName “”
        add-content $fileName “”

        }

        # Function to write the HTML Header to the file
        Function writeTableHeader
        {
        param($fileName)

        Add-Content $fileName “”
        Add-Content $fileName “ServerName”
        Add-Content $fileName “Service Name”
        Add-Content $fileName “status”
        Add-Content $fileName “”
        }

        Function writeHtmlFooter
        {
        param($fileName)

        Add-Content $fileName “”
        Add-Content $fileName “”
        }

        Function writeDiskInfo
        {
        param($filename,$Servername,$name,$Status)
        if( $status -eq “Stopped”)
        {
        Add-Content $fileName “”
        Add-Content $fileName “$servername”
        Add-Content $fileName “
        $name”
        Add-Content $fileName “
        $Status”
        Add-Content $fileName “”
        }
        else
        {
        Add-Content $fileName “”
        Add-Content $fileName “$servername”
        Add-Content $fileName “$name”
        Add-Content $fileName “$Status”
        Add-Content $fileName “”
        }

        }

        writeHtmlHeader $ServiceFileName
        Add-Content $ServiceFileName “”
        Add-Content $ServiceFileName “”
        Add-Content $ServiceFileName “ Service Details
        Add-Content $ServiceFileName “”

        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))
        {
        $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 “”

        writeHtmlFooter $ServiceFileName

        function Validate-IsEmail ([string]$Email)
        {

        return $Email -match “^(?(“”)(“”.+?””@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&’\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$"
        }

        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)
        $validfrom= Validate-IsEmail $from
        if($validfrom -eq $TRUE)
        {
        $validTo= Validate-IsEmail $to
        if($validTo -eq $TRUE)
        {
        $smtp.Send($body)
        write-output "Email Sent!!"

        }
        }
        else
        {
        write-output "Invalid entries, Try again!!"
        }
        }

        $date = ( get-date ).ToString('yyyy/MM/dd')

        sendEmail -from $From -to $to -subject "Service Status – $Date" -smtphost $SMTPMail -htmlfilename $ServiceFilename

        }

        Get-ServiceStatusReport -ComputerList D:\Powershell\server.txt -includeService "MSSQLSERVER","SQLSERVERAGENT" -To name@xyz.com -From xyz.com-SMTPMail xyz.com

    • Kumar Gowda says:

      Script absolutely working fine, only out put not generating in HTMl format.

      [image: Inline images 1]

      Regards,

      Kumara.R

    • Kumar Gowda says:

      Hi Prashanth,

      if u get a free time please have a look on this.

      Regards,

      Kumara.R

    • Kumar Gowda says:

      Prashanth, do u have chace to look this?

    • Kumar Gowda says:

      Hi Prashanth,

      I need few modification on this script. I’m looking only stopped service details. As per the script getting Running and stopped. I’m looking for only stopped service no need for running.

      Regards,

      Kumara.R

      • Hi Kumar,

        Try the below script

        Function Get-ServiceStatusReport
        {
        param(
        [String]$ComputerList,[String[]]$includeService,[String]$To,[String]$From,[string]$SMTPMail
        )
        $script:list = $ComputerList
        $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 “”
        Add-Content $fileName “”
        Add-Content $fileName “”
        Add-Content $fileName ‘Service Status Report ‘
        add-content $fileName ”
        add-content $fileName “”
        add-content $fileName “”
        Add-Content $fileName “”
        Add-Content $fileName “”

        add-content $fileName “”
        add-content $fileName “”
        add-content $fileName “”
        add-content $fileName “Service Stauts Report – $date
        add-content $fileName “”
        add-content $fileName “”
        add-content $fileName “”

        }

        # Function to write the HTML Header to the file
        Function writeTableHeader
        {
        param($fileName)

        Add-Content $fileName “”
        Add-Content $fileName “ServerName”
        Add-Content $fileName “Service Name”
        Add-Content $fileName “status”
        Add-Content $fileName “”
        }

        Function writeHtmlFooter
        {
        param($fileName)

        Add-Content $fileName “”
        Add-Content $fileName “”
        }

        Function writeDiskInfo
        {
        param($filename,$Servername,$name,$Status)
        if( $status -eq “Stopped”)
        {
        Add-Content $fileName “”
        Add-Content $fileName “$servername”
        Add-Content $fileName “
        $name”
        Add-Content $fileName “
        $Status”
        Add-Content $fileName “”
        }
        else
        {
        Add-Content $fileName “”
        Add-Content $fileName “$servername”
        Add-Content $fileName “$name”
        Add-Content $fileName “$Status”
        Add-Content $fileName “”
        }

        }

        writeHtmlHeader $ServiceFileName
        Add-Content $ServiceFileName “”
        Add-Content $ServiceFileName “”
        Add-Content $ServiceFileName “ Service Details
        Add-Content $ServiceFileName “”

        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))
        {
        $service = Get-Service -ComputerName $servername | Where-Object{$_.status -eq “Stopped”}
        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 “”

        writeHtmlFooter $ServiceFileName

        function Validate-IsEmail ([string]$Email)
        {

        return $Email -match “^(?(“”)(“”.+?””@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&’\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$"
        }

        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)
        $validfrom= Validate-IsEmail $from
        if($validfrom -eq $TRUE)
        {
        $validTo= Validate-IsEmail $to
        if($validTo -eq $TRUE)
        {
        $smtp.Send($body)
        write-output "Email Sent!!"

        }
        }
        else
        {
        write-output "Invalid entries, Try again!!"
        }
        }

        $date = ( get-date ).ToString('yyyy/MM/dd')

        sendEmail -from $From -to $to -subject "Service Status – $Date" -smtphost $SMTPMail -htmlfilename $ServiceFilename

        }

        Call the function

    • Kumar Gowda says:

      Hi Prashanth,

      Do u have option for check this?

      Regards,

      Kumara.R

  20. RK says:

    Hi Prashanth,

    If run below script directly form powershell, will get exact out put but same script execute form SQL agent job not getting output, getting only empty out put.

    Function Get-ServiceStatusReport
    {
    param(
    [String]$ComputerList,[String[]]$includeService,[String]$To,[String]$From,[string]$SMTPMail
    )
    $script:list = $ComputerList
    $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 “”
    Add-Content $fileName “”
    Add-Content $fileName “”
    Add-Content $fileName ‘Service Status Report ‘
    add-content $fileName ”
    add-content $fileName “”
    add-content $fileName “”
    Add-Content $fileName “”
    Add-Content $fileName “”

    add-content $fileName “”
    add-content $fileName “”
    add-content $fileName “”
    add-content $fileName “SPOC Rajesh Service Stauts Report – $date
    add-content $fileName “”
    add-content $fileName “”
    add-content $fileName “”

    }

    # Function to write the HTML Header to the file
    Function writeTableHeader
    {
    param($fileName)

    Add-Content $fileName “”
    Add-Content $fileName “ServerName”
    Add-Content $fileName “Service Name”
    Add-Content $fileName “status”
    Add-Content $fileName “”
    }

    Function writeHtmlFooter
    {
    param($fileName)

    Add-Content $fileName “”
    Add-Content $fileName “”
    }

    Function writeDiskInfo
    {
    param($filename,$Servername,$name,$Status)
    if( $status -eq “Stopped”)
    {
    Add-Content $fileName “”
    Add-Content $fileName “$servername”
    Add-Content $fileName “
    $name”
    Add-Content $fileName “
    $Status”
    Add-Content $fileName “”
    }
    else
    {
    Add-Content $fileName “”
    Add-Content $fileName “$servername”
    Add-Content $fileName “$name”
    Add-Content $fileName “$Status”
    Add-Content $fileName “”
    }

    }

    writeHtmlHeader $ServiceFileName
    Add-Content $ServiceFileName “”
    Add-Content $ServiceFileName “”
    Add-Content $ServiceFileName “ Service Details
    Add-Content $ServiceFileName “”

    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))
    {
    $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 “”

    writeHtmlFooter $ServiceFileName

    function Validate-IsEmail ([string]$Email)
    {

    return $Email -match “^(?(“”)(“”.+?””@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&’\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$"
    }

    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)
    $validfrom= Validate-IsEmail $from
    if($validfrom -eq $TRUE)
    {
    $validTo= Validate-IsEmail $to
    if($validTo -eq $TRUE)
    {
    $smtp.Send($body)
    write-output "Email Sent!!"

    }
    }
    else
    {
    write-output "Invalid entries, Try again!!"
    }
    }

    $date = ( get-date ).ToString('yyyy/MM/dd')

    sendEmail -from $From -to $to -subject "Service Status – $Date" -smtphost $SMTPMail -htmlfilename $ServiceFilename

    }

    Get-ServiceStatusReport -ComputerList D:\Powershell\Rajesh.txt -includeService "MSSQLSERVER","SQLSERVERAGENT" -To ABC@xyz.com -From qqq@xyz.com -SMTPMail yyy@xyz.com

  21. RK says:

    Hi Prashanth,
    I checked SQL agent account having full permission, still if i execute script directly getting out put file content same script run through SQL agent job, got only output file without content.

    • Hi RK,
      Can you tell what account the SQL agent is configured with?
      I hope its been configured with domain account, not a system account.
      I’m executing the sql jobs in my environment without any issue.
      I strongly feel it’s a problem with the security.

      –Prashanth

  22. RK says:

    I’m using domain account, same account configured for SQL agent.I will check once again.

    I’m looking only failed SQL agent jobs script for multiple server. Please share me if u have the scripts.

  23. RK says:

    Thanks Prashanth, I have searched did not get right one. I’m looking for only failed jobs and out put should be HTML format.

  24. RK says:

    The below script not working for me.
    #Find Failed SQL Jobs with Powershell
    #by Adam Mikolaj
    #www.sqlsandwiches.com

    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null;

    #let’s get our list of servers. For this, create a .txt files with all the server names you want to check.
    $sqlservers = Get-Content “C:\DevWork\scripts\computers.txt”;

    #we’ll get the long date and toss that in a variable
    $datefull = Get-Date
    #and shorten it
    $today = $datefull.ToShortDateString()

    #let’s set up the email stuff
    $msg = new-object Net.Mail.MailMessage
    $smtp = new-object Net.Mail.SmtpClient(“ExchangeServerNameHere”)
    $msg.Body = “Here is a list of failed SQL Jobs for $today (the last 24 hours)”

    #here, we will begin with a foreach loop. We’ll be checking all servers in the .txt referenced above.
    foreach($sqlserver in $sqlservers)
    {

    #here we need to set which server we are going to check in this loop
    $srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver;

    #now let’s loop through all the jobs
    foreach ($job in $srv.Jobserver.Jobs)
    {
    #now we are going to set up some variables.
    #These values come from the information in $srv.Jobserver.Jobs
    $jobName = $job.Name;
    $jobEnabled = $job.IsEnabled;
    $jobLastRunOutcome = $job.LastRunOutcome;
    $jobLastRun = $job.LastRunDate;

    #we are only concerned about jos that are enabled and have run before.
    #POSH is weird with nulls so you check by just calling the var
    #if we wanted to check isnull() we would use !$jobLastRun
    if($jobEnabled = “true” -and $jobLastRun)
    {
    # we need to find out how many days ago that job ran
    $datediff = New-TimeSpan $jobLastRun $today
    #now we need to take the value of days in $datediff
    $days = $datediff.days

    #gotta check to make sure the job ran in the last 24 hours
    if($days -le 1 )
    {
    #and make sure the job failed
    IF($jobLastRunOutcome -eq “Failed”)
    {
    #now we add the job info to our email body. use `n for a new line
    $msg.body = $msg.body + “`n `n FAILED JOB INFO:
    SERVER = $sqlserver
    JOB = $jobName
    LASTRUN = $jobLastRunOutcome
    LASTRUNDATE = $jobLastRun”

    }
    }
    }

    }
    }

    #once all that loops through and builds our $msg.body, we are read to send

    #who is this coming from
    $msg.From = “adam@sqlsandwiches.com”
    #and going to
    $msg.To.Add(“adam@sqlsandwiches.com”)
    #and a nice pretty title
    $msg.Subject = “FAILED SQL Jobs for $today”
    #and BOOM! send that bastard!
    $smtp.Send($msg)

  25. RK says:

    Hi Prashanth,

    do u have chance to look on this? The script is working for me but looking output HTML format.

    • Hi RK,
      The link is working absolutely fine. I don’t think a need for creating a new one. Did you try correcting SMTP address? Can you give a try and let me know?

      Best Regards,
      Prashanth

    • Hi RK,

      Here is the script, Just change the initial parameters of the file

      Code
      ________________

      [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null;

      #let’s get our list of servers. For this, create a .txt files with all the server names you want to check.
      $sqlservers = Get-Content “f:\powersql\server.txt”;
      $FailedJobFileName= “f:\PowerSQL\FailedSQLJob.htm”
      New-Item -ItemType file $FailedJobFileName -Force

      #we’ll get the long date and toss that in a variable
      $datefull = Get-Date
      #and shorten it
      $today = $datefull.ToShortDateString()

      #let’s set up the email stuff
      $from=”PJAYARAM@APPVION.COM”
      $to=”PJAYARAM@APPVION.COM”
      $smtp_address = “valid email address”

      Function writeHtmlHeader
      {
      param($fileName)
      $date = ( get-date ).ToString(‘yyyy/MM/dd’)
      Add-Content $fileName “”
      Add-Content $fileName “”
      Add-Content $fileName “”
      Add-Content $fileName ‘SQL Job Failure Report ‘
      add-content $fileName ”
      add-content $fileName “”
      add-content $fileName “”
      Add-Content $fileName “”
      Add-Content $fileName “”

      add-content $fileName “”
      add-content $fileName “”
      add-content $fileName “”
      add-content $fileName “SQL Job Stauts Report – $date
      add-content $fileName “”
      add-content $fileName “”
      add-content $fileName “”

      }

      # Function to write the HTML Header to the file
      Function writeTableHeader
      {
      param($fileName)

      Add-Content $fileName “”
      Add-Content $fileName “ServerName”
      Add-Content $fileName “JobName”
      Add-Content $fileName “JobLastRun Outcome”
      Add-Content $fileName “JobLastRunStatus”
      Add-Content $fileName “”
      }

      Function writeHtmlFooter
      {
      param($fileName)

      Add-Content $fileName “”
      Add-Content $fileName “”
      }

      Function writeDiskInfo
      {
      param($filename,$Servername,$name,$outcome,$Status)

      Add-Content $fileName “”
      Add-Content $fileName “$servername”
      Add-Content $fileName “
      $name”
      Add-Content $fileName “
      $Outcome”
      Add-Content $fileName “
      $Status”
      Add-Content $fileName “”

      }

      writeHtmlHeader $FailedJobFileName
      Add-Content $FailedJobFileName “”
      Add-Content $FailedJobFileName “”
      Add-Content $FailedJobFileName “ SQL Job Details
      Add-Content $FailedJobFileName “”

      writeTableHeader $FailedJobFileName

      #here, we will begin with a foreach loop. We’ll be checking all servers in the .txt referenced above.
      foreach($sqlserver in $sqlservers)
      {

      #here we need to set which server we are going to check in this loop
      $srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver;

      #now let’s loop through all the jobs
      foreach ($job in $srv.Jobserver.Jobs)
      {
      #now we are going to set up some variables.
      #These values come from the information in $srv.Jobserver.Jobs
      $jobName = $job.Name;
      $jobEnabled = $job.IsEnabled;
      $jobLastRunOutcome = $job.LastRunOutcome;
      $jobLastRun = $job.LastRunDate;

      #we are only concerned about jos that are enabled and have run before.
      #POSH is weird with nulls so you check by just calling the var
      #if we wanted to check isnull() we would use !$jobLastRun
      if($jobEnabled = “true” -and $jobLastRun)
      {
      # we need to find out how many days ago that job ran
      $datediff = New-TimeSpan $jobLastRun $today
      #now we need to take the value of days in $datediff
      $days = $datediff.days

      #gotta check to make sure the job ran in the last 24 hours
      if($days -le 1 )
      {
      #and make sure the job failed
      IF($jobLastRunOutcome -eq “Failed”)
      {
      #now we add the job info to our email body. use `n for a new line
      Write-Host $sqlserver $jobName $jobLastRunOutcome $JobLastRun
      writeDiskInfo $ServiceFileName $sqlserver $jobName $jobLastRunOutcome $JobLastRun

      }
      }
      }

      }
      }

      #once all that loops through and builds our $msg.body, we are read to send

      #who is this coming from
      #$msg.From = “pjayaram@appvion.com”
      #and going to
      #$msg.To.Add(”pjayaram@appvion.com”)
      #and a nice pretty title
      #$msg.Subject = “FAILED SQL Jobs for $today”
      #and BOOM! send that bastard!
      #$smtp.Send($msg)
      #

      Add-Content $ServiceFileName “”

      writeHtmlFooter $ServiceFileName

      $date = ( get-date ).ToString(‘yyyy/MM/dd’)

      $body = Get-Content $FailedJobFileName
      $body = New-Object System.Net.Mail.MailMessage $from,$to, “Job Status – $Date”, $body
      $body.isBodyhtml = $true
      $smtp = new-object Net.Mail.SmtpClient($smtp_address)
      $smtp.Send($body)

      _____________

      • RK says:

        Hi Prashanth,

        I’m getting below error, previously script was executing fine only out put was not in HTML format. After u modified, getting below error.

        Add-Content : Cannot bind argument to parameter ‘Path’ because it is null.
        At D:\Powershell\SQL_jobs.PS1:84 char:12
        + Add-Content <<<< $fileName "”
        + CategoryInfo : InvalidData: (:) [Add-Content], ParameterBindingValidationException
        + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddContentC
        ommand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s