Check excel cell for username against AD and retrieve e-mail

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Mike Catalano Mike Catalano 10 months, 1 week ago.

  • Author
    Posts
  • #34756
    Profile photo of Mike Catalano
    Mike Catalano
    Participant

    I have an excel report that is generated daily with usernames in column B, below the header line. I need something that would check the first 3 letters of cells b2, b3, b4, etc and query AD to find the username. Once the username is found it would look for the e-mail address associated with that user and e-mail them this excel report.

    I am able to send the e-mails fine but am having trouble looking at specific cells and only searching based on the first 3 letters found in those cells. I realize that only using 3 letters for a search might possibly result in more than i want, but i can filter those out in the get-aduser filter. I'm a bit reluctant to post any sample code i have as it clearly has not worked at all

  • #34757
    Profile photo of Christian Sandfeld
    Christian Sandfeld
    Participant

    I do not have any sample code to provide for you, but have you had a look at one of the available modules to work with Excel files? One such module is the "ImportExcel" module maintained by Doug Finke:
    https://github.com/dfinke/ImportExcel

  • #34758
    Profile photo of Mark Hammonds
    Mark Hammonds
    Participant

    If the excell data is simple text I always save it as a csv file much easier to work with. If you have no control over the input file then you will need a module like stated before once you import the date into an custom object the method you want to use to combine the first 3 letters of the first 3 cells would be substrings

    Example
    $userid = ( $exl.cell1.substring(0,2) + $exl.cell2.substring(0,2) + $exl.cell3.substring(0,2))

    Does that make sense?

  • #34762
    Profile photo of Mike Catalano
    Mike Catalano
    Participant

    I'm trying to follow but you'll need to bear with me. I literally started learning PS this week and just have what I've learned from the MVA. The module for ImportExcel would certainly solve a future plan i have to color code other cells that have reached a date criteria. I've put the folder into the module path: C:\Users\USERNAME\Documents\WindowsPowerShell\Modules and i'll start poking around the help files to see if i can make heads or tails of this.

  • #34789
    Profile photo of Mike Catalano
    Mike Catalano
    Participant

    I ended up getting this to work, but i'm sure you guys can point out way to make this more efficient or shorter. Again, i'm just starting out so I'm just glad it works.

    I had to delete the top 5 lines of the report, because it always contained junk rows. I also added the start-sleep's just to make things a bit easier to read when it's echo'ing the data on the console. The function i got from somewhere on the web, i cannot remember where to give the credit due for that one. The function basically takes the DNS name and cuts off anything after the first 3 characters. I then added a wildcard (*) to the end of every array value and searched AD with that to get e-mail addresses. The script is followed up with a send-mailmessage cmdlet that attaches the report and sends it to the users it found. Really messy, but hey it works

    
    $filepath = "\\Servershare\public\Desktop_Software_$((Get-Date).ToString('MM-dd-yyyy')).csv"
    
    $modreport = import-csv -Path $filepath
    $modreport | Select-Object -Skip 5 | Export-Csv -Path $filepath -NoTypeInformation
    $modreport = import-csv -Path $filepath
    
    
    Write-Host " Parsing the following report..."
    Write-Host
    $modreport | ft -AutoSize 
    
    Start-Sleep 3
    
    $DNS = $modreport | select -ExpandProperty DNS
    
    
    #declare new function
    function Get-Matches {
      param(
        [Parameter(Mandatory=$true)]
        $Pattern,
        
        [Parameter(ValueFromPipeline=$true)]
        $InputObject
      )
      
     begin {
      
        try {
       $regex = New-Object Regex($pattern) 
      } 
      catch {
       Throw "Get-Matches: Pattern not correct. '$Pattern' is no valid regular expression."
      }
      $groups = @($regex.GetGroupNames() | 
      Where-Object { ($_ -as [Int32]) -eq $null } |
      ForEach-Object { $_.toString() })
    }
    
    process { 
      foreach ($line in $InputObject) {
       foreach ($match in ($regex.Matches($line))) {
        if ($groups.Count -eq 0) {
         ([Object[]]$match.Groups)[-1].Value
        } else {
         $rv = 1 | Select-Object -Property $groups
         $groups | ForEach-Object {
          $rv.$_ = $match.Groups[$_].Value
         }
         $rv
        }
       }
      }
    }
    }
    
    $myregex = '^\S{3}'
    
    $shortname = $DNS | Get-Matches $myregex
    
    Write-host
    Write-host
    Write-host
    
    $search = $shortname | foreach {$_ + "*"}
    
    $getemail = ForEach($dns in $search){get-aduser -ldapfilter "(anr=$dns)" -properties mail | select-object -property mail -unique | where mail -ne $null}
    
    
    Write-Host " E-mailing the following users this report..."
    Write-Host
    Write-Host
    $getemail
    
    Start-Sleep 3
    
    

You must be logged in to reply to this topic.