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
}

I have been toying with an idea for a while since I have been diving deeper into Active Directory stuff with Powershell.

The idea is to develop a full Active Directory object backup and recovery tool. There are several excellent tools available commercially that do this, but with IT staffs and budgets shrinking it is often difficult to justify the cost.

I am sure others have thought about it, maybe dabbled with it, but it seems to me that all of tools and components are available to do this, just that nobody (that I know of) has kind of glued them all together in an easy-to-use interface.

I know many of you may say “Hey, doesn’t Microsoft have an AD Recycle Bin now?”. Well yes, they do…BUT many folks, I believe, are probably still running 2003 AD instances. Regardless of all of that, though, this sounds like a good exercise to at least explore doing a larger-scale “application” using Powershell.

This will also be my first major dive in the Powershell V2.0, so I hope to be discovering new and interesting ways to put these tools together.

From my perspective, I see several main components that will need development:
1. A user Interface. While I have done some of this with “visual” languages and a touch with Powershell, I haven’t done anything very extensive.
2. Interfacing with some type of database solution (probably SQL Server)
3. Access to AD objects without 3rd party utilities. I think this will be fairly straightforward but may end up using the Quest tools.
4. Access to the “delete objects” container in the directory and a means to reanimate those objects. (SDM Software Group has some great cmdlets for doing this which I will probably look at using).
5. General functions for manipulating AD objects (again…pretty easy)

One question keeps coming to mind, though is whether something like this would be useful? I am not entirely sure, but I hope so! To that end, I think the journey may be more worthwhile than the outcome.

Anyway, I hope to be spending more time with (when I *HAVE* spare time) and I hope to chronicle this “journey” here as I go.

If anyone has an opinion on this undertaking or has advice, please let me know.

Stay tuned for updates. Right now I am standing up a small infrastructure at home to support this development effort.

Thanks and Happy Scripting!

— Mark

Okay, this will be a quick and dirty post due to lack of time right now.
This one is kind of a tip, rather than a full-blown script or topic. Basically I was looking to grab which system was the PDC Emulator for my current domain (or NOT my current domain) and so I did some google-ing and finally ended up with these little functions.

All I need to do is pass in the DomainName and it spits out the info. For the FSMO roles, it will return an object and for the DomainMode, just the text is returned.

Hopefully you will find them useful.
That’s it for now…
Happy Scripting..
– Mark

Function get-PDCERole ($DomainName)
  {
   ## Return the PDC Emulator Role Owner for the specified Domain
   $DomainContext = New-Object System.DirectoryServices.ActiveDirectory.DirectoryContext("Domain", $DomainName)
   $Domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetDomain($DomainContext)
   $PDCE = $Domain.PDCRoleOwner
 
   Return $PDCE  
  }
 
Function get-RIDMasterRole ($DomainName)
  {
   ## Return the RID Master Role Owner for the specified Domain
   $DomainContext = New-Object System.DirectoryServices.ActiveDirectory.DirectoryContext("Domain", $DomainName)
   $Domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetDomain($DomainContext)
   $RIDMaster = $Domain.RIDRoleOwner
 
   Return $RIDMaster
  } 
 
Function Get-InfMasterRole ($DomainName)
  {
   ## Return the Infrastucture Master role owner for the specified Domain
   $DomainContext = New-Object System.DirectoryServices.ActiveDirectory.DirectoryContext("Domain", $DomainName)
   $Domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetDomain($DomainContext)
   $InfMaster = $Domain.InfrastructureRoleOwner
 
   Return $InfMaster
 }
 
Function Get-DomainMode ($DomainName)
  {
   ## Return the Domain Mode for the specified Domain
   $DomainContext = New-Object System.DirectoryServices.ActiveDirectory.DirectoryContext("Domain", $DomainName)
   $Domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetDomain($DomainContext)
   $DomainMode = $Domain.DomainMode
 
   Return $DomainMode
 
  }