I got a request to monitor multiple services and send an email to intended recipients. This post explains how to monitor 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 Output
- Email Address validation
The Function Get-ServiceStatusReport contains five parameters
- ComputerList – List of Servers
- ServiceName – Name of Services separated by comma
- SMTPMail – SMTP mail address
- FromID – Valid Email ID
- 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:-
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))
{
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 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
}
Prashanth Jayaram 
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)"
LikeLike
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
LikeLike
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.
LikeLike
have you created a file c:\computer.txt and added servername like
c:\computer.txt
abc
def
-Prashanth
LikeLike
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..
LikeLike
Yes script is compatible with PowerShell 2.0. Can you tell me the steps? I doubt it will work with Gmail SMTP configuration.
-Prashanth
LikeLike
How am I going to send the report to multiple recipients?
LikeLike
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
LikeLike
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?”
LikeLike
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
LikeLike
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.”
LikeLike
Nice script, going to try and get it to where it only emails if there is a service stopped
JB
LikeLike
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.
LikeLike
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.
LikeLike
Great!!!!
LikeLike
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
LikeLike
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.
LikeLike
Hi Prashanth, what a great script. I am wondering if it is possible to specify a different set of credentials?
Any I ideas?
Thanks
LikeLike
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
LikeLike
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.
LikeLike
Hi Sudhakar,
Download the script and at the bottom of the script
just you need to calling function with the required parameter
for example, save the download file as Servervices.ps1
function Get-ServiceStatusReport
{
—
—
–
}
Get-ServiceStatusReport -ComputerList c:\computer.txt -ServiceName SQL -SMTPMail mail01.pa.com -To pjayaram@ap.com -From pjayaram@ap.com
Create the task scheduler job
Refer the blog on how to create the task scheduler job
–Prashanth
LikeLike
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..
LikeLike
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 ?
LikeLike
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
LikeLike
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?
LikeLike
Hi Sudhakar,
Did you try to give SQL Server Virtual Cluster name?
–Prashanth
LikeLike
Hi Sudhakar,
Refer the below link
–Prashanth
LikeLike
Hi Prashanth,
Is it possible to send htm file as attachment instead of as body in mail.
With Thanks and regards,
Prasad
LikeLike
Hi Hari,
Its very simple. Can you refer the below link
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
LikeLike
thank you prashanth. It worked
LikeLike
Great!!
Thanks for reading my space. Happy Learning!!
–Prashanth
LikeLike
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.
LikeLike
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!
LikeLike
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
LikeLike
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!
LikeLike
Talderon,
Make sure that you’ve rights to said path. Try to change the path where you’ve full access.
–Prashanth
LikeLike
How to send email to multiple recipients ???
LikeLike
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
LikeLike
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
LikeLike
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
}
LikeLike
Refer the below post
sqlpowershell.wordpress.com/2016/01/14/powershell-monitoring-multiple-services-on-multiple-servers-using-wmi-class-win32_service/
LikeLike
I’m not getting entire servers list, my server list having 10 servers,I’m getting output only 6 servers.
LikeLike
RK,
Do you have access to all the servers?
–Prashanth
LikeLike
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
LikeLike
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
.
LikeLike
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
LikeLike
Script absolutely working fine, only out put not generating in HTMl format.
[image: Inline images 1]
Regards,
Kumara.R
LikeLike
Hi Prashanth,
if u get a free time please have a look on this.
Regards,
Kumara.R
LikeLike
Prashanth, do u have chace to look this?
LikeLike
Kumar,
Have updated the script. Try to execute this one and let me know. I have replied to your email as well.
–Prashanth
LikeLike
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
LikeLike
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
LikeLike
Hi Prashanth,
Do u have option for check this?
Regards,
Kumara.R
LikeLike
Hi Kumar,
I din’t get you.Can you give me more information?
Prashanth
LikeLike
Hi RK,
Get-WmiObject Win32_Service -ComputerName |Where-Object { ($_.StartMode -eq ‘Auto’ -or $_StartMode -eq ‘Manual’ -or $_.startMode -eq ‘Disabled’) -and $_.state -eq ‘Stopped’}
Can you check the output of the above query?
Let me know if this is what you are looking at your requirement
Prashanth
Prashanth
LikeLike
Hi Prashanth,
Please fwd me that query.
Regards, Kumara.R
LikeLike
Hi RK,
Change the in the below WMI query
Get-WmiObject Win32_Service -ComputerName |Where-Object { ($_.StartMode -eq ‘Auto’ -or $_StartMode -eq ‘Manual’ -or $_.startMode -eq ‘Disabled’) -and $_.state -eq ‘Stopped’}
Can you check the output of the above query?
Let me know if this is what you are looking at your requirement
Prashanth
LikeLike
Hi Prashanth,
I’m looking for only failed service with startup type ( Automatic ,Manual,Disabled) if the service is disabled our report showing as stopped. Regards,
Kumara.R
LikeLike
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
LikeLike
SQLAgent account should have enough privilege to write to a file. I think that is missing. Grant full access to specific folder and then give a try.
–Prashanth
LikeLike
Thanks, let me check, could please share me script for only failed SQL jobs for multiple servers and out put should come in HTML format.
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
Hi RK,
I don’t have handy script ready with me but I can write a one for you. When do you need it?
Did you searched over net? din’t you find any script?
–Prashanth
LikeLike
Thanks Prashanth, I have searched did not get right one. I’m looking for only failed jobs and out put should be HTML format.
LikeLike
RK,
Did you refer this link?
http://www.sqlservercentral.com/blogs/sqlsandwiches/2012/01/29/find-failed-
sql-jobs-with-powershell/
LikeLike
Posted by kumar_r19 on 15 February 2016
Got this error msg..
Exception calling “Send” with “1” argument(s): “Failure sending mail.”At line:77 char:1
+ $smtp.Send($msg)
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : SmtpException
LikeLike
Hi RK,
I think, you have not made right entry for SMTP address at the top of the code. Try giving a valid SMTP address.
$smtp = new-object Net.Mail.SmtpClient(“Valid SMTP”)
LikeLike
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)
LikeLike
Hi Prashanth,
do u have chance to look on this? The script is working for me but looking output HTML format.
LikeLike
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
LikeLike
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)
_____________
LikeLike
Refer the below post
https://sqlpowershell.wordpress.com/2016/03/02/find-failed-sql-job-in-last-
24-hours-using-powershell/
LikeLike
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
LikeLike
Hi Prashanth,
I’m looking for only failed service with startup type ( Automatic ,Manual,Disabled) if the service is disabled our report showing as stopped.
LikeLike
HI RK,
Can you send me the details with screen shot to my email?
Prashanth
LikeLike
Hi Prashanth,
I’m looking for only failed service status for multiple servers also add statup type column.
I have tried my self , getting empty out put.
ServerName ServiceName Status StatupType
[image: Inline images 2]
LikeLike
Kumar,
Can you try the below code and let me know the result?
I have not gotten your email and image as well.
************************
$serverName=’ABCDE’
$Results = @()
$s =Get-WmiObject Win32_Service -ComputerName $serverName|Where-Object { ($_.StartMode -eq ‘Auto’ -or $_.StartMode -eq ‘Manual’ -or $_.startMode -eq ‘Disabled’) -and $_.state -eq ‘Stopped’}| select name,ProcessId,ServiceType,startName,state,startMode,exitcode
foreach($status in $s)
{
if ($status.StartMode -eq “Disabled”)
{
$Properties = @{
ServerName=$serverName
Name = $status.name
ServiceeType = $status.ServiceType
startName = $status.startName
state=’Not Applicable’
startMode=$status.startMode
exitcode=$status.exitcode
status=’Disabled’
}
$Results += New-Object psobject -Property $properties
$Results | Select-Object ServerName,name,ServiceeType,startName,state,startMode,exitcode
}
else
{
$Properties = @{
ServerName=$serverName
Name = $status.name
ServiceeType = $status.ServiceType
startName = $status.startName
state=$status.state
startMode=$status.startMode
exitcode=$status.exitcode
status=’enabled’
}
$Results += New-Object psobject -Property $properties
$Results | Select-Object ServerName,name,ServiceType,startName,state,startMode,exitcode
}
}
Prashanth
LikeLike
Sent
LikeLike
Hi Prashanth,
In my environment we wont be using C$ on local desktop. In this case, I can’t create a server list on local drive. Is there anyway we write the script that can handle input file from a give location
LikeLike
Hello ylnra,
Did you try like below
Get-ServiceStatusReport -ComputerList \\abcd\c$\server.txt -includeService “MySQL”,”MpsSvc”,”W32Time” -To pjayaram@app.com -From pjayaram@ app.com -SMTPMail app01. app.com
If not please try, It should work.
Prashanth
LikeLike
Thanks for your response…I will try today
LikeLike
Hi Prashanth,
Thanks for the response…This is working fine with UNC path.
Intentionally I have made a wrong server entry in the file like as below
Server1—correct
Server2test —no server with this name /offline server
What I have observed is when an unknown server is in the list , The output is duplicating with know server (server1)
So is there anyway to handle the script while it is running on non-alive server during the report run
LikeLike
I have update the script, Please check.
You can also refer the below link. Almost similar,
Prashanth
LikeLike
Hi Prashanth,
I am not able to figure out why the report is generating with duplicate entries.
I have two server in my input file as below
SQLA (it my local desktop)
SQLB (one of my dev box)
I cold see duplicate entries for my send server. this my calling procedure…I wanted to see all my SQL Services.
Also,
Is there anyway to output failed servers to authenticate
Get-ServiceStatusReport -ComputerList C:\temp\server.txt -includeService SQL -To <>-From <> -SMTPMail <>
Output as below
MSSQL$SQL201201
ReportServer$SQL201201
SQLAgent$SQL201201
SQLBrowser
SQLWriter
MSSQL$SQL201201
ReportServer$SQL201201
SQLBrowser
SQLWriter
LikeLike
I think you have blank entries in the file. Can you validate the file make sure there is no new line or blank columns exists in the input file.
Regarding authentication, Please find my set of article which talk about authentication
http://social.technet.microsoft.com/wiki/contents/articles/35926.posh-step-by-step-disk-space-monitoring-guide.aspx
Prashanth
LikeLike
Hi Prashanth,
Thanks for useful post. i would like to call the script using batch file. could you please help me on that.
Thanks,
Siva
LikeLike