CSV to AD reporting - Part 2 of Oracle Importing

This topic contains 6 replies, has 4 voices, and was last updated by Profile photo of Jeff Scharfenberg Jeff Scharfenberg 4 days, 12 hours ago.

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

    Now that the import to AD from oracle is working...I have for sure learned that the process is only as good as the data that is entered. So, since I am already seeing minor enough mistakes in the first couple of runs that won't let this process work for a couple of people I have decided that I want to setup some simple error reporting to email to me everyday.

    Using what I already have of the import process, I would like to have a script to run after I do the import to scan the CSV and tell me what email fields don't match Active Directory.

    How can I make a simple matching script, i'd think using an IF statement and then email me the results of it being negative.
    Such as if CSVEMAIL -Notmatch ADEMAIL, create a hash table of the results and email to me along with the count (just to easily view the # of incorrect accounts input)

    import-module activedirectory
    
    #Import latest adp file, set as variable, only selecting objects from file that have email field populated.
    $adplist = import-csv "C:\Temp\OracletoADScript\ActiveDirectory.csv" # | Where-Object {$_.'EMPLID'}
    #write-host $adplist
    
    
       #Loop through each selected object in the csv.    
    ForEach ($employee in ($adplist | Where {$_.Company -Match "Company A"}))  
     

    Thanks

  • #58444
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    Compare-Object could make your life much easier in this case. 😉

  • #58447

    Hi

    How about if you create IF statementand inside that you add the value/mailaddress to hashtable? looking against userPrincipalName or emailaddress what ever you need.

    #Not Tested

    ForEach ($employee in ($adplist | Where {$_.Company -Match "Company A"})) {
    
       IF (Get-ADUser -filter (userPrincipalName -ne $employee) {
    
          #add to hashtable
    
       } ELSE {
    
          #add to another hashtable
    
       }
    
    }
    
    #send mail
    

    Or using TRY, CATCH, FINALLY.

    ForEach ($employee in ($adplist | Where {$_.Company -Match "Company A"})) {
    
       TRY {
          Get-ADUser -filter (userPrincipalName -eq $employee) -ErrorAction Stop
    
          #add to hashtable
    
       } CATCH {
    
          #add to another hashtable
    
       } FINALLY {
    
          #do something
    
       }
    
    }
    
    #send mail
    

    Jake

  • #58454
    Profile photo of Ron
    Ron
    Participant

    Off the top of my head and obviously untested:

    $ADlist = get-aduser -ldapsearch "(mail=*)" -properties mail | select -expand mail
    $unmatched = import-csv "C:\Temp\OracletoADScript\ActiveDirectory.csv" | Where-Object {$_.'EMPLID' -and $adlist -notcontains $_.email}
    
  • #58924
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    i'll have to test some of these later on...got swamped with other things.

    So overall i just need a print out of the users who's CSV email won't match the active directory email. Then a count would be nice just so it's easy to look at in case of it being quite large.

    thanks all

  • #59019
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    Jarkko,

    I like your example the best so far, but for some reason i'm getting replies that are even true.
    I found I had to declare the AD email variable and change a bit to run...

    
    $mail = get-ADUser -SearchBase "OU=OU,DC=company,DC=com" -properteries -filter mail | select -Expand Mail
    
    ForEach ($employee in ($adplist | Where {$_.Company -Match "Company A"}))  {
    
    	If ($mail -NotMatch $employee.Email_Address){
    	
    		write $employee.Last_Name}
    		
    		Else {
    		
    		}
    		
    	}
    

    I have a feeling it has something to do with the $mail variable lookup in AD.

  • #59037
    Profile photo of Jeff Scharfenberg
    Jeff Scharfenberg
    Participant

    I have no problem having the csv compare against itself with sometime like

       #Loop through each selected object in the csv.    
    ForEach ($employee in ($adplist | Where {$_.Company -Match "Company A"}))  {
    
    
    	If (($employee.Email_Address) -notlike ('*@emailaddress.com')){
    		
    		write $employee.Last_Name}
    		
    		Else {
    		
    		}
    		
    	}
    
    

    And this spits out everyone who doesn't have an email adddress ending in emailaddress.com. The issue i'm having is having this compare against AD. I'm either getting errors complaining about -filter or I get a list of pretty much everyone, thus not much help.

You must be logged in to reply to this topic.