pending/denied approvals report sent via email in form of a HTML table

Question

Hello everyone,

I've received a task to send a report of pending and denied approval requests of a specific task to an email of one of our managers.

Since I do not want the mail to become too long with just CN's I'd like to format it in a html table.

- The first column should contain the CN's of all the Users with a pending approval request. - The second column should contain the CN's of the users who denied the request. - The third one should contain the date/time when the users denied the request

I am at the point where i understand where the problem is, but I do not have the knowledge to make it work. The script first adds in the pending users and only afterwards he adds the declined.

I would be more then glad to learn new approaches or other solutions to such an task. Here a screenshot how it looks atm:

This is how far i came.

Import-Module Adaxes


$to = #Insert Email Here
$subject = "Montly SystemUpToDate Report"

$reportHeader = @"
<h3><b>System Up To Date Report</b></h3><br data-tomark-pass />
<table border="1">
    <tr>
        <th>Pending</th>
        <th>Denied</th>
        <th>Date of Decline</th>
    </tr>
"@

$reportFooter = @"
</table><br data-tomark-pass />

<p><i>Please do not reply to this e-mail, it has been sent to you for notification purposes only.</i></p>
"@
$numDays = 14

# Bind to the Approval Requests container
$approvalsPath = $Context.GetWellKnownContainerPath("ApprovalRequests")
$aprovalsContainer = $Context.BindToObject($approvalsPath)

# Get pending Approval Requests
$pendingrequests = $aprovalsContainer.GetApprovalRequests("ADM_APPROVALSTATE_PENDING")

# Get declined Approval Requests
$deniedrequests = $aprovalsContainer.GetApprovalRequests("ADM_APPROVALSTATE_DENIED")

$modificationDate = (Get-Date).AddDays(-$numDays)

    # Find pending requests in the last 15 days
    foreach ($requestID in $pendingrequests)
    {
        $guid = New-Object "System.Guid" (,$requestID)
        $guid = $guid.ToString("B")
        $requestPath = "Adaxes://<GUID=$guid>"
        $request = $Context.BindToObject($requestPath)

        $requestModificationDate = ($request.Get("whenChanged")).ToLocalTime()

        if ($requestModificationDate -lt $modificationDate)
        {
            continue # within in time-frame
        }

        $requestOperation = $request.DescriptionOfOperationToApprove

        if ($requestOperation -match "Is system up to date")
        {
           # Add requests to the message
           #$pendingTasks += "<li>Operation: $requestOperation<br data-tomark-pass /> Date: $requestModificationDate<br data-tomark-pass />" 

            $TargetObjectPending = $request.TargetObject.Name
            $reportHeader += "<tr><td>" + $TargetObjectPending + "</td>"

        }



    }

    # Find denied requests in the last 15 days
    foreach ($requestID in $deniedrequests)
    {

        $guid = New-Object "System.Guid" (,$requestID)
        $guid = $guid.ToString("B")
        $requestPath = "Adaxes://<GUID=$guid>"
        $request = $Context.BindToObject($requestPath)

        $requestModificationDate = ($request.Get("whenChanged")).ToLocalTime()

        if ($requestModificationDate -lt $modificationDate)
        {
            continue # Approved in time-frame
        }

        $requestOperation = $request.DescriptionOfOperationToApprove

        if ($requestOperation -match "Is system up to date")
        {
            # Add requests to the message
            # $deniedTasks += "<li>Operation: $requestOperation<br data-tomark-pass /> Date: $requestModificationDate<br data-tomark-pass />"

            $TargetObjectDenied = $request.TargetObject.Name
            $reportHeader += "<tr><td></td><td>" + $TargetObjectDenied + "</td><td>" + $requestModificationDate + "</td>"

        }



    }

#Build report
$report = $reportHeader + $reportFooter
# Send Mail
$Context.SendMail($to, $subject, $NULL,  $report)

asked Apr 7, 2020 by rshergh (110 points)

Hello,

as background information. We need an conformation from every user that their Computer is up to date. For this we have set up an scheduled task which runs once a month. Initiator of this request is in this case our admin user. The user accepts/declines this request once a month. Now at the end of the month we want to receive an report of the users which are still pending and the ones who declined.

With following if statement I'm filtering only the requests i need.

        $requestOperation = $request.DescriptionOfOperationToApprove

        if ($requestOperation -match "Is system up to date")

So in the first column would be a list of names of the users who are still pending . Format prefereable only the name if possible.
Column Pending and Column Denied are both approvers, since the initiator is the admin user.

commented Apr 7, 2020 by rshergh (110 points)

1 Answer

Best answer

Hello,

Thank you for the detailed explanation. Here is the script for the report. In the script:

$numDays - Specifies the number of days during which an approval request should be created/process for its approver to be included into the report.
$operationDescriptionToSearch - Specifies a text that should be included into the approval request operation description for its approver to be included into the report.
$to - Specifies the email address of the email notification recipient.
$subject - Specifies the email notification subject.
$reportHeader - Specifies the header of the email notification.
$htmlTableColumns - Specifies report columns.
$reportFooter - Specifies the footer of the email notification.

$numDays = 14 # TODO: modify me
$operationDescriptionToSearch = "Is system up to date" # TODO: modify me

# E-mail settings
$to = "recipient@domain.com" # TODO: modify me
$subject = "Approval Requests report" # TODO: modify me
$reportHeader = "<h2>Approval Requests report</h2>"
$htmlTableColumns = "
<table border='1' width='100%%'>
    <tr>
        <th>Pending</th>
        <th>Denied</th>
        <th>Date of Decline</th>
    </tr>
" # TODO: modify me
$reportFooter = "<hr /><p><i>Please do not reply to this e-mail, it has been sent to you for notification purposes only.</i></p>" # TODO: modify me

# Build filter
$filter = New-Object "System.Text.StringBuilder"
[void]$filter.Append("(&(objectClass=adm-ApprovalRequest)(adm-DescriptionOfOperationToApprove=*$operationDescriptionToSearch*)(|(adm-ApprovalState=0)(adm-ApprovalState=2))")

# Add time limit
$startTime = [System.DateTime]::UtcNow.AddDays(-$numDays)
$dateGenerilized = [Softerra.Adaxes.Utils.Transform]::ToGeneralizedTime($startTime)
[void]$filter.Append("(whenChanged>=$dateGenerilized)")

# Finish building filter
[void]$filter.Append(")")

# Bind to the 'Approval Requests' container
$path = $Context.GetWellKnownContainerPath("ApprovalRequests")
$searcher = $Context.BindToObject($path)

# Set search parameters
$searcher.SearchFilter = $filter.ToString()
$searcher.SearchScope = "ADS_SCOPE_SUBTREE"
$searcher.PageSize = 500

try
{
    # Find Approval Requests
    $searchResultIterator = $searcher.ExecuteSearch()
    $searchResults = $searchResultIterator.FetchAll()

    $pendingRequests = New-Object System.Collections.ArrayList
    $deniedRequests = New-Object System.Collections.ArrayList
    foreach ($searchResult in $searchResults)
    {
        $request = $Context.BindToObject($searchResult.AdsPath)
        if ($request.ApprovalState -eq "ADM_APPROVALSTATE_PENDING")
        {
            $approversInfo = $request.GetApproversInfo()
            $approvers = $approversInfo.GetApproversEx($request.Requestor, $request.TargetObject)
            foreach ($approver in $approvers)
            {
                $name = $approver.Get("Name")
                [void]$pendingRequests.Add($name)
            }
        }
        elseif ($request.ApprovalState -eq "ADM_APPROVALSTATE_DENIED")
        {
            $name = $request.ProcessedBy.Get("Name")
            $date = $request.Get("whenChanged").ToLocalTime()
            [void]$deniedRequests.Add(@{Name=$name;Date=$date})
        }
    }
}
finally
{
    # Release resources
    $searchResultIterator.Dispose()
}

# Build html
$recordsCount = ($pendingRequests.Count, $deniedRequests.Count | Measure -Maximum).Maximum
$html = New-Object System.Text.StringBuilder
[void]$html.Append($reportHeader)
[void]$html.Append($htmlTableColumns)
for ($i = 0; $i -lt $recordsCount; $i++)
{
    $record = New-Object System.Text.StringBuilder
    [void]$record.Append("<tr>")
    [void]$record.Append("<td>$($pendingRequests[$i])</td>")
    [void]$record.Append("<td>$($deniedRequests[$i].Name)</td>")
    [void]$record.Append("<td>$($deniedRequests[$i].Date)</td>")
    [void]$record.Append("</tr>")
    [void]$html.Append($record.ToString())
}
[void]$html.Append("</table>")
[void]$html.Append($reportFooter)

# Send mail
$Context.SendMail($to, $subject, $NULL, $html.ToString())

pending/denied approvals report sent via email in form of a HTML table

Please log in or register to add a comment.

Please log in or register to answer this question.

1 Answer

Please log in or register to add a comment.

Related questions