Apr 24 2009 4:15PM GMT
Posted by: Colin Smith
Powershell,
SQL Server,
DBA,
Monitoring,
Database Administration,
Windows Administration
Not long ago I did a post about pinging the interface of a server since you may encounter a problem connecting to WMI even if a server is up. The article is at http://itknowledgeexchange.techtarget.com/dba/powershell-ping/.
So Like I describe in that post I did not want our DBA team to be paged about a SQL Server being down when it was really an issue with the host server. In that case I would like the Windows Administration team to be notified so that they can take care of the issue. I also wanted to check on some other things about the server. I wanted to check the physical interface, check that the agent service is running, and also verify that connections to the database can be made. I have finished the script to do this and I will go over it with you a function at a time. First we will look at the Main function. This is where everything gets set up.
#################################
####### Start Script ############
#################################
$ErrorActionPreference
=
“SilentlyContinue”
$time
=
Get-Date
-displayhint
time
$date
=
date
New-PSDrive
i
-PSProvider
filesystem
-Root \\SomeServer\SomeShare
if (test-path
“i:\OUT\ping_with_service_check.txt”)
{
Clear-Content
-path
“i:\OUT\ping_with_service_check.txt”
}
##Read in List of SQL Servers And set up all needed variables for script
$NTusers
=
“phx.it.systems.nt@pni.com”
$servers
=
Import-Csv
-Path
“i:\inputs\sqltab.txt”
foreach ($server
in
$servers)
{
$machine
=
$server.server
$instance
=
$server.instance
$torp
=
$server.torp
$ping
=
$server.ping
if ($instance
-eq
“NULL”)
{
$SqlServer
=
$server.server
$folder
=
$server.server
$sqlagent
=
“sqlserveragent”
$sqlserverservice
=
“mssqlserver”
}
else
{
$SqlServer
=
“$machine\$instance”
$folder
=
“$machine-$instance”
$sqlagent
=
“sqlagent`$$instance”
$sqlserverservice
=
“mssql`$$instance”
}
echo
“`n############################”
echo
“`n############################” >> “i:\OUT\ping_with_service_check.txt”
$machine >> “i:\OUT\ping_with_service_check.txt”
$machine
if ($ping
-eq
“Y”)
{
if (test-path
“i:\$folder\wmi_Failure.txt”)
{
$wmierror
= 1
$wminoemail
= 1
$wmifile
=
Get-ChildItem
“i:\$folder”
|
where{$_.name
-eq
“wmi_failure.txt”}
$filetime
=
$wmifile.lastwritetime
if($filetime-le
$date.AddDays(-1))
{
del
“i:\$folder\wmi_Failure.txt”
}
}
if (!(test-path
“i:\$folder\wmi_Failure.txt”))
{
$wmierror
= 0
$wminoemail
= 0
}
if (test-path
“i:\$folder\ping_Failure.txt”)
{
$pingfail
= 1
$pingnoemail
= 1
$pingfile
=
Get-ChildItem
“i:\$folder”
|
where{$_.name
-eq
“ping_failure.txt”}
$pingfiletime
=
$pingfile.lastwritetime
$pingfiletime
if ($pingfiletime
-le
$date.AddHours(-6))
{
echo
“File was created more than 1 minute ago. $pingfiletime”
del
“i:\$folder\ping_failure.txt”
}
}
if (!(test-path
“i:\$folder\ping_Failure.txt”))
{
$pingfail
= 0
$pingnoemail
= 0
}
if (test-path
“i:\$folder\agent_Failure.txt”)
{
$agentfail
= 1
}
if (!(test-path
“i:\$folder\agent_Failure.txt”))
{
$agentfail
= 0
}
Ping_Interface
}
if ($ping
-eq
“N”)
{
echo
“$sqlserver - is not being pinged at this time. Check SQLTAB” >> i:\OUT\ping_with_service_check.txt
echo
“############################” >> “i:\OUT\ping_with_service_check.txt”
}
}
Ok so you can see from looking at the script that this is where the script starts even though, you will see when you see the entire script, that this is the end of the script. That is because in Powershell you must define all functions before the script begins.
As you can see I start out by setting some things upand clearing out my log file. You could just keep appending to the file but I chose not to. You can also see that I set up a variable called $servers. This is populated by a .csv file that I have that has all the information about my SQL Servers that I am going to need for this script and for my other monitoring script. The heading of that file is as follows.
Monitor,Server,Instance,TorP,ErrorLog,Ping,OS2000
This way I know if I want to monitor the server, I may choose not to if it is out of commission for a while, I know the hostname, instance name, if it is Test or Production, if I want to run this Ping Servers Script on it and if it is running Windows Server 2000 as I can not run powershell locally on those servers.
Now that I have that variable populated I can start my loop. I start setting up each individual variable that I am going to need later on. Now I start looking for some touch files called wmi_failure.txt and ping_failure.txt. These are so I will know if either a wmi call or a physical interface ping failed on the last run of the script. On both of these I do not want to send out notification every 10 minutes but for wmi failures just once a day and for interface failures once every 6 hours. Now you may be asking why only every 6 hours if a servers interface is down since that means the server is basically useless. I would agree with you, but remember I wanted to notify our Windows Team about this issue and this was there call. No pages and only email us once every 6 hours about the issue. I was shocked at this response. Anyway, I look at the file if it is there and then I check how old it is based on the current time of the script running. If it has been passed the allotted amount of time then I remove that file and set the failure variables to 0 so the script will know to send out notifications about the failures should they still occur. Once I set all that up I can call my firs function if this script should be run against the server. That is noted by $ping being equal to Y or N. Next time I will show you my Ping_Interface function.
If you have any questions or comments about this script please let me know at http://sysadminsmith.com and click the ‘Submit a Question’ link to the right.
0 Comments
RSS Feed
Email a friend
