Looping but pulling the wrong data

PowerShell Help
1

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' - $_"
     }
}
2

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.

3

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 "$_" 
  }        
}
4

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.

5

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' - $_"
	}
}
6

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: (:slight_smile: , COMException
    • FullyQualifiedErrorId : System.Runtime.InteropServices.COMException