Everyday Powershell - Part 38 - Get-WindowsUpdateStatus

Got annoyed this week with some windows update configuration issues, so wrote this powershell to pull the relevant configuration information from the windows registry;

function get-windowsupdatestatus{     param(         $COMPUTERNAME     )     $report = invoke-command -ComputerName $COMPUTERNAME -ScriptBlock {             $days = "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"             $auoptions = "Notify before download","Automatically download and notify of installation","Automatically download and schedule installation","Automatic Updates is required and users can configure it"             $temp= "" | select UpdateServer, UpdateDay, UpdateTime, NoAutoRebootWithLoggedOnUsers, NoAutoUpdate, UpdateOptions             $temp.updateserver = (get-itemproperty HKLM:\\Software\Policies\Microsoft\Windows\WindowsUpdate).wuserver             $schedule = get-itemproperty HKLM:\\Software\Policies\Microsoft\Windows\WindowsUpdate\AU             $temp.NoAutoRebootWithLoggedOnUsers = $schedule.NoAutoRebootWithLoggedOnUsers             $temp.NoAutoUpdate = $schedule.NoAutoUpdate             $temp.UpdateOptions = $auoptions[$schedule.AUOptions -2]             $temp.updateday = $days[$schedule.ScheduledInstallDay -1]             $temp.updatetime = (get-date -Hour ($schedule.ScheduledInstallTime).tostring().padleft(2,'0') -Minute 00 -Second 00 -Format "HH:mm")             $temp     }     $report | select PScomputername, NoAutoRebootWithLoggedOnUsers,NoAutoUpdate,UpdateDay, UpdateTime,UpdateServer, UpdateOptions }

This allowed us to see at a glance where the issues were across all our servers.

The script only runs in a single thread so it's slow. But it wouldn't be hard to multi-thread. In our case there were less than 100 servers to be reviewed so it wasn't a priority. If there's demand for a faster version we'll make it happen. Might be a good demo of how to take a single threaded powershell function and make it multi-threaded.

Everyday Powershell - Part 27 - Uploading files to a FTP server

Full credit to Goyuix for his awesome example on StackOverflow which I am shamlessly ripping off for this Post!

$logpath = "C:\SomePath" $ftpserver = "ftp://someFTP.com//" $user = "User" $password = "Password" $ftppath = "Somefolder" $todayslogs = Get-ChildItem $logpath | where {$_.CreationTime -gt ((get-date).AddDays(-1))} foreach($log in $todayslogs){     $uploadpath = $ftpserver + $ftppath + "/" + $log.Name      # create the FtpWebRequest and configure it     $ftp = [System.Net.FtpWebRequest]::Create($uploadpath)     $ftp = [System.Net.FtpWebRequest]$ftp     $ftp.Method = [System.Net.WebRequestMethods+Ftp]::UploadFile     $ftp.Credentials = new-object System.Net.NetworkCredential($user,$password)     $ftp.UseBinary = $true     $ftp.UsePassive = $true     # read in the file to upload as a byte array     $content = [System.IO.File]::ReadAllBytes($log.FullName)     $ftp.ContentLength = $content.Length     # get the request stream, and write the bytes into it     $rs = $ftp.GetRequestStream()     $rs.Write($content, 0, $content.Length)     # be sure to clean up after ourselves     $rs.Close()     $rs.Dispose() }

This one was written to automate the upload of some logs. It happens every day so that's why we just (get-date).addays(-1) but that where on get-childitem can be anything you like.

One thing to note is the hardcoded password. You may not want to do this, depends on your security requirements. You can save the credentials in another file as a secure.string if you like. There's probably a post coming about that.

Everyday Powershell - Part 25 - Monitor for CryptoWall

So the bastards got us! Yup we got Cryptowalled!

We caught the infection before it did too much damage, isolated the PC, configured the firewall to block the IPs it was trying to get to and restored the pwnd files from backup. Business as usual really.

But what do we do to monitor for this in the future?

Whacked this together quickly;

$protectedpaths = "\\server1\someshare", "\\server2\someother" $filename = "-Canary-.txt" $foldername = "\-----AAA--TOP-----\" $canarystring = "If this can be read everything is ok" $logpath = "C:\scripts\cryptolockercanary.txt" $recipients = "someadmin@mail.com" $smtpserver = "somemailserver" $report = @() foreach ($path in $protectedpaths){     $temp = "" | select Time, Path, EncryptedStatus     $pathwithfolder =  $path + $foldername     $canarypath =  $pathwithfolder + $filename     $temp.time = get-date     $temp.path = $pathwithfolder     if (test-path $canarypath){         $test = Get-content $canarypath         if ($test -eq $canarystring){             $temp.EncryptedStatus = $false         }         else{             $temp.EncryptedStatus = $true             Send-MailMessage -to $recipients -From some@email.com  -Subject ("CryptoLockerCanary Has been changed! " + $temp.time) -BodyAsHtml ($temp | convertto-html | Out-String) -SmtpServer $smtpserver         }     }     else{         mkdir $pathwithfolder         out-file $canarypath -InputObject $canarystring         Set-ItemProperty -Path $canarypath -Name attributes -Value ((Get-ItemProperty $canarypath).attributes -BXOR ([io.fileattributes]::Hidden))         if (test-path $canarypath){             Send-MailMessage -to $recipients -From some@email.com  -Subject ("CryptoLockerCanary file is not present - creating new one " + $temp.time) -BodyAsHtml ($temp | convertto-html | Out-String) -SmtpServer $smtpserver         }         else{             Send-MailMessage -to $recipients -From some@email.com -Subject ("CryptoLockerCanary file is not present and COULD NOT CREATE new one - Recomend investigation " + $temp.time) -BodyAsHtml ($temp | convertto-html | Out-String) -SmtpServer $smtpserver         }     }     $report += $temp } $report | export-csv -Path $logpath -Append -NoTypeInformation $report

It'll take any folders defined in $protectedpaths and stick a "canary" file in there. If it sees a change to the canary file it'll send out emails to $recipients.

This is scheduled to run every five minutes so this way we'll get alerted really quickly if it get's through again.

Everyday Powershell - Part 17 - Using new-mailboxexportrequest with NON-US dates

Here's a crazy making problem!

The new-mailboxexportrequest commandlet will only accept dates in US formats. Roland Paix has made this post about the issue. The conclusion is switch your culture to EN-US before loading powershell. Sooo frustrating! But hey, something has to be the default datetime and Microsoft is an American company. No point getting emotional, let's just fix it.

Here's a neat trick you can use to set the Culture of the "Current Thread" you are running.

[System.Reflection.Assembly]::LoadWithPartialName("System.Threading") [System.Reflection.Assembly]::LoadWithPartialName("System.Globalization") [System.Threading.Thread]::CurrentThread.CurrentCulture = [System.Globalization.CultureInfo]::CreateSpecificCulture("en-us") Add-PSSnapin *exchange* $start = (get-date).adddays(-28) $end = (get-date).adddays(-14) $filter = "(Received -gt '"+$start+"') -and (Received -lt '"+$end+"')" new-Mailboxexportrequest -mailbox SOMEMAILBOX -ContentFilter $filter -filepath C:\SOME.pst

This should trick new-mailboxexportrequest into believing that your dates are in the "correct" format!

Everyday Powershell - Part 15 - Reading event logs with specific date ranges

Last post we were talking about using specific dates in our event log checker... So here's how;

$ComputerName = "SOMESERVER" [String[]]$EventLogNames=@("Application","System")#Main eventlogs $EventStartDate = get-date -date "18/12/2013 7:00pm" #(Get-Date).addhours(-1) $EventEndTime = get-date -date "18/12/2013 9:00pm" #(Get-Date).addhours(-0) $EventCritea = @{logname = $EventLogNames; StartTime=$EventStartDate; EndTime=$EventEndTime} Get-WinEvent -ComputerName $ComputerName -FilterHashTable $EventCritea  -ErrorAction SilentlyContinue | fl

Pretty simple really.

Everyday Powershell - Part 14 - Working with event logs at specific times

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

Missed a week over Christmas and a day late with this post. We trust you'll forgive us, holidays is holidays. For us that means no computer.

Today's script is a simple one for looking at all the events in an event log within a certain time frame.

It's the kind of script you need when the boss reports odd behavior on his or her PC. You ask "what time was this boss?" bang in the time frames the boss reports into this script and you'll get a list of events in that time frame! Oh powershell you're so useful!

[String]$ComputerName = "someserver" [String[]]$EventLogNames=@("Application","System")#Main eventlogs $EventStartDate = (Get-Date).addhours(-6) $EventEndTime = (Get-Date).addhours(-4) $EventCritea = @{logname = $EventLogNames; StartTime=$EventStartDate; EndTime=$EventEndTime} Get-WinEvent -ComputerName $ComputerName -FilterHashTable $EventCritea  -ErrorAction SilentlyContinue

$eventstatdate and $eventstarttime don't have to be relative to the current time. Bonus points if you can figure out the correct syntax for inputting an arbitrary time.

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.

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.