Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

PowerShell- Monitoring Group Of Services on Group of Servers with Alternate Credential

This post explains how to monitor a multiple services on a group of servers with alternate credentials.

This script will allow you to read a list of servers from the input file and use credentials to connect remote/DMZ servers to pull service status information for a set of specific services and send services status information to all intended recipients

Get-Service cmdlet don’t accept credentials parameter hence I’ve used win32_service win32 class which accepts the credential parameter. You need to comment or uncomment the required portion of code.

Most of the time it’s not good practice to hard code the credentials. In that case you might need encrypt the password and use the secured code in the script. You need to run the below Powershell script to pull the encrypted password to c:\SecurePassword.txt and copy code in the script.

$password = read-host -prompt "Enter your Password"  
$secure = ConvertTo-SecureString $password -force -asPlainText  
ConvertFrom-SecureString $secure |Out-File c:\SecurePassword.txt
 In the below example, Test@2013##) password is encrypted and it’s content is shown below
 

 There are some instance where you are OK to store the password in the script itself. 

Refer the below screenshot and change the code according to your requirement

 

The Get-ServiceStatusReport

  1. Credential to connect to DMZ server
  2. HTML Ouptut
  3. 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

 Download the code 

http://gallery.technet.microsoft.com/PowerShell-Group-Of-d0010648

Sample Call:-

Get-ServiceStatusReport -ComputerList C:\servers.txt -includeService  “Dfs”,”Dhcp” -To <pjayaram@app.com> -From <pjayaram@app.com> -SMTPMail <SMTPMail>

Output:-

 

You can also refer the below link if you want to run the script with default account

http://sqlpowershell.wordpress.com/2013/12/04/powershell-monitoring-multiple-service-on-a-group-of-servers/

 

 

PowerShell
<# 
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 
 
Sample Call:- 
 
Get-ServiceStatusReport  
-ComputerList C:\servers.txt  
-includeService  "Dfs","Dhcp"  
-To <pjayaram@app.com> 
-From <pjayaram@app.com> 
-SMTPMail <SMTPMail> 
#> 
 
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 
 
 
# Enter the Credentials details 
<# 
$password = read-host -prompt "Enter your Password"  
write-host "$password is password"  
$secure = ConvertTo-SecureString $password -force -asPlainText  
ConvertFrom-SecureString $secure |Out-File c:\SecurePassword.txt 
#> 
 
#Replace the $encrypted value from the contents of c:\SecurePassword.txt  
 
$encrypted = "01000000d08c9ddf0115d1118c7a00c04fc297eb01000000efa85d14f32d8648a2d335e29d3f57f6b"  
$user = "testwint"  
$password = ConvertTo-SecureString -string $encrypted  
$Credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist $user,$password 
 
<# Hard code the the Credentials details 
 
$User = “testcog” 
$Pass = ConvertTo-SecureString “testasd#%)” -AsPlainText -Force 
#contain the username and password in a variable 
$Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User,$Pass 
 
#> 
 
# 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,$Statusif$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 include 
 
#$InlcudeArray = $inlcudeService 
 
Foreach($ServerName in (Get-Content $script:list)) 
{ 
$service = Get-WMIObject Win32_Service -computer $ServerName -credential $Credentials 
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  $item.MachineName $item.name $item.Status  
    writeDiskInfo $ServiceFileName $ServerName $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 
 
} 

Comments

Leave a comment on the original post [sqlpowershell.wordpress.com, opens in a new window]

Loading comments...