Monitoring failovers on clustered servers

I've been asked to write a script to monitor whether a clustered server is up and alive and if so which node it's actually running on. Apparently there's been some problems of failover to the passive server without anyone knowing that it happened and they want to know. Any suggestions?The script below will result in capturing the currently active node:

begin tran
create table #tmp (hostname varchar(128) null)
commit tran
insert #tmp exec master.dbo.xp_cmdshell 'hostname' , no_infomsgs
select * from #tmp where hostname is not null
go
drop table #tmp
go|||We're in an active/passive environment, so when I run this script I just get the virtual servername back.

Originally posted by rdjabarov
The script below will result in capturing the currently active node:

begin tran
create table #tmp (hostname varchar(128) null)
commit tran
insert #tmp exec master.dbo.xp_cmdshell 'hostname' , no_infomsgs
select * from #tmp where hostname is not null
go
drop table #tmp
go|||And what do you get when you run HOSTNAME from command line? I am getting the active node name. In fact, I went to our lab environment and ran it, then I forced a failover and got the other node's name - which proves that it workes as expected in identifying the active node.|||From the command line I do get the active server name, but when I run that script I get the virtual server name.

Originally posted by rdjabarov
And what do you get when you run HOSTNAME from command line? I am getting the active node name. In fact, I went to our lab environment and ran it, then I forced a failover and got the other node's name - which proves that it workes as expected in identifying the active node.|||SQL Service account should belong to local administrators group on the box.|||Yes, it does.

Originally posted by rdjabarov
SQL Service account should belong to local administrators group on the box.|||When you were testing the script, did you run it from QA or scheduled it as a job? As I said, I ran this script in both prod and lab environments, and even did a failover on the lab cluster, and hostname reported the correct active node. Anyone else sees something I missed?|||I've done it both ways - both through QA then through a ksh script and I'm still getting the virtual server name back. How odd!

Originally posted by rdjabarov
When you were testing the script, did you run it from QA or scheduled it as a job? As I said, I ran this script in both prod and lab environments, and even did a failover on the lab cluster, and hostname reported the correct active node. Anyone else sees something I missed?|||Yup, it sure is.