send email if extensionAttribute equals

Question

I'm using two separate powershell scripts. The first one manually defines a date for the specified user's extensionAttribute15. The second one we intend to invoke via schedule to send an email 14 days from the extensionAttribute15 date but I'm getting "Error parsing query". It still sends the email but the date reference isn't functional.

First script is:

    $username = Read-Host 'Enter username'
    $ADuser = Get-ADUser -Filter 'sAMAccountName -eq $username'
    $string = Read-Host = 'Please enter a date using the format MM/DD/YYYY'
    $Date= [DateTime] $string
    Write-Host $date -ForegroundColor DarkYellow
    set-aduser $username -replace @{extensionattribute15="$Date"}
    Get-ADUser -Identity $username    -Properties * | select extensionattribute15

Second script is:

import-module activedirectory
#Show me who
Get-ADUser -filter {extensionAttribute15 -eq (Get-Date).adddays(14) -and Description -like 'Test Do not modify' -and Enabled -eq $True} -Properties * | select CN, extensionAttribute15
$users = Get-ADUser -filter {extensionAttribute15 -eq (Get-Date).adddays(14) -and Description -like 'Test Do not modify' -and Enabled -eq $True} -Properties * | select CN, extensionAttribute15
$users | Foreach-Object{
    $message = (Get-Content "C:\Test\reminder.htm" | Out-String )
    $message = $message -replace "USRname",$_.GivenName
    $message = $message -replace "USRalias",$_.SamAccountName
    $message = $message -replace "USRemail",$_.EmailAddress
    
    ### SMTP Mail Settings
    $SMTPProperties = @{
        To = $_.EmailAddress
        From = "me@org.org"
        Subject = "Reminder: Action Required"
        SMTPServer = "mail.org.org"
    }
    Send-MailMessage @SMTPProperties -Body $message -BodyAsHtml
}

How can I best define an extensionattribute as a date then use it for invoking an email at a future date?

Thanks!

Answer 1

$date = (Get-Date).date

$usersToActive = Get-ADUser -Filter "extensionAttribute15 -like '*'" -Properties extensionAttribute15 | where-object { [datetime]::Parse($_.extensionAttribute15).AddDays(-14) -eq $date }

Looking for users with something in attribute and then 14 days in the future.

via.

Answer 2

You can format the dates how you like in the extensionAttribute15, so if you prefer format MM/dd/yyyy, then that's fine as long as you parse them out in the exact same manner.

In script 1, change

Set-ADUser $username -replace @{extensionattribute15="$Date"}

to

# make sure the formatting is exactly how you want to parse it later
$dateToInsert = '{0:MM/dd/yyyy}' -f $Date
Set-ADUser $username -replace @{extensionattribute15=$dateToInsert}

Then use script 2 like:

$refDate = (Get-Date).AddDays(14).Date   # 14 days from now
$message = Get-Content 'C:\Test\reminder.htm' -Raw
$filter  = "extensionAttribute15 -like '*' -and Description -like '*Test Do not modify*' -and Enabled -eq 'True'"
# or use -LDAPFilter "(&(extensionAttribute15=*)(description=*Test Do not modify*)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))"

Get-ADUser -Filter $filter -Properties extensionAttribute15, EmailAddress | 
    Where-Object { [datetime]::ParseExact($_.extensionAttribute15, 'MM/dd/yyyy', $null) -eq $refDate } | Foreach-Object {
    Write-Host "Sending email to user $($_.Name)"
    ### SMTP Mail Settings
    $SMTPProperties = @{
        To         = $_.EmailAddress
        From       = 'me@org.org'
        Subject    = 'Reminder: Action Required'
        SMTPServer = 'mail.org.org'
        # you can chain multiple -replace
        Body       = $message -replace 'USRname', $_.GivenName -replace 'USRalias', $_.SamAccountName -replace 'USRemail', $_.EmailAddress
        BodyAsHtml = $true
    }
    Send-MailMessage @SMTPProperties
}

---

If you want to make sure you get a warning about users with a value in their extensionAttribute15 property that is NOT in date format MM/dd/yyyy, you can change the code for script #2 into:

$refDate = (Get-Date).AddDays(14).Date   # 14 days from now
$message = Get-Content 'C:\Test\reminder.htm' -Raw
$filter  = "extensionAttribute15 -like '*' -and Description -like '*Test Do not modify*' -and Enabled -eq 'True'"
# or use -LDAPFilter "(&(extensionAttribute15=*)(description=*Test Do not modify*)(!(userAccountControl:1.2.840.113556.1.4.803:=2)))"

Get-ADUser -Filter $filter -Properties extensionAttribute15, EmailAddress | Foreach-Object {
    $username = $_.Name  # capture these properties in case we hit the catch block
    $extn15   = $_.extensionAttribute15
    try {
        if ([datetime]::ParseExact($extn15, 'MM/dd/yyyy', $null) -eq $refDate) {
            Write-Host "Sending email to user $($_.Name)"
            ### SMTP Mail Settings
            $SMTPProperties = @{
                To         = $_.EmailAddress
                From       = 'me@org.org'
                Subject    = 'Reminder: Action Required'
                SMTPServer = 'mail.org.org'
                # you can chain multiple -replace
                Body       = $message -replace 'USRname', $_.GivenName -replace 'USRalias', $_.SamAccountName -replace 'USRemail', $_.EmailAddress
                BodyAsHtml = $true
            }
            Send-MailMessage @SMTPProperties
        }
    }
    catch {
        # inside a catch block the $_ automatic variable represents the actual exception object
        Write-Warning "User $username has a wrong date format in extensionAttribute15: '$extn15'"
    }            
}

With that, you should be able to see which of the users cause the error message and you can see exactly what is in that property. For extra clarity, I have single-quoted the value of the property in the warning message, so you will also be able to spot extraneous whitespaces that could trigger the error.