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.
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.
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
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
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
Please see what I have… i just would like to send just one email to the manager.
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)
#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}})
$result = $result | Sort “SamAccountName”
#$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”
#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