T-SQL Tuesday #111 – Automate All the Things

Words: 1085

Time to read: ~ 5 minutes

T-SQL Tuesday time!

It is time for the first T-SQL Tuesday of the year. This month we have Garry Bargsley ( blog | twitter ) who asks us for two tasks for this months topic:

  • What do you want to automate or what automation are you proud of completing?
  • What is your go-to technology for automation?

Now I’ve been a fan of automation for the past few years so I am loving this months topic!

I would love to talk about some of my favourite modules (cough dbatools, dbachecks cough) and how they can free up your time to devote to other activities you want to do but I’m not going to do that. Not because they are not worthy of being talked about; nothing could be further from the truth! More because that I am certain that other, better bloggers and techies will be talking about them and can do better justice to them than I could.

So we’re going to go a little off script and not talk about databases, although I’m sure you’ll see that this automation can easily be extended to databases with a few, small tweaks.

So without further ado, let me tell you the story of James.


There once was a report writer named James. Somehow, unbeknownst to anyone including himself, James had managed to make it so that he was constantly getting locked out.

What didn’t help was this occurred right after the holidays, a holidays that the SysAdmin had happily and deservedly taken. So there was no one left to help James apart from a DBA who knew how to unlock his account but not how to troubleshoot what was causing it to be unlocked in the first place.

Automation 01

James comes to the DBA and says that he is locked out.

Queue the DBA:

  • Connecting to the Domain Controller,
  • Opening up Server Manager,
  • Clicking “Tools” and opening up “Active Directory Users and Computers”,
  • Right clicking the domain name and clicking “Find”,
  • Typing “james” and clicking “Find now”,
  • Right clicking the user and selecting “Properties”,
  • Selecting the “Account” tab,
  • Checking the “Unlock Account” check box, and
  • Click Apply/OK

That is quite a lot of steps. So around 10 minutes later when James found that he was locked out again and when I looked over all the steps that I had to do…well…

Time, nobody for that, got!

So let us automate that away! I happen to know that there is an ActiveDirectory module so using Get-Command, 1 of the trifecta PowerShell commands, I found the two commands in the ActiveDirectory module that could help me out here.

  • Search-ADAccount -LockedOut
  • Unlock-ADAccount

Search-ADAccount LockedOut |
Where-Object Name -eq 'James ReportWriter' |

Now, whenever James tells me that he is locked out, I can just run the above script and he’s unlocked once more.

Automation 02

Great! Automation done? No.

Next came James asking if there was some way to figure out if his account was locked out or not. Apparently he was sick of having to walk up and down the stairs to me every time he came back from a break.

Thankfully I’ve been following the exploits of Joshua King ( blog | twitter ) and knew about his module BurntToast. So I open up a new PowerShell console, import the BurntToast module, and leave this script running in it.

while ((Get-Date) -lt (Get-Date '2019-01-08 17:00:00')) {
$JamesLockedAccount = Search-ADAccount LockedOut | Where-Object Name -eq 'James ReportWriter'
if ($JamesLockedAccount) {
New-BurntToastNotification Text 'James is locked out again.'
# Since this is a script, I'll manually clean up after myself.
Remove-Variable Name JamesLockedAccount
Write-Host '.' NoNewLine
Start-Sleep Seconds 180

This little baby pops up a little event to my computer whenever it checks and finds that James that is locked out.

Then it’s just a simple case of running my original script and James can go back to trying to figure out what is locking him out. All is good…or is it?

Automation Many

Great! Automation done? No.

Honestly, I was getting annoyed. I feel most people would be if they were getting flooded with alerts.

This does not seem fair to me. It’s James that is getting locked out, why is it that I am the one being inundated with messages! I also don’t want to walk over to his desk every single time, what about if he’s not there?

Thankfully I remembered that there was also another module available from another PowerShell powerhouse, Warren Frame ( blog | twitter ); PSSlack.

Yes, the same guy I talked about with Join-Object.

Now, instead of getting swamped with Toast notifications, I can change the above code from New-BurntToast to Update-JamesStatus and it’ll send a Slack to James every time he is logged out.
This works since I know James has Slack on his phone so he’ll receive the message even if he’s not at his computer.

function Update-JamesStatus {
[Parameter(Position = 1)]
$SlackTitle = 'James is locked out again…',
[Parameter(Position = 2)]
$SlackText = '…again.',
[Parameter(Position = 0, Mandatory)]
begin { $SlackToken = 'Get-your-own-dont-try-and-use-mine' }
process {
$SlackMessageAttachment = @{
Color = $_PSSlackColorMap.green
Title = $SlackTitle
Text = $SlackText
Fallback = $SlackTitle
$SlackMessage = @{
Channel = $SlackUser
IconEmoji = ':bomb:'
AsUser = $true
UserName = 'TFS_Bot'
$null = New-SlackMessageAttachment @SlackMessageAttachment |
New-SlackMessage @SlackMessage |
Send-SlackMessage Token $SlackToken

Automation Lots

Great! Automation done? No.

While these were fun and I got to use some and learn some PowerShell, these aren’t viable workarounds while we wait for the SysAdmin to come back.

Plus I was taking the next day off so James was going to be all alone without anyone to unlock him. Since I was taking my laptop, there was no way that just leaving it on at work in an loop was going to work.

Luckily there are still more PowerShell powerhouses out there. Up next Boe Prox ( blog | twitter ) and his piece on Constrained Endpoints!

Now normally I would try and walk you through setting these up but this post is getting extremely long already so I’ll just leave you with the brief summary.

  • I set up a constrained endpoint to the Domain Controller using the above link, called UnlockJamesEndpoint.
  • I created a function that wrapped around the script to unlock James, calling it Unlock-James naturally 😉
  • I restricted the endpoint to only be able to run the function Unlock-James, nothing else! Not Get-Member, not Get-Help, nothing!
  • I slammed my head against a desk for a few hours trying to get PowerShell remoting to work.
  • I nearly cried when I got it working, all thanks to Steve Valdinger ( twitter | blog ) and gpupdate /force
  • I granted permission to invoke that endpoint to James’ manager and told him what to do when James got locked out.
Invoke-Command -ComputerName <domain controller> -ConfigurationName UnlockJamesEndpoint -ScriptBlock { Unlock-James }

And I went on my merry way for my day off happy knowing that I had automated the problem away until it could be fully fixed.

All Together

Granted this was not about automation with databases but it’s something that I am proud of.

Plus I can think of a few ways I can use these with databases. Create a few endpoints so people can query the status of backups, no problem. Send Slack messages when needed, easy now. Push a Toast notification when a job finishes, yes please!

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

One thought on “T-SQL Tuesday #111 – Automate All the Things”

Leave a Reply

%d bloggers like this: