Send just one email with accounts expired for their manager.

This topic contains 3 replies, has 3 voices, and was last updated by  BGomes 3 weeks, 6 days ago.

  • Author
    Posts
  • #99717

    BGomes
    Participant

    Hello experts!!

    I would like to send just one email to the manager with all accounts that will expire in x days, I do not want to send one by one to the manager. Is anybody tried that?

    Thanks.

  • #99727

    iain Barnetson
    Participant

    Not for AD accounts but for many similar requirements, failed agent jobs, error log reports, etc.
    Just a case of storing values from your loop to a variable then exporting to a csv, or Excel file, or building a table, and emailing it.
    Here's an example of one I do:

    
    $FailedJobs = @()
    $FailedJobs += Run-PAgentJobHistory $SqlServers | Select Server, JobName, StepName , RunDate, Message
    
    # If there are no failed jobs stop the process here
    if(!($FailedJobs)) { LogWrite "No Agent Log report" ; Break}
    
    # Construct table for email
    $Table = $null
    $Table = New-Object System.Data.DataTable "FailedJobs"
    $Col1 = New-Object System.Data.DataColumn ServerName,([string])
    $Col2 = New-Object System.Data.DataColumn RunDate,([string])
    $Col3 = New-Object System.Data.DataColumn JobName,([string])
    $Col4 = New-Object System.Data.DataColumn StepName,([string])
    $Col5 = New-Object System.Data.DataColumn Message,([string])
    $Table.Columns.Add($Col1)
    $Table.Columns.Add($Col2)
    $Table.Columns.Add($Col3)
    $Table.Columns.Add($Col4)
    $Table.Columns.Add($Col5)
    
    foreach($failedjob in $FailedJobs) 
    {
        # Add rows to table for each failed job
        $Row = $Table.NewRow()
        $Row.ServerName = $FailedJob.server
        $Row.RunDate = $FailedJob.RunDate
        $Row.JobName = $FailedJob.JobName
        $Row.StepName = $FailedJob.StepName
        $Row.Message = $FailedJob.Message
        $Table.Rows.Add($row)
    LogWrite "Job $($FailedJob.JobName) failed on $($FailedJob.Server)"
    }   
    
    # Format HTML report
    $a = ""
    $a = $a + "BODY{background-color:#FFFFFF;}"
    $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
    $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
    $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
    $a = $a + ""
    
    $style = "BODY{font-family: Arial; font-size: 10pt;}"
    $style = $style + "TABLE{border: 1px solid black; border-collapse: collapse;}"
    $style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px;}"
    $style = $style + "TD{border: 1px solid black; padding: 5px;}"
    $style = $style + ""
    
    $MsgBody = "
    Hello DBA team,
       
    RE: Agent Job Failures                            
    Please check the listed Agent jobs
     
    $($Table | ConvertTo-Html -Property ServerName,RunDate,JobName,StepName,Message -Head $style | Out-String)
     
     
    Thank You,
    Enterprise Technical Services Team
    
    "
    # Construct & send email
    $MailMessage = @{
            To = "ibarnetson@goempyrean.com"
            Cc ="ibarnetson@goempyrean.com"
            #To = "dl-dba@goempyrean.com"
            #Cc = "RMiller@goempyrean.com"
            From = "dl-dba@goempyrean.com"
            Subject = "SQL Agent Logs Report"
            Body = $MsgBody
            Smtpserver = "smarthost.goempyrean.com"
        }
    
    Send-MailMessage @MailMessage -BodyAsHtml
    
    
  • #99729

    Jeremy Corbello
    Participant

    You need to create an array that holds the accounts that are expiring in x days. Then send the data from that array using the

    Send-MailMessage

    cmdlet.

    Post what you have and I'll show you how to adjust it to your needs. Don't want to do all the work for you or you'll never learn 😀

    • #99736

      BGomes
      Participant

      Please see what I have.... i just would like to send just one email to the manager.

      thanks.
      =================================
      cls
      Import-Module ActiveDirectory
      $OU="OU=x,OU=x,DC=x,DC=x"
      $report = $null
      $table = $null
      #$date = Get-Date -format "dd/MM/yyyy" # Date format
      $date = Get-Date -uformat "%d/%B/%Y" # Date format
      $startDate = Get-Date
      $endDate = $startDate.AddDays(100)
      $file_html = "ad-contractors_expiration.html" # HTML file
      $file_csv = "ad-contractors_expiration.csv" # CSV file
      $total = (Get-ADUser -filter {AccountExpirationDate -gt $startDate -and AccountExpirationDate -lt $endDate} -SearchBase $OU).count # Total accounts on OU
      $domain = (Get-ADDomain).Forest # Domain name
      $team = "IT Team" # Team name
      $company = "xxx" # Company name
      $title_html = "Active Directory – Contractors expiration accounts"
      $area = "xx"
      #$path = ".\" # Reports path

      #–USER LIST————————————————#
      $table += "$total – USERS WITH EXPIRATION ACCOUNT ON: $OU"

      $prop = @('SamAccountName','Mail','Department','Company','Title','Enabled','Created','AccountExpirationDate','Description','CanonicalName','Manager')

      $users = @(Get-ADUser -filter {AccountExpirationDate -gt $startDate -and AccountExpirationDate -lt $endDate -or AccountExpirationDate -lt $startdate } -SearchBase $OU -Properties $prop)
      # List AD user information
      #PS C:\AD> Get-ADUser xx -Properties *

      $result = @($users | Select-Object SamAccountName, Mail, Department, Company, Title, Enabled, Created, AccountExpirationDate, Description, CanonicalName, @{Name='Manager';Expression={(Get-ADUser $_.Manager).sAMAccountName}})

      # Sort by user account A-Z
      $result = $result | Sort "SamAccountName"

      # This line will not show script running.
      #$result | ft -auto

      $table += $result | ConvertTo-Html -Fragment

      $format=
      "

      BODY{font-family: Calibri; font-size: 12pt;}
      TABLE{border: 1px solid black; border-collapse: collapse; font-size: 12pt; text-align:center;margin-left:auto;margin-right:auto; width='1000px';}
      TH{border: 1px solid black; background: #F9F9F9; padding: 5px;}
      TD{border: 1px solid black; padding: 5px;}
      H3{font-family: Calibri; font-size: 16pt;}

      "
      $title=
      "

      $title_html
      $company – Domain: $domain – Report Date: $date – Responsible: $team

      "

      $message = ""
      $message = $message + "BODY{font-family: Calibri;font-size:20;font-color: #000000}"
      $message = $message + "TABLE{margin-left:auto;margin-right:auto;width: 800px;border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
      $message = $message + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color: #F9F9F9;text-align:center;}"
      $message = $message + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;text-align:center;}"
      $message = $message + ""
      $message = $message + ""
      $message = $message + "ACTIVE DIRECTORY AUDIT"
      $message = $message + "Active Directory users's list: $OU"
      $message = $message + "xxx"
      $message = $message + ""

      $report = $format + $title + $table

      #–CREATE HTML—————————————————–#
      #$report | Out-File "$path\$file_html" -Encoding Utf8
      $report | Out-File $file_html -Encoding Utf8

      #–EXPOT CSV FILE
      #$result | Sort Company | Export-Csv "$path\$file_csv" -NoTypeInformation -Encoding Utf8

      $result | Sort Company | Export-Csv $file_csv -NoTypeInformation -Encoding Utf8

      #–SEND MAIL NOTIFICATION————————————–#
      $from = "xxxx.com"
      $to = @("xxxx.com")
      $subject = "$area-$title_html – $date"
      $PSEmailServer = "xxxx.com"
      #$smtp = "192.168.1.200"
      #$porta = "25"

      # We do not use smpt and port
      #Send-MailMessage -From $to -To $to -Subject $subject -Attachments $file_html,$file_csv -bodyashtml -Body $message -SmtpServer $smtp -Port $porta

      Send-MailMessage -From $from -To $to -Subject $subject -Attachments "$file_html","$file_csv" -bodyashtml -Body $message

You must be logged in to reply to this topic.