Automated PowerShell Reports Delivered to Your Inbox

In my past two columns—“Automating PowerShell Reports, Part 1” and “Automating PowerShell Reports, Part 2”—I've been preparing you to be able to use PowerShell to create Active Directory (AD) reports automatically and, even better, to deliver those reports to your mailboxes. To that end, I've examined PowerShell's send-mailmessage command (which will do the emailing for you) and talked about how to ensure that send-mailmessage can successfully send that email in a modern secured email infrastructure. Now you’re ready to assemble a report that PowerShell can run for you daily.

You would like to get a report of all the users who haven't logged on in 120 days, and get that sorted by how long it has been since they logged on. That would be this command in PowerShell:

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

 
To automate this, you would simply put the above command into a text file, with one change (to capture its output in a text file), add to that file a send-mailmessage command that uses the text file as the body of the message, save the file containing the two commands with a .ps1 extension, and then schedule the command to run daily in Task Scheduler:

powershell -executionpolicy remotesigned -command nameoffile.ps1 

First, create the .ps1 file. Find a folder where you'll store your PowerShell commands and report outputs. (I use a folder named C:scripts for that, but anything will work.) Then create a new text file to hold the PowerShell commands that will run your report. (I call mine oldusers.ps1.) Open the file in Notepad, and type these three commands on separate lines:

import-module activedirectory search-adaccount -usersonly -accountinactive -timespan "120"|select samaccountname,lastlogondate|sort lastlogondate|ft -auto > C:scriptsoldusers.txt send-mailmessage -to youremail -from powershell@yourcompany -subject "Daily inactive user report" -smtpserver yoursmtpservername -body (get-content c:scriptsoldusers.txt|out-string)

 
I added that first line—import-module activedirectory—because AD commands need the AD module. Next, I added > c:scriptsoldusers.txt to tell PowerShell to store the result of that long search-adaccount command in a text file. (Again, you're welcome to use any filename and folder you want.) Now, the send-mailmessage command looks like the ones we talked about a couple of months ago, but you have to personalize it to your company's email and domains, as well as the filename specified in the get-content command (which has to match the name of the file that you just wrote out with the search-adaccount command). So, if you were [email protected] with a local SMTP server at mail.bigfirm.com, the three lines would look like 

import-module activedirectory search-adaccount -usersonly -accountinactive -timespan "120"|select samaccountname,lastlogondate|sort lastlogondate|ft -auto > c:scriptsoldusers.txt send-mailmessage -to [email protected] -from [email protected] -subject "Daily inactive user report" -smtpserver mail.bigfirm.com -body (get-content c:scriptsoldusers.txt|out-string)

 
You might reasonably ask why I didn't just use the PowerShell pipeline to take search-adaccount's output and stuff it into send-mailmessage's -body parameter, making the two lines into one. Honestly, I felt that doing so would have resulted in history's longest, least readable PowerShell line.

The .ps1 file is probably ready to be scheduled, but it never hurts to check it. Now, you're running a PowerShell script and by default Windows systems won't run scripts, which is why it's nice that the powershell.exe command includes a command (-executionpolicy remotesigned) to let you temporarily override that. Use that to invoke your script (even from inside a PowerShell prompt): 

powershell -executionpolicy remotesigned -command scriptname

 
In the case of my example, you'd type 

powershell -executionpolicy remotesigned -command C:scriptsoldusers.txt

 
If that doesn’t work, and you don’t get a message, first check for typos. Then, from a PowerShell command prompt, try just the search-adaccount command without the >filename end to it. Look again for typos, and ensure that you're not running from an account that doesn’t have the privilege to do search-adaccount commands. Once that's done, run the command again, restoring the >filename part. Doing so will give you the file oldusers.txt (or whatever you decided to call it), so you can then run the send-mailmessage command by itself. If that fails, it's probably an SMTP permission problem, as I discussed in the aforementioned articles. Use the advice in those articles to smoke it out.

Finally, schedule the task from Task Manager. Create a new task, giving it any name you want, and define its Triggers (e.g., when to run it—just set it On a schedule, and as often as you like) and its Actions. For Actions, tell it to Start a program (with a Program/script value of powershell), and in Add arguments, specify the rest of the command, as in -executionpolicy remotesigned -command C:scriptsoldusers.ps1. Tell it to run the command under System. Once you've scheduled the new task in Task Manager, you needn't wait: Make it run immediately by right-clicking it and choosing Run.

Best of luck with your first automated report! Now start thinking about what else PowerShell can deliver to your mailbox!