Mastodon
Showing posts with label Remote connection. Show all posts
Showing posts with label Remote connection. Show all posts

Sunday, 13 March 2011

Remote monitoring of disk space using PowerShell Part 1

Continuing on the theme of monitoring servers, I've been working on monitoring the available disk space on a number of our servers using PowerShell. My original intention was to call this from IPMonitor, but unfortunately that doesn't seem to reliably pick up the changing result fed back from the script, which obviously makes it a somewhat useless as method monitoring! In addition, while I could monitor these things on the servers themselves, I felt that left too much scope for a problem on the server also preventing alerts being generated to let us know.

Writing the script turned out to be quite a challenge, considering my limited experience with PowerShell, but it was certainly an exciting one. As it progressed the complexity slowly increased, sometimes due to the realisation that I hadn't taken something into account, for instance checking the script could even connect to the target server, and other times simply as I thought of additional ways I could improve and expand the functionality. To say I learnt a lot from the process would be an understatement, and I thought I'd share some of those discoveries with all of you.

First of all though is the script itself. As you can see below, I wrote it to be as generic as possible so anyone could easily use it again for another server, simply by adjusting the few details at the top. The script connects to the specified server, and loops through a list of drives testing that each one has more that the configured minimum disk space available. Since the requirement is different for each drive, this is configured individually per drive rather than script wide. If the test fails then the script generates an error e-mail including details of the available space, and the configured threshold. Now since I wanted the ability to run the script fairly regularly, but didn’t want to be constantly bombarded with alerts, the script keeps a record of when an alert has been sent, counts the number of times the test has failed since the alert and then doesn't send another one for a defined number of times. Finally, once the test passes again (eg you've resolved the space issue on the server), the script sends a notification to confirm that and resets the counters.

# Remote space monitoring script - by Keith Langmead 02/03/2011
#
Script connects to a remote server and checks the available disk space on each specified drive
#
the monitored drives and their individual thresholds are stored in the $serverdrives hash.
#
For each failure an e-mail alert is generated the first failure, repeat alerts are generated
#
at the frequency specified in $alertthreshold, and once the available space is above the
#
threshold again a recovery e-mail is generated.

# ------ Config Section -------

$mailfrom="Alert Address <alert@domain.com>"
$mailto="Keith Langmead <me@domain.com>"
$mailserver="mail.domain.com"
# contains drives to be monitors, "<drive letter>"=<min mb> with multiple drives separated by a semi-colon
$serverdrives=@{"c"=30000;"e"=15000;"f"=20000;"g"=20000}
$servername="My Server"
$serverip="192.168.0.1"
# specifies how many times to skip alerting
[INT]$alertthreshold=12

$encrypted = "<encrypted password>"
$username = "MyServer\MyUser"
$password = ConvertTo-SecureString -string $encrypted
$myCred = New-Object System.Management.Automation.PSCredential $username, $password

# ---------------------------------------

# Function code from http://halr9000.com/article/506
function df ( $Path ) {
if (!$path) {$path = (get-location -psprovider filesystem).providerpath}
if (!($Drive = (get-item $path -ea silentlycontinue).root -replace "\\")) {$Drive = $Path}
$output = get-wmiobject -query "select freespace from win32_logicaldisk where deviceid = `'$drive`'" -computername $serverip -Credential $myCred
return [INT]"$($output.freespace / 1mb)"
}

if (test-connection -computername $serverip -Quiet)
{
write-host "Connection to $servername successful"
# Loop through the list of drives specified above
foreach ($sd in $serverdrives.keys)
{
$sdpath=$sd+":"
# Define the environment variable names for the current drive
$envVarServerFail = "spaceMonitorEmail" + $servername + $sd.ToUpper() + "fail"
$envVarServerCount = "spaceMonitorEmail" + $servername + $sd.ToUpper() + "count"
# Call the DF function to find available space on current drive
$currentspace=df $sdpath
# If the available space is above the threshold
if (($currentspace) -gt $serverdrives.Get_Item($sd)) {
write-host "Space is fine on " $sd":"
# If the test previously failed generate a recovery e-mail
if ([Environment]::GetEnvironmentVariable($envVarServerFail, "User") -eq 1) {
$minspace=$serverdrives.Get_Item($sd)
$driveletter=$sd.ToUpper()
$mailsubject="[Notifier] $servername Disk Space Notification $driveletter Drive Recovered"
$bodytext="The free space on $sdpath is back above specified limits. Current space available is $currentspace MB and the threshold for alerts is $minspace MB"
send-mailmessage -from $mailfrom -to $mailto -subject $mailsubject -body $bodytext -smtpServer $mailserver
write-host "recovery e-mail sent"
}
# Reset the environment variables to 0
[INT][Environment]::SetEnvironmentVariable($envVarServerFail, 0, "User")
[
INT][Environment]::SetEnvironmentVariable($envVarServerCount, 0, "User")
}
else {
# If the test failed and it's either the first time or the failure count is above the alert threshold send a notification
if ([Environment]::GetEnvironmentVariable($envVarServerFail, "User") -eq 0 -OR [INT][Environment]::GetEnvironmentVariable($envVarServerCount, "User") -gt [INT]$alertthreshold) {
$minspace=$serverdrives.Get_Item($sd)
$driveletter=$sd.ToUpper()
$mailsubject="[Notifier] $servername Low Disk Space Notification $driveletter Drive"
$bodytext="The free space on $sdpath is getting low. Current space available is $currentspace MB and the threshold for alerts is $minspace MB"
send-mailmessage -from $mailfrom -to $mailto -subject $mailsubject -body $bodytext -smtpServer $mailserver
write-host "failure e-mail sent"
# Set fail and count to 1 or reset count back to 1
[INT][Environment]::SetEnvironmentVariable($envVarServerFail, 1, "User")
[
INT][Environment]::SetEnvironmentVariable($envVarServerCount, 1, "User")
}
else {
# If the test failed, it's not the first time and not above the alert threshold increment the count
[INT]$incFailVar = [Environment]::GetEnvironmentVariable($envVarServerCount, "User")
[
INT]$incFailVar = $incFailVar + 1
[
INT][Environment]::SetEnvironmentVariable($envVarServerCount, $incFailVar, "User")
}
}
# --- Start Debugging info - so you can see what is happening when running manually, you can remove this section if you want
write-host "Current space on " $sd": is" $currentspace " MB with minimum threshold of " $serverdrives.Get_Item($sd) "MB"
$foo1 = [Environment]::GetEnvironmentVariable($envVarServerFail, "User")
write-host $envVarServerFail "equals " $foo1
$foo2 = [Environment]::GetEnvironmentVariable($envVarServerCount, "User")
write-host $envVarServerCount "equals " $foo2
# --- End Debugging info
}
}
else {
write-host "connection to $servername failed, script aborted"
}

I think I'll leave it there for today, but I will be back to write more about the issues I found, including using user level Environment variables, hash tables and type setting.

Finally, my thanks to jrich, Kazun and Albert Widjaja on http://social.technet.microsoft.com/Forums/en/winserverpowershell/thread/55ae39fd-d585-4579-8648-61e8c949bd22 for their help while I was working on getting this script to work.

Monday, 15 November 2010

Encrypting PowerShell credential passwords

So continuing from my last post, after getting my monitoring script to work, I set about trying to introduce some semblance of security to at least partially hide the plain text password held within the script.

My initial success was through the use of the ConvertTo-SecureString and ConvertFrom-SecureString cmdlets. Essentially (or least as far as I understand it), the first of these converts some form of password string into some kind of system value, eg if you try simply outputting the contents of $password in the first script, it simply returns something like 'Secure-String', rather than a representation of the password itself. The second cmdlet converts that system value into an actual string value, though it's encrypted rather than the original ascii text as we'll see shortly.

So, my first task was to get hold of that encrypted version of the string, so I could then put that direct into the script, and use it in place of the plain text password. Using the following code I encrypted the password and then outputted it into a text file.

$password = "<password>"
$secure = ConvertTo-SecureString $password -force -asPlainText
$bytes = ConvertFrom-SecureString $secure
$bytes | out-file c:\batchscripts\securepassword.txt

Extracting the resulting string from the text file, I added it into my script, and then adjusted the ConvertTo-SecureString line to receive an encrypted string rather than a plaintext one as you can see below.

$encrypted = "01000000d0818c7a00c04fc297eb0100005facd6ca6de9cea76f00d66edd9b000200000ad8d083414008862f428071c6ae0100e0000bc55db88fe323270008f8719303e96f00000a81f1800000003660000a8000000100000009b2d8ff163728442ad93544b7dccad8500000000048000c9ddf0115d1100a000e0045dfadc703191efaac7e6000490ad18f2ced20db2ccabac0000b2b660bd2a75dc73ba7a278f448"
$username = "<username>"
$password = ConvertTo-SecureString -string $encrypted
$myCred = New-Object System.Management.Automation.PSCredential $username, $password
if (test-connection -computername <computername> -source <source> -Credential $myCred -Quiet)
{"1";exit "1"}
else {"2";exit "2"}

To my relief this then worked, exactly like the original script! Filled with joy (ok, maybe not joy, but certainly pleased with myself), I updated the monitoring system to use the new script, set it running, and can you guess what happened? Yep, it failed. I went through all the settings, they were fine. I switched back to using the old script, it worked again.

After scratching my head for a while I had an idea, possibly a crazy one, but worth a try. I set the monitoring system to execute the script that generates the encrypted password so I could see what the output would look like. It was completely different to the one outputted direct on the server! I re-ran the server version, and sure enough, it produced the same output as it did the first time.

Clearly something about the encryption process used in ConvertTo-SecureString is dependant on where it is run. I updated the script with the new string, set the monitoring system going again, and finally it worked!

So the lesson here is that if you try this yourself, what ever system you are using to call your script, you must also get it to call the encryption script as well, and use THAT output to populate your monitoring script with its password.

Friday, 12 November 2010

Remote connection monitoring with PowerShell and IPMonitor

Recently I've been trying to find a way to use a combination of PowerShell and our monitoring system IPMonitor to check connections between two sites.

Picture the scene, three different sites, A, B and C. At site A we have a monitoring system to check various aspects of the services we look after, sites B and C contain various servers, with VPN links joining A to B, A to C and B to C. From A I can check that the VPN links to B and C are up, but what about the link between B and C? That's where I figured PowerShell could come in handy.

After a bit of searching I found the test-command cmdlet which not only allows you to test a connection like you would with ping, but also to specify the source server to test from. After a little tinkering I came up with the following :

$username = ""
$password = ConvertTo-SecureString "" -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential $username, $password
if (test-connection -computername -source -Credential $myCred -Quiet)
{"1"}
else {"2"}

As you can see, running the script outputs either 1 or 2 depending on the result of the test. Within IPMonitor I then used an External Process Monitor to call the script, compare the script output with the test value (1 obviously), and then display if the connection is up.

The final piece to the puzzle was making the whole thing at least a bit more secure. For the terminally paranoid, having a script sat there containing a server password doesn't really fill you with joy, so I managed to encrypt the password and use that instead. As it turned out though, doing that in conjunction with IPMonitor turned out to be harder than expected, but I'll go into details on that later.

Wednesday, 18 August 2010

Remote connections to an MSDE database using Enterprise Manager

I recently had an interesting challenge at work. A 3rd party app we use had stopped working, and was generating an error when it tried to start. The error suggested a problem with its MSDE database, though unfortunately it didn't provide a lot of detail. As the resident SQL "expert" I was asked to look at it, and see if I could find a way to repair the database, since the alternative would be a re-installation, and a lot of work re-configuring everything.

Now I've never really played with MSDE before, just full fat SQL, but I soldiered on to see what I could come up with. After trying the obvious things using osql I'd quickly exhausted all the ideas I could think of, and found every article, post or blog relating to database repair made reference to using Enterprise Manager... not much use to me you might think! Well with a little searching I found that you can connect to an MSDE instance using EM, but it's not that straight forward, especially if you're connecting from a different computer. All the information is out there, but even after the fact I can't find anywhere that pulls it all together... so here goes!

There are two or three steps required, depending on whether you are connecting from a local or remote EM installation.

1) Enable TCP/IP on the MSDE database.

Run 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe' on the MSDE machine, and then select tcp/ip and move it to enabled protocol.

2) Create a sysadmin user in the database to allow you to login.

Connect to the MSDE instance by running :

'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql -E'

on the MSDE machine. Once connected enter the following commands (nothing will happen in osql until after the 'go' statement) :

1> use master
2> EXEC sp_addlogin 'YourSysAdminUserName','YourPasswd'
3> EXEC sp_addsrvrolemember 'YourSysAdminUserName', 'sysadmin'
4> go

Now on a local installation this may be enough, however on a remote installation you will likely receive an error :

"Login failed for user 'YourSysAdminUserName'. Reason: Not associated with a trusted SQL Server connection."

in which case you will need to change the authentication mode of your MSDE instance to Mixed Mode to allow you to connect with your new SQL login, so...

3) Change MSDE authentication mode from Windows Integrated to Mixed.

(The following is taken direct from MS KB285097)

---
To set this key to mixed-mode, follow these steps:

1. Stop SQL Server and all related services, such as SQLAgent, from the Services control panel.
2. Click Start, click Run, type regedt32, and then click OK.
3. Find the HKEY_LOCAL_MACHINE window on the local computer.
4. Navigate to the following registry key for the default instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer
5. Navigate to the following registry key for a named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode
6. On the right-hand pane, find the value LoginMode and double-click it.
7. In the DWORD Editor window, set the value to 0 or 2. Also, make sure that the Hex radio button is selected.
8. Click OK.

Restart SQL Server and SQL Server Agent services for this change to take effect.

Note When you change the default login authentication mode from Windows NT authentication to SQL authentication, we recommend that you change the sa password to a strong password.
---

So that was an interesting day in the office! Annoyingly after all that and many hours of fiddling I still manage to repair the problem myself. I was able to repair it with a 3rd party recovery tool, but it would have been so much more satisfying to have managed it myself.


References :
http://forums.deftechgroup.com/showthread.php?t=204
http://support.microsoft.com/kb/889615
http://support.microsoft.com/kb/285097