simple error checking between AD and CSV

This topic contains 20 replies, has 5 voices, and was last updated by Profile photo of Geir Endre Jenssen Geir Endre Jenssen 6 months, 4 weeks ago.

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

    I am writing a very simple error reporting script to check users on a CSV against the contacts in Active Directory.

    I think it's close but it's still coming back as seeing everyone is there, when in fact some are and some aren't. I'm just trying to get a simple report of the Display Name and if they're found or not in a txt file.

    param($CSVInput='C:\Temp\file.csv',$Logfile='C:\Temp\file.txt')
    
    $CSVUSERS = Import-csv $CSVInput | select -Expand 'Display Name'
    
    Import-Module ActiveDirectory
    
    "Display Name, Search Result" | Add-Content $Logfile
    
    foreach ($User in $CSVUSERS)
    
    {
    
    $Search = Get-ADObject -LDAPFilter "objectClass=Contact" | Select -Expand Name
    
    IF ($search)
    
    {
    
    "$User, Found" | Add-Content $Logfile
    }
    
    Else
    
    {
    
    "$User, Not Found" | Add-Content $Logfile
    
    }
    
    }
    
  • #60490
    Profile photo of Ron
    Ron
    Participant

    Your LDapFilter is wrong. It likely needs to be something like this.

    "(&(objectClass=Contact)(displayName=$User))"

    Also remove "| Select -Expand Name", your are forcing object creation when null is returned.

  • #60493
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    It still is showing everyone on the CSV as found. 🙁

  • #60499
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    In line 13 you search for every single user you have in your csv the AD for all contacts. Again and again and again. But you dont even use that. Your line 15 checks if the variable $search exists and contains something.

    You import the data from your csv and extract only the DisplayName. In your AD search you exctract the Name. Does these object properties match actually?

  • #60502
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    ... if they match something like this could work (I'm just not able to test)

    param($CSVInput='C:\Temp\file.csv',$Logfile='C:\Temp\file.txt')
    $CSVUSERS = Import-csv $CSVInput | select -Expand 'Display Name'
    Import-Module ActiveDirectory
    "Display Name, Search Result" | Add-Content $Logfile
    $Search = Get-ADObject -LDAPFilter "objectClass=Contact" | Select -Expand Name
    
    foreach ($User in $CSVUSERS){
        IF ($search -contains $User){
            "$User, Found" | Add-Content $Logfile
        }
        Else{
            "$User, Not Found" | Add-Content $Logfile
        }
    }
    
  • #60652
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    Olaf,

    I think that's on the right track. More tinkering needed as now it shows everyone as not found. So kind of the same, but in the right direction. Maybe I should use email as the search identifier over display name.

  • #60657
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    I've changed it a bit...

    param($CSVInput='C:\Temp\file.csv',$Logfile='C:\Temp\file.txt')
    
    $ContactsOU = "OU=company,OU=Contacts,DC=US,DC=Company,DC=com"
    $CSVUSER = Import-csv $CSVInput | select -Expand 'Email Address'
    $Search = Get-ADObject -LDAPFilter "objectClass=Contact" -Properties Mail -Searchbase $ContactsOU | select -Expand Mail
    Import-Module ActiveDirectory
    
    "Email Address, Search Result" | Add-Content $Logfile
    
    $CSVUSER |foreach
    
    {
    
    
    IF ($_ -contains $Search)# if not empty or null
    
    {
    
    "$User, Found" | Add-Content $Logfile
    }
    
    Else
    
    {
    
    "$User, Not Found" | Add-Content $Logfile
    
    }
    
    }
    
    

    So I have it declare the variables before the loop. When I look at the variables in powershell they show only the email address. Now I just want to compare the 2 and tell me which match and are found and which dont. I also need it to NOT be caps sensitive.
    It's the foreach part that i'm not sure about. How can I say to just see if everything in the CSV that's declared matches the 1 filed in AD (Mail). The CSV also doesn't show any header when I look at $CSVUSER

  • #60660
    Profile photo of Fredrik Kacsmarck
    Fredrik Kacsmarck
    Participant

    I think it would help if you describe what you want to do in pseudocode.

    E.g.
    1.Import AD module
    2.Load data from csv
    3.Get data from AD
    4.Go through each line in CSV and
    .
    .
    and so forth.

    At least I find that it's sometimes easier to spot the problem that way rather than in plain code.
    E.g. in the code above it doesn't make sense to load the AD module after you're trying to use the Get-ADObject statement.

    Reason for not showing the header in CSVUSERS is because you use -Expand/-Expandproperty in that statement.
    So you'll get one entry per row without any header.

    The if statements seems to be reversed (to me at least) since you are iterating through the CSVUSERS list.
    E.g. if you want to check that X is in the Search variable it should be.

    if($search -contains $_)
    

    Since $_ will be different through each loop and $search is the list you check against.
    Otherwise you will check if $_ contains whatever is included in the $search variable which I would assume would be quite different from any line from $CSVUSER

  • #60667
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    You make a good point... Here is my end goal.

    1) Import AD Module – (I do this so much I might as well put the active directory module in $PSModulePath at this point)
    2) Import CSV (which includes Display Name, Email Address, First Name, Last Name, Phone Number)
    1a) Have the import only select the Email Address.
    3) Get Data from a specific OU in Contacts, which is why i Used Get-ADObject, and add Mail to the properties ( this is correct?)
    4) Go through each line in the CSV and compare against the Active Directory Contacts OU.
    5) At the end i'd like for it to give a % of good and % of bad
    6) Email the report to myself

    I'm starting to wonder if it's best to leave the import with all the headers in the CSV and the search in AD with all the attributes. Then in the Foreach and If statesments drill down to the Email field only.

    Fredrik, when I changed the position of $Search and $_ in the if I get the following....

    cmdlet ForEach-Object at command pipeline position 1
    Supply values for the following parameter:
    Process[0]:

  • #60673
    Profile photo of Ron
    Ron
    Participant

    IF ($Search -contains $_)

    That should fix the current version. Your original approach will work too, you just needed to fix the LDAPFilter.

    There are (at least) 2 approaches. Load all of the contacts into an array that you search, which is your second version, and the original version where you look up each one. They will both work, the second may be more efficient, but it depends on the volumes of contacts vs the number of csv records you are processing. You really need to run your script with ISE and put in break points. Then you can step through the script and examine variables and program flow to see where it is failing.

  • #60676
    Profile photo of Fredrik Kacsmarck
    Fredrik Kacsmarck
    Participant

    Jeff,

    The reason you get the "error" is because you do the following in the code

    $CSVUSER | foreach
    .
    .
    .
    

    Not sure if it's by design but when you do a | foreach expects you to continue the code on that line.
    So the Process[0] is really asking for the first thing to process.
    You could use "backtick" ` to "escape" the return and line feed but I've found that it's a hit and miss if it works or not.
    So I would suggest you do the following.

    foreach($c in $CSVUSER)
      {
      if($search -contains $c)
        {
        "$c, Found" | Add-Content $LogFile
        }
      else
        {
        "$c, Not Found" | Add-Content $LogFile
        }
       }
    

    Constructing a foreach this way you could add as many line breaks and what not between the curly braces {} of that foreach.

  • #60679
    Profile photo of Fredrik Kacsmarck
    Fredrik Kacsmarck
    Participant

    In regard to the question about having all the fields, it really depends on how you want to get the information.
    Without the header you don't need to specify which header/column to use.
    E.g.
    You would then need to do (using my example above):

    if($search -contains $c.EmailAddress)
    

    If by looking at the earlier examples you expand the column 'Email Address' which is not really nice when doing the above.
    You would then have to do.

    if($search -contains $c.'Email Address')
    

    It should work but it looks messy and it sure can trip you up later on if you need to enclose it in other circumstances.
    Also you would need to adjust the the lines where you take the string and add-content to the logfile.
    You would need to change those to:

    "$($c.'Email Address'), Found" | Add-Content $Logfile
    

    So bottom line is if the input have a space in the columns I would prefer to expand the data and just have the e-mail addresses.

  • #60684
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant
    param($CSVInput='C:\Temp\file.csv',$Logfile='C:\Temp\file.txt')
    $CSVUSERS = Import-csv $CSVInput
    Import-Module ActiveDirectory
    "Display Name, Search Result" | Add-Content $Logfile
    $ContactsOU = "OU=company,OU=Contacts,DC=US,DC=Company,DC=com"
    $Search = Get-ADObject -LDAPFilter "objectClass=Contact" -Properties mail -Searchbase $ContactsOU
    
    foreach ($User in $CSVUSERS){
        IF ($search.mail -contains $User.'Email Address'){
            "$User, Found" | Add-Content $Logfile
        }
        Else{
            "$User, Not Found" | Add-Content $Logfile
        }
    }

    You mentioned before:

    2) Import CSV (which includes Display Name, Email Address, First Name, Last Name, Phone Number)

    If the 'field' in your csv is really named 'Email Address' (including a white space) the code should work. Otherwise you will have to correct the header name to match your conditions.
    And BTW: If you have PS version higher than 2 you don't have to explicitly import your AD module. PS will do that for you automaticly.

  • #60705
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    Olaf,

    Yes the header is Email Address with a space. Tad backstory may help... We are an umbrella company with 7-10 entities. I recently have enforced that all entities send us their GAL every quarter to update our GAL. I'm trying to overall standardize this so it's very easy for everyone...with me as the test dummy.

    I saw that the active directory module was already in the PSModulePath so i did re-add the import-module part.

    fingers crossed...

    AS I test, how can I make it so the log.txt file is always overwritten. Append doesn't work so well and makes it hard to look at.

    —After the test as it was above, it still doesn't work and gives too much info. I want it cut down so its easier to read. email address and name would be best.

  • #60706
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    There must be something small i'm missing....

    For testing I went through manually and looked at the variables before the ForEach statement.

    Here is what I have...a bit more clean...i think

    #Set parameters for CSV file and Log out file
    param($CSVInput='C:\Temp\test.csv',$Logfile='C:\Temp\log.txt')
    
    #Import CSV file and set for variable
    $CSVUSER = Import-csv $CSVInput
    
    #Set Varible for Email Addresses in CSV
    $User = $CSVUSER | select -Expand 'Email Address'
    
    #Import Active Directory Module
    Import-Module activedirectory
    
    #Set variable for Active Directory Contacts OU
    $ContactsOU = "OU=CompanyA,OU=Contacts,DC=US,DC=CompanyB,DC=com"
    
    #Set Active Directory Object Class  as Variable and Include Mail Properties with SearchBase
    $Search = Get-ADObject -LDAPFilter "objectClass=Contact" -Properties Mail -Searchbase $ContactsOU | select -Expand Mail
    
    #Add Headers in Logfile
    "Email Address, Search Result" | Add-Content $Logfile
    
    #Begin Loop of CSV check
    ForEach($User in $Search)
    {
    
    #If email address in contact contains email address in CSV User
    IF ($Search -eq $User)
    
    {
    
    "$USER, Found" | Add-Content $Logfile
    }
    
    Else
    
    {
    
    "$USER, Not Found" | Add-Content $Logfile
    
    }
    
    }
    

    When I go in and look at $User it shows email addresses from the CSV in order = Good
    I look at $Search I see email addresses from the Contacts OU. = Good.

    Then I run the script and it comes up as everyone found = BAD. Is it possible that I have something wrong where it's not even getting the chance to get to the Not Found section, so just marks all as Found?

  • #60709
    Profile photo of Ron
    Ron
    Participant
    "Email Address, Search Result" | Set-Content $Logfile

    Overwrite the log file to start fresh.

    IF ($Search -contains $User)

    $Search is an array of all the contacts.

  • #60711
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant
    IF ($Search -eq $User)

    you have your complete list of email addresses from your contacts OU in $Search and you compare it to one single email address and that comes with a positive match? Really?

    If you want to check if a single item is in a list (an array) of items you should use -contains. Did you try the code I posted?

  • #60714
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    Thanks Ron,

    Olaf, I did but it came up the same. My C# guy here took a quick look and said a double nested ForEach may be best as it's searching in 2 containers. Then decare the variables for success and failure. Then use an If statement to add the findings to the log output.

    Hmmmmm...that sounds interesting.. Thoughts?

  • #60717
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    hmmmm ... for me the most sofisticated way would be to use Compare-Object. It's made for comparing objects! 😉

    param($CSVInput='C:\Temp\file.csv',$Logfile='C:\Temp\file.txt')
    $CSVUSERS = Import-Csv -Path $CSVInput | Select-Object -Property *,@{Name='mail';Expression={$_.'Email Address'}}
    $ContactsOU = "OU=company,OU=Contacts,DC=US,DC=Company,DC=com"
    $Search = Get-ADObject -LDAPFilter "objectClass=Contact" -Properties mail -Searchbase $ContactsOU
    
    Compare-Object -ReferenceObject $CSVUSERS -DifferenceObject $Search -Property 'mail' -PassThru -IncludeEqual -OutVariable Output
    
    $Output | Export-Csv -Path 'C:\Temp\output.csv' -NoTypeInformation
    
  • #60967
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    Olaf, the compare-object may be best! I'd just like to add some % success markers, but overall i think that should do. Also add an email script to the end, but not too bad!

    Good idea

  • #60981
    Profile photo of Geir Endre Jenssen
    Geir Endre Jenssen
    Participant

    Or you could just do it like this:
    1. Get-ADUser | Export-Csv -path c:\ADusers.csv
    2. Use Excel to compare the two csv files.

    The Vlookup function might do the trick...

    So you only extract data from AD once!!!

You must be logged in to reply to this topic.