Okay, so if you live under a rock and haven’t heard, the CTP for the Windows Management Framework 3.0 has been released by Microsoft (http://www.microsoft.com/download/en/details.aspx?id=27548).

I know, you are probably thinking “Big Deal!!” another Community Tech Preview. Not so fast there… THIS CTP includes Powershell 3.0!

Let me restate: “THIS CTP INCLUDES POWERSHELL 3.0!”

What I hope to do is post more as I begin to learn about what is new, what is cool, what is fresh, etc.

A little story about me and Powershell 3.0 ….

When I found out about the Windows 8 – Developer Preview, I wasn’t excited about the new interface, the new look of familiar applications… no. After I found out how to actually get to a command prompt, my first thing was to run Powershell and do a:

$host.version

To my UTTER DELIGHT, I saw the number “3″ in the output. My colleagues, of course, we interested in why I was ooo-ing and ahhh-ing.. then they saw that I was sitting at a Powershell prompt (of course) and the “geek”, “nerd”, and “dork” comments began.

Anyway, needless to say, I soon discovered I could install Powershell 3 on my Windows 7, SP1 laptop at home so I prompty did so upon arriving home from work.

Wow…that was a longer little story than I was expecting.

The bottom line is that I finally have it installed and have begun playing with it and I am a TOTAL GEEK.

——————————

One of the things I was afraid of was losing my Powershell 2.0 installation, yet I had read that they could co-exist. Well, seeing is believing, and I am a believer now.

Launching “powershell.exe” from a command line, by default, will open up version 3.

Launching “powershell.exe -version 2″ from a command line will bring up version 2.

Yes, it really is that simple.

Upon a cursory glance at the cmdlets available I notice that there are many more geared toward command line management of the system and features.

Also there is a cmdlet that helps you explore all of the cmdlets available via a GUI:

show-command

I have, literally, been playing with the new powershell for about an hour, so stay tuned for more updates on this.

Please, everyone, go grab it, kick the tires, run it through its paces, and poke it with a stick.

As always, happy scripting and have a good time discovering Powershell 3.0.

— Mark

Ah…wow.. has it really been over a YEAR since I last post? Sorry about that, but it has been an absolutely nutty year.

Anyway..
In my new job and new role, I get to have fun showing people the power of automation with Powershell.
One of the recent tasks I was helping with is to take a list of users (from AD) and query a database to get some info about these users.

While I had done some work with SQL queries with Powershell, I hadn’t ever “functionalized” it, so I decided to take this opportunity to do so.

While I am sure there are a bazillion examples of this out there, this is what I came up with and here is what it looks like.
Sorry there aren’t many (any) in-line comments like I normally do, but if you have questions, please let me know!
Thanks and hope you find this helpful.

As always… Happy Scripting!
– Mark

##-------------------------------------------------------------------
## Script: Get-SQLData.ps1
## Written by: Mark A. Weaver 
## Website: vmweaver.com
## 
## Version: 1.0
## Creation Date: 3/16/2011
## Purpose: This is really a function that you pass 3 parameters to. It will connect to the 
##          SQL Server you specify, to the Database you specify, and execute the
##          query you specify.
##          This connects with you existing credentials (integrated).
##
##    
##   Input: 
##                      -SQLServer "Name of your sql server"
##                      -Database "Name of your database"
##                      -Query "your query"
##     *Note if you want to have a query that spans multiple lines, etc, you can pass in a 
##      'here-string' or construct another multi-line (not array) string.
##          
##
##  Output: 
##      An array is passed back containing the data from your query.
##      If no data is returned, the array will be $null
##                 
##                              
##  Assumptions: 
##               
##               
#############################
## Updates:
##    
##
##----------------------------------------------------------------------
Function Get-SQLData
{
   param ([string]$SQLServer,
          [string]$Database,
          [string]$Query)    
 
        $SQLData=@()     
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Server = $SqlServer; Database = $Database; Integrated Security = True"
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
        $SqlCmd.CommandText = $Query
        $SqlCmd.Connection = $SqlConnection
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $DataSet = New-Object System.Data.DataSet
        $SqlAdapter.Fill($DataSet) | Out-Null
        $SqlConnection.Close()
 
        $RecordCount = ($DataSet.tables[0] | Measure-Object).count
        if ($RecordCount -eq 0)
        {
            $SQLData = $Null
        }
        else
        {
            $SQLData = $DataSet.tables[0] 
        }
  return $SQLData
}

Once again, my apologies for my lack of posting..

Anyway…

An interesting thing came up at work the other day when one of my fellow administrators asked me if I could resolve an unknown SID he was seeing in some logs to see what the heck it belonged to.

Since I had been telling him that Powershell could do ANYTHING (slight exaggeration, I know)… that it should be able to do this.

Well, it certainly is an interesting notion.

I know that I have run into this in the past where you have file system ACLs set and there are a bunch of SIDs sitting in there that nobody seems to know who they belong to.

While it isn’t THAT important to resolve them since the user account is most likely no longer around, it IS an interesting thought exercise.

After perusing the web looking for others who have done something similar, I feel I had enough to throw something together..

Basically when an Active Directory object (like a user) is “deleted”, it is really just Tombstoned for a period of time and is moved to the hidden container “Deleted Objects” and then removed after like 90 days or so.

Here is my solution to “finding” those objects.

First you will have to know what Domain you want to look at for this object AND you have know the SID you are looking for.

function Resolve-DeletedUserSID($Domain, $UserSID)
{
	## This is kind of a mashup of a few different scripts I found online in some forums.
	## Unfortunately I don't remember who did them.  If it was you, point me to your post and I will
	## give you the credit for your piece.
	## 
	## Returns User information for deleted account with the specified SID and User Domain
 
	$DomainRoot = "LDAP://" + $Domain.trim()
	$DomainDN = ([adsi] ( $DomainRoot )).DistinguishedName
	$adspath = "LDAP://" + $DomainDN
	$root = [system.directoryservices.Directoryentry] $Adspath
	$root.psbase.AuthenticationType = [system.directoryservices.authenticationtypes]::Fastbind
	## We will be looking in the "Deleted Objects" container which is normally hidden, etc.
	## You will need to execute this with an account that has DomainAdmin rights to the domain you are
	## querying.
	$root.psbase.path = "LDAP://cn=Deleted Objects," + $DomainDN
	$search = [system.directoryservices.directorysearcher] $root
	$search.filter = "(&(isDeleted=TRUE)(!(objectClass=computer))(objectclass=user))"
	$search.Tombstone = $true
 
	# If you have more than 1000 users, you must NOT define SizeLimit (we haven't)
	# and PageSize must be less than the default value (of 1000). 
	# I found this a bit strange...but as long as we understand it, I guess it is okay
	$Search.PageSize = 500
 
	# Only look in the top level of the Deleted Objects container.
	$search.SearchScope = [system.directoryservices.searchscope]::OneLevel
	$result = $search.FindAll()
 
	# If the SID isn't found, you will get nothing returned.
	$result | Select-Object @{ Name = "Name" ; Expression = { $_.Properties.Item("Name")[0].split("`n")[0] } }, `
	@{ Name = "SAMAccountName" ; Expression = { $_.Properties.Item("SAMAccountName") } }, `
	@{ Name = "SID" ; Expression = { New-Object System.Security.Principal.SecurityIdentifier($_.Properties.Item("ObjectSID")[0], 0) } }, `
	@{ Name = "WhenChanged" ; Expression = { $_.Properties.Item("WhenChanged") } } `
	| where-Object { $_.SID -ieq $UserSid.Trim() }
}

As always…

Happy Scripting!!!! and let me know if you have questions or problems.

– Mark