Wednesday, December 18, 2013

Everyday Powershell - Part 13 - Working with SQL Server in powershell

This is the next part in an ongoing series about Powershell. You may have heard about how awesome Powershell is but have struggled to find ways to make it useful in your day to day work. That's what this series is going to address. It'll provide scripts and knowledge to address practical everyday problems


Today's script isn't very long. It's difficult to write SQL that will work on generic servers. This line will list the all the Databases on a given SQL server.


invoke-sqlcmd -ServerInstance "SOMESQLSERVER" -Query "SELECT * FROM [master].[dbo].[sysdatabases]"

Yes that's right you can send a SQL query off to your DB server and get the results back as a powershell object! This is freaking amazing! You'll have to use a bit of imagination and think up ways to make SQL queries in your environment, but once you start coming up with scenarios you won't stop! There are any number of powerful combinations possible when you can pull SQL data into powershell. 

We've just implemented a monitoring script that checks a table for new rows and if there aren't any new rows within the last ten minutes it emails us.


Add-PSSnapin SqlServerCmdletSnapin100
$test = invoke-sqlcmd -ServerInstance "
SOMESQLSERVER" -Query "SELECT top 1 *
  FROM [SOMEDATABASE].[dbo].[SOMETABLE]
  order by [CreationDate] desc"

$tenminutesago = (get-date).addminutes(-10)
foreach ($item in $test)
{
    if ($item.creationdate -lt $
tenminutesago)
    {
        Send-MailMessage -SmtpServer SOMEMAILSERVER -from SOMEONE@SOMEDOMAIN.COM -to 
SOMEONE@SOMEDOMAIN.COM -subject "No data since $today" -Body $item
    }
}

To make this invoke-sqlcmd work we need to install the SQL management tools. We've done this by running the SQL 2012 installer wizard and making sure Management tools are ticked. Also in some OS configuration we've had to load the snapin manually.

Add-PSSnapin SqlServerCmdletSnapin100

Jay Querido has a great write-up over on his blog on what to do if invoke-sqlcmd doesn't work.

We'll see what other SQL scripts we can come up with over the next few weeks. We want to give examples that will work anywhere so if you think of anything let us know.

Wednesday, December 11, 2013

Everyday Powershell - Part 12 - Change the owner of all files in a folder

This is the next part in an ongoing series about Powershell. You may have heard about how awesome Powershell is but have struggled to find ways to make it useful in your day to day work. That's what this series is going to address. It'll provide scripts and knowledge to address practical everyday problems

This one was another request. We love requests please send more!

The reader wanted a script that would recursively set  the owner of all files in a folder.

As usual the scripting guys are all over this kind of thing.
http://blogs.technet.com/b/heyscriptingguy/archive/2008/04/15/how-can-i-use-windows-powershell-to-determine-the-owner-of-a-file.aspx

Our contribution was putting it in a loop and adding a progress indicator. Vital sanity proofing if you are going to watch this thing as it runs over thousands of files...
$path = c:\test
$objUser = New-Object System.Security.Principal.NTAccount("SOMEDOMAIN", "SOMEUSER")

$list = get-childitem $path -recurse
$count = $list.count
$i = 0

foreach ($file in $list)
{
    $i++
    $prog = ($i/$count* 100
    Write-Progress -Activity "Processing" -percentcomplete $prog -Status $file.fullname
    $objFile = Get-Acl $file.fullname
    $objFile.SetOwner($objUser)
    Set-Acl -aclobject $objFile -path $file.fullname
}

Working with Access Control Lists is a bit funny. We can't just add things to the ACL we've got make a copy of it;
$objFile = Get-Acl $file.fullname

Perform the modification;
$objFile.SetOwner($objUser)

and then apply it to the file as a new ACL.
Set-Acl -aclobject $objFile -path $file.fullname 

Monday, December 9, 2013

10,000 views

We just cracked the 10,000 views milestone!


We'd like to thank all the spiders, bots and spammers for their assistance hitting this important milestone!

Seriously though if we've helped only 10% of the visitors to this blog that's still 1000 people that have derived value from this collection of Occasionally Useful articles! That's a great feeling! We'll keep posting so long as you keep reading!

Wednesday, December 4, 2013

Everyday Powershell - Part 11 - Server Shutdown Comments

This is the next part in an ongoing series about Powershell. You may have heard about how awesome Powershell is but have struggled to find ways to make it useful in your day to day work. That's what this series is going to address. It'll provide scripts and knowledge to address practical everyday problems

Today we look at a most under appreciated source of IT comedy. The server shutdown comments! You know the ones... When you are in the middle of an outage, rebooting a server and the shutdown event tracker asks you "why?"! This can result in comedy gold! We ran this across our fleet and had a pretty good giggle at some of the comments.

Run this across so servers that have been in production for any length of time and I bet you have a chuckle at some of the comments your colleagues or you yourself have made over the years. I'd give you an example but the language in all our amusing comments is a little too 'salty'.

$servers = Get-ADComputer -filter {OperatingSystem -like "*Server*"}
$report = @()
$count = $servers.count
$i = 1
foreach ($server in $servers)
{
    $prog = ($i / $count* 100
    Write-Progress  -Activity "Asking servers why they've been rebooted" -percentcomplete $prog
    $events = $null
   
try
    {
        $events = Get-WinEvent -ComputerName $server.DNSHostName -filterhashtable @{logname="system"; id="1074"| select TimeCreated, message
    }
    Catch
    {
        out-null
    }

    if ($events.count -gt 0)
    {
        $server.DNSHostName
        foreach ($event in $events)
        {
            $temp = "" | Select-Object Server, Time, Comment
            $start = ($event.Message).indexof('Comment:'+ 9
            $comment = ($event.message).substring($start)
            $temp.server = $server.DNSHostName
            $temp.time = $event.TimeCreated
            $temp.comment =  $comment
            $report += $temp                 
        }
    }
    $i++
}
$report

This script will query AD for anything with "Server" in it's Operating System name. It then queries those servers system event logs for event ID 1074. The old "Shutdown event tracker".

You'll probably see a few errors when running this script. They'll be safe to ignore. We're not doing any fancy error handling, just a simple try/catch. So basically we ask the script to TRY something and if it fails CATCH that and do something else. In this case we're not doing anything just suppressing any errors.

We do have a fancy progress indicator though. The write-progress command is doing that for us. It requires us to know how many items we're going to process $count and how far through the list we are $i. We divide $i by $count for an indication of Percent Complete $prog.

We then chop up mangle the string in the .message property because we're really only interested in the "Comment" so we use substring to chop up the message. We've tried to keep that simple to understand.

We bundle our work into a $temp object and whack that on the end of $report which is what gives us our output.

So there you have it! Some more IT comedy! Obviously comedy is the optimal use of this sort of thing, but it could be used for practical purposes if you really wanted. You can just change that filterhastable to find any kind of event you want, I don't think many will be as funny, but you may well find some other useful applications.

Wednesday, November 27, 2013

Everyday Powershell - Part 10 - Powershell to check if certificates are going to expire

This is the next part in an ongoing series about Powershell. You may have heard about how awesome Powershell is but have struggled to find ways to make it useful in your day to day work. That's what this series is going to address. It'll provide scripts and knowledge to address practical everyday problems

This weeks script was a request from a reader. He wanted a reminder of when certificates were going to expire on his server. Having felt the stress of running through Extended Validation for production certificates with only hours to spare before expiry, the need for such a script was obvious.

$days = 60
$certs = $null
$certs = Get-ChildItem -Path cert: -Recurse -ExpiringInDays $days | select Subject, thumbprint, notafter
if ($certs -ne $null)
{
    [string]$body = $certs | convertto-html -Body $_
    Send-MailMessage -To someone@someserver.com -From someone@someserver.com -Subject "Certificates Expiring in $days days" -SmtpServer somemailserver -BodyAsHtml $body
}

The great thing is it's bloody easy with Powershell 4. If you are not up to date you should be it's easy!

Just schedule this to run at a regular schedule if only there was some kind of guide to do that in powershell.

Requests are fun! If you have a need for a script to do anything post a comment or send me tweet @benhaslett I'd be happy to give it a crack! Or at the very least I can Google around and see if someone has done something similar!

Wednesday, November 20, 2013

Everyday Powershell - Part 9 - Scheduling Powershell in Powershell

This is the next part in an ongoing series about Powershell. You may have heard about how awesome Powershell is but have struggled to find ways to make it useful in your day to day work. That's what this series is going to address. It'll provide scripts and knowledge to address practical everyday problems

Todd Klindt brings us this awesome bit of trickery. You may remember from Part 1 of this series we went over how to schedule a script using the GUI... But what kind of scripter uses the GUI for anything!? Well Todd's post over on the weekend scripter gives us everything we need to ditch the GUI for scheduling tasks.

So this script can be used to schedule any other scripts. We've used the one from Part 2 saved it in C:\powershell with the filename say-bofhexcuse.ps1

#http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/09/weekend-scripter-use-powershell-to-back-up-sharepoint-installation.aspx?utm_source=twitterfeed&utm_medium=twitter
$password = ConvertTo-SecureString 'Wouldn'tyouliketoknow?' -AsPlainText -Force
$account = New-Object system.management.automation.pscredential 'SomeUser', $password
$opt = New-ScheduledJobOption -RunElevated
Register-ScheduledJob -Name BOFH-SPEAKS -FilePath C:\Powershell\say-BOFHexcuse.ps1 -Credential $account -ScheduledJobOption $opt

$t = New-JobTrigger -atlogon

Get-ScheduledJob -Name BOFH-SPEAKS | Set-ScheduledJob -Trigger $t

Once we've got the job setup we can test it with;
Start-Job -DefinitionName BOFH-SPEAKS

The New-JobTrigger bit will accept all kinds of triggers, you'll need to check the doco for all the tricky parts. We're just running this one at logon. So when you login of a morning you'll get a new excuse!

Monday, November 18, 2013

They're having a laugh right?

So I'm doing some doco on our Active Directory Forest Restore procedure and found this gem in the documentation... The excess of procedural detail here made me giggle.


Do we really need such detailed instructions to add 100,000 to another number?

Here's the original article if you don't believe it;
http://technet.microsoft.com/en-us/library/8e3e4377-ef54-4a70-9215-a5d2ba4d0eb9(v=ws.10)#RaiseRIDPool

I mean I get that this is probably because someone made a bad mistake with this process back in the day and Microsoft are covering themselves.

BUT I'd PREFER to believe that is some passive aggressive technical writer having a dig at relative intellect of the average Active Directory admin.

Wednesday, November 13, 2013

Everyday Powershell - Part 8 - Powershell Slideshow

This is the next part in an ongoing series about Powershell. You may have heard about how awesome Powershell is but have struggled to find ways to make it useful in your day to day work. That's what this series is going to address. It'll provide scripts and knowledge to address practical everyday problems

First let me congratulate you finding this article! Especially if you came via Google. Finding a blog post on "Powershell Slideshows" among all the "Powerpoint" slideshows would have been an interesting challenge!

Let's say you wanted a PC to boot up and show a slideshow of images in a given folder. Do you think that you'd need some third party software? Could windows photo viewer do it? A colleague of mine was about to create a video in Windows Movie Maker before I asked...

"Why don't we try POWERSHELL?"

$folder = "C:\Users\someuser\Documents\Pictures\"
$wait = 1

[void][reflection.assembly]::LoadWithPartialName("System.Windows.Forms")
$form = new-object Windows.Forms.Form
$form.Text = "Image Viewer"
$form.WindowState= "Maximized"
$form.controlbox = $false
$form.formborderstyle = "0"
$form.BackColor = [System.Drawing.Color]::black

$pictureBox = new-object Windows.Forms.PictureBox
$pictureBox.dock = "fill"
$pictureBox.sizemode = 4
$form.controls.add($pictureBox)
$form.Add_Shown( { $form.Activate()} )
$form.Show()

do
{
    $files = (get-childitem $folder | where { ! $_.PSIsContainer})
    foreach ($file in $files)
    {
        $pictureBox.Image = [System.Drawing.Image]::Fromfile($file.fullname)
        Start-Sleep -Seconds $wait
        $form.bringtofront()
    }
}
While ($running -ne 1)

Most of this was pinched from here;

Our contribution is the fact it's Fullscreen and the Do While loop. Those clever observers among us will notice the $running will never equal 1 so that do loop is infinite. Which suits our purpose just fine.

What started as a way to settle an argument with a colleague (about the usefulness of powershell in media rich environments) became a great way to demo the windows.forms objects!

Beware though it is a bit of mess from a UI perspective. You can't quit the form once it's shown, you can alt tab and kill it from task manager when you need to close it. It suits what we need because it's just for a PC that boots up and runs a slide show, then shuts down at night. The users won't have to ever touch this PC. This is not something I'd give to end users to interact with. But it's fine for our purposes.

Wednesday, November 6, 2013

Everyday Powershell - Part 7 - Powershell to Upgrade Powershell 4

This is the next part in an ongoing series about Powershell. You may have heard about how awesome Powershell is but have struggled to find ways to make it useful in your day to day work. That's what this series is going to address. It'll provide scripts and knowledge to address practical everyday problems!

This is a script you can run that'll upgrade you to the newest version of the Windows Management Framework (version 4). What's so good about WMF4? Why Powershell 4 is in it!

#http://download.microsoft.com/download/3/D/6/3D61D262-8549-4769-A660-230B67E15B25/Windows8-RT-KB2799888-x64.msu

if ($PSVersionTable.psversion.Major -ge 4)
{
    write-host "Powershell 4 Installed already you don't need this"
    Exit-PSSession
}

$powershellpath = "C:\powershell"

function download-file
{
    param ([string]$path, [string]$local)
    $client = new-object system.net.WebClient
    $client.Headers.Add("user-agent", "PowerShell")
    $client.downloadfile($path, $local)
}


if (!(test-path $powershellpath))
{
    New-Item -ItemType directory -Path $powershellpath
}

if (($PSVersionTable.CLRVersion.Major) -lt 4)
{
    $DownloadUrl = "http://download.microsoft.com/download/B/A/4/BA4A7E71-2906-4B2D-A0E1-80CF16844F5F/dotNetFx45_Full_setup.exe"
    $FileName = $DownLoadUrl.Split('/')[-1]
    download-file $downloadurl "$powershellpath\$filename"
    ."$powershellpath\$filename"
}

#You may need to reboot after the .NET 4.5 install if so just run the script again.

if ([System.IntPtr]::Size -eq 4)
{
    $DownloadUrl = "http://download.microsoft.com/download/3/D/6/3D61D262-8549-4769-A660-230B67E15B25/Windows6.1-KB2819745-x86-MultiPkg.msu"
}
Else
{
    $DownloadUrl = "http://download.microsoft.com/download/3/D/6/3D61D262-8549-4769-A660-230B67E15B25/Windows6.1-KB2819745-x64-MultiPkg.msu"
}

$FileName = $DownLoadUrl.Split('/')[-1]
download-file $downloadurl "$powershellpath\$filename"

."$powershellpath\$filename"

You can use this script to save you typing ".net 4.5 framework" and "windows management framework 4" into Google. It's got the Microsoft download URLs hardcoded, and we use our tricky download-file function from weeks ago. Which is the great thing about getting into scripting, you really only have to solve a particular problem once! When the solution is in your script repository you can clone your own work!

The bulk of this script is just checking what .net and powershell versions are installed continuing or breaking depending on that. This should be familiar territory if you've been following along for the past 6 weeks.

We do check the Environment and check if we're running a 64bit version of windows then download the correct WMF file for your OS. That's a bit tricky.

We've also included the WMF for windows 8 so if you've got it installed you can copy and paste that into the $downloadurl (while you wear a potplant for a hat you mad windows 8 bugger you).


Wednesday, October 30, 2013

Everyday Powershell - Part 6 - Replacing Printers

This is the next part in an ongoing series about Powershell. You may have heard about how awesome Powershell is but have struggled to find ways to make it useful in your day to day work. That's what this series is going to address. It'll provide scripts and knowledge to address practical everyday problems!

When the print server migrations happen they can be a big pain. Our last migration was breeze for us and the users thanks to Powershell!

This script requires a little more leg work than usual in that you've got to setup a file that can be referred to to map the old print queues to the new ones. Once you've got that though, you're a loop and a few WMI queries away from promised land of automagic printer migration!

The tricky part here is running the script as the user logs on... There are many ways to do this, and there's probably a good post on the subject coming.

$replacements = import-csv "\\SOMENETWORKSHARE\printers.csv"
$printers = Get-WmiObject -class win32_printer # obviously you can tart up this query with a bit of filtering e.g. | where {$_.name -like "*Toshiba*" -and $_.systemname -eq "\\OLD PRINT SERVER" -and $_.name -notlike "*SOMESITE*"}
$default = Get-WmiObject -class win32_printer -Filter "default = $true"

foreach ($printer in $printers)
{
    $printername = ""
    $newprinter = ""
    $newprinterpath = ""
    if ($printer.systemname -ne "\\NewPrintServer")
    {
        $printername = $printer.name
        $newprinter = $replacements | where {$_.old -like "*$printername*"| select -expandproperty new
        $newprinterpath = '\\NewPrintServer\'+$newprinter
        write-host "Replacing... $printername with $newprinterpath"
        (new-Object -com WScript.Network).AddWindowsPrinterConnection($newprinterpath| out-null
        $printer.delete()
    }
}

$printername = $default.sharename
$defaultprinter = $replacements | where {$_.old -like "*$printername*"| select -expandproperty new
(get-wmiobject -class win32_printer | where {$_.sharename -like "$defaultprinter"}).setdefaultprinter() 


This worked really well for our users. The tricky part was mapping the default printer to the new queue. I just ended up running it through the same logic as I'd setup for the replacements.

This is also another good example of a foreach loop in action.

The CSV needs two colums "old" and "new" this should contain the sharenames of the printers you are migrating... Like this;

old,new
old-hp,new-hp
old-samsung,new-samsung

Post a comment if you need any clarification on how to use this.

Wednesday, October 23, 2013

Everyday Powershell - Part 5 - Terminating AD User Accounts

This is the next part in an ongoing series about Powershell. You may have heard about how awesome Powershell is but have struggled to find ways to make it useful in your day to day work. That's what this series is going to address. It'll provide scripts and knowledge to address practical everyday problems!

So today we've got a script that let's us dig into handing arguments from the command line, if statements and a little bit of active directory!

Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin

#make sure we've got a user to work with
$user = $args[0]
if ($args.count -lt 1)
{
    $user = read-host "Please enter the username"
}

# make sure we've got the right user, not sure if out-gridview -passthru is the best option.
$users = get-aduser -Filter "samaccountname -like '$user*'"
if ($users.count -gt 1)
{
    Write-host "More than one user found. Who are we after? (highlight a name and press enter)"
    $user = ($users | Out-GridView -PassThru).samaccountname
}

Set-Mailbox -Identity $user -HiddenFromAddressListsEnabled $true

# Removed mailbox and user account
$groups = Get-ADuser $user -Properties memberof | select -ExpandProperty memberof
$groups | Remove-ADGroupMember -members $user
Disable-ADAccount $user
$disabledname = (Get-ADUser $user -Properties displayname).displayname + " (Disabled)"
Set-aduser $user -DisplayName $disabledname

# Tell the Manager
$manager = Get-ADUser $user -Properties manager |  %{(Get-AdUser $_.Manager -Properties EmailAddress).EmailAddress}
$subject = "$user has been disabled."
$body = "HI, $manager, $user has left the organisation. We've disabled the user account."
Send-MailMessage -From "helpful@helping.com" -to $manager -Subject $subject -Body $body -SmtpServer smtp.gmail.com

Let's run through this section by section
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin
This line just loads a snapin for exchange it's nifty how powershell can be extended like this.

$user = $args[0]
So you can see we're setting $user to this $args[0] thing... this is how we tell our script to accept the "arguments" that came after the name of our script. If you save the whole script to c:\temp\terminate-user.ps1 you could call the script with an argument like this;
C:\temp\terminate-user.ps1 USERNAME
This would run the script with $user set to the argument "USERNAME". The [0] is just refering to the first argument handed to the script (yup you can hand more than one!)

if ($args.count -lt 1)
{
    $user = read-host "Please enter the username"
}
This chunk is our first if statement. We're checking the .count of $args to see if there is Less Than (-lt) 1. IF there aren't any $args we ask the operator for a username using read host. IF $args.count is 1 or greater powershell knows to skip over the script block defined by the curly braces { and }

$users = get-aduser -Filter "samaccountname -like '$user*'"
Doing a similar thing here with $users... We're going out to Active Directory and finding all the accounts with a name like $user 

if ($users.count -gt 1)
{
    Write-host "More than one user found. Who are we after? (highlight a name and press enter)"
    $user = ($users | Out-GridView -PassThru).samaccountname
}
Now we're checking $users to see if it has a .count Greater Than (-gt) 1. If it does we throw a warning and give the operator a grid of users to pick from. We do this so we can be sure that we're disabling the user we mean to.

Set-Mailbox -Identity $user -HiddenFromAddressListsEnabled $true
Hiding the user from the Global address list.

$groups = Get-ADuser $user -Properties memberof | select -ExpandProperty memberof
$groups | Remove-ADGroupMember -members $user
Removing the user from all AD groups by creating $groups populated with all the groups $user is a member of then piping $groups to remove-adgroupmember... Isn't powershell cool!?

Disable-ADAccount $user
$disabledname = (Get-ADUser $user -Properties displayname).displayname + " (Disabled)"
Set-aduser $user -DisplayName $disabledname
This next bit disables the user... and then we add (Disabled) to the display name. This way anyone snooping through AD won't freak out when they discover staff that have departed. 

$manager = Get-ADUser $user -Properties manager |  %{(Get-AdUser $_.Manager -Properties EmailAddress).EmailAddress}
$subject = "$user has been disabled."
$body = "HI, $manager, $user has left the organisation. We've disabled the user account."
Send-MailMessage -From "helpful@helping.com" -to $manager -Subject $subject -Body $body -SmtpServer smtp.gmail.com

Lastly we have a little value add for the users manager. We send them a little email so they can be sure the account has been disabled. You'll have to jiggle that send-mailmessage command so it works in your environment.

Monday, October 21, 2013

I knew this was coming...

How long is it from the time the boss becomes aware of a particular capability until we are asked to use it across the whole organisation...?

Not long ;D

http://occasionalutility.blogspot.com.au/2013/07/deleting-emails-from-users-mailboxes.html

Some bit of communication got out of the gates too early and the Boss wanted a "recall"

It was really rewarding to be able to say "No problem Boss! We've got a script for that." Clickety Click, she's all fixed!

My advice? Start building your arsenal of awesome scripts as soon as you can.

Wednesday, October 16, 2013

Everyday Powershell - Part 4 - Chore List

This is the next part in an ongoing series about Powershell. You may have heard about how awesome Powershell is but have struggled to find ways to make it useful in your day to day work. That's what this series is going to address. It'll provide scripts and knowledge to address practical everyday problems!

I found this in my travels a while back;
http://www.reddit.com/r/PowerShell/comments/1n8w5w/assigning_daily_family_chores/

It's not my script but I have made a couple of changes for the sake of compatibility. I've picked it out because it's a fantastic idea and a great opportunity to look at two important functions in powershell. import-csv and foreach.

Have a look at the script;

# requires -version 2
# Chores.ps1
# Parses chores.csv, randomly assigns the chores and sends an email to each person
# Retrieves family members from family.csv. First person listed in family.csv receives full summary email

$today = (get-date).DayOfWeek
$chores = import-csv chores.csv | where-object {$_.$today -eq 1| select Chore, Weight
$chores = $chores | get-random
-count ($chores.count) | sort-object -property Weight -Descending
$family = import-csv family.csv
$choremsg = @()
$smtpuser = "person@gmail.com"
$smtppassword = "notapassword"

write-host Generating chores list for $Today

# Create first column in $chorelist array to hold the names of who is doing the chore
foreach ($familymember in $family)
{
    $choremsg = $choremsg + ("`r`n" + $familymember.person + "'s Chores for the day:`r`n-------------------------------`r`n")
}

$choreworker = 2
foreach ($chore in $chores)
{
    $choremsg[$choreworker - 1] = $choremsg[$choreworker - 1] + $chore.chore + "`r`n"
    $choreworker++
    if ($choreworker -gt $family.count) { $choreworker = 1 }
}

$choremsg = $choremsg + ("`r`nPlus other duties as assigned `r`n")

$choremsg
$choremsgsubject = ("Chores for " + $today)

$smtpconn = new-object system.net.mail.smtpClient
$smtpconn.host = "smtp.gmail.com"
$smtpconn.port = 587
$smtpconn.EnableSsl = $true
$smtpconn.Credentials = new-object system.net.networkcredential($smtpuser,$smtppassword)
foreach ($familymember in $family)
{
    $smtpconn.send('person@gmail.com',$familymember.email,$choremsgsubject,$choremsg)
}

It's a cool bit of powershell that automates dishing out household chores to the family. You need to setup 2 CSV files for this to work.

Chores.csv Chore,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Weight Feed dog,1,1,1,1,1,1,1,1 Feed cat,1,1,1,1,1,1,1,1 Clean Main Floor Bathroom,0,0,1,0,0,0,1,3 Family.csv Person, Email Dad,dad@gmail.com Mom,mom@gmail.com kid1,kid1@gmail.com kid2,kid2@gmail.com

Once you've got those CSV files we can use the import-csv function to pull them into powershell as objects that we can use!

Give it a try! Fill $family with the content of the CSV
$family = import-csv family.csv

Cool now just type;
$family

You'll see that your CSV is now a powershell object with properties corresponding to the column headers in your CSV.

Now try
$family.person

This'll just give you all the people. Now try
$family.email

This'll output just the emails.

The other thing to note with this script is the foreach command. Basically this is saying for each thing in a group of things do something. This kind of loop is used all the time in powershell and it's worth taking some time to get your head around.

This script is the essence of Everyday Powershell! Scripting with powershell shouldn't be intimidating, it's automation made awesome! The best way to learn is to just get stuck in and start solving problems and I can't think of much better problems to solve than eliminating arguments about who does what around the house!

So big thanks to Reddit user MessageforyouSir for this little gem. It's a been great excuse to play with and learn about import-csv! Oh and remember if you want to schedule a script check out Part 1 of this series.

Security Note - The security aware among you will note that the script requires that you hard-code a password in plain text. This is generally not considered good practice and wouldn't be necessary if you run you own SMTP server that allows relaying. There's probably a post or two just in managing and storing and using credentials in this way. You'll have to stay tuned for those.