Powershell script to pull data from AD and make a table

We are working on sharing data with Oracle from our AD to keep our records up to date, etc. I am having the hardest time creating a script to automate the pull of certain data from active directory and include it in a csv, which then needs to be turned into a pipe delimeted form. The pipe delimeted part i think I can handle, but getting there is just not working.

What they require is the following. A table that has 6 headers such as:

METADATA|Worker|FLEX:PER_PERSONS_DFF|enterpriseId(PER_PERSONS_DFF=Global Data Elements)|EffectiveStartDate|PersonNumber

The first 3 columns will need to have nothing but the headers METADATA|Worker|FLEX:PER_PERSONS_DFF and under them have
the words MERGE | Worker | Global Data Elements.

The next fields will be pulled from Active Directory and be username | startdate | employeeID.

So the final will look somewhat like this…
METADATA|Worker|FLEX:PER_PERSONS_DFF|enterpriseId(PER_PERSONS_DFF=Global Data Elements)|EffectiveStartDate|PersonNumber
MERGE |Worker|Global Data Elements| employeeA username |10/27/2016 |100000000
MERGE |Worker|Global Data Elements| employeeB username |10/27/2016 |100000001
MERGE |Worker|Global Data Elements| employeec username |10/27/2016 |100000002

so the first 3 columns will always be the same and columns D, E, F will be pulled from AD.

I have something that will show me what I want to pull from AD when I run it that is in part like this… and the varibles for ADServer and Searchbase are defined.

$AllADUsers = Get-ADUser -server $ADServer -searchbase $SearchBase -filter * -Properties * | select sAMAccountName, EmployeeNumber, EmployeeID
$Co

$AllADUsers |
Select-Object
@{Label = “METADATA”;Expression = {“MERGE”}},
@{Label = “Worker”;Expression = {“Worker”}},
@{Label = “FLEX:PER_PERSONS_DFF”;Expression = {“Global Data Elements”}},
@{Label = “enterpriseId(PER_PERSONS_DFF=Global Data Elements)”;Expression = {$.sAMAccountName}},
@{Label = “EffectiveStartDate”;Expression = {$
.EmployeeNumber}},
@{Label = “PersonNumber”;Expression = {$_.EmployeeI

The output is great but when I | export-Csv it looks like this…

IsReadOnly IsFixedSize IsSynchronized Keys Values SyncRoot Count
FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 2
FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 2
FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 2
FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 2
FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 2
FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 2

So the export doesn’t look anything like what powershell shows.

If anyone could assist me i’d love it! I’m still fairly new…

So, you lost some code, there. Please see the instructions above the posting textbox for how to format code so that we can read it ;). I’m a little worried in this case that the problem is in the chopped-off code. Can you try again?

As Don stated, a lot of code is cutoff. From experience, my guess is that you don’t have a comma after one of your calculated expressions, like so:

Get-ADUser jsmith | Select Name, 
                           @{Name="Value1";Expression={$_.Name.ToUpper()}} #Should be a comma here
                           @{Name="Value2";Expression={$_.Name.ToLower()}}

you’re right…sorry. Early day and all…

$AllADUsers =  Get-ADUser -server $ADServer -searchbase $SearchBase -filter * -Properties * | select sAMAccountName, EmployeeNumber, EmployeeID
$Co

$AllADUsers |
Select-Object 
@{Label = "METADATA";Expression = {"MERGE"}},
@{Label = "Worker";Expression = {"Worker"}},
@{Label = "FLEX:PER_PERSONS_DFF";Expression = {"Global Data Elements"}},
@{Label = "enterpriseId(PER_PERSONS_DFF=Global Data Elements)";Expression = {$_.sAMAccountName}},
@{Label = "EffectiveStartDate";Expression = {$_.EmployeeNumber}},
@{Label = "PersonNumber";Expression = {$_.EmployeeID}} `

#Export CSV report

 | Export-Csv -Path C:\Users\jscharfenberg\Documents\scripts\Oracle\oracle.csv -NoTypeInformation -encoding "UTF8" -Append

I hope this comes up better.

Obviously, when you export the data to a CSV file where there are columns which may have object (with some properties), it will not show you the resultant data or property there, it will show you the Object Type in that column. As in CSV file, one column can have one single value only. If you want to have an object in a column then use Export-CliXML to export the XML and maintain the hierarchy.

SavindraSingh,

I need the table to come out as a csv a specific manor so it can be sent to Oracle. An XML changes the entire format. In the end this process will be automated and ran daily.

Well, it’s a valid point, though - you need to construct your data so that it’s a completely flat file, not a hierarchy. CSV can’t represent hierarchical data, full-stop. However, what I’m seeing…

Select-Object 
@{Label = "METADATA";Expression = {"MERGE"}},
@{Label = "Worker";Expression = {"Worker"}},
@{Label = "FLEX:PER_PERSONS_DFF";Expression = {"Global Data Elements"}},
@{Label = "enterpriseId(PER_PERSONS_DFF=Global Data Elements)";Expression = {$_.sAMAccountName}},
@{Label = "EffectiveStartDate";Expression = {$_.EmployeeNumber}},
@{Label = "PersonNumber";Expression = {$_.EmployeeID}} `

Looks like flat data to me, provided EmployeeNumber and EmployeeID aren’t collections.

Honestly, I think all you’re doing “wrong” is confusing Export-CSV. It’s seeing a hash table, and reacting accordingly. I’d suggest creating a new object manually.

$AllADUsers |
ForEach {
 New-Object -Type PSObject -Prop @{'EffectiveStartDate'=$_.EmployeeNumber ; 'PersonNumber'=$_.EmployeeID}
}

Obviously with all of your needed columns. See if that doesn’t help.

The problem i’m still having is the fact that the first 3 columns aren’t pulled from anywhere as they are statically set to be MERGE, Worker, and Global Data Elements. Would it be easier to make a file with those so it can pull those fields to fill or can I have it so those first 3 columns always are just set to be filled in as those objects?

So far from what you gave me i have this…

$AllADUsers |
ForEach {
 New-Object -Type PSObject -Prop @{'METADATA'=MERGE ; 'Worker'=Worker ; 'FLEX:PER_PERSONS_DFF'=Global Data Elements ; 
									'enterpriseId(PER_PERSONS_DFF=Global Data Elements'=$_sAMAcountName ; 'EffectiveStartDate'=$_.EmployeeNumber ; 'PersonNumber'=$_.EmployeeID}
}

Putting quotes around MERGE, Worker, and Global Data Elements helped it run and it looked ok in the powershell screen so this is definitely the right direction. But the output is all messed up as a csv, and i think it needs to be a csv as the commas need to be turned to pipes afterward. there is the output as exported to csv…

ClassId2e4f51ef21dd47e99d3c952918aff9cd	pageHeaderEntry	pageFooterEntry	autosizeInfo	shapeInfo	groupingEntry
033ecb2bc07a4d43b5ef94ed5a35d280				Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo	
9e210fe47d09416682b841769c78b8a3					
27c87ef9bbda4f709f6b4002fa4af63c					
4ec4f0187cb04f4cb6973460dfe252df					
cf522b78d86c486691226b40aa69e95c					
033ecb2bc07a4d43b5ef94ed5a35d280				Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo	
9e210fe47d09416682b841769c78b8a3					
27c87ef9bbda4f709f6b4002fa4af63c					
4ec4f0187cb04f4cb6973460dfe252df					
cf522b78d86c486691226b40aa69e95c					
033ecb2bc07a4d43b5ef94ed5a35d280				Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo	
9e210fe47d09416682b841769c78b8a3					
27c87ef9bbda4f709f6b4002fa4af63c					
4ec4f0187cb04f4cb6973460dfe252df					

What you’re seeing there is the output of a Format cmdlet. You can’t format something and then export it.

And, incidentally, Export-CSV lets you specify a -Delimiter other than commas.

Try this:

$AllADUsers =  Get-ADUser -server $ADServer -searchbase $SearchBase -filter * -Properties * | 
               Select @{Label = "enterpriseId(PER_PERSONS_DFF=Global Data Elements)";Expression = {$_.sAMAccountName}},
                      @{Label = "EffectiveStartDate";Expression = {$_.EmployeeNumber}},
                      @{Label = "PersonNumber";Expression = {$_.EmployeeID}} `

$AllADUsers | Add-Member -MemberType NoteProperty -Name "METADATA" -Value "MERGE"
$AllADUsers | Add-Member -MemberType NoteProperty -Name "Worker" -Value "Worker"
$AllADUsers | Add-Member -MemberType NoteProperty -Name "FLEX:PER_PERSONS_DFF" -Value "Global Data Elements"

$AllADUsers

Rob, that output in powershell worked like a charm once I added | FT at the end.

Now the export is the final piece of the puzzle it would seem.

I think i’m 99% complete!! The final task is the export. I have it sorted in the order and exporting how i need.

I am finding that the csv file, i renamed to a .dat file as Oracle needs, has quotes all over it! I found that i can use convertto-csv to remove the quotes, but then I use out-file and this line #TYPE System.Management.Automation.PSCustomObject is added to the top.

This is the final piece of the puzzle. Thanks for all your help!! It’s been great! When this is done i’ll write the code so anyone who is going to use Oracle to exchange data with you can use it and not bang your head on the wall for a couple days.

Objective complete! Thanks for all your help on this one. It is now Oracle approved…

Here is the final code. I learned a lot from this little exercise…

#import the ActiveDirectory Module
Import-Module ActiveDirectory

#Sets the OU to do the base search for all user accounts, change as required.
$SearchBase = "OU=OrganizatioanlUnit,DC=company,DC=company,DC=com"


#Define variable for a server with AD web services installed
$ADServer = 'ADserver'

#Sets columns required for Oracle Export to Variable $ALLADUsers
$AllADUsers =  Get-ADUser -server $ADServer -searchbase $SearchBase -filter * -Properties * | 
	Select @{Label = "enterpriseId(PER_PERSONS_DFF=Global Data Elements)";Expression = {$_.sAMAccountName}}, 
		   @{Label = "EffectiveStartDate";Expression = {$_.EmployeeNumber}}, 
		   @{Label = "PersonNumber";Expression = {$_.EmployeeID}}
					  
#Add Field METADATA with the value of MERGE to the Variable $ALLADUsers					  
$AllADUsers | Add-Member -MemberType NoteProperty -Name "METADATA" -Value "MERGE"

#Add Field Worker with the value of Worker to the Variable $ALLADUsers	
$AllADUsers | Add-Member -MemberType NoteProperty -Name "Worker" -Value "Worker"

#Add Field FLEX:PER_PERSONS_DFF with the value of Global Data Elements to the Variable $ALLADUsers	
$AllADUsers | Add-Member -MemberType NoteProperty -Name "FLEX:PER_PERSONS_DFF" -Value "Global Data Elements"

#Put the fields needed in order to prepare for exporting to csv
$AllADUsers | Select-Object METADATA,Worker,'FLEX:PER_PERSONS_DFF','enterpriseId(PER_PERSONS_DFF=Global Data Elements)','EffectiveStartDate','PersonNumber' | `

#Export variable to csv while changing the delimiter from , to |
Export-CSV -delimiter "|" C:\temp\oracle1.csv -NotypeInformation

#Import CSV to remove quotes and export as DAT file
Import-csv -path C:\temp\oracle1.csv | convertto-csv | % { $_ -replace '"', ""} | Out-File c:\temp\oracle.dat

#import DAT file and remove top line #TYPE System.Management.Automation.PSCustomObject
(Cat C:\temp\oracle.dat) | % { $_ -replace '#TYPE System.Management.Automation.PSCustomObject', ""} > C:\temp\oracle.dat

It’d be nice, if you’re interested (email webmaster@, if you are), to have you write up your experience in a blog article here. Not just the end code, which is nice, but also the barriers and “ah ha!” moments you had along the way. Understanding the process can be really helpful to others!

Well it would appear that Oracle decided NOT to give me the requirements until just now for how the values need to be layed out so i’m back at this once again…Thanks Oracle for that one!!

It would appear that I need to change how the format for the EffectiveStartDate comes out. As of now these dates are going to be input into Active Directory as MM/DD/YYYY. The attribute that is going to store this is still in EmployeeNumber, but I need to then have that number changed into a date when exporting, and completely changed to YYYY/MM/DD before it can be sent out.

Do you guys have any suggestions on what the best process would be to pull in the current values, declare them as [DateTime] as MM/DD/YYYY then be able to change the format to YYYY/MM/DD before exporting it to the csv.

The other piece of the puzzle they just told me is that I need to trim any EmployeeID’s of 0’s.

This is a calculated expression:

@{Label = "EffectiveStartDate";Expression = {$_.EmployeeNumber}},

While it’s being used to simply rename the property, the expression can actually execute code. Let’s look at formatting dates:

#Standard Date Format: https://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.110).aspx
PS C:\Users\Rob> Get-Date -Format d
11/1/2016

#Custom Date Format:  https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx
PS C:\Users\Rob> Get-Date -Format "yyyy/dd/MM"
2016/01/11

#You can also specify a date
PS C:\Users\Rob> Get-Date "12/25/2016" -Format "yyyy/dd/MM"
2016/25/12

#However, the date you provide has to be parsable as a standard
#date.  You haven't showed, what is in the EmployeeNumber field,
#so understand if you don't get a value this could be happening
PS C:\Users\Rob> Get-Date "NOT_A_DATE" -Format "yyyy/dd/MM"
Get-Date : Cannot bind parameter 'Date'. Cannot convert value "NOT_A_DATE" to type "System.DateTime". Error: "The string was not recognized as a valid DateTime. There 
is an unknown word starting at index 0."
At line:1 char:10
+ Get-Date "NOT_A_DATE" -Format "yyyy/dd/MM"
+          ~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Get-Date], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.GetDateCommand

This allow you do something like:

@{Label = "EffectiveStartDate";Expression = {Get-Date ($_.EmployeeNumber) -Format "yyyy/dd/MM"}},

Next let’s play with numbers. By default, when you’re pulling data out of most sources, they will be strings. It’s actually more of a pain to pad with zeros than to remove them, because what you’ll find is that a number cannot start with zeros. So, all we need to do is cast the EmployeeID to a integer:

PS C:\Users\Rob> $empNumber = "000043424"

PS C:\Users\Rob> $empNumber
000043424

PS C:\Users\Rob> $empNumber.GetType()

IsPublic IsSerial Name                                     BaseType                                                                                                      
-------- -------- ----                                     --------                                                                                                      
True     True     String                                   System.Object                                                                                                 

PS C:\Users\Rob> [int]$empNumber
43424

Now you just need to update your calculated expressions with the information you have from above.

Sweet Rob! I’ll give that a test later on today. I came up with something pretty close to that, but since i’m a tad new to PS I get confused by where you can and cannot use {} () “” ‘’ and stuff.

#Expressions and remoting use ScriptBlocks
PS C:\Users\Rob> $scriptBlock = {}
#Object properties, Splatting or a standard hash table 
PS C:\Users\Rob> $hashTable = @{}

PS C:\Users\Rob> $array = @()

Parenthesis are also used for the order of operations. Please Excuse My Dear Aunt Sally (Parenthesis, Exponent, Multiplication…). You’ll see it many times with Get-Date, because Get-Date has to execute first to get a date before something. You can also see it when you want to call a property or method, like (Get-Date).AddHours(10). Take a look at this: https://technet.microsoft.com/en-us/library/hh847732.aspx

As far as single or double or qoutes. Double qoutes will resolve variables, single qoute are literal. Test this:

$test = "Blue"

"Little boy $test"
'Little boy $test'