Wednesday, February 14, 2018

Exporting variables to Export-CSV

Greetings!

I was asked to create a data feed (a text file) that contained the following information from Active Directory:
Employee Number
Employee Email Address
The last part of the employee's ObjectSID
Employee Login

Getting the information was relatively simple.  Since the information was in three different Organizational Units (OUs), I had to run 3 separate Get-ADUser statements, as follows:

$ouUsers = $()

$ouUsers += get-aduser -Filter { objectClass -eq "user"} -Properties * -SearchBase 'ou=ou1,dc=abc,dc=com' | Where-object {$_.Enabled -eq "True" -AND $_.employeeid -ne $null } | Select-Object employeeid,samAccountName,objectSID,EmailAddress  #| export-csv -NoTypeInformation rwsc_info.csv

$ouUsers += get-aduser -Filter { objectClass -eq "user"} -Properties * -SearchBase 'ou=ou2,dc=abc,dc=com' | Where-object {$_.Enabled -eq "True" -AND $_.employeeid -ne $null } | Select-Object employeeid,samAccountName,objectSID,EmailAddress  #| export-csv -NoTypeInformation rwsc_info.csv

$ouUsers += get-aduser -Filter { objectClass -eq "user"} -Properties * -SearchBase 'ou=ou3,dc=abc,dc=com' | Where-object {$_.Enabled -eq "True" -AND $_.employeeid -ne $null } | Select-Object employeeid,samAccountName,objectSID,EmailAddress  #| export-csv -NoTypeInformation rwsc_info.csv



Note: be sure to have the $ouUsers = $() statement or the variable will continue to grow since the  script uses += for each get-aduser statement.

Once I had all the information in the $ouUsers variable I create a FOREACH loop:
foreach($ouUser in $ouUsers)

It is within this loop I did two things:
1. Collect the last section of the ObjectSID
    Since the ObjectSID is separated into 8 parts (separated by a '-') I used the following code:
         $sid = $ouUser.objectSID # This puts the information into a separate variable
         $empSID = $sid.Value.Split('-',8)[7] # the '-' is the separator.  '8' tells how many array items.  # '[7]' is the last item in the array (array items start at [0])     

2. Export the information to the CSV file
    This was the section that took me the longest to figure out.  I though all I had to do was gather all
    the information into another variable and then export the data to a file (i.e., $data | Export-CSV
    c:\documents\datafile.csv).  This did not work.  All I got was the length of each variable.

   I did some research and found the following solution:

       [pscustomobject]@{

        EMPLOYEEID = $ouUser.employeeid

        EMAILADDRESS = $ouUser.EmailAddress

        SINGLESIGNON = $empSID

        NETWORKLOGON = $ouUser.samAccountName

       } | export-csv -append -NoTypeInformation datafile.csv


The full FOREACH loop is as follows:
      foreach($ouUser in $ouUsers) {

        $sid = $ouUser.objectSID

        $empSID = $sid.Value.split('-',8)[7]


  
       [pscustomobject]@{

          EMPLOYEEID = $ouUser.employeeid

          EMAILADDRESS = $ouUser.EmailAddress

          SINGLESIGNON = $empSID

          NETWORKLOGON = $ouUser.samAccountName

        } | export-csv -append -NoTypeInformation datafile.csv

      }  

As I said, getting the data to a CSV file took me a few hours to figure out.  I was able to find the solution on the StackOverflow web site (http://stackoverflow.com).

Let me know if you have any questions regarding this script.

See you soon,
Mike
 

No comments:

Post a Comment