Looping but pulling the wrong data

This topic contains 5 replies, has 2 voices, and was last updated by Profile photo of dudzy dudzy 5 months, 3 weeks ago.

  • Author
    Posts
  • #63150
    Profile photo of dudzy
    dudzy
    Participant

    Hello all, I know there is a lot to take in here, but im not sure where the failure is because the results are not fully wrong. This script is used to generate emails, it is looping the correct amount of times (per lines in CSV file) however it is pulling the final line from the CSV file to populate the data.

    Its obvious there is something wrong with my foreach statement at the final line, however im unsure of what that is wrong, so I just put it all out there.

    $input_csvfile = Import-Csv "$DesktopPath\table_export.csv"
    $OrderNumber = $line.ORDER
    $Fulfiller = $line.FULFILLER_SHORTNAME
    $Group = $line.GROUP_NAME
    $Owner = $line.GROUP_OWNER
    $User = $line.RECIPIENT_SHORTNAME
    $Approver1 = $line.GROUP_APPROVER1
    $Approver2 = $line.GROUP_APPROVER2
    $Requester = $line.REQUESTOR_SHORTNAME
    $RequesterDN = Get-ADUser -identity $Requester -Properties DisplayName | Select Displayname | Out-String
    $RequestApproval = $line.APPROVER_SHORTNAME
    $RequestApprovalDN = Get-ADUser -identity $RequestApproval -Properties DisplayName | Select Displayname | out-string
    $ToAddress1 = "$Approver1@youremail.com"
    $ToAddress2 = "$Approver2@youremail.com"
    $FromAddress = 'youremail@youremail.com
    $UserDN = Get-ADUser -identity $user -Properties DisplayName | Select Displayname | out-string
    $OwnerDN = get-aduser -identity $Owner -properties DisplayName | select DisplayName | out-string
    $Subject = "Operations | $ordernumber for $user is awaiting your approval"
    $SmtpServer = 'mail-youremail.com'
    $EmailBody = @"
     
     
     Hello from IT Administration
     
     
    Attention - Your approval is needed for #$OrderNumber
    
    
    Order Details:
    Group Requested: $Group
    
    Requested for:   $User
    $UserDN
    
    Request Submitted By:    $Requester
    $RequesterDN
    
    Request Approved By:     $RequestApproval
    $RequestApprovalDN
    
     
    
         
        
            Approve
        
        
            Deny
         
     
     
    
     
     
    "@ 
    
    foreach ($line in $input_csvfile) {
        try { 
             Send-MailMessage -SmtpServer $SmtpServer -To "$toaddress1; $ToAddress2" -From $fromaddress -Subject "$subject" -Body "$EmailBody" -BodyAsHtml -ErrorAction Stop
        Write-host "Approval email sent to $Approver1 for order# $OrderNumber" -ForegroundColor Yellow 
             }
        catch {
         "FAIL '$Group' - $_"
         }
    }
    
  • #63153
    Profile photo of Don Jones
    Don Jones
    Keymaster

    At the top...

    $input_csvfile = Import-Csv "$DesktopPath\table_export.csv"
    $OrderNumber = $line.BPS_ORDER
    

    Where is $line being populated? I don't see $line again until your foreach loop, but the entire thing at top is outside the foreach.

    • #63154
      Profile photo of dudzy
      dudzy
      Participant

      I wont lie, i'm still learning.. here is what I understand, and maybe someone can fill in the blanks. I have always used "foreach ($line in $input_csvfile)" for my loop, I was under the understanding that $line is something that powershell already understands (I only say this because other scripts such as the one below work correctly). So the looping statement doesn't actually use $line except when I am calling it to run thru all lines within the CSV. This is looping the correct amount of times..

      lets say csv file has this data:
      line 1 is($Fulfiller = Tom, $Owner = my-group, $User = Dan, and $Approver1 = Mike)
      line 2 is($Fulfiller = Tom, $Owner = my-group, $User = Dan, and $Approver1 = Mike)

      Instead of starting at line 1 this is looping twice but with data from line 2

      Like I said i'm new and learning, so I am most likely doing something wrong and I got lucky with my script below.. lol

      example that does work

      foreach ($line in $input_csvfile)
      {
        $group = $line.Group
        $user = $line.User
        try 
        {
          Remove-ADGroupMember -Identity $group -Members $user -Confirm:$false
          write-output "$user removed from: $group"
          $members = Get-ADGroupMember -Identity $group -Recursive | Select -ExpandProperty Name
          If ($members -contains $user) {
            Write-Host "$user exists in the group"
              } Else {
              Write-Host "$user does not exists in the $group"-ForegroundColor red
          }
        }
        catch
        {
          write-output "$_" 
        }        
      }
      
    • #63157
      Profile photo of dudzy
      dudzy
      Participant

      I also understand that most of this is written outside of the actual loop, for learning purposes I have always created custom objects because that is how I was taught.

    • #63162
      Profile photo of dudzy
      dudzy
      Participant

      found the issue, thank you for all your time, it is very much appreciated!

      You should define your variables within your foreach loop. This is why you are seeing everything as the final entry in the CSV file – you might've run it once and if your re-using the same runspace it will have values in $line already to populate your variables at the top

      So reflowing this to work correctly – it would look like this

      $input_csvfile = Import-Csv "$DesktopPath\table_export.csv"
       
      $SmtpServer = 'mail'
       
       
      foreach ($line in $input_csvfile)
      {
      	$OrderNumber = $line.BPS_ORDER
      	$Fulfiller = $line.FULFILLER_SHORTNAME
      	$Group = $line.GROUP_NAME
      	$Owner = $line.GROUP_OWNER
      	$User = $line.RECIPIENT_SHORTNAME
      	$Approver1 = $line.GROUP_APPROVER1
      	$Approver2 = $line.GROUP_APPROVER2
      	$Requester = $line.REQUESTOR_SHORTNAME
      	$RequesterDN = Get-ADUser -identity $Requester -Properties DisplayName | Select Displayname | Out-String
      	$RequestApproval = $line.APPROVER_SHORTNAME
      	$RequestApprovalDN = Get-ADUser -identity $RequestApproval -Properties DisplayName | Select Displayname | out-string
      	$ToAddress1 = "'youremail"
      	$ToAddress2 = "'youremail"
      	$FromAddress = 'youremail'
      	$UserDN = Get-ADUser -identity $user -Properties DisplayName | Select Displayname | out-string
      	$OwnerDN = get-aduser -identity $Owner -properties DisplayName | select DisplayName | out-string
      	$Subject = "Operations | $ordernumber for $user is awaiting your approval"
      	$EmailBody = @"
      
      
      Hello from IT Administration
      
      
      Attention - Your approval is needed for #$OrderNumber
       
      
      Order Details:
      Group Requested: $Group
      
      Requested for: $User
      $UserDN
      
      Request Submitted By: $Requester
      $RequesterDN
      
      Request Approved By: $RequestApproval
      $RequestApprovalDN
       
       
       
       
      
      Approve
      
      
      Deny
       
       
       
       
      
      
      "@
      	try
      	{
      		Send-MailMessage -SmtpServer $SmtpServer -To "$toaddress1; $ToAddress2" -From $fromaddress -Subject "$subject" -Body "$EmailBody" -BodyAsHtml -ErrorAction Stop
      		Write-host "Approval email sent to $Approver1 for order# $OrderNumber" -ForegroundColor Yellow
      	}
      	catch
      	{
      		"FAIL '$Group' - $_"
      	}
      }
      
  • #63165
    Profile photo of dudzy
    dudzy
    Participant

    Trying to now add a line to write to an access database, never have tested this until today, so i may have made a stupid error

    This was added to bottom of "try" statement within foreach

    $cursor = 2
    $lock = 3
    $query = "Select * from [Approvals_sent]"
    $PathACCDB = "$DesktopPath\Group_Access_Powershell_export.accdb"
    $Ado = New-Object -ComObject ADODB.Connection
    $recordset = New-Object -ComObject ADODB.Recordset
    $Ado.open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $pathACCDB")
    $recordset.Addnew()
    $recordset.Fields.Item("ORDER") = "$OrderNumber"
    $recordset.Fields.Item("FULFILLER_SHORTNAME") = "$Fulfiller"
    $recordset.Fields.Item("GROUP_NAME") = "$Group"
    $recordset.Fields.Item("GROUP_OWNER") = "$Owner"
    $recordset.Fields.Item("RECIPIENT_SHORTNAME") = "$User"
    $recordset.Fields.Item("GROUP_APPROVER1") = "$Approver1"
    $recordset.Fields.Item("GROUP_APPROVER2") = "$Approver2"
    $recordset.Fields.Item("GROUP_APPROVER3") = "$Approver3"
    $recordset.Fields.Item("GROUP_APPROVER4") = "$Approver4"
    $recordset.Fields.Item("GROUP_APPROVER5") = "$Approver5"
    $recordset.Fields.Item("GROUP_APPROVER1") = "$Approver1"
    $recordset.Fields.Item("GROUP_APPROVER1") = "$Approver1"
    $recordset.Fields.Item("GROUP_APPROVER1") = "$Approver1"
    $recordset.Fields.Item("GROUP_APPROVER1") = "$Approver1"
    $recordset.Update()
    $recordset.close()
    $ado.close()
    }
    

    error im getting is below:
    Unrecognized database format 'C:\Users\XXXXXX\Desktop\Group_Access_Powershell_export.accdb'.At
    Z:\filepath here
    Generation.ps1:78 char:5
    + $Ado.open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

You must be logged in to reply to this topic.