HOWTO: Convert SQL data to PowerShell Objects

As you develop your PowerShell skills, you’ll start to see how they can be useful everywhere – even in places where it might not seem obvious.
Let’s say you have a SQL database that you need to extract information from but you’re not terribly strong with SQL.  You are pretty good with PowerShell though.
Wouldn’t it be great if you could easily "convert" your SQL data into a native PowerShell object and then do all of your filtering and customization there?  In a language you know and love?

I found myself in that exact situation and I’m pleased to say I found a simple and elegant solution.

Sidenote: As I was preparing for this blog post, I realized it can be incredibly difficult to build screenshots and database queries that don’t contain potentially sensitive information.

Let’s make up a scenario in which we have a database called ‘rdm’ and inside that there is a table called ‘ConnectionLog’.
We want to find the 5 dates in which the user ‘rvance’ logged in most frequently.  Let’s assume we are doing some kind of security analysis. 

Continue reading

Adele would be amazing at Karaoke

I was browsing the news today and learned that Adele recently performed on Saturday Night Live.  It’s clear by this point that she is a very capable singer. But for a major television performance, I always just cynically assume that the artist is seldom actually singing.  I often expect them to be either dubbed based on a studio recording or with liberal use of Autotune being applied.  Here is an example of what I mean:

 

Or how about this?  Here is a much more humorous take on how much work is required to make singers sound actually good called “Sound Engineer’s Hard Work”:

 

All of this brings us to the point.  The reason I knew Adele performed on SNL is because her raw mic audio feed was leaked from the performance.  If this were to happen to a good chunk of modern musicians these days, I suspect their PR teams would be working a lot of overtime.  But Adele is not most musicians.  The raw audio feed was in my opinion even better than the full song with all of the other instruments and backup singers.  There are not many artists that can carry a song entirely on their own with nothing but their voice but Adele would be one of them.

Continue reading

HOWTO: Create Fastest Possible Custom Objects [Revisit]

In November of last year I did a post called the "Fastest Possible Way to Make Custom Objects in PowerShell".

http://pleasework.robbievance.net/howto-fastest-possible-way-to-make-custom-objects-in-powershell/

That post came about because I was frustrated with the techniques I was using at the time to make custom objects and decided to sit down and once and for all simply the process.
If you have a look at that code, you’ll see using it was simple enough but initializing it was all sorts of bizarre.

I’m pleased to say that in the year since I’ve wrote that post, I’ve learned a lot about PowerShell. I’ve learned that I made things far, far more complicated than it needed to be.*
(* The technique above technically is still useful for PowerShell 2 environments as the technique below I’m pretty sure isn’t supported there)

Let’s say you want to make a custom object.  Specifically let’s say you want to show the filename of all files in a folder and the first line of each file.  Look how easy this is now:

Get-ChildItem c:\windows\*.log | % { [pscustomobject]@{‘Filename’=$_.fullname; ‘FileContent’= (Get-Content $_.fullname | select -first 1)} }

If that doesn’t make any sense, here is the important part.  To make a custom object, do the following:

[pscustomobject]@{‘column1name’=column1content;’column2name’=column2content;}

You’re using a "type accelerator" called [pscustomobject] and defining it as a hash table (series of key/value pairs).  The right side of the equals in each line can be wrapped in round brackets allowing you to run anything you want and save it into the final object, just as I did above with Get-Content.

Very cool stuff!

HOWTO: Reusable Template with Windows Server Trial

This HOWTO covers how use the free 180 trial version of Windows Server 2012 R2 to build a reusable template for lab and development purposes.
The objective of the steps below are as follows:

  • Ensure that whenever we need to deploy a new Windows Server for testing, it will always have the latest updates and customizations
  • Each deployment will be sysprepped ensuring there are no SID conflicts when using multiple machines
  • Allow for the latest updates and new customizations to be be added in the future without impacting already deployed test machines
  • New Server 2012 R2 VMs including fresh domain controllers and domain joined member servers must be spun up as quickly as possible with as little user intervention as possible
  • The process must allow for an unlimited number of Syspreps to take place
  • This entire guide must be repeated every 6 months as we are using the free trial version of Windows Server 2012 R2
  • Any machines deployed during the 6 month trial period will reset and start from their own 180 day counter independent of the source
  • The implement will leverage the linked clone snapshot feature found in VMware Workstation

 

Overview

The idea behind this build is to provide the fastest possible way to build new Windows 2012 R2 Active Directory lab environments.
I’m aware there are other solutions available but I wanted to see what I could accomplish using nothing more than the 180 day trial ISO, VMware Workstation and PowerShell.
The configuration may seem a little complex but once these steps are in place, it becomes hilariously easy and fast to deploy new test servers at home for testing.
The idea is to build out a new VM using the trial, snapshot it, sysprep it and then use PowerShell scripts to automatically build new Domain controllers and join to the domain using Desired State Configuration.

Continue reading

HOWTO: Desired State Configuration (DSC) Overview

I found myself curious if I could get my entire lab environment to build itself from scratch in a 100% automated fashion including deploying and configuring Active Directory.

All of the cmdlets to perform these tasks already exist in PowerShell and are trivial to perform individually.  But what happens if you want to combine them together?  In order to do that, you have to be able to manage restarts and be able to continue your script where it left off.  That turns out to be a non-trivial problem for traditional PowerShell scripts.  As I was researching how I might accomplish this, I stumbled across something called “Desired State Configuration” or DSC.  I’ve heard it talked about constantly over the last year or two and all of the major PowerShell bloggers have stated that it is the concept to learn after you’ve got the basics of PowerShell down.  I’ve never had a reason to look into it though… until now.

The first thing I did was start watching the Microsoft JumpStart series on DSC.  I just assumed it would be a module’s length but to my shock and surprise, Microsoft created an entire course on just DSC and that course is 2 full days – equal to that of the JumpStart for PowerShell itself!  The next thing that jumped out at me is that at the beginning of the video series, one of the hosts and inventor of PowerShell, Jeffrey Snover said:

“DSC is not just a priority at Microsoft, it is THE priority at Microsoft.”  That may sound like hyperbole coming from most people but remember that Jeffrey Snover is now the Technical Lead Architect for Windows Server.  So even accepting he probably meant his team specifically, that’s still a big deal for those working in IT as most of our time is spent with the products he is responsible for.

With that backstory out of the way, what exactly is Desired State Configuration and why should you care?  Here is what I’ve been able to piece together so far.  DSC is a mechanism for you to define how a server or servers should be configured.  Notice I did not say how specifically to configure them.  This is the first thing that took me a while to wrap my head around.  In DSC, you never define HOW to configure a server, only WHAT you want the final product to look like.  Think of DSC more as a manager than a programmer.

The way this is accomplished is Microsoft (and others) create something called “resources” which are effectively large, complex PowerShell modules that are written by professional programmers that contain all of the logic and error handling and dependency management that is almost always missing from amateur scripts.  You then use a special subset syntax of PowerShell to declare what you want and PowerShell will go out and do it for you.

Continue reading

Why are people on Kijiji like this?

I’ve recently put up several items on Kijiji and in doing so received a number of interested replies.  Unfortunately, many of those replies are practically unintelligible.

Why is this?  At first I thought that many of them are some kind of bot or automated software that intentionally uses exceptionally poor grammar but I couldn’t think a reason anyone would benefit from this since the people on the other end are already willing to provide you details and meet you.  So these replies must be from actual human beings.

Is our Education system really that bad?  Could it be that they are all non-native English speakers?  I’ve been in enough countries and met enough non-native English speakers to know that it’s not that.  ESL writers can come across as having poor English certainly, but it’s… different.  You can spot the mix up of language structures or at the very least identify that some modicum of effort was put into the writing.

As a public service, I’d like to present two such replies I received from two different people and rewrite the sentences in a way that makes some better degree of sense.  Names and phone numbers are changed.

“evething is gor 100 , am poor woma but my son is crasy got one of thid box i cant buy it . but is it 100 i like to buy it gor him ,let mr know pleas thank you .5551234567 bob”

“Everything is for $100.00.  I am a poor woman but my son is crazy.  He wants one of those Xboxes but I cannot afford it.  But is it really $100?  I’d like to buy it for him.  Let me know please.  Thank you.  555-123-4567 – Bob”

“I'm interested of Xbox, can you send me, cll no. Then I ca pay the xbox”

“I am interested in your Xbox.  Can you send me your cell phone number?  Then I can pay you for the Xbox.”

HOWTO: Stop VMs from locking when idle

Tell me if you’ve heard this one before.  You build a bunch of new VMs for your lab environment.  You have them all running but every time you switch back to a VM you find that it’s always locked and you  have to press Control-Alt-Delete and enter your password.  In a production environment, this is a great behavior but in a home lab it is maddening.  I finally sat down to try and fix this and it turns out it seems pretty simple.  Simply add the following two lines to your deployment script:

powercfg -change -monitor-timeout-ac 0
powercfg -change -standby-timeout-ac 0

What these commands do is change the following two properties (Turn off the display and Put the computer to sleep) for the currently active PowerPlan and sets them to Never.

image

HOWTO: Build Tasklist for Studying for Microsoft Exams

This HOWTO is fairly specific to me but since I needed to document it anyway I figured I would share it in case in benefited others.
I have scheduled yet another Microsoft Exam for October.  One of the techniques I use during studying is to take the base “Skills Measured” list from Microsoft and build a checklist around it so I can keep track of what to review next.  To do this I traditionally copy and paste the list from the Microsoft exam site and painstakingly edit it to make it compatible with a commercial task tracking software I purchased called Swift To-Do List.  It’s mind-numbing repetitive work and I always tell myself I’m going to try and automate it but never do.  Well that ends today.

What am I talking about exactly?  Well let’s use an example.  I’m scheduled to write the 70-462 – Administering SQL 2012.  So I visit the site (https://www.microsoft.com/learning/en-ca/exam-70-462.aspx) and get something that looks like this:

image

There are a series of one level collapsible trees that contain all types of items on the exam.  The catch is rather than provide them in a list, the entries are separated by semi-colons.  What would be so much more useful is if I could transform that list into something like this:

image

Continue reading

HOWTO: Enable SharePoint Enabled Lists with Office 365

Imagine this scenario.  You have a SharePoint 2007/2010/2013 server in your environment along with an Exchange 2007/2010/2013 server.  You have configured email-enabled lists in your SharePoint environment by leveraging an SMTP server configured on your SharePoint server along with a send connector on your Exchange server.  Everything works great and people can email an address and have their content automatically added as a SharePoint list entry.

Then you migrate to Office 365 in a full cutover migration, obsoleting your Exchange on premises server.  Your email to list functionality breaks.  What now?

I found myself in this exact situation and had to come up with a solution.  Here is what I came up with.  I won’t be covering every step here and instead will only consider high level requirements.  If you need more specific detail, please ask in the comments.

First, if you have performed a cut over migration, you likely don’t need the firewall rule you had for port 25 to your Exchange server anymore.  So the first thing to do would be to modify that firewall rule and redirect traffic for it from your Exchange server to your SharePoint server.

From there, you can do the following:

1) Modify your existing Exchange contact that is used for email to list functionality.  Change the email address from [name]@sharepoint.domain.com to [name]@domain.com where domain.com matches your primary production domain.
Next, if you are using some type of Directory Synchronization tool, perform a sync to Office 365.

clip_image001

Continue reading

HOWTO: Clear all AD Attributes from Former OCS/Lync Deployment

Imagine for a moment you have just deployed Office 365 in your environment using a cutover migration.  Everything is working well and you’ve now decided to grant access for your users to use Skype for Business.  (I so hate that name).

You find that some users are not appearing in the Skype For Business Users control panel.  You scratch your head and Google until you come across this excellent article:

http://blog.rickzeleznik.com/2014/07/29/issues-provisioning-lync-online-users-after-dirsync/

It basically tells you that at some point in the past, someone deployed either Office Communications Server or Lync server in your environment and the users that aren’t showing up have their msRTCSIP Active Directory attributes populated which is confusing Skype for Business.  The article goes on to show you how to clear the attributes.

Now imagine you test this process and it works and solves your problem.  But then you slink into your chair when you realize you may potentially have hundreds of users to update and each user has as many as 14 attributes each that need to be modified.

That’s the situation I found myself in.  If you’ve seen any other posts on this site, you already know how I solved this – PowerShell!

I figured I’d share my solution in the event anyone else is in this situation.  This is very rough code designed to solve the immediate problem and as a result I’d suggest that you have some comfort with PowerShell before attempting to use this code.

Continue reading