Nagios Checks for Dell OpenManage Disk Health Written in PowerShell

I wrote these in PowerShell to run with nrpe/nsclient.  They query Dell OpenManage’s command line and return a Nagios-readable result.

(There are plugins available on Nagios Exchange, but they all seemed to be… more than I wanted.  I just wanted to know, “Is my RAID okay?”  Because whenever I’ve had an Event, I like to monitor for that event happening again.)

This checks the Physical Disks in the array.  If one or more disks reports anything other than OK, it alerts:

$status = 0; omreport storage pdisk controller=0 | Where-Object {$_ -match "^status"} | %{if($_ -notlike "*OK*"){$status=2}}

If ($status -eq 0) {
Write-Host "OK:  Physical Disks report OK"
} else {
Write-Host "CRITICAL:  Check OpenManage"
}
exit $status

You might have to edit the scripts to check Virtual Disk health.  It could probably be made more elegant, but it suits my purposes.

This script checks the health of my C drive (vdisk 0):

omreport storage vdisk controller=0 vdisk=0 | ?{$_ -match "^status"} | %{$status=0}{if($_ -notlike "*OK*"){$status=2}}

If ($status -eq 0) {
Write-Host "OK:  Virtual Disk (OS) reports OK"
} else {
Write-Host "CRITICAL:  Check OpenManage"
}
exit $status

This script checks the health of my data drive (E, vdisk 1):

omreport storage vdisk controller=0 vdisk=1 | ?{$_ -match "^status"} | %{$status=0}{if($_ -notlike "*OK*"){$status=2}}

If ($status -eq 0) {
Write-Host "OK:  Virtual Disk (data) reports OK"
} else {
Write-Host "CRITICAL:  Check OpenManage"
}
exit $status

Running these involves adding lines like this to the external script section of nsclient.ini or equivalent:

check_physicaldisk = cmd /c echo scripts\\pdiskcheck.ps1; exit($lastexitcode) | powershell.exe -command -

check_virtualdisk = cmd /c echo scripts\\vdiskcheck.ps1; exit($lastexitcode) | powershell.exe -command -

And lines like this to command.cfg in Nagios:

define command {
command_name    check_physicaldisk
command_line    /usr/lib/nagios/plugins/check_nrpe -H $HOSTADDRESS$ -c check_physicaldisk
}
define command {
command_name    check_CRaid
command_line    /usr/lib/nagios/plugins/check_nrpe -H $HOSTADDRESS$ -c check_virtualdisk
}

Comments Off on Nagios Checks for Dell OpenManage Disk Health Written in PowerShell

Filed under Uncategorized

Katherine’s Excellent Log-Shipping Adventure

Or, Log-Shipping over 1200 databases automagically.

Picture1

Continue reading

Comments Off on Katherine’s Excellent Log-Shipping Adventure

Filed under monitoring, powershell, sql

Log-Shipping SQL Express with PowerShell

Someone on Server Fault asked a question about which versions of SQL Server support log shipping.  He uses Express.

Log shipping uses SQL Agent, and Express doesn’t come with SQL Agent, but you can still manually log ship with PowerShell and Task Scheduler.  Use the log backup script of your choice (I like Ola Hallengren’s, which can also be used with Task Scheduler), either back up to a share or copy the files to a share (perhaps with robocopy /mir?), and then run this or something like this (for a read-only copy):

$restoreserver=”YourServer”
$logbackpath=”C:\path\to\logs”
$standby=”C:\path\to\ROLLBACK_UNDO_YourDB.BAK”
$dbname=”YourDB”
$dbsearch = “SomeSearchString*”

$logbacks = Get-ChildItem $logbackpath | Where-Object {$_.Name -like $dbsearch} | Sort-Object LastAccessTime

foreach ($logback in $logbacks)
{
$query = “RESTORE LOG [” + $dbname + “] FROM  DISK = ‘” + $logback.FullName + “‘ WITH  FILE = 1,  STANDBY = N'” + $standby + “‘,  NOUNLOAD,  STATS = 10”
sqlcmd -E -S $restoreserver -Q $query
}

Or this (for an offline warm standby):

$restoreserver=”YourServer”
$logbackpath=”C:\path\to\logs”
$dbname=”YourDB”
$dbsearch = “SomeSearchString*”

$logbacks = Get-ChildItem $logbackpath | Where-Object {$_.Name -like $dbsearch} | Sort-Object LastAccessTime

foreach ($logback in $logbacks)
{
$query = “RESTORE LOG [” + $dbname + “] FROM  DISK = ‘” + $logback.FullName + “‘ WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10”
sqlcmd -E -S $restoreserver -Q $query
}

(If you’re copying and pasting, don’t forget to fix the “smart quotes,” which aren’t smart.)

Comments Off on Log-Shipping SQL Express with PowerShell

Filed under powershell

Peachtree Road Race

For those of you not in the know, the Peachtree Road Race is a ginormous (60,000 runners) 10K held every July 4th in Atlanta. This was my third Peachtree.

Anyway, this is what I did for the 4th of July.

326343_201415781_Medium 326343_201188324_Medium

Comments Off on Peachtree Road Race

Filed under running

Squee!!!

As of yesterday, I’m a Microsoft MVP in Enterprise Security. 😀

Comments Off on Squee!!!

Filed under Uncategorized

Rename a lot of databases with PowerShell

I was given the task of renaming a bunch of databases named something like “name,” “name_1,” “name_2,” etc., to “nameold,” “nameold_1,” “nameold_2,” etc.  Our convention is for the database name to match the file names.  So…

$controlfile=”path\to\listofdatabases.txt”
$server=”servername\instance”
$mdfpath=”c:\path\to\Data”
$ldfpath=”c:\path\to\Log”
$dbnameappend=”old”
$dbnameappendarchive = $dbnameappend + “_”

foreach ($dbname in get-content $controlfile)
{
$newdbname = $dbname + $dbnameappend
if ($dbname -match ‘_[0-9]’) {$newdbname = $dbname -replace ‘_’, $dbnameappendarchive}
Write-Host $newdbname

$mdf=$dbname + “.mdf”
$ldf=$dbname + “_log.ldf”
$newmdf=$newdbname + “.mdf”
$newldf=$newdbname + “_log.ldf”

$query = “ALTER DATABASE ” + $dbname + ” SET SINGLE_USER WITH ROLLBACK IMMEDIATE;”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $dbname + ” MODIFY NAME = ” + $newdbname
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” SET offline”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” MODIFY FILE (NAME = ‘[namingconvention]_log’, FILENAME = ‘” + $ldfpath + “\” + $newldf + “‘)”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” MODIFY FILE (NAME = ‘[namingconvention]’, FILENAME = ‘” + $mdfpath + “\” + $newmdf + “‘)”
sqlcmd -E -S $server -Q $query

Move $ldfpath\$ldf $ldfpath\$newldf -Force
Move $mdfpath\$mdf $mdfpath\$newmdf -Force

$query = “ALTER DATABASE ” + $newdbname + ” SET online”
sqlcmd -E -S $server -Q $query

$query = “ALTER DATABASE ” + $newdbname + ” SET MULTI_USER;”
sqlcmd -E -S $server -Q $query

}

This was so fast that I decided to share the joy. 🙂

(I plan to use this script if I ever need to bring my log-shipped databases online, hence the append.  I might need to do thing_oldprod, stuff_oldprod, whatsit_oldprod, etc.)

Comments Off on Rename a lot of databases with PowerShell

Filed under powershell, scripting

SQL Server Express Maintenance

Don’t you love looking at your old scripts?

I’ve rewritten all of our maintenance jobs, including the jobs in task manager that handle SBSMonitoring.  I’ve rid myself of Windows Internal Database, but was still using the script I wrote for it.  Not any more!

I decided to use and abuse Ola Hallengren’s scripts, so what I’m using for SBSMonitoring is:

Download and install CommandExecute.sql, DatabaseIntegrityCheck.sql, and IndexOptimize.sql. Then, create the following .sql file:

Declare @4wks datetime
set @4wks = DateAdd(hh, DateDiff(hh, 0, GETDATE()) – 672, 0)

EXECUTE [dbo].[IndexOptimize] @Databases = ‘ALL_DATABASES’, @OnlyModifiedStatistics = ‘Y’
EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ‘ALL_DATABASES’

exec msdb.dbo.sp_delete_backuphistory @oldest_date = @4wks

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ AND TABLE_NAME = ‘CommandLog’))
BEGIN
DELETE FROM [dbo].[CommandLog] WHERE DATEDIFF(dd,StartTime,GETDATE()) > 30
END

IF @@SERVERNAME = ‘MYSBSSERVER\SBSMONITORING’
BEGIN
EXECUTE [SBSMonitoring].[dbo].[CleanupDatabase]
END

This can be called with sqlcmd in Task Scheduler.  (“sqlcmd -E -SmyServer -i c:\path\to\scriptname.sql”)

I’ve also started using Microsoft’s script to reindex SUSDB.  I migrated that into SQL Server, so it’s an actual job, but SBS doesn’t support migrating the SBSMonitoring database out of the SBSMonitoring 2005 Express instance that’s installed by default, hence the scripty maintenance.

Comments Off on SQL Server Express Maintenance

Filed under Uncategorized

Backups FTW!

I could say it’s because I’m a DBA and SysAdmin and it’s an occupational hazard, and frequently do.  I could also say it’s because I’m neurotic.  😉  But it’s probably the most honest to say, “Because I once experienced a catastrophic data loss…”

For whatever reason–pick one or more above–I’m compulsive about backups of my home equipment.  I actually use two different backup programs for different use cases:

  1. “My house burned down.”
  2. “I accidentally deleted a file.”
  3. “My computer is so dead that I can’t boot it to get the data off it, so I bought a new one.”
  4. “My hard drive died.”

For the first three, just about any cloud-based solution will work.  The first product I started using I picked for a highly dorky reason:  Neil Gaiman, my author crush, tweeted about it.  Crashplan protects me against data loss caused by stolen equipment and burning houses, restores accidental deletions, and when my old laptop died it painlessly put my data on the new laptop.  (Okay, yes, I could have cracked the case, put the old drive in an external enclosure, etc.  The point is that I didn’t have to.)

However, it’s not really intended for use #4.  Well.  You could use it for use #4, after reinstalling the OS and all your apps, or putting the vendor image back on and reinstalling your apps, or whatever.  However, what I really want for use #4 is something that pulls an image.  For that, I use Acronis, which I picked because my office uses it.

Acronis has a cloud service, but my main desktop has iTunes, and multiple season passes of NCIS and Doctor Who and the like, so I’m using 390 GB.  I’m a little… iffy… about uploading 390 GB worth of data a second time.  I also haven’t compared the cloud storage prices.  However, I <3 Acronis as of last week, because my hard drive failed.  In my main desktop.  The one with all that data on it.

I replaced the hard drive with a similarly-sized non-dead drive, booted it off the previously-burned recovery media, set it to restoring, and went to bed.  The next morning, it was like nothing ever happened.  Well.  I had to reapply a Windows update and recover a file.

So, my point is… Neil Gaiman is hot enough that I buy backup programs because he tells me to?  😉

Seriously, that could have been painful and traumatic, and instead it just ran overnight and all was love and goodness.  Because I do backups.  Backups FTW!

Comments Off on Backups FTW!

Filed under geekiness

The Career Venn Diagram

Every now and then, people ask me for career advice.  Or, you know.  They start complaining about their careers and I annoy them with unsolicited advice.  😉  Either way, my advice for choosing a career comes down to:

  1. Things you can do
  2. that people will pay you to do
  3. that don’t suck.

As a Venn Diagram, it looks like this:

CareerVenn

(Sorry about the graphic. MS Paint FTW!)

You’d be surprised–or maybe you wouldn’t–by the resistance I get to this suggestion.  “Well, that’s fine for you because you’re in IT, but no one will pay me to sing opera/write poetry/play World of Warcraft/play ice hockey/go to Mars/etc.”

I didn’t specify that it had to be your lifelong dream.  I know that little children usually say they want to be an astronaut, or a ballerina, or write books, or be the President, and don’t generally say they want to be accountants or purchasers or write limericks about snack foods.  I just said a job that doesn’t suck.  Apparently, many people believe that if it doesn’t suck, no one will pay them to do it.  Not true!

Lots of people settle for two out of three.  (Things you can do that people will pay you to do is the only viable two out of three.  You can do things you can do that don’t suck for no money if you’re independently wealthy, or you can try to do things that don’t suck that pay well but that you’re ill-suited to doing.  For most people, neither of those will last long.)  Of course, you need to eat, but I always urge people to go for three out of three.

So, if you’re someone who’s considering a transition–like someone I know–think of the Venn diagram when you’re doing your research.  Don’t invest a lot of time and energy into moving into a high-paying direction you hate, or a high-unemployment and/or low-paying direction.  They pay some people to play ice hockey, but if you’re 45 and have bad knees it probably won’t be you.  That doesn’t mean your life has to suck.  There are alternatives.

Comments Off on The Career Venn Diagram

Filed under career

Fun with PXE, redux.

You may recall that I’m serving up not only Windows Deployment Services capture and deploy but also Acronis recovery media over PXE.

Well, now I’m also serving up Windows Defender Offline. When chosen from the PXE boot menu, it boots into Windows PE, automagically launches Defender, updates the definitions, and runs a full scan.  And then sits there and waits for you to see the results and hit reboot.

Here’s what I did.  This assumes you have Windows Deployment Services and the Windows Automated Installation Kit.

  1. Download Windows Defender Offline for your architecture.
  2. Run the tool to create an ISO (“to burn to CD/DVD later”).
  3. Mount the ISO with Virtual CloneDrive (or similar product).
  4. Fish boot.wim out of /sources on the virtual CD drive.
  5. Create a directory to mount the wim into if one doesn’t already exist.
  6. Mount boot.wim into that directory. Dism /Mount-Wim /WimFile:C:\temp\boot.wim /MountDir:C:\temp\wim /Index:1
  7. If you have a directory full of drivers for your environment, you can pre-emptively add them with: Dism /Image:C:\temp\wim /Add-Driver /Driver:c:\drivers /Recurse /ForceUnsigned. (If not, cross your fingers and carry on with the next step.)
  8. Copy mpam-fex64.exe (or similarly named file for x86)  into the top level of the wim from the top level of your virtual CD drive.
  9. Save and unmount the wim.  Dism /Unmount-Wim /MountDir:C:\temp\wim /Commit
  10. Upload the new wim to your WDS server and add it as a boot option.

I’ve also rewritten a lot of my batch scripts to be PowerShell, but I think that’s a post for another day.

Comments Off on Fun with PXE, redux.

Filed under Uncategorized