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 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 

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!

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.

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!

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!

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.