Wednesday, May 30, 2018

Account Auditing in Active Directory

A couple of months ago, I implemented a workstation logon restriction GPO that would only allow users who were a member of a particular AD Group to logon to a managed workstation in my Division.

To seed this list of names, I ran a SQL statement in our Student SIS (Banner 8) to get a list of active staff members. I dumped this from Toad into Excel and passed it off to my Staff to populate our new AD group from this list.

At the end of the Spring semester we often have several Student Employees leave (no one tells us) so I ran a simple Powershell script against this AD Group to get a extract (csv) of logon IDs

Import-Module ActiveDirectory

$Groups = @("EM-Everyone-GG")

$Table = @()

$Record = [ordered]@{
"Group Name" = ""
"Name" = ""
"Username" = ""

Foreach ($Group in $Groups)

$Arrayofmembers = Get-ADGroupMember -identity $Group | select name,samaccountname

foreach ($Member in $Arrayofmembers)
$Record."Group Name" = $Group
$Record."Name" = $
$Record."UserName" = $Member.samaccountname
$objRecord = New-Object PSObject -property $Record
$Table += $objrecord



$Table | export-csv "C:\temp\EMEveryone.csv" -NoTypeInformation

to import into Banner and see if these are active staff in our Division with the following SQL:

       gzbpfpm_LAST_NAME AS LAST_NAME,
       gzbpfpm_FIRST_NAME AS FIRST_NAME,
       gzbpfpm_EMAIL_ADDRESS AS EMAIL,
       gzbpfpm_PRIMARY_HR_ROLE AS ROLE,
       gzbpfpm_ORGN_CODE_HOME AS ORG_HOME,
       gzbpfpm_ORGN_CODE AS ORG_CODE,
       gzbpfpm_ORGN_NAME AS ORG_NAME,
       gzbpfpm_JOB_TITLE AS TITLE,
       gzbpfpm_EMPL_STATUS AS STSTUS,
       UPPER(ad_user) as AD_NETID,
  FROM gzbpfpm,
       (  SELECT UPPER (ad_user) AS AD_USER,
                 LISTAGG (ad_role, '; ') WITHIN GROUP (ORDER BY ad_role)
                    AS ad_roles
            FROM em_ob_sec_ad
        GROUP BY ad_user) AD
 WHERE UPPER(ad_user) = UPPER(gzbpfpm_NETID(+));

As I was filtering the results, this one account stuck out at me:


The Description on this account stated: "LDAP auth acct for email Admin's server" and the account was created about 3 years ago.

Not having any documentation on this account that it was created by us or used for any service we support, I opened a Help Desk ticket with our Core-IT Services Team for additional information.

They were able to provide me with log information that showed that one of my Staff members did add this to our AD group - and the date. The date coincided with the time we prepopulated the group from my SQL extract. Hmmm...

It was now looking like we just fat-fingered the name when mass-adding logon IDs to this AD group. But, even better, THIS is probably what happened.

The Excel worksheet that was provided from Banner did not have any Login ID that started with email - BUT, the column header in the worksheet was named "EMAIL"

Sure enough, if I manually add a Login ID to this group in AD and just type EMAIL, only one account resolves to it: EMAILAUTHACCOUNT. What must have happened was that when selecting the rows, the header row must have been copied with the Login IDs and resolved to this account.

Mystery solved!


  1. The blog is really nice and interesting to read about various ways. we provide smsf auditor service in Australia at affordable prices. for more info visit our website.

  2. Clearly, It is an engaging blog for us that you have provided here about SMSF Auditor This is a great resource to enhance our knowledge about it. Thank you.