Automating PowerShell Reports, Part 1

I want to take a short break from my in-depth look at PowerShell-based Active Directory (AD) query tools and show you how to put some of that query power to work in a concrete way. You've already seen that the command

search-adaccount -usersonly -accountinactive -timespan "90"|select samaccountname,lastlogondate|sort lastlogondate|ft

will generate a fairly useful list of domain users who haven't logged on in the past 90 days, sorted by how many days have passed since that last logon. It's of some value and not too ugly a command to remember, but who wants to have to open a PowerShell prompt and type it to get that information? This month and next, I’ll show you how to get PowerShell to automatically generate that report daily and deliver it to you via email.

Before you can get there, however, you need a PowerShell cmdlet called send-mailmessage, because it's how you'll get PowerShell to send that report to your mailbox. Understanding its syntax is mostly trivial, but there are a few not-so-simple parameters, so here it is in parts. In its simplest form, send-mailmessage looks like

send-mailmessage -to [email protected] -from [email protected] -cc [email protected] -bcc [email protected] -subject "Looks good, let's sign it Tuesday at noon" -body "Let's meet at the Pungo Grill and ink this deal." -smtpserver po.cogswellcogs.net

It's a long line, but it’s a simple one. Any SMTP client must, at minimum, allow you to specify those items. If you don't want to have to type the SMTP server's name, you can create a default one in your PowerShell profile. To do so, create a folder in Users yournameDocumentsWindowsPowerShell (if it doesn't yet exist). In that folder, create a text file named Microsoft.Powershell_profile.ps1. Open that file and add the line

$psemailserver=yourSMTPServer

as in

$psemailserver="mail1.bigfirm.com"

Finally, from the PowerShell command prompt, type

set-executionpolicy remotesigned

What you've done is modify or create your own PowerShell profile, a text file in which you can type commands that get automatically executed whenever you enter PowerShell (and yes, I've covered this before, but it's been a while). The set-executionpolicy command, which you need only type once on your computer, gives your PC permission to run PowerShell scripts (and I'll cover that in greater detail in the future). Now, exit PowerShell and re-enter it to enable the new/modified profile, then type

$psemailserver

PowerShell will display your current default SMTP server. Before going any further, test that first send-mailmessage command, substituting some local-to-you accounts and the name or IP address of your local SMTP server. If it fails, it’s because of your SMTP server's security. Mine is set up so that if a client is either connecting from my internal subnet or providing domain credentials, it will send mail, so I needn't provide credentials. However, if your SMTP server wants credentials, you just add -credential username and you'll be prompted for a password. (In the long run, you will, however, need to tweak your SMTP server's authentication requirements, because you’ll ultimately want to schedule this command to run automatically.)

That syntax will work fine, but clearly the body that I specified was pretty basic—just one line and, yes, PowerShell wants you to somehow stuff the whole body of the message inline. Here are a few ways to do that.

First, if you have just a few short lines to type, you can indicate line breaks with the PowerShell "escape code" of `n—that's a backtick and a lowercase n. For example, here's a two-line body:

-body "Meet me at noon.`nI'll be waiting."

That would show up as two lines. Sometimes, though, you'll want to grab the contents of a file and have send-mailmessage use that as the body. That's easily accomplished with a cmdlet named get-content that essentially grabs the contents of a file and shows it on the screen. Typing

get-content test.txt

would show test.txt's contents. So, you'd think that putting that in parentheses would make PowerShell quite happy, as in

… -body (get-content test.txt) ….

But what looks like text (or System.Text in PowerShell-ese) there isn't exactly text; it's what .NET and PowerShell call a System.Object type of data. You can fix that by piping the get-content cmdlet into another cmdlet called out-string that—you guessed it—converts System.Object to System.String, making PowerShell happy, and then this works:

send-mailmessage -to [email protected] -from [email protected] -subject "Here's the report!" -body (get-content c:filesreport.txt|out-string)

You can shorten a line like that a bit with any of get-content's three aliases: cat, type, or gc. Even better, if your file is HTML rather than text, then send-mailmessage will send it as HTML if you add the parameter -bodyashtml.

I think you can understand send-mailmessage's usefulness and see why I fell in love it as soon as I met it, but it's still missing two things. First, it would be nice to run a cmdlet and make that cmdlet's output the body text for an email message. Second, wouldn’t it be cool if you could schedule send-mailmessage to run automatically? We'll do that next time.