Powershell script to pull data from AD and make a table

This topic contains 25 replies, has 5 voices, and was last updated by Profile photo of Jeff Scharfenberg Jeff Scharfenberg 1 month ago.

  • Author
    Posts
  • #56168
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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....

  • #56177
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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?

  • #56203
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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()}}
    
  • #56213
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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.

  • #56221

    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.

  • #56224
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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.

  • #56225
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

  • #56257
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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}
    }
    
    
  • #56258
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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					
    
  • #56260
    Profile photo of Don Jones
    Don Jones
    Keymaster

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

  • #56261
    Profile photo of Don Jones
    Don Jones
    Keymaster

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

  • #56264
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    
  • #56278
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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.

  • #56290
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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.

  • #56402
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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
    
    
  • #56404
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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!

  • #56530
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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.

  • #56585
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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.

  • #56587
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    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.

  • #56594
    Profile photo of Rob Simmers
    Rob Simmers
    Participant
    #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'
    
  • #56728
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    I think so far that is working for the initial part. The next part i need to work on is a full change of every single employee ID changed from existing to updated one (without leading zeros). I have something that works pretty well so far but the where part seems to not work

    ForEach ($employee in ($adplist | Where {$employee.Company -ne "Company A"})) {
    Write-host $employee.'First Name' $employee.'Last Name' $employee.Company
    

    That is just the start of it but the output shows me everyone on the list including everyone in Company A. I thought it should show me everyone excluding the people in Company A.

  • #56746
    Profile photo of Alexandru
    Alexandru
    Participant

    i think that's because you're not doing the operation in the where clause on the current piped in object , like so

    #example 1
    ForEach ($employee in ($adplist | Where {$_.Company -ne "Company A"})) {
    Write-host $employee.'First Name' $employee.'Last Name' $employee.Company
    #example 2
    ForEach ($employee in ($adplist | Where Company -ne "Company A")) {
    Write-host $employee.'First Name' $employee.'Last Name' $employee.Company
    #example 3
    ForEach ($employee in ($adplist | Where -Property Company -ne -Value "Company A")) {
    Write-host $employee.'First Name' $employee.'Last Name' $employee.Company
    
  • #56807
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    The first one spit out the same as the code I posted and the last 2 gave errors not knowing what Company is or what Property conditions were.

  • #56809
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    What did work was changing it to Where {$_.Company -Match "Company B"}...

    then it spit out only those in company B. It's strange how this code used to work but updates or something changed how powershell reacts with AD overall and needs to be changed.

  • #56855
    Profile photo of Alexandru
    Alexandru
    Participant

    strange , the second and third syntax should work just fine in powershell v3+ , notice that there is no scriptblock (braces { }) in the second and third examples , the braces are from the foreach
    with the -match operator you can do a lot more , check out help about_Regular_Expressions , also with the -like operator you can use wildcards

    PS C:\> Get-ADUser -Properties * -Filter * -SearchBase $base | select name,company
    
    name   company
    ----   -------
    User01 Company A
    User02 Company B
    User03 Company ABC
    User04 Company BCA
    User05 Company BC
    User06 Company AB
    User07 Company AC
    User08 Company C
    User09 Company B
    User10 Company A
    
    
    PS C:\> $us = Get-ADUser -Properties * -Filter * -SearchBase $base
    
    PS C:\> ForEach ($u in ($us | Where -Property Company -eq -Value "Company A" )) { Write-host $u.Name $u.Company }
    User01 Company A
    User10 Company A
    PS C:\> ForEach ($u in ($us | Where -Property Company -ne -Value "Company A" )) { Write-host $u.Name $u.Company }
    User02 Company B
    User03 Company ABC
    User04 Company BCA
    User05 Company BC
    User06 Company AB
    User07 Company AC
    User08 Company C
    User09 Company B
    PS C:\> ForEach ($u in ($us | Where Company -ne "Company B" )) { Write-host $u.Name $u.Company }
    User01 Company A
    User03 Company ABC
    User04 Company BCA
    User05 Company BC
    User06 Company AB
    User07 Company AC
    User08 Company C
    User10 Company A
    PS C:\> ForEach ($u in ($us | Where Company -eq "Company B" )) { Write-host $u.Name $u.Company }
    User02 Company B
    User09 Company B
    PS C:\>
  • #56918
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    So again I wanted to thank everyone for helping me through this PIA of a script for Oracle. I have a meeting with the developers this afternoon to show them my final code. I'm expecting them to be fairly pleased.

    Thank you all again!!! I learned a lot and sooooo much more to learn!! I'd say i'm a newbie + 1 or maybe 2 :-D.

You must be logged in to reply to this topic.