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" = $Member.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:

SELECT gzbpfpm_NETID AS NETID,
       gzbpfpm_LAST_NAME AS LAST_NAME,
       gzbpfpm_FIRST_NAME AS FIRST_NAME,
       gzbpfpm_EMAIL_ADDRESS AS EMAIL,
       gzbpfpm_CAMP_PHONE_AREA AS AREA_CODE,
       gzbpfpm_CAMP_PHONE_NUMBER AS CAMPUS_PHONE,
       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,
       AD.AD_ROLES AS AD_ROLES
  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:

EMAILAUTHACCOUNT

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!


Thursday, July 7, 2016

Oracle Audit or Review?


Whether they call it a Review or something else, it feels like, looks like - an Audit. Before you fill out any forms or run any scripts on your system at Oracle's request, please seek the assistance of a third-party beforehand. The saying: "What you say can be used against you" holds true here and like having legal counsel to represent you - you will want someone to explain the process to you from the Oracle side and give you the confidence to push back.

A third-party can help you understand areas such as:

  • Your licensing inventory
  • What is installed where
  • Understanding metrics
  • Virtualization impacts
  • Mismanaged fault tolerance
  • Unintentional license misuse 


Here are some providers:

Palisade Compliance:
http://palisadecompliance.com/oracle-license-audit/

Madora Consulting:
http://madora.co.uk/licence-health-check/


I will list others as they become known. I do not endorse any of these so perform the same due diligence you would when selecting legal counsel.

Oracle Audit or Review?


Whether they call it a Review or something else, it feels like, looks like - an Audit. Before you fill out any forms or run any scripts on your system at Oracle's request, please seek the assistance of a third-party beforehand. The saying: "What you say can be used against you" holds true here and like having legal counsel to represent you - you will want someone to explain the process to you from the Oracle side and give you the confidence to push back.

A third-party can help you understand areas such as:

  • Your licensing inventory
  • What is installed where
  • Understanding metrics
  • Virtualization impacts
  • Mismanaged fault tolerance
  • Unintentional license misuse 


Here are some providers:

Palisade Compliance:
http://palisadecompliance.com/oracle-license-audit/

Madora Consulting:
http://madora.co.uk/licence-health-check/


I will list others as they become known. I do not endorse any of these so perform the same due diligence you would when selecting legal counsel.