Screenshots :

Purpose :

This project uses the following features :

• build GUI with Powershell (form, tab, button,…)
• query the Active Directory or a SQL database

Pre-requesites :
This script needs some configuration :

• line 143 : modify the ldap path according to your infrastructure
• line 155 : modify the SQL connection string
• line 158 : modify the SQL select to query and retrieve data from your own SQL server

Scripts :

Import-Module ActiveDirectory -Cmdlet Get-aduser

# Load the Winforms assembly
[reflection.assembly]::LoadWithPartialName( "System.Windows.Forms")

# Create the form
$form = New-Object Windows.Forms.Form
$form_selectgroup = New-Object Windows.Forms.Form

# Create the tabcontrol
$tabcontrol = New-Object windows.Forms.TabControl
$tabpage_ad = New-Object windows.Forms.TabPage
$tabpage_sql = New-Object windows.Forms.TabPage
$tabcontrol.width = 1150
$tabcontrol.Height = 540

#Set TabPages
$tabpage_ad.Text = "Active Directory Users"
$tabpage_sql.Text = "SQL Database Query Tool"

#Set the dialog title
$form.text = "Active Directory and SQL Database Query tool"
$form.Width = 1150
$form.Height = 600
$form.FormBorderStyle = "FixedSingle"
$form.ControlBox = $false

# Create the label control and set text, size and location
$label_lastname_ad = New-Object Windows.Forms.Label
$label_lastname_ad.Location = New-Object Drawing.Point 10,50
$label_lastname_ad.Size = New-Object Drawing.Point 100,15
$label_lastname_ad.text = "Search lastname"

$label_lastname_sql = New-Object Windows.Forms.Label
$label_lastname_sql.Location = New-Object Drawing.Point 10,50
$label_lastname_sql.Size = New-Object Drawing.Point 100,15
$label_lastname_sql.text = "Search lastname"

# Create TextBox and set text, size and location
$textfield_lastname_ad = New-Object Windows.Forms.TextBox
$textfield_lastname_ad.Location = New-Object Drawing.Point 110,45
$textfield_lastname_ad.Size = New-Object Drawing.Point 160,15

$textfield_lastname_sql = New-Object Windows.Forms.TextBox
$textfield_lastname_sql.Location = New-Object Drawing.Point 110,45
$textfield_lastname_sql.Size = New-Object Drawing.Point 160,15

# create grids
$ad_grid = New-Object Windows.Forms.DataGridview
$ad_grid.DataBindings.DefaultDataSourceUpdateMode = 0
$ad_grid.Name = "grouplist"
$ad_grid.DataMember = ""
$ad_grid.TabIndex = 1
$ad_grid.Location = New-Object Drawing.Point 300,40
$ad_grid.Size = New-Object Drawing.Point 800,440
$ad_grid.readonly = $true
$ad_grid.AutoSizeColumnsMode = 'AllCells'
$ad_grid.SelectionMode = 'FullRowSelect'
$ad_grid.MultiSelect = $false
$ad_grid.RowHeadersVisible = $false
$ad_grid.allowusertoordercolumns = $true

$sql_grid = New-Object Windows.Forms.DataGridview
$sql_grid.DataBindings.DefaultDataSourceUpdateMode = 0
$sql_grid.Name = "ad_contact_grid"
$sql_grid.DataMember = ""
$sql_grid.TabIndex = 1
$sql_grid.Location = New-Object Drawing.Point 300,40
$sql_grid.Size = New-Object Drawing.Point 800,440
$sql_grid.readonly = $true
$sql_grid.AutoSizeColumnsMode = 'AllCells'
$sql_grid.SelectionMode = 'FullRowSelect'
$sql_grid.MultiSelect = $false
$sql_grid.RowHeadersVisible = $false
$sql_grid.allowusertoordercolumns = $true

# Create Button and set text and location
$button_exit = New-Object Windows.Forms.Button
$button_exit.text = "Exit"
$button_exit.Location = New-Object Drawing.Point 15,545

$button_searchad = New-Object Windows.Forms.Button
$button_searchad.Location = New-Object Drawing.Point 10,70
$button_searchad.Size = New-Object Drawing.Point 100,23
$button_searchad.text = "Query AD"

$button_searchsql = New-Object Windows.Forms.Button
$button_searchsql.Location = New-Object Drawing.Point 10,70
$button_searchsql.Size = New-Object Drawing.Point 100,23
$button_searchsql.text = "Query SQL"

# Set up event handler to extarct text from TextBox and display it on the Label.
$button_searchad.add_click({
$grid = $ad_grid
$lnquery = "*"+$textfield_lastname_ad.Text.ToString()+"*"
search_contact_ad($lnquery)
})
$button_searchsql.add_click({
$grid = $sql_grid
search_contact_sql($textfield_lastname_sql.Text.ToString())
})

$textfield_lastname_ad.add_KeyPress({
If ($_.KeyChar -eq 13) {
$grid = $ad_grid
$lnquery = "*"+$textfield_lastname_ad.Text.ToString()+"*"
search_contact_ad($lnquery)
}
})
$textfield_lastname_sql.add_KeyPress({
If ($_.KeyChar -eq 13) {
$grid = $sql_grid
search_contact_sql($textfield_lastname_sql.Text.ToString())
}
})

$button_exit.add_click({
$form.Close()
})

# Add the controls to the Form
$form.Controls.Add($tabcontrol)
$form.controls.add($button_exit)

$tabcontrol.tabpages.add($tabpage_ad)
$tabcontrol.tabpages.add($tabpage_sql)

$tabpage_ad.controls.add($label_lastname_ad)
$tabpage_ad.controls.add($textfield_lastname_ad)
$tabpage_ad.controls.add($button_searchad)
$tabpage_ad.controls.add($ad_grid)

$tabpage_sql.controls.add($label_lastname_sql)
$tabpage_sql.controls.add($textfield_lastname_sql)
$tabpage_sql.controls.add($button_searchsql)
$tabpage_sql.Controls.Add($sql_grid)

$form.add_Load($OnLoadForm_UpdateGrid)

function search_contact_ad([string]$lastname_str){
if ($lastname_str) {
$array_ad = New-Object System.Collections.ArrayList
$Script:procInfo = @(Get-ADUser -Filter {sn -like $lastname_str} -Properties sn,givenname,mail,displayname -SearchBase "dc=domain,dc=local" |sort-object -property sn |Select-Object givenname,sn,displayname,mail)
$array_ad.AddRange($procInfo)
$grid.DataSource = $array_ad
$form.refresh()
}
else {
[windows.forms.messagebox]::show('Please enter a lastname to search before clicking the button','Warning','OK',[Windows.Forms.MessageBoxIcon]::Warning)
}
}
function search_contact_sql([string]$lastname_str){
if ($lastname_str) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=DB_Server_Hostname;Database=master;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select Lastname,Firstname,Username,CountryCode,Department,ManagerName,EmailAddress from DatabaseName `
where upper(LastName) like '%"+($lastname_str).toupper()+"%' ORDER BY LastName"
$SqlCmd.Connection = $SqlConnection
$reader = $SqlCmd.ExecuteReader()

$myarray = @()
while ($Reader.Read()) {
$rows = New-Object System.Object
$rows | Add-Member -type NoteProperty -name Lastname -Value $Reader.GetValue(0)
$rows | Add-Member -type NoteProperty -name Firstname -Value $Reader.GetValue(1)
$rows | Add-Member -type NoteProperty -name Username -Value $Reader.GetValue(2)
$rows | Add-Member -type NoteProperty -name Office -Value $Reader.GetValue(3)
$rows | Add-Member -type NoteProperty -name Department -Value $Reader.GetValue(4)
$rows | Add-Member -type NoteProperty -name Manager -Value $Reader.GetValue(5)
$rows | Add-Member -type NoteProperty -name Mail -Value $Reader.GetValue(6)
$myarray += $rows
}

$array = New-Object System.Collections.ArrayList
$Script:procInfo = @($myarray | Select Firstname,Lastname,Username,Office,Department,Manager,Mail)
$array.AddRange($procInfo)
$grid.DataSource = $array

$form.refresh()
$SqlConnection.Close()
}
else {
[windows.forms.messagebox]::show('Please enter a username to copy before clicking the button','Warning','OK',[Windows.Forms.MessageBoxIcon]::Warning)
}
}

# Display the dialog
$form.ShowDialog()