OK so I managed to write a script that works and does what I need See Below:
But the issue is that now my Script goes out and gets all the info and sets the account for approval but I cant seem to set the Full name or the user logon name (pre-Windows 2000) with the script it seems to use the values defined by the predefined fields I need the power shell script to set that info.
I made a Business role that runs this script first to get the info then it submits it for approval. How do I get the script to set those 2 properties?
Import-Module Adaxes
function Invoke-Sqlcmd2
{
[CmdletBinding(
DefaultParameterSetName='Query'
)]
param(
[Parameter( Position=0, Mandatory=$true)]
[string]$ServerInstance,
[Parameter( Position=1, Mandatory=$false)]
[string]$Database,
[Parameter( Position=2,
Mandatory=$true,
ParameterSetName="Query",
ValueFromPipeline = $true)]
[string]$Query,
[Parameter( Position=2,
Mandatory=$true,
ParameterSetName="File")]
[ValidateScript({test-path $_})]
[string]$InputFile,
[Parameter(Position=3, Mandatory=$false)]
[string]$Username,
[Parameter(Position=4, Mandatory=$false)]
[string]$Password,
[Parameter(Position=5, Mandatory=$false)]
[Int32]$QueryTimeout=600,
[Parameter(Position=6, Mandatory=$false)]
[Int32]$ConnectionTimeout=15,
[Parameter(Position=7, Mandatory=$false)]
[ValidateSet("DataSet", "DataTable", "DataRow","SingleValue")]
[string]$As="DataRow",
[Parameter(Position=8, Mandatory=$false)]
[System.Collections.IDictionary]$SqlParameters,
[switch]$DBNullToNull
)
if ($InputFile)
{
$filePath = $(Resolve-Path $InputFile).path
$Query = [System.IO.File]::ReadAllText("$filePath")
}
$conn = New-Object System.Data.SqlClient.SQLConnection
if ($Username)
{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
else
{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
$conn.ConnectionString = $ConnectionString
#Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
if ($PSBoundParameters.Verbose)
{
$conn.FireInfoMessageEventOnUserErrors=$true
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Write-Verbose "$($_)" }
$conn.add_InfoMessage($handler)
}
$conn.Open()
$cmd = New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
if ($SqlParameters -ne $null)
{
$SqlParameters.GetEnumerator() |
ForEach-Object {
If ($_.Value -ne $null)
{ $cmd.Parameters.AddWithValue($_.Key, $_.Value) }
Else
{ $cmd.Parameters.AddWithValue($_.Key, [DBNull]::Value) }
} > $null
}
$ds = New-Object system.Data.DataSet
$da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
#This code scrubs DBNulls
$cSharp = @'
using System;
using System.Data;
using System.Management.Automation;
public class DBNullScrubber
{
public static PSObject DataRowToPSObject(DataRow row)
{
PSObject psObject = new PSObject();
if (row != null && (row.RowState & DataRowState.Detached) != DataRowState.Detached)
{
foreach (DataColumn column in row.Table.Columns)
{
Object value = null;
if (!row.IsNull(column))
{
value = row[column];
}
psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value));
}
}
return psObject;
}
}
'@
switch ($As)
{
'DataSet'
{
$ds
}
'DataTable'
{
$ds.Tables
}
'DataRow'
{
if(-not $DBNullToNull)
{
$ds.Tables[0]
}
else
{
#Scrub DBNulls if specified.
#Provides convenient results you can use comparisons with
#Introduces overhead (e.g. ~2000 rows w/ ~80 columns went from .15 Seconds to .65 Seconds - depending on your data could be much more!)
Add-Type -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data','System.Xml'
foreach ($row in $ds.Tables[0].Rows)
{
[DBNullScrubber]::DataRowToPSObject($row)
}
}
}
'SingleValue'
{
$ds.Tables[0] | Select-Object -Expand $ds.Tables[0].Columns[0].ColumnName
}
}
}
#Get Employee Data
$Global:empData = Invoke-Sqlcmd2 -ServerInstance "localhost" -Database "ERS" -Query "select * FROM tAssociateExport Where associateID=%employeeid%"
#Create Password
$Global:pass = $empdata["AssociateID"]+$empdata["FirstName"].substring(0,1).toupper()+$empdata["LastName"].substring(0,4).tolower()+"!"
$Global:securePass = convertto-securestring -asplaintext -force -string $pass
$Global:deptName = $empdata["DepartmentDescription"]
$Global:DivName = $empdata["DivisionDescription"]
$Global:title = $empdata["JobCodeDescription"]
$Global:lastName = (Get-Culture).TextInfo.ToTitleCase($empdata["LastName"].tostring().tolower())
$Global:firstName = (Get-Culture).TextInfo.ToTitleCase($empdata["FirstName"].tostring().tolower())
$Global:Parkabr = $empdata["SiteCode"]
$Global:description = "$ParkAbr - $division - $deptName - $title"
#Deal with a first name containing a space
if ($empdata["FirstName"] -like "* *")
{
$FirstName = $empData["FirstName"].split(" ")
$FirstName = $FirstName[0]+$FirstName[1]
}
else {
$FirstName = $empdata["FirstName"]
}
# Set the users login name. First.Last format
if ($empData["LastName"] -like "* *")
{
$LastName = $empData["LastName"].split(" ")
$Lastname = $Lastname[0]+$Lastname[1]
}
else {
$LastName = $empdata["LastName"]
}
$Global:sam = $FirstName.tolower()+ "." +$LastName.tolower()
#Set the Propertyies of Adaxes
$Context.SetModifiedPropertyValue("firstname", $FirstName)
$Context.SetModifiedPropertyValue("lastname", $LastName)
$Context.SetModifiedPropertyValue("samaccountname", $sam)
$Context.SetModifiedPropertyValue("givenName", $FirstName)
$Context.SetModifiedPropertyValue("sn", $lastName)
$Context.SetModifiedPropertyValue("displayName", $FirstName+","+$lastName)
$Context.SetModifiedPropertyValue("description", $description)
$Context.SetModifiedPropertyValue("department", $deptName)
$Context.SetModifiedPropertyValue("company", $DivName)
$Context.SetModifiedPropertyValue("Title", $title)
$Context.SetModifiedPropertyValue("fullname", $FirstName+" "+$lastName)