question

SkipHofmann-5788 avatar image
0 Votes"
SkipHofmann-5788 asked SkipHofmann-5788 commented

Help with powershell script

Hello all

Hello all

This is what i am trying to accomplish.

  1. csv file contains a list of users UPN (header in csv = upn)

  2. script reads all users from csv import file (step1.) does a compare or hash table against users in three specific OU's in AD using (upn).

  3. If a match is found, extend account expiration + 90 days from the day the script is run

  4. if a match is not found write the non matched accounts to a separate .csv file

ISSUE
I cant get the differences between the import .csv file and what is found in the OU's written to the output file

 $OUNames = "OU=FMI,OU=Cognizant,OU=FM Users,OU=Corp,DC=test-tech,DC=com", "OU=BPO and RPA,OU=Cognizant,OU=Consultants,OU=Users,OU=Corp,DC=test-tech,DC=com"
  Import-Csv C:\temp\test2.csv |
      ForEach-Object{
           $u = get-aduser -Filter "userPrincipalName -eq '$($_.upn)'"
              if ($u){
              $OU = $u.DistinguishedName.Substring($u.DistinguishedName.IndexOf('OU=',[System.StringComparison]::CurrentCultureIgnoreCase))
              if ($OUNames -contains $OU){
                  Set-ADAccountExpiration -Identity $u.distinguishedName -TimeSpan 90.0:0
              }
              else{
                  $_
              }
          }
          else {
              $_
          }
      } | Export-Csv C:\temp\WhoAreThesePeople.csv -NoTypeInformation




windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered SkipHofmann-5788 commented

Try this one:

 $OUNames = "OU=FMI,OU=Cognizant,OU=FM Users,OU=Corp,DC=test-tech,DC=com", "OU=BPO and RPA,OU=Cognizant,OU=Consultants,OU=Users,OU=Corp,DC=test-tech,DC=com"
 # Load hash with UPNs
 $UPNs = @{}
 $OUNames |
     ForEach-Object{
         Get-ADUser -Filter * -SearchBase $_ -SearchScope OneLevel |
             ForEach-Object{
                 $UPNs[$_.UserPrincipalName] = $false
             }
     }
 Import-Csv C:\temp\test2.csv |
     ForEach-Object {
         $u = get-aduser -Filter "userPrincipalName -eq '$($_.upn)'"
         if ($u) {
                     Set-ADAccountExpiration -Identity $u.distinguishedName -TimeSpan 90.0:0
                     $UPNs.($_.UPN) = $true
         }  else {
             [PSCustomObject]@{
                 UPN = $_.UPN
                 Reason = "UPN not found in AD"
             }
         }
     } | Export-CSV C:Temp\NotInAD.csv -NoTypeInformation
 $UPNs.GetEnumerator()|
     ForEach-Object{
         if (-not $_.Value){
             [PSCustomObject]@{
                 UPN = $_.Key
                 Reason = "UPN not in CSV, or UPN in different OU"
             }
         }
     } | Export-Csv C:\Temp\NotInCSV.csv -NoTypeInformation
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NotinCSV.csv exports, but its not exporting the users upn.


147110-image10.gif



NotinAD.csv doesn't export


0 Votes 0 ·
image10.gif (35.9 KiB)

Change line 8 and 19. I've corrected the code sample I posted.

Line 8 used "$<underbar>.UPN" and it should have been "$<underbar>.UserPrincipalName".
Line 19 used "$<underbar>" and it should have been "$<underbar>.UPN"

0 Votes 0 ·

That did it! Thank you very much. @RichMatheisen-8856 @MotoX80 I just want to let you both know i am very grateful for all of your help. Thank you once again

0 Votes 0 ·
MotoX80 avatar image
0 Votes"
MotoX80 answered SkipHofmann-5788 commented

What error do you get? Examine the data before you try to write it to the csv.

 $WhoAreThesePeople = @() 
  $OUNames = "OU=FMI,OU=Cognizant,OU=FM Users,OU=Corp,DC=test-tech,DC=com", "OU=BPO and RPA,OU=Cognizant,OU=Consultants,OU=Users,OU=Corp,DC=test-tech,DC=com"
   Import-Csv C:\temp\test2.csv |
       ForEach-Object{
            $u = get-aduser -Filter "userPrincipalName -eq '$($_.upn)'"
               if ($u){
               $OU = $u.DistinguishedName.Substring($u.DistinguishedName.IndexOf('OU=',[System.StringComparison]::CurrentCultureIgnoreCase))
               if ($OUNames -contains $OU){
                   Set-ADAccountExpiration -Identity $u.distinguishedName -TimeSpan 90.0:0
               }
               else{
                   $WhoAreThesePeople+= $_
               }
           }
           else {
               $WhoAreThesePeople+= $_
           }
       } 
 "Here are the people we can't find"
 $WhoAreThesePeople
 $WhoAreThesePeople | Export-Csv C:\temp\WhoAreThesePeople.csv -NoTypeInformation
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello thank you for the assistance. I'm trying your suggestion in my lab. I'm not getting an error, but the differences between what is in the .csv import file and the OU's is not being written to the output .csv file. What is getting written to the output file is the same thing that is in the input file. my csv import file csv header is upn.

Thank you again.

0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered SkipHofmann-5788 commented

The exported CSV's contents will only include the same information found in your imported CSV.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Okay, i probably wasn't clear. My apologies. I need the difference between what is in the .csv input file and the OU's written to the .csv output file.

Thank you again.

0 Votes 0 ·
MotoX80 avatar image
0 Votes"
MotoX80 answered MotoX80 commented

Keep count of where in your processing you might have an error.

  $WhoAreThesePeople = @() 
  $FoundUser = 0
  $UserInOU = 0
  $GotOne = 0
  $NotInOU = 0  
  $UnknowUser = 0 
  $OUNames = "OU=FMI,OU=Cognizant,OU=FM Users,OU=Corp,DC=test-tech,DC=com", "OU=BPO and RPA,OU=Cognizant,OU=Consultants,OU=Users,OU=Corp,DC=test-tech,DC=com"
   Import-Csv C:\temp\test2.csv |
       ForEach-Object {
            $u = get-aduser -Filter "userPrincipalName -eq '$($_.upn)'"
            if ($u) {
                 $FoundUser++
                 $OU = $u.DistinguishedName.Substring($u.DistinguishedName.IndexOf('OU=',[System.StringComparison]::CurrentCultureIgnoreCase))
                 if ($OUNames -contains $OU) {
                     $GotOne++                                    # fixed per Rich 
                     Set-ADAccountExpiration -Identity $u.distinguishedName -TimeSpan 90.0:0
                 } else {
                     $NotInOU++
                     $WhoAreThesePeople+= $_
                 }
            }  else {
                 $UnknowUser++ 
                 $WhoAreThesePeople+= $_
            }
       } 
  "We found {0} users." -f $FoundUser 
  "Of the users that we found, {0} were NOT in the OU." -f $NotInOU   
  "Expiration was set on {0} users." -f $GotOne
  "Count of users NOT found in AD: {0}" -f $UnknowUser  
  $WhoAreThesePeople | Export-Csv C:\temp\WhoAreThesePeople.csv -NoTypeInformation
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm still not getting any errors, however the output file is still only dumping what is exactly in the input .csv file. The two accounts that the script said it couldn't find are the same accounts that are in the input .csv file. 146962-date1.gif


0 Votes 0 ·
date1.gif (22.8 KiB)

The users that are matched from the .csv input file and the OU's need to have the account expiration set. Any accounts that are not matched between the .csv input file and the OU's need to be dumped to the . csv output file "whoareThesePeople.csv"

Thank you again for all of your help

0 Votes 0 ·

If you're only working with two users, why not just display the $OU variable on the console? Is it in the correct format? Is it one of the ones in the $OUNames list?

0 Votes 0 ·
Show more comments

On line 3 you have a declared but unreferenced variable.
On line 4 you declare the variable $GotOne BUT on line 15 you increment a variable named $GoneOne which you never reference again.
On line 28 you report the value of $GotOne -- which remains with it's initial value of 0 -- as the number of users that had their expiry date set.

Two questions remains: is the value in $OU correct? Are the OU names in $OUNames correct?

0 Votes 0 ·
MotoX80 avatar image MotoX80 RichMatheisen-8856 ·

I knew there would be a typo involved with this problem somehow.....

0 Votes 0 ·
MotoX80 avatar image MotoX80 RichMatheisen-8856 ·

Fixed.

0 Votes 0 ·
SkipHofmann-5788 avatar image
0 Votes"
SkipHofmann-5788 answered RichMatheisen-8856 commented

To make things easier. I am testing against one OU. If i run $OU and $o2 the OU matches exactly what is found in $OUNames

146939-image4.gif



image4.gif (33.4 KiB)
· 13
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have trailing spaces $OU? Look at $ou.length or do a $OU.Trim().

0 Votes 0 ·

I copied exactly what was in ADSIEDIT for the DN of the OU and pasted it into the script. but what's interesting is $ou.length = 49
However below = 55
OU=FMI,OU=Cognizant,OU=FM Users,OU=Corp,DC=ip-tech,DC=com


This is making me crazy. lol wth??

0 Votes 0 ·

Try using this to get the DN of each OU instead of specifying them as literals. This way the OU DNS will match regardless of what domain you're working in:

 $OUList = "FMI","BPO and RPA"
 $OUList |
        ForEach-Object{
            [array]$OUNames += (Get-ADOrganizationalUnit -Filter "name -eq '$_'").distinguishedName
        }

This assumes that the names of the OU's match the 1st OU element in the list you provided.

0 Votes 0 ·

Okay, so tell me what I'm missing.

 $OUNames = "OU=FMI,OU=Cognizant,OU=FM Users,OU=Corp,DC=ip-tech,DC=com", "OU=BPO and RPA,OU=Cognizant,OU=Consultants,OU=Users,OU=Corp,DC=ip-tech,DC=com"
 $OU1 =     "OU=FMI,OU=Cognizant,OU=FM Users,OU=Corp,DC=ip-tech,DC=com"
 $OUNames -contains $OU1
 $OUNames[0] -eq $OU1

Both tests return "True".

In the script you posted, though, the 1st DC component in each OU in $OUNames is "test-tech", not "ip-tech" as in your screenshot.

0 Votes 0 ·
MotoX80 avatar image MotoX80 RichMatheisen-8856 ·

Good catch.

0 Votes 0 ·

I dont know what your missing. lol It came back "True" for me. I was modifying the DN name to "TEST" because i was going back and forth from my lab environment

146956-image5.gif


0 Votes 0 ·
image5.gif (42.9 KiB)

So you've proven that "-contains" works when the array of names contains the OU name. That indicates that there is some issue with OU name when you read it from AD.

Did you add a .trim() to remove trailing spaces?



0 Votes 0 ·
Show more comments
MotoX80 avatar image
0 Votes"
MotoX80 answered RichMatheisen-8856 commented

If get-aduser works, then $FoundUser will be incremented by one.
If get-aduser fails, then $UnknowUser will be incremented by one.

$FoundUser + $UnknowUser will equal the total number of rows in the csv.

If $OUNames -contains $OU, then $GotOne will be incremented by one. (Assuming that you corrected my typo error )
If the OU test fails, then $NotInOU will be incremented by one.

$Gotone + $NotinOU will equal $FoundUser.

If $NotInOU is greater than one, or $UnknowUser is greater than one, then the current object (dollar underscore) will be added to the $WhoAreThesePeople array.

If your csv only has one row in it, does $FoundUser equal one? If not, go figure out what is wrong as to why it's not finding the user.

Once get-aduser works, does $NotInOU equal 1? If it does, analyze the OU names to figure out why it's not working. Does $OU.gettype() show that it is a string? Does $OU.length match the number of characters that visually display on the screen?

Here is an updated script with more diagnostics. Please post the entire output of this.

Be advised that I don't have a good way to test this. It may contain mistakes.


   $WhoAreThesePeople = @() 
   $FoundUser = 0
   $GotOne = 0
   $NotInOU = 0  
   $UnknowUser = 0 
   $OUNames = "OU=FMI,OU=Cognizant,OU=FM Users,OU=Corp,DC=test-tech,DC=com", "OU=BPO and RPA,OU=Cognizant,OU=Consultants,OU=Users,OU=Corp,DC=test-tech,DC=com"
    Import-Csv C:\temp\test2.csv |
        ForEach-Object {
             $u = get-aduser -Filter "userPrincipalName -eq '$($_.upn)'"
             if ($u) {
                  $FoundUser++
                  $OU = $u.DistinguishedName.Substring($u.DistinguishedName.IndexOf('OU=',[System.StringComparison]::CurrentCultureIgnoreCase))
                  if ($OUNames -contains $OU) {
                      $GotOne++                                    # fixed per Rich 
                      Set-ADAccountExpiration -Identity $u.distinguishedName -TimeSpan 90.0:0
                  } else {
                      $NotInOU++
                      $WhoAreThesePeople+= $_
                      "A user was added to WhoAreThesePeople because we did not find the OU."
                      "The OU we tested is {0}" -f $OU
                      "The OU variable type is {0} " -f $OU.gettype().Name
                      "The string length is {0}" -f $OU.length                       
                      "Current WhoAreThesePeoplecount is {0}" -f $WhoAreThesePeople.count 
                  }
             }  else {
                  $UnknowUser++ 
                  $WhoAreThesePeople+= $_ 
                  "An unknown user was added to WhoAreThesePeople. Current count is {0}" -f $WhoAreThesePeople.count 
             }
        } 
   "We found {0} users." -f $FoundUser 
   "Of the users that we found, {0} were NOT in the OU." -f $NotInOU   
   "Expiration was set on {0} users." -f $GotOne
   "Count of users NOT found in AD: {0}" -f $UnknowUser  
   "Total WhoAreThesePeople count is {0}" -f $WhoAreThesePeople.count 
   $WhoAreThesePeople | Export-Csv C:\temp\WhoAreThesePeople.csv -NoTypeInformation


· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

only one user in my .csv file. header of .csv file = UPN

147103-image8.gif


0 Votes 0 ·
image8.gif (14.2 KiB)

Excellent! it's working!!.

Now add a bogus user to the csv file and verify that it gets written to the WhoAreThesePeople.csv file.

0 Votes 0 ·

Okay, now i know what you are doing. You are looking at what is in the .csv file and comparing it to what is in the OU, and then dumping the difference to WhoAreThesePeople.csv

I just put a bogus user into the import.csv file and sure enough it dumped the bogus user into "WhoAreThesePeople". What i am trying to achieve is dump all users in the OU that dont match what is also found in the import-csv file to WhoAreThesePeople.csv.

OMG I had a feeling I wasn't being 100% clear.

0 Votes 0 ·
Show more comments

Its finding the user in the .csv file, and its setting the expiration date of the user, and its correctly identifying the number of users that are NOT found in AD:, but "WhoAreThesePeople: count is 0 . I have 100 users in the "OU=FMI,OU=Cognizant,OU=FM Users,OU=Corp,DC=ip-tech,DC=com" OU . This is OU is what is in the script

147114-image9.gif


0 Votes 0 ·
image9.gif (21.5 KiB)

Test with 2 entries in the input csv file. One that is a legitimate user and one whose upn name is garbage.

0 Votes 0 ·
Show more comments