Solved

Powershell Script to alert when cluster node failover from active to passive node

Posted on 2016-09-26
7
51 Views
Last Modified: 2016-10-16
On Windows 2012 R2, I have a two-node cluster that I need to monitor using Powershell. If I am not always paying attention, I could have a failover go unnoticed for days. Just wondering if anyone has a script that will catch these failovers and notify me via email.

Example: say that NodeA is the active node and NodeB the passive node, I need to know when the resources have moved to the passive node making it the new active node. So the notification would say something like this: NodeB is now the active node [in cluster] for the last 2 hours and 15minutes, etc. Thanks in advance!
0
Comment
Question by:eviem
  • 4
  • 3
7 Comments
 
LVL 40

Expert Comment

by:Subsun
ID: 41816829
You can either use the event log alerts. to alert for fail over events..
Ref: https://blogs.technet.microsoft.com/jhoward/2010/06/16/getting-event-log-contents-by-email-on-an-event-log-trigger/

Or a simple script like following to alert. Following script check if the OwnerNode is changes to SQLTEST02 on any resources, if yes, send an email alert.
Import-Module FailoverClusters
$Cluster = Get-ClusterGroup -Cluster SQLTESTCLUST |?{$_.OwnerNode -eq "SQLTEST02"}
If ($Cluster){
	$Message = "OwnerNode is now SQLTEST02, Check cluster SQLTESTCLUST" 
	Send-MailMessage -From admin@mail.com -To admingroup@mail.com -SmtpServer smtp.mail.com -Subject $Message -Body $Message
}

Open in new window

Replace cluster name, node name etc based on your environment.. You can schedule this script to run every 15 in, so every 15 in the script will check the status and trigger email, if there is a matching scenario...

How to Use the Windows Task Scheduler to Run a Windows PowerShell Script
Ref : https://blogs.technet.microsoft.com/heyscriptingguy/2012/08/11/weekend-scripter-use-the-windows-task-scheduler-to-run-a-windows-powershell-script/
0
 

Author Comment

by:eviem
ID: 41816895
Subsun,
 Thanks for the quick response. Once I get the email, the goal is to further research to find the root cause of the failure. However, since I will not be failing it back to the original node, SQLTEST01 for example, how do I stop getting the same email every 15 minutes "OwnerNode is now SQLTEST02, Check cluster. In other words, once I get the email saying that SQLTEST02 is the ownernode, I don't want to get another email until SQLTEST01 becomes the ownernode again (another failover). Thanks!
0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points (awarded by participants)
ID: 41816917
Well, In that case, you can store the current status in a csv file and keep checking it. If the cluster status is different from the status in file then trigger an email. By this way you can avoid continues email alerts,

Example..
$path = "C:\Scripts\FailoverClusters.csv"
$Cluster = "SQLTESTCLUST"
Import-Module FailoverClusters
#If file doesn't exist create a file with current status
If (!(Test-Path $path)){
 Get-ClusterGroup -Cluster $Cluster | Select Name,OwnerNode,State | Export-csv $path -nti
}
$ClusterStatus = Get-ClusterGroup -Cluster $Cluster | Select Name,OwnerNode,State
If (Compare $ClusterStatus (Import-Csv $path)){
   $ClusterStatus | Select Name,OwnerNode,State | Export-csv $path -nti
	$Message = "Cluster State Changed Please Investigate" 
	$Body = $ClusterStatus | Select Name,OwnerNode,State | ConvertTo-Html | Out-String
	Send-MailMessage -From admin@mail.com -To admingroup@mail.com -SmtpServer smtp.mail.com -Subject $Message -BodyAsHtml $Body
}

Open in new window

Check and see if it works for you..
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:eviem
ID: 41818144
Subsun,
Again thanks for your assistance. I am not able get an email even though the script appears to run successfully. I have done several failovers but still no luck. I know the email part is correct because I am able to get the email using the first script. Not sure if the compare part is working correctly. Wondering if you are someone can check and verify for me.
0
 
LVL 40

Assisted Solution

by:Subsun
Subsun earned 500 total points (awarded by participants)
ID: 41818185
I don't see any issue with script on triggering mail.. Did you try to run the script from PowerShell console and see if you get the email?

Also check if the status in CSV file and result of following command is matching after failover.
Get-ClusterGroup -Cluster SQLTESTCLUST | Select Name,OwnerNode,State
Import-Csv C:\Scripts\FailoverClusters.csv

Open in new window

0
 

Author Comment

by:eviem
ID: 41842035
Yes, we can close it. It does work but I just need to do some more editing to have it do exactly what I want it to do. I have been very busy lately so I just have not been able to dedicate the time to it. Please award the points to Subsun, and again, I appreciate the effort.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 41845509
Script was tested and worked for me.. Unless @eviem have any objection the question can be closed..
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

"Migrate" an SMTP relay receive connector to a new server using info from an old server.
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now