DEPRECATED PAGE.

Please use this instead:

    http://iamweb1.iam.gatech.edu/docs/services/GTED-Datawarehouse-tables




Some GTED people and account data is extracted to the Data Warehouse regularly and is available by SQL.

Basic GTED people and account data is exported hourly from GTED into comma separated files and uploaded into sql tables in the data warehouse (currently once per day). These table names in the data warehouse are:

             Table or View  Status  Update frequency 
 GTED_ACCOUNTS                                         Current               nightly
 GTED_ACCTS_CATEGORIES  Current  nightly
 GTED_ACCTS_ENTITLEMENTS  Current   nightly
 GTED_PEOPLE    deprecated  nightly
 GTED_PEOPLE_AFFILIATION  Current   nightly
 GTED_PEOPLE_EMAIL  Current   nightly
 GTED_PEOPLE_SCOPED_AFFILIATION            Current  nightly
 GTED_PEOPLE_TITLES  Current  nightly
 PEOPLE_ENTITLEMENTS  New  hourly
 PEOPLE2  Deprecated  nightly
 PEOPLE3  New  hourly
 ACCOUNT2  View  
 PEOPLE_EMAIL2  View                                            
 PEOPLE_GTCOURSEINFORMATION  New 2016/IAM Internal use   hourly
 PEOPLE_CURRICULUM   New 2016/IAM Internal use  hourly
 PEOPLE_INTERNAL_P_ENTITLEMENT  New/2017/IAM Internal Use  hourly
     

  

See below for details on schema for these tables.

In addition there are new views that are helpful, using the same key as PEOPLE2 or PEOPLE3:

  ACCOUNT2 (like GTED_ACCOUNTS but using index of  GTPERSONDIRECTORYID  )
  PEOPLE_EMAIL2 (like GTED_PEOPLE_EMAIL but using index of  GTPERSONDIRECTORYID  )
  INDEXVIEW (includes only identifiers like GTPERSONDIRECTORYID,GTGTID, GTPIDM,GTIMSID,GTEMPLID,GTMAGEPERSONINDEX ,GTMAGEGUESTINDEX ,GTGUESTIDENTITYSPONSOR,GTACCESSCARDNUMBER from the PEOPLE2 table)

Access to this data can be requested via the normal request method by filling out this form:

     https://iam.gatech.edu/gted/gted_request_form.html

This will ensure that data stewardship is considered, and a service ticket is created for tracking purposes. To use sql access to the data warehouse tables, you must choose SQL on this from. Other types of access are LDAP or CAS SAML. 

The older tables are created nightly from files exported from GTED (GT enterprise directory). These are truncated and rebuilt nightly, so there are short time periods where they are empty. However, the newer tables are being updated hourly, and eventually all of these tables will be replaced with thsi new process.


 GTED_ACCOUNTS 

  
U_ID    					VARCHAR2(300)
PERSON_DIRECTORY_ID    	VARCHAR2(300)
UID_NUMBER    			VARCHAR2(70)
REPRESENTS_PERSON_FLAG 	VARCHAR2(12)    
  
These are what the fields mean:

   u_id is the user name ex jb123 gtg123 jsmith5
   Person Directory Id is the unique identifier for the account's owner, in GTED
   uid_Number is the numeric unix uid for the account
   representsPerson is false for service accounts, and true for accounts that represent a person.         

 GTED_ACCOUNTS_CATEGORIES
 U_ID   VARCHAR2(300)
 ACCOUNT_CATEGORY   VARCHAR2(300)

 GTED_ACCOUNTS_ENTITLEMENTS
 U_ID   VARCHAR2(300)
 ENTITLEMENT   VARCHAR2(300)
Each account may have multiple entitlements in GTED, so will have multiple rows in this table.
ex
sak-student4,"/gt/services/central/spectrum/enabled"
sak-student4,"/gt/services/central/lawn/enabled"
sak-student4,"/gt/services/central/sakai/enabled"
sak-student5,"/gt/services/central/gtad/enabled"
sak-student5,"/gt/services/central/spectrum/enabled"
sak-student5,"/gt/services/central/lawn/enabled"
sak-student5,"/gt/services/central/sakai/enabled"

 ACCOUNTS2 

  
 U_ID    VARCHAR2(300)
 GTPERSONDIRECTORYID    VARCHAR2(300)
 UID_NUMBER    VARCHAR2(70)
 REPRESENTS_PERSON_FLAG     VARCHAR2(12)
    
 GTED_PEOPLE (deprecated, please use PEOPLE3)
 GTID   VARCHAR2(9)
 EMAIL   VARCHAR2(200)
 PRIMARY_AFFILIATION   VARCHAR2(70)
 PERSON_DIRECTORY_ID   VARCHAR2(300)
 PRIMARY_GTACCOUNT_USERNAME   VARCHAR2(300)
 MAGE_GUEST_INDEX   VARCHAR2(70)
 CONFIDENTIALITY_FLAG   VARCHAR2(20)

Each field is double quoted and there is a comma between each field. If the data does not exist for the person, an empty field is generated. Here is an example where the confidential flag is set:
  "900055555","ex16@gatech.edu","member","B0FA68A4D594FFCAA028585966666666","ex16","","YES"

or not set:
 "900055555","ex16@gatech.edu","member","B0FA68A4D594FFCAA028585966666666","ex16","",""

Here is an example of a guest, which doesn't have that flag set: 
 "","","guest","FA24C7191AB331ABDD8C936341111111","msmith3","81431",""
 ^^ and has no gtid either. Guests do not have GTIDs. A guest's primary affiliation, is of course "guest".

 These are what the fields mean : 
  gtid is the GT id from Banner (empty for guests)
  email is the primary email address in GTED
  Primary Affiliation is from GTED eduPersonPrimaryAffiliation, and is their strongest affiliation with GT. 
      ex. staff, student, guest, affiliate, employee,faculty, member, retiree
      Students: students that don't work at GT or that areGRA, GTA, Student Assistants, etc
      Staff/Employee: People who work at GT that donot have student titles
      Faculty: People marked as faculty in PeopleSoft
      Members are those without stronger affiliationsabove.
  PERSON_DIRECTORY_ID from GTED gtPersonDirectoryId is the primary person identifier which unifies Guests and people with GTIDs. This is the one identifier that all people objects have in GTED.
  PRIMARY_GTACCOUNT_USERNAME from GTED gtPrimaryGTAccountUsername is the person's primary GT account username. A person can possess many accounts, but this is their main account for their main role at GT. 
  MAGE_GUEST_INDEX from GTED gtMageGuestIndex is the Guest identifier from Mage (empty forpeople with GTIDs)
  CONFIDENTIALITY_FLAG from GTED gtStudentConfidentiality will be YES for students with the registrar confidentiality flag set in Banner
 

 GTED_PEOPLE_AFFILIATION

 PERSON_DIRECTORY_ID   VARCHAR2(240)
 AFFILIATION   VARCHAR2(240)
Each person can have multiple affilations at GT, so they will have multiple lines in this table, one for each affiliation in GTED 

 GTED_PEOPLE_EMAIL
 PERSON_DIRECTORY_ID   VARCHAR2(240)
 EMAIL   VARCHAR2(540)
Ex. Each line has an ID and a quoted value of an email address. These come from GTED gtSecondaryMailAddress attributes, so is a list of all email addresses known for that person by GTED or Mage.
Each account can have multiple lines, one for each email address for this person
ex.
909470BAFFF88F56DB9F1677C0093FB0,"bryson@oit.gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"jb434@mail.gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"jb434@oit.gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"john.bryson@oit.gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"johnbryson@gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"oittest@oit.gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"pmx@mail.gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"isspam@mail.gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"notspam@mail.gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"is-spam@gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"not-spam@gatech.edu"
909470BAFFF88F56DB9F1677C0093FB0,"iam-changereview@oit.gatech.edu"

 GTED_PEOPLE_SCOPED_AFFILIATION
 PERSON_DIRECTORY_ID   VARCHAR2(240)  SCOPED_AFFILIATION   VARCHAR2(240) 
 GTED_PEOPLE_TITLES
 PERSON_DIRECTORY_ID   VARCHAR2(240)  TITLE   VARCHAR2(540)
Ex. Each record or line will have and id and a quoted title from GTED. Each person can have multiple lines, one for each title in GTED (from Peoplesoft HR)
ex.
D94008DA1FBEEBFB934BD8679EE16FE2,"Int Tax Compliance Spec:422X56@gt"
D94008DA1FBEEBFB934BD8679EE16FE2,"Int Tax Compliance Spec:422X56@ohr"
D94008DA1FBEEBFB934BD8679EE16FE2,"Int Tax Compliance Spec:422X56@psdept 820:OHR:Human Resources, AVP"
C064DDB7351E926904BCD1F2CC3FF197,"Misc-Tech Temp:510X37@gt"
C064DDB7351E926904BCD1F2CC3FF197,"Misc-Tech Temp:510X37@psdept 114:IPST-GT:Inst of Paper Sci & Tech at GT"
78193E7460182FB3A6216CE7D0576C58,"Affiliate:995X03@gt"
78193E7460182FB3A6216CE7D0576C58,"Affiliate:995X03@me"
78193E7460182FB3A6216CE7D0576C58,"Affiliate:995X03@psdept 250:ME:Mechanical Engineering

 PEOPLE_EMAIL2
 GTPERSONDIRECTORYID   VARCHAR2(240)  EMAIL   VARCHAR2(540) 

 PEOPLE_ENTITLEMENTS
 GTPERSONDIRECTORYID  NOT NULL VARCHAR2(256) ENTITLEMENT  NOT NULL VARCHAR2(2048) 
 PEOPLE2 (deprecated, please use PEOPLE3)
 GTPERSONDIRECTORYID   VARCHAR2(256)  GTGTID    VARCHAR2(32)  GTPIDM    VARCHAR2(32)  GTIMSID   VARCHAR2(32)  GTEMPLID   VARCHAR2(32)  GTMAGEPERSONINDEX   VARCHAR2(32)  GTMAGEGUESTINDEX   VARCHAR2(32)  GTGUESTIDENTITYSPONSOR    VARCHAR2(256)  GTACCESSCARDNUMBER   VARCHAR2(256)  EDUPERSONPRIMARYAFFILIATION   VARCHAR2(256)  GTPRIMARYAFFILIATION   VARCHAR2(256)  GTLEGALCN   VARCHAR2(256)  GTLEGALDISPLAYNAME   VARCHAR2(256)  DISPLAYNAME   VARCHAR2(256)  CN   VARCHAR2(256)  SN   VARCHAR2(256)  GIVENNAME   VARCHAR2(256)  INITIALS   VARCHAR2(256)  GTEMPLOYEEHOMEDEPARTMENTNUMBER    VARCHAR2(32)  GTEMPLOYEEHOMEDEPARTMENTNAME   VARCHAR2(256)  MAIL   VARCHAR2(256)  HOMEPHONE   VARCHAR2(256)  HOMEPOSTALADDRESS   VARCHAR2(256)  TELEPHONENUMBER   VARCHAR2(256) 
 PEOPLE3

 GTPERSONDIRECTORYID   NOT NULL VARCHAR2(128)
 GTGTID     VARCHAR2(9)
 GTPIDM     NUMBER(8)
 GTIMSID    VARCHAR2(32)
 GTEMPLID    VARCHAR2(11)
 GTMAGEPERSONINDEX    NUMBER(8)
 GTMAGEGUESTINDEX    NUMBER(8)
 GTROLEINDEX    NUMBER(8)
 GTGUESTIDENTITYSPONSOR     VARCHAR2(128)
 GTACCESSCARDNUMBER    VARCHAR2(64)
 EDUPERSONPRIMARYAFFILIATION    VARCHAR2(64)
 GTPRIMARYAFFILIATION    VARCHAR2(64)
 GTLEGALCN    VARCHAR2(256)
 GTLEGALDISPLAYNAME    VARCHAR2(256)
 DISPLAYNAME    VARCHAR2(256)
 CN    VARCHAR2(256)
 SN    VARCHAR2(256)
 GIVENNAME    VARCHAR2(128)
 INITIALS    VARCHAR2(128)
 GTEMPLOYEEHOMEDEPARTMENTNUMBER VARCHAR2(32)
 GTEMPLOYEEHOMEDEPARTMENTNAME    VARCHAR2(64)
 MAIL    VARCHAR2(256)
 HOMEPHONE    VARCHAR2(256)
 HOMEPOSTALADDRESS    VARCHAR2(256)
 GTPRIEMPLOYEEGTACCOUNTUSERNAME     VARCHAR2(256)
 GTPRISTUDENTGTACCOUNTUSERNAME    VARCHAR2(256)
 GTPRIMARYGTACCOUNTUSERNAME    VARCHAR2(256)
 GTSTUDENTCONFIDENTIALITY    VARCHAR2(256)
 GTFERPACONFIDENTIALITYAPPLIES    VARCHAR2(256)
 TELEPHONENUMBER    VARCHAR2(256) 



 PEOPLE_GTCURRICULUM

 GTPERSONDIRECTORYID   NOT NULL VARCHAR2(128)  
 GTCURRICULUM                  NOT NULL  VARCHAR2(256)

 

 PEOPLE_GTCOURSEINFORMATION 


GTPERSONDIRECTORYID  NOT NULL VARCHAR2(128)

GTED_COURSE_ATTRIBUTE_NAME  NOT NULL VARCHAR2(64)

COURSE  NOT NULL VARCHAR2(256)


 PEOPLE_INTERNAL_P_ENTITLEMENT

 GTPERSONDIRECTORYID  NOT NULL VARCHAR2(128)  
 ENTITLEMENT                  NOT NULL  VARCHAR2(256)

 

 


Usage Cautions:
o Note the name of the primary identifier is different in old tables (PERSON_DIRECTORY_ID) and new ones (GTPERSONDIRECTORYID )o Guests do not have GTIDS, so to include them you must join on GTPERSONDIRECTORYID, not GTIDo Some fields are not guaranteed to have a value so you may need to do subqueries of Left Join to get all expected rows sometimes

o Some views were created to make joins easier. For example, these were created to rename PERSON_DIRECTORY_ID to GTPERSONDIRECTORYID 

create view gted.indexview as select GTPERSONDIRECTORYID,GTGTID, GTPIDM,GTIMSID,GTEMPLID,GTMAGEPERSONINDEX ,GTMAGEGUESTINDEX ,GTGUESTIDENTITYSPONSOR,GTACCESSCARDNUMBER from GTED.PEOPLE2;
create view gted.accounts2 (U_ID,GTPERSONDIRECTORYID,UID_NUMBERREPRESENTS_PERSON_FLAG)
as select * from gted.gted_people_email;
create view gted.people_email2 (GTPERSONDIRECTORYID,email) as 
select * from gted.gted_people_email;

Usage Examples:
o To get a list of employees gtid, primary username and primary email, delimited by colons, you might do this query:

SELECT gtid ||':' || primary_gtaccount_username ||':'||email||':'
FROM gted.gted_people
WHERE person_directory_id in
   (SELECT person_directory_id
    FROM GTED_PEOPLE_AFFILIATION
    WHERE affiliation in ('employee', 'retiree'))
Notice the subquery to get a list of person_directory_ids which is the key/identifier for the table of person affiliations  GTED_PEOPLE_AFFILIATION.
 
o Get a count of all people in the GTED sql table:
 select count(*) from GTED_PEOPLE;

o Get list of all gtids: (recall that guests don't have gtids):
  select gtid  from GTED_PEOPLE;

o Get a count and list of students with the confidentiality flag set:
  select count(*)  from GTED_PEOPLE where  CONFIDENTIALITY_FLAG='YES'    ;
  select gtid  from GTED_PEOPLE where  CONFIDENTIALITY_FLAG='YES'    ; 
o Get all of John's accounts:
ducos.iam.gatech.edu:  queryDW<<<"SELECT gtgtid, mail, u_id, gtemplid FROM gted.people2 LEFT JOIN gted.gted_accounts ON (gted_accounts.person_directory_id=people2.gtpersondirectoryid)  where GTGTID='901828828';" |sed 's/\t//g' 901828828john.bryson@oit.gatech.edubryson    503728 901828828john.bryson@oit.gatech.edueiscbt1     503728 901828828john.bryson@oit.gatech.edueiscbt2     503728 901828828john.bryson@oit.gatech.edueiscbt3     503728 901828828john.bryson@oit.gatech.edujb434    503728 901828828john.bryson@oit.gatech.eduiam_read    503728 901828828john.bryson@oit.gatech.edujbtwentytwocharname012    503728 901828828john.bryson@oit.gatech.edujohnb    503728 901828828john.bryson@oit.gatech.eduj    503728 901828828john.bryson@oit.gatech.edujb    503728 ducos.iam.gatech.edu:  queryDW<<<"SELECT gtgtid, mail, u_id, gtemplid FROM gted.people3 LEFT JOIN gted.gted_accounts ON (gted_accounts.person_directory_id=people3.gtpersondirectoryid)  where GTGTID='901828828';" |sed 's/\t//g' 901828828john.bryson@oit.gatech.edu bryson      503728 901828828john.bryson@oit.gatech.edu eiscbt1     503728 901828828john.bryson@oit.gatech.edu eiscbt2     503728 901828828john.bryson@oit.gatech.edu eiscbt3     503728 901828828john.bryson@oit.gatech.edu jb434     503728 901828828john.bryson@oit.gatech.edu iam_read     503728 901828828john.bryson@oit.gatech.edu jbtwentytwocharname012      503728 901828828john.bryson@oit.gatech.edu johnb     503728 901828828john.bryson@oit.gatech.edu j     503728 901828828john.bryson@oit.gatech.edu jb     503728
Notice the queries above didn't show John's service accounts. Just the accounts owned by his student or employee roles in Wand:
     Primary account: jb434       Other accounts: bryson                       eiscbt1                       eiscbt2                       eiscbt3                       iam_read                       j                       jb                       jbtwentytwocharname012                       johnb
That is correct because service accounts are not really owned by a person, but a Mage role.

o People that are required to do 2-step web logins:

SELECT DISTINCT gtgtid  
FROM people2 
JOIN gted_accounts on (gtpersondirectoryid = person_directory_id) 
JOIN gted_accts_entitlements using (u_id) 
WHERE entitlement='/gt/central/services/iam/cas/cas-duorequired-prod/enabled' 
  AND gtgtid IS NOT NULL


o People with accounts that are set up in Duo:

SELECT DISTINCT gtgtid 
FROM people2 
JOIN gted_accounts on (gtpersondirectoryid = person_directory_id) 
JOIN gted_accts_entitlements using (u_id) 
WHERE entitlement='/gt/central/services/iam/two-factor/duo-user' 
  AND gtgtid IS NOT NULL