Hi Friends,

I am new member of  powershellcommunity,

Auto send email alert when sql server services stopped unexpected. this script is working fine. but i could not able to attachement body of email in text file. In "D" drive i have created bodymsg.txt, can anyone help me how to add text file and send email CC copy to different mailid.

$servers=get-content "D:\services\servers.txt"
foreach($server in $servers) {
 # go to each server and return the name and state of services
# that are like "SQLAgent" and where their state is stopped
 # return the output as a string
$body=get-wmiobject win32_service -computername $server |
select name,state | where {
($_.name -like "MSSQLSERVER" -or $_.name -like "SQLSERVERAGENT*") ` -and $_.state -match "Stopped"}

if ($body.Length -gt 0) {
 #Create a .net mail client $smtp = new-object Net.Mail.SmtpClient("10.4.54.22")
 $subject="Microsoft SQL Service & SQL Agent Service is down on " + $server
$msg.body = (D:\services\BodyMsg.txt)
$smtp.Send("JERPCOKER@RIL.COM", "ananda.murugesan@ril.com", $subject, $body,$msg.body )
"message sent" }
 }

Error :
Property 'body' cannot be found on this object; make sure it exists and is settable. At D:\services\servicescheck1.ps1:21 char:8 + $msg. <<<< body = (D:\services\BodyMsg.txt) Cannot find an overload for "Send" and the argument count: "5". At D:\services\servicescheck1.ps1:22 char:13 + $smtp.Send <<<< ("JERPCOKER@RIL.COM", "ananda.murugesan@ril.com", $subject, $body,$msg.body) message sent

Thanks

Looks like you are trying to use a MailMessage object, but forgot to create one in your code where you reference $msg.body. In order to send an attachment through the smtpClient you need to use a System.Net.Mail.MailMessage object. It often helps to read the MSDN document, look at the C# or VB.NET code samples and translate them to Powershell.

http://msdn.microsoft.com/en-us/lib...ssage.aspx

Here's what I came up with by doing just that. The code hasn't been tested...

$from = new-object System.Net.Mail.MailAddress 'JERPCOKER@RIL.COM'
$to = new-object System.Net.Mail.MailAddress 'ananda.murugesan@ril.com'
$msg New-Object System.Net.Mail.MailMessage ($from,$to)

$subject="Microsoft SQL Service & SQL Agent Service is down on " + $server
$msg.Subject = $subject

$attachment = New-Object System.Net.Mail.Attachment "D:\services\BodyMsg.txt"
$msg.Attachments.Add($attachment)

$smtp = new-object Net.Mail.SmtpClient("10.4.54.22")
$smtp.Send($msg

Hi,
Thanks for your reply..

Your script attachement was working fine, but i need one more help from you.

Email Body out-string messages has comming, but i need out-string result with body message, please givel me how tto do?

$servers=get-content "D:\services\servers.txt"
foreach($server in $servers)
{
$body=get-wmiobject win32_service -computername $server |
select name,state |
where {($_.name -like "MSSQLSERVER" -or $_.name -like "SQLSERVERAGENT*") `
-and $_.state -match "Stopped"} | Out-String

--> here i want message like -
$txt= @*Message from JerpCoker Test Setup Server DFG12343, Microsoft SQLSERVER Services unexpected stopped, Please kindly check it ASAP and avoid downtime SQLSERVER. This is an auto generated mail notification from Micosoft SQLSERVER2005, Please do not reply.*@


if ($body.Length -gt 0)
{
$smtp = new-object Net.Mail.SmtpClient("10.4.54.22")
$subject="Microsoft SQL Service & SQL Agent Service is down on " + $server
$from="JerpCoker@ril.com"
$msg = New-Object system.net.mail.mailmessage
$msg.From = $from
$msg.To.add("ananda.murugesan@ril.com")
$msg.Subject = $subject
$msg.Body = $body
$Attachment = New-Object System.Net.Mail.Attachment "D:\services\BodyMsg.txt"
$msg.Attachments.Add($Attachment)
$msg.txt= $txt
$smtp.Send($msg)
}
}

Thanks

Looking at the MailMessage class documentation there isn't a txt property i.e. $msg.txt in your script. Sounds like you want to set the body property to something like the example you've provided. You could do something like this:

$servers=get-content "D:\services\servers.txt" 
foreach($server in $servers)
{
$body=get-wmiobject win32_service -computername $server |  
where {($_.name -like "MSSQLSERVER" -or $_.name -like "SQLSERVERAGENT*") `
-and $_.state -match "Stopped"} | select SystemName, Name
}


foreach ($item in $body)
{
$txt = @"
*Message from JerpCoker Test Setup Server $($item.SystemName), Microsoft $($item.name) Services unexpected stopped, Please kindly check it ASAP and avoid downtime $($item.name). This is an auto generated mail notification from Micosoft SQLSERVER2005, Please do not reply.*
"@

$smtp = new-object Net.Mail.SmtpClient("10.4.54.22")
$subject="Microsoft SQL Service & SQL Agent Service is down on " + $server 
$from="JerpCoker@ril.com" 
$msg = New-Object system.net.mail.mailmessage
$msg.From = $from 
$msg.To.add("ananda.murugesan@ril.com")
$msg.Subject = $subject 
$msg.Body = $txt
$Attachment = New-Object System.Net.Mail.Attachment "D:\services\BodyMsg.txt" 
$msg.Attachments.Add($Attachment)
$smtp.Send($msg)
}

Hi cmille19 thanks a lots, the above scripts is working fine.

i created one batch file like 'servicecheck.bat',
this contain powershell.exe -command D:\services\FinalServiceCheck.ps1 and batch file configured at system schedule task when system starup event and batch file running always the shedule task.

If whenever sql services stop and  batch file sending contunious email, i need recevied mail one time when service stop, so please give me suggestion how to control sequence mail alert and how to shedule batch file?

Thanks.
ananda

There are a few different ways to implement Alert suppression. Probably the easiest is to touch an empty file and then compare the LastWriteTime to whatever time threshold. Here's an example which assumes you've already created the lastupdate.txt file and sets the threshold to 60 minutes.

$threshold = 60

foreach ($item in $body)
 ...
if ($(new-timespan $(get-childitem D:\services\lastupdate.txt).LastWriteTime $(get-date)).Minutes -gt $threshold)
{
$smpt = new-object ... 
$( get-childitem d:\services\lastupdate.txt).LastWriteTime = get-date
$smpt.Send($msg)
}