Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Data Dictionary for EIC Tables

robcivitello
New Contributor III
New Contributor III

Hello - Is there a documented Data Dictionary for the EIC database tables?  I'm specifically looking for a description of the valid values for the certification_account_entitlement1_status.certified and certification_certifier.certifer_type fields.

12 REPLIES 12

SumathiSomala
All-Star
All-Star

@robcivitello Refer the below document

Database Schema Reference (saviyntcloud.com)

If this reply answered your question, please Accept it As Solution to help others who may have a similar problem.

Regards,
Sumathi Somala

If this reply answered your question, please Accept As Solution and give Kudos.

robcivitello
New Contributor III
New Contributor III

Hello - i marked as solved in haste.  Looking through the document it appears that the "Fields and Descriptions of Key Tables"  section is incomplete.  Is there information available for fields and descriptions of the certification_certifier and certification_account_entitlement1_status tables?

 
 
TABLE_NAME COLUMN_NAME IS_NULLABLE DATA_TYPE COLUMN_TYPE
certification_certifierCERT_CERTIFIERKEYNObigintbigint(20)
certification_certifieraccountkeyYESbigintbigint(20)
certification_certifierCERTIFIERNObigintbigint(20)
certification_certifierCERTIFIER_TYPENObigintbigint(20)
certification_certifierCERTKEYNObigintbigint(20)
certification_certifierENTKEYYESbigintbigint(20)
certification_certifierNOTIFICATIONSENTYESbitbit(1)
certification_certifierrolekeyYESbigintbigint(20)
certification_certifierSTATUSYESbigintbigint(20)
certification_certifierUSERKEYNObigintbigint(20)
certification_certifiercustom_propertyYESvarcharvarchar(255)
certification_certifiercertification_role_user_account_keyYESvarcharvarchar(255)
certification_certifierendpointkeyYESbigintbigint(20)
certification_certifierISSECONDARYCERTIFIERYESbitbit(1)
certification_certifierELASTIC_IDYESvarcharvarchar(255)
certification_certifierPARENT_ES_IDYESvarcharvarchar(255)
certification_certifierPARENT_ENABLEDYESbitbit(1)

 

 
 
TABLE_NAME COLUMN_NAME IS_NULLABLE DATA_TYPE COLUMN_TYPE
certification_account_entitlement1_statusCERTKEYNObigintbigint(20)
certification_account_entitlement1_statusCERT_ENTITLEMENT_VALUEKEYNObigintbigint(20)
certification_account_entitlement1_statusCERT_ACCOUNTKEYNObigintbigint(20)
certification_account_entitlement1_statusACCESSYESvarcharvarchar(255)
certification_account_entitlement1_statusARSTASKKEYYESbigintbigint(20)
certification_account_entitlement1_statusASSIGNEDFROMCOMPROLEYESbitbit(1)
certification_account_entitlement1_statusASSIGNEDFROMROLEYESbigintbigint(20)
certification_account_entitlement1_statusASSIGNEDFROMRULEYESvarcharvarchar(255)
certification_account_entitlement1_statusASSIGNEDFROMCHILDYESbitbit(1)
certification_account_entitlement1_statusAUDIT_TRAILYESlongtextlongtext
certification_account_entitlement1_statusCERTIFIEDYESbigintbigint(20)
certification_account_entitlement1_statusCOMMENTSYESlongtextlongtext
certification_account_entitlement1_statusENDDATEYESdatetimedatetime
certification_account_entitlement1_statusJRMRULESYESvarcharvarchar(255)
certification_account_entitlement1_statusLASTCERTIFIEDCAMPAIGNNAMEYESvarcharvarchar(255)
certification_account_entitlement1_statusLASTUSEDENDDATEYESdatetimedatetime
certification_account_entitlement1_statusRISKSYESlongtextlongtext
certification_account_entitlement1_statusSTARTDATEYESdatetimedatetime
certification_account_entitlement1_statusTASKSTATUSNObigintbigint(20)
certification_account_entitlement1_statusUPDATEDATEYESdatetimedatetime
certification_account_entitlement1_statusUPDATEUSERYESbigintbigint(20)
certification_account_entitlement1_statusLASTCERTIFIEDDATEYESdatetimedatetime
certification_account_entitlement1_statusLASTCERTIFIEDUSERYESvarcharvarchar(255)
certification_account_entitlement1_statusTASKIDYESbigintbigint(20)
certification_account_entitlement1_statusISREASSIGNEDYESbitbit(1)

 


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

HI Rushikesh - what I'm looking for is a description of valid values for the following fields:

certification_certifierCERTIFIER_TYPENObigintbigint(20)

 

certification_account_entitlement1_statusCERTIFIEDYESbigintbigint(20)

Update: 11/08/2023

I would also like to know the valid values for :

certification_role_user_account_status.certified

I am trying to write queries to capture certification progress and the values of the certified column are confusing.  In some cases I am seeing that a value of 0 corresponds to Rejected, and in other cases a value of '2' seems to correspond to rejected. 

It would be extremely helpful if the data dictionary included valid field values.  

Hi @robcivitello ,

We were able to find information for certification_account_entitlement1_status and certification_role_user_account_status.

 

certification_account_entitlement1_status

WHEN CERTIFIED=2 THEN 'CERTIFY'
WHEN CERTIFIED=3 THEN 'REVOKE/REJECTED'
WHEN CERTIFIED=5 THEN 'CONDITIONALLY CERTIFY'
WHEN CERTIFIED=-1 THEN 'TERMINATED'

certification_role_user_account_status

WHEN CERTIFIED=2 THEN 'CERTIFY'
WHEN CERTIFIED=3 THEN 'REVOKE/REJECTED'
WHEN CERTIFIED=5 THEN 'CONDITIONALLY CERTIFY'

Can you tell me for which campaign type are you seeing the value 0 for rejected? Please share the query that results in 0 for you for a rejected response on the UI.

Thanks.

@DaanishJawed  is this be going to be updated in the document?

Regards
Manish Kumar
If the response answered your query, please Accept As Solution and Kudos
.

uthra_rahul
Saviynt Employee
Saviynt Employee

@Krishnan Please update the doc as required and notify on this thread.

robcivitello
New Contributor III
New Contributor III

@DaanishJawed - you asked for a query that shows certified value of 0 as rejected and 1 as approved.  The actual query i'm using is pretty long and complicate, but you can see the values just by looking on the certification_account_entitlement1_status table.  See below query compared with actual view of the certification through the GUI:

select certified, count(*) as occurrences from certification_account_entitlement1_status where certkey = 26652 group by certified

robcivitello_0-1699971990890.png

Also on certification_role_user_account_status I've observed by comparing to the GUI that 1 seems to be approved and 2 is rejected which also doesn't line up with the case statement above.  It seemed weird to me that the field values would not be consistent which is why i started the thread.  In your example case statement above they DO appear consistent (which is what i would have expected) but that isn't matching what is actually on the tables.

note: I've tested this against 23.6 and 23.11 and get the same results in both.

HI @robcivitello ,

  • What is the type of the campaign that you are checking for ?
  • Are you checking for an old campaign that was launched in previous versions or is this a newly launched campaign?

Thanks

robcivitello
New Contributor III
New Contributor III

I'm working on queries for Entitlement Owner, Role Owner, and User Manager.  So far I've completed Entitlement Owner and Role Owner.  I am looking at currently active (In Progress) campaigns. 

The campaigns are recently launched in their respective versions (23.11 test and 23.6 prod)

 

Hi @robcivitello ,

Please ignore my previous comment. Below is the list of values and their meanings along with the corresponding tables that you can refer for RO, UM and EO campaigns. All these tables are visible in Data Analyzer which will help you format your queries.

 

Campaign TypeAction on the UICertified Column Value(DB)Table Name
UM - Step 1Works for me2certification_user_status
 Does not work for me3certification_user_status
 Terminated4certification_user_status
UM - Step 2 (Base Accounts)Certified2certification_user_account_status
 Revoked3certification_user_account_status
UM - Step 2 (Entitlements)Certified2certification_account_entitlement1_status
 Revoked3certification_account_entitlement1_status
 Conditional Certified 5certification_account_entitlement1_status
EO - Step 1Belongs To Me1cert_ent_values_status
 Does Not Belong To Me4cert_ent_values_status
EO - Step 2Approved1certification_account_entitlement1_status
 Rejected0certification_account_entitlement1_status
Role OwnerApproved1certification_role_user_account_status
 Rejected2certification_role_user_account_status

UM - User Manager

EO - Entitlement Owner

RO - Role Owner

I have tested and validated the above values. Please let me know if you see any discrepancies.

We will work with the PM team and also include other campaign types in the above dataset.

@uthra_rahul@KrishnanPlease refer this comment for DOC update.

Thanks.

Thank you, this is what I needed!