locked
Populating Values from CSV Based on Another Column RRS feed

  • Question

  • Hi All,

    I need your help getting a script to work based on the following criteria:

    I have a list of users (samaccountname) in one column of a csv file and their managers (name) in another column. 

    I am trying to send an email to each manager with a list of users in the csv, who have that specific manager in the csv.

    I have the email working, but it is currently based on the list of users and so it sends a separate email to each manager regarding one specific user and instead I would like managers to only receive one email with a list of all their users.

    Could you help me build the variables to find each manager in the csv, get his list of employees listed next to his name in the list so I can populate the list in the email body making sure only one email is sent to each manager?

    Thanks in advance,

    W

    Wednesday, March 15, 2017 10:37 AM

All replies

  • You forgot to post your script!  ;-)

    Grüße - Best regards

    PS:> (79,108,97,102|%{[char]$_})-join''

    Wednesday, March 15, 2017 11:03 AM
  • Use Import-csv  and then filter the properties using where-object.

    post your script in case further help is required.

     

    Thanks, Samer Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!

    Wednesday, March 15, 2017 11:34 AM
  • I thought the email script would not do anything for you guys, but here it is:

    It needs to be adjusted as well, as right now it is set up to send an email to a manger based on a list of users.


    $UserList = (Get-Content .\2.Users.txt)
    $Manager = ""
    
    $header = "blablablabla
    
    here I need the list of users from CSV
    "
    
    foreach ($CurrentUser in $UserList)
    {
          $Time = Get-Date -Format "HH:mm:ss.fff"
    $ScriptName = $MyInvocation.MyCommand.Name
    $baseName = (Get-Item $ScriptName).BaseName
    $LogName = "$basename.log"
    $Log = ".\" + "\" + $LogName
    "[$ScriptName] [$Time] [Starting]" | Out-File $Log -Append 
    "[$ScriptName] [$Time] Getting Manager and sending email for $CurrentUser]" | Out-File $Log -Append
          $UserFullName = (Get-ADUser $CurrentUser -Properties DisplayName)
          $Manager = (get-aduser (get-aduser $CurrentUser -Properties manager).manager).samaccountName
          $UserEmail = (Get-ADUser $CurrentUser -properties mail).mail
          $ManagerEmail = (Get-ADUser $Manager -properties mail).mail
          
    
          #Send Email to $Manager and CC $CurrentUser
          
          
          #SMTP server name
          $smtpServer = ""
          #Creating a Mail object
          $msg = new-object Net.Mail.MailMessage
          #Creating SMTP server object
          $smtp = new-object Net.Mail.SmtpClient($smtpServer)
          #Email structure 
          $msg.From = ""
          $msg.To.Add($ManagerEmail)
          $msg.CC.Add($UserEmail)
          $msg.BCC.Add('')
          $msg.ReplyTo = ""
    
          $date = Get-Date -Format d
          $msg.subject = "Account Expiration notice for " + $UserFullName.DisplayName + " - " + $date
          $msg.body = $header + "`r`n`r`n -   '" + $UserFullName.DisplayName + "  -  " + $CurrentUser + "'`r`n`r`n" + $footer
    
          #Sending email 
          $smtp.Send($msg)
          "[$ScriptName] [$Time] [Finished]" | Out-File $Log -Append
          #>    
          
          
    
    
    }


    Wednesday, March 15, 2017 11:54 AM
  • Here is an example of how you can do this

    #Assuming the header of csv is UserName and Manager. Change the headers in the script based on your csv
    $import = import-csv C:\test.csv
    $uniqueManagers = $import | select manager -Unique | % {$_.manager}
    
    $uniqueManagers | ForEach-Object {
    
    $uniqueManager = $_
    $users = $import | where {$_.manager -eq $uniqueManager} | % {$_.username}
    $ManagerEmail = (Get-ADUser $uniqueManager -properties mail).mail
    $users | % {
    $user = $_
    $userEmail = (Get-ADUser $User -properties mail).mail
    $UserEmailIDs += $username,
    
    }
    #Start your email part here
    #Removing last comma(,) from email IDs
    $userEmailIDs = $userEmailIDs -replace ".$"
    ##write rest of your email stuff here now you have Mangager email ID in $ManagerEmail Variable and associated users ID in $userEmailIDs variable
    
    }
    


    Thanks, Samer Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!

    Wednesday, March 15, 2017 12:53 PM
  • There are a number of scripts in the Gallery that do this in some very nice ways.

    This is the general pattern for doing this in PowerShell.

    $maiprops = @{
    	From = <from>
    	Subject = 'subject'
    	SmtpServer = <smtpserver>
    	Port = 25
    	
    }
    Import-Csv <file> | 
    Group manager | ForEach-Object { $body = $_.Group | Format-Table | Out-String $tomail = Get-AdUser $_,Name | select -expand mail $usersmail = $_.Group | %{Get-AdUser $_.user |select -expand mail} Send-MailMessage @mailprops -To $tomail -cc $usersmail -Body $body } }


    \_(ツ)_/





    • Edited by jrv Wednesday, March 15, 2017 6:34 PM
    Wednesday, March 15, 2017 6:31 PM
  • Sorry to be this stupid, guys, but I'm just not able to put this together :( 

    $import = import-csv .\test.csv
    $uniqueManagers = $import | select manager -Unique | % {$_.manager}
    
    $uniqueManagers | ForEach-Object {
    
    $uniqueManager = $_
    $users = $import | where {$_.manager -eq $uniqueManager} | % {$_.username}
    $ManagerEmail = (Get-ADUser $uniqueManager -properties mail).mail
    $users | % {
    $user = $_
    $userEmail = (Get-ADUser $User -properties mail).mail
    $UserEmailIDs += $username
    }}
    
    
    #Edit Msg body here.  Use "`r`n" for inline carraige return if wanted/needed.  Might be a better way than this though.
    
    $header = "blablabla.
    "
    
    $footer = "blablabla"
    
    foreach ($CurrentUser in $users)
    {
    
          
    
          #Send Email to $Manager and CC $CurrentUser
          
          
          #SMTP server name
          $smtpServer = "..."
          #Creating a Mail object
          $msg = new-object Net.Mail.MailMessage
          #Creating SMTP server object
          $smtp = new-object Net.Mail.SmtpClient($smtpServer)
          #Email structure 
          $msg.From = "..."
          #$msg.To.Add($ManagerEmail)
          $msg.CC.Add($userEmail)
          #$msg.BCC.Add('...')
          #$msg.ReplyTo = "..."
    
          $date = Get-Date -Format d
          $msg.subject = "Contractor Account Expiration notice for  - " + $date
          $msg.body = $header + "`r`n`r`n -   '" + $UserFullName.DisplayName + "  -  " + $CurrentUser + "'`r`n`r`n" + $footer
    
          #Sending email 
          $smtp.Send($msg)
           #>    
                     
    }


    Wednesday, March 22, 2017 1:41 PM
  • I posted the answer above.


    \_(ツ)_/

    Wednesday, March 22, 2017 5:59 PM
  • I posted the answer above.


    \_(ツ)_/

    Where do I put the body and all the other stuff using that code?
    Thursday, March 23, 2017 7:47 AM
  • I am sorry but you will have to learn PowerShell so you can understand the advice being given.  We cannot teach you this.

    \_(ツ)_/

    Thursday, March 23, 2017 8:05 AM