Report Request EmployeeNumber

Question

Hello, could you guys help create a report for us to run via a schedule task.

I need something I could configure over a specific OU that will look for user objects with a "null" value in the employeeNumber attribute. I can do this somewhat easily with powershell and a CSV, but have seen you guys set it up so the script can email a HTML with these values to a particular user.

Is that something you could assist us with? Thanks!

Side Note, when are we going to be able to automate the canned reports you provide? That would be great!

Answer 1

Hello Ben,
Have a look at the following script from our repository: http://www.adaxes.com/script-repository ... k-s432.htm.

Side Note, when are we going to be able to automate the canned reports you provide? That would be great!

Starting from the next version of Adaxes, 2017.2, it will be possible with the new Reports subsystem that is currently under development.

Comments

Great, thanks! Excited for the expanded reporting functionality.

In the simplest form, I should be able to update the attributeName variable here to what attribute I want the report to run on, and place an email address in the to field. I've attempted to set this up as a custom command, configuring it to look at OU objects, and then run it off an OU and can't get it to email a report.

I also created a scheduled task and pointing it to an OU and came up blank. Thanks!

$attributeName = "employeeNumber" # TODO: modify me

# Email message settings
$to = 'Emailaddress@domain.com' # TODO: modify me
$subject = "Users whose '$attributeName' attribute is empty" # TODO: modify me
$reportHeader = "<h1><b>Users whose '$attributeName' attribute is empty</b></h1><br/>" # TODO: modify me
$table = @"
<table border="1">
    <tr>
        <th>Full name</th>
        <th>Logon name</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

# Search all users in the target object whose attribute is empty
$searcher = $Context.TargetObject
$searcher.PageSize = 500
$searcher.SearchScope = "ADS_SCOPE_SUBTREE"
$searcher.SearchFilter = "(&(sAMAccountType=805306368)(!($attributeName=*)))"
$searcher.SetPropertiesToLoad(@("userPrincipalName","cn"))

try
{
    $searchResultIterator = $searcher.ExecuteSearch()
    $searchResults = $searchResultIterator.FetchAll()

    # Build report
    if ($searchResults.Length -eq 0)
    {
        $html = $reportHeader + "<b>No users found</b>" + $reportFooter
    }
    else
    {
        foreach ($searchResult in $searchResults) 
        {
            # Add users to report
            $table += "<tr><td>" + $searchResult.Properties["cn"].Value + "</td>"
            $table += "<td>" + $searchResult.Properties["userPrincipalName"].Value + "</td>"
        }

        $html = $reportHeader + $table + "</table>" + $reportFooter
    }

}
finally
{
    # Release resources
    if ($searchResultIterator) { $searchResultIterator.Dispose() }
}

---

Hello Ben,

You've built the email message, but forgot to send it :)

Insert the following 2 lines at the very end of your script:

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

---

Yeah, thats my bad. Sorry about that.

It appears this will send one email per user object that is missing the configurable attribute. I'm wondering, would it be possible to put all of the users with the missing attribute in one single table, and email that off?

---

Hello Ben,

The script creates and emails a list of all users who don't have a certain AD attribute set. If you configure your Scheduled Task for User object type, an e-mail notification will be sent for each user in the OU you select in the Activity Scope. Configure your Scheduled Task for the Organizational Unit object type to get an e-mail with a list of users.

---

Well, here's the problem I'm trying to resolve.

I'd like to have a report that I can spin up over a particular OU that will email out a single email that lists all users in that OU who have a blank EmployeeNumber in our AD environment. I don't want to bombard them 15-20 emails for example.

My plan is to then create a security role for that allows a particular user (or group of users) to be able to write a value in the EmployeeNumber field. I'd like to sent this report out on every Friday for example...so they can circle back and update the EmployeeNumber on the couple of users created that week since we don't receive that when the user is created.

I can work on doing this via a CSV and powershell...but I like the HTML interface you offer and running all of these reports from one place.

Thanks!

---

Hello Ben,

What exactly does not meet your requirements in using a Scheduled Task and the script as we suggested in our previous posts?

If you create a Scheduled Task configured for Organizational Unit object type, there will be only one email notification sent by the script for each OU in the Activity Scope of the task. The notification will contain a full list of all users in the OU with blank property.

---

Ok, I thought that also but when I set up the scheduled task...set it to run over one OU where we have about 50+ user objects with missing EmployeeNumbers, I received one single email for each user who had a missing EmployeeNumber, and emails that contained no data. You can see from the image below that some are blank, and some contain single lines with Full name and Logon name. Thanks again for the help on this.

$attributeName = "employeeNumber" # TODO: modify me

# Email message settings
$to = 'ben.burrell@domain.org' # TODO: modify me
$subject = "Users whose '$attributeName' attribute is empty" # TODO: modify me
$reportHeader = "<h1><b>Users whose '$attributeName' attribute is empty</b></h1><br/>" # TODO: modify me
$table = @"
<table border="1">
    <tr>
        <th>Full name</th>
        <th>Logon name</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

# Search all users in the target object whose attribute is empty
$searcher = $Context.TargetObject
$searcher.PageSize = 500
$searcher.SearchScope = "ADS_SCOPE_SUBTREE"
$searcher.SearchFilter = "(&(sAMAccountType=805306368)(!($attributeName=*)))"
$searcher.SetPropertiesToLoad(@("userPrincipalName","cn"))

try
{
    $searchResultIterator = $searcher.ExecuteSearch()
    $searchResults = $searchResultIterator.FetchAll()

    # Build report
    if ($searchResults.Length -eq 0)
    {
        $html = $reportHeader + "<b>No users found</b>" + $reportFooter
    }
    else
    {
        foreach ($searchResult in $searchResults) 
        {
            # Add users to report
            $table += "<tr><td>" + $searchResult.Properties["cn"].Value + "</td>"
            $table += "<td>" + $searchResult.Properties["userPrincipalName"].Value + "</td>"
        }

        $html = $reportHeader + $table + "</table>" + $reportFooter
    }

}
finally
{
    # Release resources
    if ($searchResultIterator) { $searchResultIterator.Dispose() }
}

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

---

Could you provide us with a screenshot of the Scheduled Task you have created with the Activity Scope? We need something like the following: