0 votes

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: Screenshot.png

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)
by (110 points)
0

Hello,

Sorry for the confusion, but we are not sure how you need the report to look like. Could you, please, provide a live example?

Also, could you, please, specify the version of Adaxes you are currently using? For information on how to check it, have a look at the following help article: https://www.adaxes.com/help/HowDoI.ManageService.CheckAdaxesServiceVersion.html.

0

Hello again,

yes, certainly.

We are running version 3.12.17423.0 (64 bit).

If its possible I'd like that the users in column "Denied" start at the top and not leave the space empty. I've tried to replicate a example in excel. EXCEL.png

If its possible to create a report in a different im open for a different approach.

+1

Hello,

Thank you for the provided details. It is possible to create such a report. For us to provide you with the script, please, specify the following:

  • Do we understand correctly that the first and second column of report should include a user RDN (e.g. CN=John Smith)? Or it should only be the name (e.g. John Smith)?
  • Should the first column contain users that are listed as approvers for requests that were created during the specified period of time?
  • Should the second column contain users that denied at least one approval request during the specified period of time?
0

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.

1 Answer

+1 vote
by (270k points)
selected by
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())
0

Hello,

sorry that I didnt reply to you yesterday. It became late for me. I want to thank you very much!! :) Today I tried it out and worked perfectly fine!

I have one last question, can you recommend me any book or courses which could help me improve my Powershell skills?

Other then that i wish you a great day and easy work!

Best regards!

0

Hello,

It became late for me. I want to thank you very much!! :) Today I tried it out and worked perfectly fine!

Thank you for the confirmation, it is much appreciated!

I have one last question, can you recommend me any book or courses which could help me improve my Powershell skills?

Unfortunately, we cannot recommend anything specific as we are not aware of your PowerShell level and the skills you would like to improve. However, the PowerShell in a Month of Lunches books might be helpful.

Related questions

0 votes
1 answer

Bit of giving something back..., especially as it includes snippets of code that I've been given advice on...! We've piloted some code to allow users ... " Verification FAILED" } Write-Host Write-Host "Closed Server Connection" $imapStrShut Cleanup-Variables

asked Jun 17, 2013 by firegoblin (1.6k points)
0 votes
1 answer

A little bit of context: There are 3 departments that share 1 Active Directory. Now each department has its own OU. I would like to have an email sent when a user is ... if this is possible without Powershell? If not, is there a pre-existing script for this?

asked Oct 3, 2023 by Cas (100 points)
0 votes
0 answers

It would be great if we could run a report on an OU and get the following information: Computer Name Local Accounts Whether or not the account is an administrator ... this is less important. Thanks in advance. Your support team is great and appreciated.

asked Sep 8, 2021 by mikek (80 points)
0 votes
1 answer

We have four OUs in Active Directory (Pending Deletion, Disabled with Mail Delegates, Disabled with HR Extensions and Disabled_Temp_Leave) that users are moved to prior to their eventual ... past 7 days have been moved to one of 4 of these OUs. Thanks!

asked Jun 3, 2021 by RayBilyk (230 points)
0 votes
1 answer

Hello, we want to setup a scheduled report with all our teams (security groups) and their respective team-leader (specified in "managedBy" of security group). I get the name of ... the team-leader. And this is my problem... Can you help me with this problem?

asked Oct 9, 2020 by lohnag (140 points)
3,326 questions
3,026 answers
7,727 comments
544,684 users