DaveWentzel.com            All Things Data

CheckServices

# Function to check windows services related to SQL Server
Function checkservices  ([string] $Hostname )
{
$Services=get-wmiobject -class win32_service -computername $hostname|where {$_.name -like '*SQL*'}| select-object 
Name,state,status,Started,Startname,Description
foreach ( $service in $Services)
{
if($service.state -ne "Running" -or  $service.status -ne "OK" -or $service.started -ne "True" )
{
$message="Host="+$Hostname+" " +$Service.Name +" "" +$Service.state +" +$Service.status +" " +$Service.Started +" " +$Service.Startname
write-host $message -background "RED" -foreground "BLACk"
}
else
{
$message="Host="+$Hostname+" " +$Service.Name +" " +$Service.state +" " +$Service.status +" " +$Service.Started +" " +$Service.Startname
write-host $message -background "GREEN" -foreground "BLACk"
}
}
}

1 comments

I am trying to pass input as text file (list of servers) to the foreach loop and call these function checkservice inside the foreach loop
foreach($hostname in get-content "C:\Users\lagala\Desktop\sql1.txt")
{

if (test-Connection -ComputerName $hostname -Count 2 -Quiet )
{

#call function

checkservices $hostname

}

else

{
$smtpServer = "xxxx.com "
$smtpPort = 25
$emailFrom = "xxxxxxxca"
$emailTo = "xxxxxxxca"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "SQL service is stopped state"
$body = "body"
$smtp.Send($emailFrom, $emailTo, $subject, $body)

} }

# Function to check windows services related to SQL Server
Function checkservices ([string] $Hostname )
{

$Services=get-wmiobject -class win32_service -computername $hostname|
where {$_.name -like '*SQL*'}| select-object
Name,state,status,Started,Startname,Description

foreach ( $service in $Services)

{
if($service.state -eq "Running" -or $service.status -eq "OK" -or $service.started -eq "True" )
{
#Do nothing
$smtpServer = "xxxx.com "
$smtpPort = 25
$emailFrom = "xxxxxxxca"
$emailTo = "xxxxxxxca"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "test"
$message="Host="+$Hostname+" " +$Service.Name +"
" +$Service.state +" " +$Service.status +"
" +$Service.Started +" " +$Service.Startname
$body = $message # changed
$smtp.Send($emailFrom, $emailTo, $subject, $body)

}

else
{
$smtpServer = "xxxx.com "
$smtpPort = 25
$emailFrom = "xxxxxxxca"
$emailTo = "xxxxxxxca"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "SQL service is stopped state"
$message="Host="+$Hostname+" " +$Service.Name +"
" +$Service.state +" " +$Service.status +"
" +$Service.Started +" " +$Service.Startname
$body = $message # changed
$smtp.Send($emailFrom, $emailTo, $subject, $body)

}
}
}

Add new comment