Fun with Exchange Management Shell

I’m really digging the Exchange Management Shell.  It’s FUN FOR THE WHOLE FAMILY!

Okay, maybe not the whole family.  😉

Short example:  someone wanted to know if I could tell them how many emails someone sent in a specific time period.  Short answer:

Get-MessageTrackingLog -Sender username@yourdomain.com -EventID “send” -start ‘1/1/13’ -end ‘2/20/13’ -resultsize unlimited | select-object sender | Group-Object pattern | select count

I spent time playing with that command and checking out how many emails we’ve sent offsite since the start of the year and such.  Better than a water park, I’m telling you!

Comments Off on Fun with Exchange Management Shell

Filed under powershell, sysadmin

Obituary spam

There is a special circle of hell for people who spam obituaries.

As it turns out, I have a modest talent for obituaries.  (Also job references, and for the same reason:  I’m good at finding unique good things about people and saying them.)  Unfortunately, I have now been called upon to exercise this talent for both parents.

Because I’m putting obituaries on the Internet, that means I’m also called upon to fight spam.  I could just turn off comments, but one of my mother’s friends discovered she was dead via google and if someone is in that position and wants to comment…

I can now endorse the following:

  • Bad Behavior.  There are plugins for WordPress, MediaWiki, Drupal, and more.
  • Akismet.  There are plugins for WordPress, MovableType, Drupal plugins that tie in to the service, and more.

Akismet runs your comments through their web service, comparing them to known spam.  Bad Behavior detects malformed spammy requests, and has an optional tie in to http:BL.  They both beat learning how to kill people with your brain.

Comments Off on Obituary spam

Filed under web

So, um, I have this friend… *shifty eyes*

So, I have this friend *cough* who had a domain controller that needed a new motherboard. Due to the excitement of the hardware vendor’s tech forgetting to reattach the RAID, then the server no longer recognizing its network cards, and then the tech realizing that he forgot to replace the fan shroud, um, my… friend… kind of forgot to check the system time. It was a long week (which included parental death).

(The system time was 12 hours fast, so it looked right at a glance.)

This was Saturday. The system time situation was discovered Sunday on my… um… friend’s workstation.  (If the time is wrong on the domain controller, that wrong time propagates across the entire network.  This is to prevent Kerberos issues. If this had happened during the week, people would have randomly lost their ability to log on or connect to network shares until the times on workstations and servers were synchronized.  To the wrong time, since the domain controller is the official source.  Yeah.  And that’s the optimistic scenario.)

Since I’m not fooling anyone, I’ll take responsibility for the fix.

  1. Fixed the time on the domain controller.  (Date/time control panel followed by “w32tm /resync” to get the precise time from an internet time server.)
  2. Did search-and-replace on a list of computers from dsquery to strip it down to nothing but computer names, and saved the list as control.txt.
  3. Ran this simple script.

@ECHO ON

set controlfile=control.txt

FOR /F %%L IN (%controlfile%%) DO (
w32tm /resync /computer:%%L
)

I’m a little embarrassed to post my five-liner to my blog, but there you are.  Ten minute fix.

Comments Off on So, um, I have this friend… *shifty eyes*

Filed under scripting

Validation!

And how do I feel about that?

Comments Off on Validation!

Filed under career

Generic Backups

I support developers.  In our dev/QA environment, people add, delete, and change the recovery model of databases all the time without telling me.  How do I keep track of which databases need to be backed up?

I don’t.  I back up everything.

Master has information on all databases.  That means master can give you a list of what needs to be backed up.  Of course, you don’t want to back up tempdb (because you can’t restore tempdb), and I haven’t customized model, so:

Declare @DBname varchar(255),
@RowsToProcess int,
@CurrentRow int,
@SQL nvarchar(max),
@today nvarchar(50),
@servername nvarchar(50)

set @today = convert(nvarchar,GETDATE(),12)
set @servername = REPLACE(@@SERVERNAME,’\’,’-‘)

CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), dbname nvarchar(max) )
INSERT into #maintenancetemp (dbname) select name from sys.databases where name <> ‘tempdb’ and name <> ‘model’
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@DBname=dbname
FROM #maintenancetemp
WHERE RowID=@CurrentRow
set @SQL=’BACKUP DATABASE [‘ + @DBName + ‘] TO DISK = N”S:\backups\’ + @servername + ‘-‘ + @DBName + ‘-‘ + @today + ‘.bak” WITH FORMAT, INIT, NAME = N”Full ‘ + @DBName + ‘Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10’
–print @SQL
EXEC SP_EXECUTESQL @SQL
END

drop table #maintenancetemp

Here’s the full backup script that loads that into a temp table and loops through the rows and backs them up.  You can, of course, customize this script to suit your preferred file name convention and backup location.  You can, of course, also plop that into a job or, if you’re running something like Windows Internal Database or SQL Express, run it via sqlcmd and Task Manager.

What about log backups?  If it’s just dev/QA data, is it important?  Is QA going to jump out of a window if they lose data?

I don’t ask.  I just back it up.  Master knows what’s in full recovery, too:

select name from sys.databases where name <> ‘tempdb’ and name <> ‘model’ and recovery_model IN (1, 2)

This log backup script assumes hourly backups, which might be overkill for your environment and might not.  I’ve seen “dev” and “QA” environments that were treated like prod because they were actually for training and the company lost money if training couldn’t take place, so I’d rather err on the side of being backed up.  Again, customize to suit your environment.

I’ve started running these against any new internal server-based SQL Server instance I find out about.  We do have instances of SQL Server 2005 Express and the like, for testing, so sqlcmd continues to be my friend.

Comments Off on Generic Backups

Filed under scripting

Thanksgiving Gluttony

Yum, Nagios gluttony!

I’m donating Nagios monitoring to a couple of nonprofits, and this brings up how Nagios configurations grow.  In short, you learn over time what you need to keep an eye on.

For example:  On one nonprofit, someone forgot to renew the domain (oops!).  It just so happens that there’s a plugin for that.  Godaddy outage hoses DNS?  Add a check for that.  SSL cert expires (oops!)?  Add a check for that.  The web site returns 200 OK (thereby showing up as okay in Nagios) but no content appears?  Add a check for that.

And then apply all those checks to your other hosts.  So the same thing doesn’t happen to them.

And this is how you end up with so many checks.

define command {
command_name check_content
command_line $USER1$/check_http -r “</body>” -H $HOSTADDRESS
}

define command {
command_name DNS_resolving
command_line $USER1$/check_dns -H $HOSTADDRESS
}

define command {
command_name check_domain
command_line $USER1$/check_domain -d $HOSTADDRESS
}

define command {
command_name check_cert
command_line $USER1$/check_http -ssl -C 14 -H ‘$HOSTADDRESS’
}

Yes, I added checks on Thanksgiving.  *facepalm*

Comments Off on Thanksgiving Gluttony

Filed under monitoring

Your Halloween Scare

South Carolina Department of Revenue hacked:

South Carolina state officials announced Friday evening that the social security numbers of some 3.6 million state residents and 387,000 credit and debit card numbers were exposed in a data breach. The SSNs were stored unencrypted, and while most of the credit cards were encrypted, some 16,000 card numbers were not.

South Carolina Governor Nikki Haley sounds angry:

“I want this person slammed against the wall,” she said, referring to the attacker as “an international hacker.” “I want that man just brutalized,” Haley said.

Yes, well.  I want my SSN encrypted.

We come, alas, to a personal anecdote.

Once upon a time, I had a server that ran an application that stored card swipe numbers.  Most of them were numbers generated by my employer, but some of them–my coworkers’ cards–were SSNs.  This server was placed without my knowledge in an insecure location.  (The AC went out, so they tied the door open.)  When I found out, I tried to remove the server and was told I wasn’t allowed.  One of the things I did do was to quietly go in and delete my coworkers’ SSNs out of the database.  Including the SSN of the person I hold responsible for the situation.

You’re welcome, by the way. (I think this is the first time I ever told anyone I did that.)

I don’t blame Governor Haley for being angry.  I’d be angry if I were a South Carolina resident, too.  I would have been angry if someone messed with the server in the overheated, insecure location, but I would have blamed the people who told me to leave it there as much as the hacker.  Encrypting PII (Personally Identifiable Information) wouldn’t have been complete protection, but at least it would have made it harder.  Especially since people can’t opt out of paying their taxes on the grounds that they don’t trust the DOR to protect their data.

And, you know.  Not to single out South Carolina.  How secure is your state’s Department of Revenue?

Happy Halloween!  Sleep well!

Comments Off on Your Halloween Scare

Filed under sysadmin

Seekrit Message to a Friend

You didn’t get this from me.  You downloaded it off the internet.

In fact, why don’t I turn that bunch of scripts into a series of jobs and give it to you as one big lump?  (Download.)

How to use:

  1. Load file into SQL Server Management Studio.
  2. Replace all instances of BACKUP DATABASE [DBNAME] with the proper database name in the square brackets.
  3. Either create the directory f:\sql or edit the jobs that refer to it to go to a different path.
  4. Click execute.

And remember, I know nothing.  If I knew something, I’d have to charge.

Comments Off on Seekrit Message to a Friend

Filed under scripting, sysadmin

Never throw away a script!

You never know when you’re going to have to plagiarize yourself.

Seriously, I just repurposed the “migrate databases to new drives” script to be a “migrate databases to a new server” script.  Rather than starting from scratch, I changed a couple of lines on the existing script and it’s percolating away right now.  (I got the list of databases from sys.databases, of course.

Comments Off on Never throw away a script!

Filed under scripting

Downloadable scripts

It has come to my attention that WordPress has replaced my quotes with smart quotes, which causes EPIC SCRIPT FAIL.  Therefore, I’ve uploaded downloadable versions of the scripts.

Smart quotes inserted,
Script fails upon copy-paste.
“Smart quotes” are not smart.

Comments Off on Downloadable scripts

Filed under haiku