How to use PowerShell to search Active Directory

PowerShell 2.0 introduced 76 new CmdLets, including Get-ADUser which allow you to query Active Directory.
This was a great progress as the first release of PowerShell was miserably failing in providing a good interface with Active Directory which is unbelievable for a Microsoft product!
So now, Get-ADUser and many other CmdLets are available; great! Here are the details on Microsoft Website

Hang on a minute, there is a limitation to that, you need Windows 2008 R2 domain controllers...

If you can't upgrade your Windows Active Directory controllers to Windows Server 2008 R2, there is a workaround: you can install Active Directory Management Gateway Service on your Windows Server 2003 or 2008 domain controllers.

In the meantime, there are still options to query Active Directory using PowerShell via LDAP.

The script below will show you an easy way of doing it.

[string]$category = ""
[string]$name = ""
[string]$data = ""
[string]$attribute = ""
[string]$strFilter = "(&(objectCategory=" + $category + ")(" + $name + "=" + $data + "))"
$objDomain = New-Object System.DirectoryServices.DirectoryEntry
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.PageSize = 1000
$objSearcher.Filter = $strFilter
$objSearcher.SearchScope = "Subtree"
$colResults = $objSearcher.FindAll()
$colResults | foreach {$_.Properties.Item("$attribute")}

Let's first have a look at the input variables:

• $category is the category of object you're searching search in Active Directory (could be user, group, contact, groupPolicyContainer...)
• $name is the name of the attribute you're using as a filter to find the object (could be displayName, sAMAccountName...)
• $data is the value of the attibute you're using as a filter to find the object ("Joe Bloggs", ...)
• $attribute is the attribute's value you want to read from Active Directory for all objects matching your criteria (givenName, telephoneNumber, department)

Once the parameters in place, you create a domain object ($objDomain) where the search will be conducted (no need to mention the domain, the one in use by the computer running the script will be automatically selected)
Then, you create a searcher object ($objSearcher) where you define all the settings of your search (domain where you do it, maximum number of object to return, filter to apply to your search and scope of your search)
Finally, you run the search by using the FindAll() which will return a collection of objects to $colResults.
The last line returns the result(s) of your search. The SearchResult collection will return objects with the Properties property which stores the data of your Active Directory objects. To read it, you just have to use the Item property with mentioning the attribute you want to read to get its value.

Once, you get it right, you can wrap it up in a function like:

function Get-AdsiData {
    [CmdletBinding()]
    param (
        [string]$category,
        [string]$name,
        [string]$data,
        [string]$attribute
    )
    $strFilter = "(&(objectCategory=" + $category + ")(" + $name + "=" + $data + "))"
    $objDomain = New-Object System.DirectoryServices.DirectoryEntry
    $objSearcher = New-Object System.DirectoryServices.DirectorySearcher
    $objSearcher.SearchRoot = $objDomain
    $objSearcher.PageSize = 1000
    $objSearcher.Filter = $strFilter
    $objSearcher.SearchScope = "Subtree"
    $colResults = $objSearcher.FindAll()
    return $colResults | foreach {$_.Properties.Item("$attribute")}
}

Read more on the DirectoryEntry object and the DirectorySearcher and the SearchResult objects...

How to query a SQLite database with PowerShell

Relational databases are really useful to store data and can achieve far more than flat files, so you might be interested in accessing databases with your PowerShell scripts.
However, most of the relational databases applications require proper installation and quite a lot of resource.
For SQLite, this is not the case; this is a "self-contained, serverless, zero-configuration, transactional SQL database engine"; it combines the best of both flat files and relational databases. For more information, please visit the website http://www.sqlite.org/

In order for PowerShell to be able to communicate with SQLite, you need to download and install the ADO.NET adapter for SQLite: http://system.data.sqlite.org, which includes the engine.

Once installed, the following script will show you how to query a SQLite database:


[string]$sqlite_library_path = "C:\Program Files\System.Data.SQLite\2010\bin\System.Data.SQLite.dll"
[string]$db_data_source = "C:\Temp\SQLite\MyDatabase.db"
[string]$db_query = "SELECT * FROM MyTable"

[void][System.Reflection.Assembly]::LoadFrom($sqlite_library_path)

$db_dataset = New-Object System.Data.DataSet

$db_data_adapter = New-Object System.Data.SQLite.SQLiteDataAdapter($db_query,"Data Source=$db_data_source")
[void]$db_data_adapter.Fill($db_dataset)
$db_dataset.Tables[0]




Let's go into details...

Let's talk about the variables:
First, you have to provide the location of the binary to use:
[string]$sqlite_library_path = "C:\Program Files\System.Data.SQLite\2010\bin\System.Data.SQLite.dll"

Then, you need to provide the location of your database:
[string]$db_data_source = "C:\Temp\SQLite\MyDatabase.db"

And finally, provide the SQL query you want to execute:
[string]$db_query = "SELECT * FROM MyTable"

Now, we load the SQLite library:
[void][System.Reflection.Assembly]::LoadFrom($sqlite_library_path)

You need to use a System.Data.DataSet object to store the data retrieved from the database:
$db_dataset = New-Object System.Data.DataSet

The  SQLiteDataAdapter will run the query in the $db_query variable against the database described in the "Data Source=$db_data_source" connection string.
$db_data_adapter = New-Object System.Data.SQLite.SQLiteDataAdapter($db_query,"Data Source=$db_data_source")

Once done, we pass the result to the DataSet using the Fill() method:
[void]$db_data_adapter.Fill($db_dataset)
([void] here is used to ignore the value the Fill() method will return)

Finally, the result is displayed when reading the table from the DataSet:
$db_dataset.Tables[0]

Hope this helped...

How to query a SQL database with Powershell

You can use PowerShell to read or execute query to various database like SQL, MySQL, or SQLite.

The following example shows you one of the possibilities of retrieving data from a SQL database:


[string]$db_server = "My_SQL_Server"
[string]$db_name = "My_Database_Name"
[string]$db_query = "My SQL Query"
[string]$db_connectionstring = "Data Source=$db_server;Database=$db_name;Integrated Security=true;"
$db_data_adapter = New-Object System.Data.SqlClient.SqlDataAdapter($db_query,$ db_connectionstring )
$db_dataset = New-Object System.Data.DataSet
[void]$db_data_adapter.Fill($db_dataset)
$db_dataset.Tables[0]

Let's have a look at the different steps involved in this script.

First of all, you need to define your 3 parameters: location of the database, database name and your SQL query.

[string]$db_server = "My_SQL_Server"
[string]$db_name = "My_Database_Name"
[string]$db_query = "My SQL Query"

Using the first two parameters, you'll be able to create your connection string:
[string]$db_connectionstring = "Data Source=$db_server;Database=$db_name;Integrated Security=true;"


Integrated Security = true means you'll use the Windows account running the script to authenticate on the database; if another type of authentication is required, look at System.Data.SqlClient.SqlCredential.

You create a Data Adapter object to retrieve the data from the database mentioned in the connection string using the query (3rd parameter):
$db_data_adapter = New-Object System.Data.SqlClient.SqlDataAdapter($db_query,$ db_connectionstring )

Then, you initiate a DataSet object in order to receive the data from the Data Adapter
$db_dataset = New-Object System.Data.DataSet

You store the data from the Data Adapter to the DataSet object using the Fill method:
[void]$db_data_adapter.Fill($db_dataset)

And finally, you display the result:
$db_dataset.Tables[0]

Another option should be to use the SqlDataReader but it seems to be less efficient and also slower; it keeps the database connection opened longer than the SqlDataAdapter.

Once your script gets more complicated, you might need to use the SqlConnection and SqlCommand objects to gain more control about within your script and connection, but for a simple query, this solution will do it.

Hope this was helpful.

How to create a HTA like in Powershell

HTML Applications (HTA) are very useful to provide a Graphical User Interface (GUI) to your VBscript, to ease its use, avoid typing mistakes and provide better output.

Would it be possible to do similar thing in PowerShell?

Unfortunately, Powershell does not allow you to use HTA as you can do to provide a GUI for your VBscripts.

However, Powershell gives you access to .Net libraries which means that you can create a GUI using Windows Forms. Here is a example of what it looks like:

[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")

$objForm = New-Object System.Windows.Forms.Form
$objForm.Text = "My PowerShell Form"

$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(20,20)
$objLabel.Text = "Hello World!"
$objForm.Controls.Add($objLabel)

$ExitButton = New-Object System.Windows.Forms.Button
$ExitButton.Location = New-Object System.Drawing.Size(80,80)
$ExitButton.Text = "Exit"
$ExitButton.Add_Click({$objForm.Close()})
$objForm.Controls.Add($ExitButton)

$objForm.ShowDialog()


You can copy the code in file, save it as test.ps1 and execute the script with PowerShell; you'll see a GUI like this:

This is not an easy task as Windows Forms will require a lot more coding than HTA, but you can design really nice interfaces for your scripts.

In order to ease the delivery of forms, without having to type hundreds of lines of code, you can use a free tool from Sapien to create the code behind the GUI called PrimalForms. You'll be able to design the GUI and it will generate the code for you. Then, all you have to do is to merge the "GUI code" into your script and link the events.

To find PrimalForms Community Edition, you'll have to access the download section on Sapien website and create an account. I'm telling you: it's worth doing it, and it's free.

References:
System.Windows.Forms Namespace
Sapien - PrimalForms Community Edition

How to Add an Exit Function to a HTA

Following my previous post about the WScript.sleep function, here is an function to add to an exit function to a HTA.


Again, using HTA is really useful to bring life to a VBScript by adding an interface. So, converting your VBScript files to a HTA is not really complicated as VBScript is native, however some functions like the WScript.Quit are not available.


In a HTA, an easy way of exiting is to call the close method of the current Window object (self). Then, you have just to call the sbExitHTA sub to close the Window.


Also, if you want to get rid of the close button from the Windows title bar, add SysMenu="no" in the HTA:APPLICATION section.


<html>
<head>
<title>Simple HTA</title>
<HTA:APPLICATION
     ID="oSimpleHTA"
     APPLICATIONNAME="SimpleHTA"
>
</head>
<script Language="VBScript">
Sub Window_Onload
      dataarea.innerhtml = "<p>This is a simple HTA</p>" & _
      "<p>Exit button: </p>" & _
      "<input type=""button"" value=""Exit"" onClick=""sbExitHTA"" />"
End Sub
Sub sbExitHTA
      Self.Close()
End Sub
</script>
<body>
    <h1>Simple HTA</h1>
    <span id="dataarea"></span>
</body>
</html>

How to add a sleep function to an HTA

The WScript.Sleep function in VBScript is useful. However, when you decide to use HTA to provide an interface to your script, the sleep function become a problem as you can't use WScript functions in HTA.


For the WScript.Echo, you can use MsgBox, however, there is no equivalent for the WScript.Sleep.


You could do it using a loop, that's working but it's resource intensive. You could also create a VBScript file on the fly where you include the WScript.Sleep command and run it via the HTA but there is an easier, faster and quite efficient solution. 
So, here is an option that I used several times which did the trick for me.

In the following example, the sbWait sub is run a command line to do a ping to the localhost IP address for the number of times of your choice. Because each ping request is done every second, you just have to mention using the iSeconds variable the number of time you want to do it.

Enjoy.

<html>
<head>
<title>Simple HTA</title>
<HTA:APPLICATION
     ID="oSimpleHTA"
     APPLICATIONNAME="SimpleHTA"
>
</head>
<script Language="VBScript">
Sub Window_Onload
    dataarea.innerhtml = "<p>This is a simple HTA</p>"
    dataarea.innerhtml = "<p>Let's wait for 5 seconds</p>"
    sbWait(5)
    dataarea.innerhtml = "<p>Done.</p>"
End Sub
Sub sbWait(iSeconds)
    Dim oShell  : Set oShell = CreateObject("WScript.Shell")
    oShell.run "cmd /c ping localhost -n " & iSeconds,0,True
End Sub
</script>
<body>
    <h1>Simple HTA</h1>
    <span id="dataarea"></span>
</body>
</html>