Author Posts

April 25, 2018 at 6:38 pm

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.

April 25, 2018 at 7:02 pm

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

April 25, 2018 at 7:03 pm

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 😀

April 25, 2018 at 7:32 pm

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