1. Administering Oracle Advanced Authentication and Oracle Adaptive Risk Management
  2. Appendices
  3. Understanding OAA/OARM Schema Reference

D Understanding OAA/OARM Schema Reference

OAA/OARM provides you access to a rich set of forensic data to generate custom reports for investigation and analysis.

To query and generate reports on information in the OAA/OARM database schema, you can use any reporting solution, such as Oracle Business Intelligence (BI) Publisher.

This chapter contains in-depth information on database tables. It contains the following sections:

D.1 Viewing the Details of Database Tables

Learn about the specifics of each database table.

Topics

D.1.1 VCRYPT_USERS

Discover the specifics of the VCRYPT_USERS database table.

Description: Stores user information, including login ID, group ID, and creation/update timestamps. This table references the VCRYPT_USER_GROUPS table.

Database table name: VCRYPT_USERS

Primary Key: USER_ID

Database Column Name Database Column Type Description Length
USER_ID (PK) BIGINT Auto-generated user ID. 16
EXT_USER_ID VARCHAR Auto-generated unique user identifier used to correlate users with an external user store. 255
LOGIN_ID   Refers to user login ID.  
GROUP_ID BIGINT Refers to the group to which the user belongs. It references VCRYPT_USER_GROUPS#GROUP_ID.  
CREATE_TIME DATETIME Timestamp when the user record was created. 6
UPDATE_TIME TIMESTAMP Timestamp when the user record was updated. 6

D.1.2 VCRYPT_USER_GROUPS

Discover the specifics of the VCRYPT_USER_GROUPS database table.

Description: This table contains the user group details.

Database table name: VCRYPT_USER_GROUPS

Primary Key: GROUP_ID

Database Column Name Database Column Type Description Length Enum Values
GROUP_ID (PK) BIGINT Auto generated group ID 16 -
GROUP_NAME VARCHAR Name of the group used in Create Context APIs 4000 -
DESCRIPTION VARCHAR Description for this group 4000 -
CREATE_TIME DATETIME Timestamp indicating when the user record was created 6 -
UPDATE_TIME TIMESTAMP Timestamp indicating when the user record was updated 6 -
USERGROUP_TYPE_CODE INT Type of the User group - -
USERGROUP_STATUS_CODE INT Status of the User group 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
NOTES VARCHAR Note 4000 -

D.1.3 VCRYPT_TRACKER_USERNODE_LOGS

Discover the specifics of the VCRYPT_TRACKER_USERNODE_LOGS database table.

Description: Stores session information related to the user's activity, such as IP address, unique session ID (request_id), user login and group ID, actions performed, authentication activity score, and other session-related data. You can use this table to retrieve session and action information for a specific user based on a given time range.

Database table name: VCRYPT_TRACKER_USERNODE_LOGS

Primary Key: USER_NODE_LOG_ID

Database Column Name Database Column Type Description Length
USER_NODE_LOG_ID (PK) BIGINT Auto generated sequence ID 16
REQUEST_ID VARCHAR ID of the request. It is used to correlate user activity within the OARM system and is automatically generated each time a context is created. It is also referred to as contextID.

It is referenced by VT_SESSION_ACTION_MAP and VR_RULE_LOGS.

 256
EXT_SESSION_ID VARCHAR External session ID 512
CLIENT_DEVICE_ID VARCHAR ID of the device which is generated by the application. 256
REMOTE_IP_ADDR BIGINT Refers to client's IP address, which is stored in LONG format. For example, a client with IP address 123.221.111.101 is saved as 2078109541. 15
BASE_IP_ADDR BIGINT Refers to the client's IP address, which is stored by dropping the last octet and converting it to LONG format. For example, a client with IP address 123.221.111.101 is saved as 2078109440. It is referenced by VCRYPT_IP_LOCATION_MAP. 15
CLIENT_APPLICATION   Name of the client application provided when creating the context.  
NODE_ID BIGINT ID of the nodeID. 16
TRACKER_NODE_HISTORY_ID BIGINT ID of the Tracker Node History (if available). 16
USER_ID VARCHAR Automatically generated unique user identifier or user ID for linking users with an external user store. 256
USER_LOGIN_ID VARCHAR Login ID of the user. 256
USER_GROUP_ID VARCHAR Group name of the user. 256
USER_SUB_GROUP_ID VARCHAR Sub GroupID of the user if available. 256
AUTH_STATUS INT Status of the authentication. The value corresponds to one of the options in auth.status.enum. 3
CREATE_TIME DATETIME Date/time for this log. 6
UPDATE_TIME TIMESTAMP Last update time for this object. 6
EXEC_TIME TIMESTAMP The time when this request was processed. 6
IS_REGISTERED CHAR Whether this node is registered. -
SENT_DIG_SIG_COOKIE VARCHAR Digital signature cookie that was sent by the UI 128
EXPECTED_DIG_SIG_COOKIE VARCHAR Digital signature cookie that was expected by the server from the UI for this node 128
SENT_SECURE_COOKIE VARCHAR Secure cookie that was sent by the UI 128
EXPECTED_SECURE_COOKIE VARCHAR The secure cookie that was expected by the server from the UI for this node 128
AUTH_CLIENT_TYPE_CODE INT Refers to the type of authentication method used. The value corresponds to one of the options in auth.client.type.enum. 2
CLIENT_VERSION VARCHAR Version of the client used for authentication 24
DIGITAL_CLIENT_TYPE_CODE INT Type of the client used by the digital cookie client 2
DIGITAL_CLIENT_VERSION VARCHAR Version of the client used by the digital cookie client 24
SECURE_CLIENT_TYPE_CODE INT Type of the client used by the secure cookie client 2
SECURE_CLIENT_VERSION VARCHAR Version of the client used by the secure cookie client 24
DIGITAL_FP_ID BIGINT Fingerprint ID of the digital cookie request 16
FPRINT_ID BIGINT Log ID for the fingerprint 16
LOAD_DURATION INT Time taken to load the page 8
DEVICE_SCORE INT Score for the device for this login 8
PREAUTH_SCORE INT Pre Authentication score 8
POST_SCORE INT Score derived from the user authentication policy. 8
PREAUTH_ACTION VARCHAR Pre Authentication action 256
POST_ACTION VARCHAR Actions resulting from the user authentication policy. 256
CITY_SCORE INT Score for the city for this login 8
STATE_SCORE INT Score for the state for this login 8
COUNTRY_SCORE INT Score for the country for this login 8
POST_PROCESS_STATUS INT Status of the post processing 5
POST_PROCESS_RESULT INT Result of the post processing 5
LOGIN_FLAG INT Flagging this authentication 3
IS_DEVICE_DERIVED CHAR Is the device identified using derived mechanism. -
NOTES VARCHAR Note against this node 255
CACHE VARCHAR Cache data for this node log 4000
CHALLENGE_CACHE CLOB Challenge cache data -

D.1.4 VCRYPT_TRACKER_NODE

Discover the specifics of the VCRYPT_TRACKER_NODE database table.

Description: This table represents a node; device or computer.

Database table name: VCRYPT_TRACKER_NODE

Primary Keys: NODE_ID

Database Column Name Database Column Type Description Length
NODE_ID (PK) BIGINT Node ID for this node. 16
NODE_VERSION BIGINT This keeps track of how many times this node got updated. 16
CREATE_TIME DATETIME Date/time for this node. 6
UPDATE_TIME TIMESTAMP Last update time for this object. 6
RELATED_NODE_ID BIGINT Related node. 16
RELATION_TYPE INT Type of the relation. 5
DIG_SIG_COOKIE VARCHAR Digital signature cookie. 128
SECURE_COOKIE VARCHAR Secure cookie. 128
REMOTE_IP_ADDR BIGINT The IP address from where the client connected. 15
REMOTE_HOST VARCHAR The host name from where the client connected. 256
FPRINT_ID BIGINT Log ID for the fingerprint. 16
DIGITAL_FP_ID BIGINT Fingerprint ID of the digital cookie request. 16
STATUS INT Status of this device. 3
DEVICE_SCORE INT Score for the device for this login. 6
IS_DEVICE_DERIVED CHAR Is the device identified using derived mechanism. -
IS_COOKIE_DISABLED INT Is the secure cookie disabled for this device or in learn mode. 1
IS_FLASH_DISABLED INT Is the flash cookie disabled for this device or in learn mode. 1
NOTES VARCHAR Note against this message. 255
CACHE VARCHAR Cache. 4000

D.1.5 VT_USER_DEVICE_MAP

Discover the specifics of the VT_USER_DEVICE_MAP database table.

Description: This table maintains the list of devices the user is using.

Database table name: VT_USER_DEVICE_MAP

Primary Key: MAP_ID

Database Column Name Database Column Type Description Length
MAP_ID (PK) BIGINT Map ID. 16
USER_ID BIGINT ID of the user. 16
NODE_ID BIGINT ID of the node ID. 16
REQUEST_ID VARCHAR ID of the request which last updated this row. 256
CREATE_TIME DATETIME Date/time when this object was created. 6
UPDATE_TIME TIMESTAMP Last update time for this object. 6
LAST_USED_TIME DATETIME Last used time for this device. 6
LAST_AUTH_STATUS INT Last authentication status for the user using this device. 3
IS_SECURE CHAR Is this node secure for this user. -
TOTAL_COUNT INT Total authentication count for this user/device. 10
SUCCESS_COUNT INT Total success count for this user/device 10
FAILED_COUNT INT Total failed count for this user/device. 10
CACHE VARCHAR Cache 4000
IS_COOKIE_DISABLED INT Is the secure cookie disabled for this device or in learn mode. 1
IS_FLASH_DISABLED INT Is the flash cookie disabled for this device or in learn mode. 1
FPRINT_ID BIGINT Fingerprint of secure cookie. 16
DIGITAL_FP_ID BIGINT Fingerprint Id of the digital cookie request. 16

D.1.6 VT_SESSION_ACTION_MAP

Discover the specifics of the VT_SESSION_ACTION_MAP database table.

Description: This table stores information about actions generated as part of the rules. Actions are based on the request_Id from (VCRYPT_TRACKER_USERNODE_LOGS) and user activity type. This table helps in identifying all actions and scores related to sessions and custom activities.

Database table name: VT_SESSION_ACTION_MAP

Primary Key: MAP_ID

Database Column Name Database Column Type Description Length
MAP_ID (PK) BIGINT Map ID. 16
CREATE_TIME DATETIME Date/time when this object was created. 6
REQUEST_ID VARCHAR Request ID or context ID. Referenced by VCRYPT_TRACKER_USERNODE_LOGS:REQUEST_ID. 256
TRX_ID BIGINT ID of the custom activity used for processing the rules. When it is not null, it refers to VT_TRX_LOGS#LOG_ID. 16
RUNTIME_TYPE INT User or custom activity type. The value is retrieved from profile.type.enum. 6
ACTION VARCHAR Action result for the user activity. The value is retrieved from rule.action.enum. 256
ORIGINAL_ACTION VARCHAR This was the original action, which got overridden finally. 256
OVERRIDE_REASON INT Override reason. -
ACTION_LIST VARCHAR List of actions resulting from the execution of rules for the request or user activity. 256
SCORE INT Score result from user authentication policy. -
IS_FINAL_ACTION CHAR Is this final action. -
EXEC_TIME_MS   Time taken for rule execution, measured in milliseconds.  
RULE_TRACE_FP_ID   Fingerprint ID for the mapping of executed rules.  

D.1.7 VT_USER_GROUPS

Discover the specifics of the VT_USER_GROUPS database table.

Description: This table contains the user group details.

Database table name: VT_USER_GROUPS

Primary Key: LOCAL_GROUP_ID

Database Column Name Database Column Type Description Length Enum values
LOCAL_GROUP_ID (PK) BIGINT ID for the User Group. 16 -
EXT_USERGROUP_ID VARCHAR External User group ID. 255 -
DESCRIPTION VARCHAR Description for this group. 2000 -
CREATE_TIME DATETIME Date/time creation of this user. 6 -
UPDATE_TIME TIMESTAMP Date value. 6 -
USER_LIST_ID BIGINT ID of the user list. 16 -
USERGROUP_STATUS_CODE INT Status of the User group. 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
NOTES VARCHAR Note. 4000 -

D.1.8 V_FPRINTS

Discover the specifics of the V_FPRINTS database table.

Description: This table contains the fingerprints.

Database table name: V_FPRINTS

Primary Key: FPRINT_ID

Database Column Name Database Column Type Description Length
FPRINT_ID (PK) BIGINT ID for fingerprint. 16
CREATE_TIME DATETIME Date/time of this fingerprint. 6
FPRINT_TYPE INT Type of fingerprinting. 6
PATTERN_ID BIGINT ID for the pattern this maps to. 16
HASH_VALUE VARCHAR Hash value for the fingerprint. 512
DATA_VALUE VARCHAR Data value for the fingerprint. 4000

D.1.9 V_FP_NV

Discover the specifics of the V_FP_NV database table.

Description: This table refers to name value pairs in the fingerprint.

Database table name: V_FP_NV

Primary Key FP_NV_ID

Database Column Name Database Column Type Description Length
FP_NV_ID (PK) BIGINT ID for name value. 16
FPRINT_ID BIGINT ID for the fingerprint. 16
ATTR_NAME VARCHAR Name of the attribute. 64
ATTR_VALUE VARCHAR Value of the attribute. 256

D.1.10 V_FP_MAP

Discover the specifics of the V_FP_MAP database table.

Description: This table maintains the map for fingerprint.

Database table name: V_FP_MAP

Primary Key: MAP_ID

Database Column Name Database Column Type Description Length
MAP_ID (PK) BIGINT ID for map. 16
FPRINT_ID BIGINT ID for the fingerprint. 16
FPRINT_TYPE INT Type of fingerprinting. 6
ATTR_NAME VARCHAR Name of the attribute. 64
ATTR_VALUE VARCHAR Value of the attribute. 256

D.1.11 VCRYPT_COUNTRY

Discover the specifics of the VCRYPT_COUNTRY database table.

Description: This table stores country-specific information provided by the geo data provider..

Database table name: VCRYPT_COUNTRY

Primary Key: COUNTRY_ID

Database Column Name Database Column Type Description Length
COUNTRY_ID (PK) BIGINT An auto-generated unique identifier for the country. 16
COUNTRY_CODE VARCHAR Country code provided by the geo-location provider. 64
COUNTRY_NAME VARCHAR Country name provided by the geo-location provider. 4000
CREATE_TIME TIMESTAMP Date/time for this log. 6
UPDATE_TIME TIMESTAMP Last update time for this object. 6
CONTINENT VARCHAR Continent to which this country belongs to. 64
NOTES VARCHAR Notes for this country. 4000

D.1.12 VCRYPT_STATE

Discover the specifics of the VCRYPT_STATE database table.

Description: This table stores information about states provided by the geo data provider.

Database table name: VCRYPT_STATE

Primary Key: STATE_ID

Database Column Name Database Column Type Description Length
STATE_ID (PK) BIGINT An auto-generated unique identifier for the state. It references VCRYPT_CITY#STATE_ID and VCRYPT_IP_LOCATION_MAP#STATE_ID. 16
COUNTRY_ID BIGINT Refers to the country associated with this state. It references VCRYPT_COUNTRY#COUNTRY_ID. 16
STATE_CODE VARCHAR State code provided by the geo-location provider. 64
STATE_NAME VARCHAR Name of the state provided by the geo-location provider. 4000
CREATE_TIME TIMESTAMP Date/time for this log. 6
UPDATE_TIME TIMESTAMP Last update time for this object. 6
NOTES VARCHAR Notes for this state. 4000

D.1.13 VCRYPT_CITY

Discover the specifics of the VCRYPT_CITY database table.

Description: This table stores information about city provided by the geo data provider..

Database table name: VCRYPT_CITY

Primary Key: CITY_ID

Database Column Name Database Column Type Description Length
CITY_ID (PK) BIGINT An auto-generated unique identifier for the city. It references VCRYPT_IP_LOCATION_MAP#CITY_ID . 16
STATE_ID BIGINT Refers to the state associated with this city. It references VCRYPT_STATE#STATE_ID. 16
CITY_CODE VARCHAR City code provided by the geo-location provider. 64
CITY_NAME VARCHAR Name of the city provided by the geo-location provider. 4000
CREATE_TIME TIMESTAMP Date/time for this log. 6
UPDATE_TIME TIMESTAMP Last update time for this object. 6
LATITUDE VARCHAR Latitude information provided by the geo-location provider. 20
LONGITUDE VARCHAR Longitude information provided by the geo-location provider. 20
TIMEZONE VARCHAR Time zone information provided by the geo-location provider. 20
NOTES VARCHAR Notes for this city. 4000

D.1.14 VCRYPT_ISP

Discover the specifics of the VCRYPT_ISP database table.

Description: This table represents the ISP listing.

Database table name: VCRYPT_ISP

Primary Key: ISP_ID

Database Column Name Database Column Type Description Length
ISP_ID (PK) BIGINT ID for this ISP. 16
ISP_NAME VARCHAR Name of the ISP. 4000
CREATE_TIME TIMESTAMP Date/time for this log. 6
UPDATE_TIME TIMESTAMP Last update time for this object. 6

D.1.15 VCRYPT_IP_LOCATION_MAP

Discover the specifics of the VCRYPT_IP_LOCATION_MAP database table.

Description: This table stores data based on IP addresses and their associated geographic location details. It is imported from the location provider's geo-data.

Database table name: VCRYPT_IP_LOCATION_MAP

Primary Key: IP_RANGE_ID

Database Column Name Database Column Type Description Length
IP_RANGE_ID (PK) BIGINT ID for this range. 16
FROM_IP_ADDR BIGINT Refers to the starting IP address for a specific geo-location, which is stored in LONG format. For example, the IP address 100.102.34.0 is saved as 1684414976. 15
TO_IP_ADDR BIGINT Refers to the ending IP address for a specific geo-location, which is stored in LONG format. For example, a client with IP address 100.102.34.0 is saved as 1684414976. 15
CREATE_TIME TIMESTAMP Date/time for this log. -
UPDATE_TIME TIMESTAMP Last update time for this object. -
COUNTRY_ID BIGINT Refers to the country details associated with this geo-location record. It references VCRYPT_COUNTRY#COUNTRY_ID. 16
STATE_ID BIGINT Refers to the state details associated with this geo-location record. It references VCRYPT_STATE#STATE_ID. 16
CITY_ID BIGINT Refers to the city details associated with this geo-location record. It references VCRYPT_CITY#CITY_ID. 16
METRO_ID BIGINT ID of the metro for this IP. 16
ISP_ID BIGINT ID for the ISP to which this IP range belongs to. 16
ROUTING_TYPE INT IP routing type. 3
CONNECTION_TYPE INT Refers to the information about the connection type for this record. The possible values are derived from the connection.type.enum enum. 10
CONNECTION_SPEED INT Refers to the information about the connection speed for this record. The possible values are derived from the location.linespeed.enum enum. 10
TOP_LEVEL_DOMAIN VARCHAR Top level domain. 25
SEC_LEVEL_DOMAIN VARCHAR Second level domain. 128
ASN VARCHAR ASN 25
CARRIER VARCHAR Refers to the service provider details for the data. 128
ZIP_CODE VARCHAR Postal code for this location. 24
DMA INT U.S. Designated Market Area, AC Nielsen. 6
MSA INT Metropolitan Statistical Area. 6
PMSA INT Primary Metropolitan Statistical Area. 6
REGION_ID BIGINT ID of the region. 16
PHONE_AREA VARCHAR Phone area code. 10
IS_SPLIT NUMBER Is the IP split. If so, in some queries, we might have to do additional checks. 1
COUNTRY_CF INT Confidence factor of the country. 4
STATE_CF INT Confidence factor of the state. 4
CITY_CF INT Confidence factor of the city. 4
NOTES VARCHAR Notes for this IP range. 255

D.1.16 VT_TRX_DEF

Discover the specifics of the VT_TRX_DEF database table.

Description: This table defines the transaction meta data.

Database table name: VT_TRX_DEF

Primary Key: TRX_DEF_ID

Database Column Name Database Column Type Description Length Enum Values
CREATE_TIME DATETIME Date/time creation of this object. 6 -
UPDATE_TIME TIMESTAMP Date value. 6 -
TRX_DEF_ID (PK) BIGINT ID for transaction definition. 16 -
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255 -
LABEL VARCHAR Name for transaction. 4000 -
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000 -
DESCRIPTION VARCHAR Description of the object. 4000 -
DESC_RBKEY VARCHAR Resource bundle key for the description. 4000 -
TRX_DEF_KEY VARCHAR Key name to be used for the transaction, for example bill_pay, etc. This has to be passed in the handleTransactionLog API call. The context map should have an attribute key called transactionType. 4000 -
STATUS INT Status 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
NOTES VARCHAR Note for this object. 4000 -

D.1.17 VT_TRX_INPUT_DEF

Discover the specifics of the VT_TRX_INPUT_DEF database table.

Description: This table contains the definition of transaction input meta data.

Database table name: VT_TRX_INPUT_DEF

Primary Key: TRX_DEF_ID

Database Column Name Database Column Type Description Length Enum Values
CREATE_TIME DATETIME Date/time creation of this object. 6 -
UPDATE_TIME TIMESTAMP Date value. 6 -
TRX_DEF_ID (PK) BIGINT ID for transaction definition. 16 -
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255 -
LABEL VARCHAR Name for transaction. 4000 -
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000 -
DESCRIPTION VARCHAR Description of the object. 4000 -
DESC_RBKEY VARCHAR Resource bundle key for the description. 4000 -
TRX_DEF_KEY VARCHAR Key name to be used for the transaction, for example bill_pay, etc. This has to be passed in the handleTransactionLog API call. The context map should have an attribute key called transactionType. 4000 -
STATUS INT Status 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
NOTES VARCHAR Note for this object. 4000 -

D.1.18 VT_ENTITY_DEF

Discover the specifics of the VT_ENTITY_DEF database table.

Description: This table provides the definition of entity meta data.

Database table name: VT_ENTITY_DEF

Primary Key: ENTITY_DEF_ID

Database Column Name Database Column Type Description Length Enum Values
CREATE_TIME DATETIME Date/time creation of this object. 6 -
UPDATE_TIME TIMESTAMP Date value. 6 -
ENTITY_DEF_ID (PK) BIGINT ID for entity definition. 16 -
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255 -
LABEL VARCHAR Name for entity. For example address, customer. 4000 -
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000 -
DESCRIPTION VARCHAR Description of the object. 4000 -
DESC_RBKEY VARCHAR Resource bundle key for the description 4000 -
ENTITY_DEF_KEY VARCHAR Key of the entity. For example, address, merchant, etc. 256 -
STATUS INT Status 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
KEY_GEN_SCHEME INT Key generation scheme. This scheme generates a key which is unique for an entity instance. Points to an enum and supported ones are ByKey, Digest, and so on. - -
KEY_GEN_PARAMS VARCHAR Static parameters to be passed to the Java class for key generation. 4000 -
NAME_GEN_SCHEME INT Name generation scheme. This scheme generates a name which would be how the corresponding entity would be displayed throughout the application in every report. Points to an enum and supported ones are Direct, concatenate, substring, and so on. - -
NAME_GEN_PARAMS VARCHAR Static parameters to be passed to the Java class for name generation. For example, is a delimiter of ',' 4000 -
NOTES VARCHAR Note for this object. 4000 -

D.1.19 VT_TRX_ENT_DEFS_MAP

Discover the specifics of the VT_TRX_ENT_DEFS_MAP database table.

Description: This table defines the association between an entity and the transaction.

Database table name: VT_TRX_ENT_DEFS_MAP

Primary Key: MAP_ID

Database Column Name Database Column Type Description Length Enum Values
CREATE_TIME DATETIME Date/time creation of this object. 6 -
UPDATE_TIME TIMESTAMP Date value. 6 -
MAP_ID (PK) BIGINT ID for map. 16 -
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255 -
LABEL VARCHAR Name for the map. 4000 -
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000 -
DESCRIPTION VARCHAR Description of the object. 4000 -
DESC_RBKEY VARCHAR Resource bundle key for the description. 4000 -
TRX_DEF_ID BIGINT Parent data definition ID. 16 -
ENTITY_DEF_ID BIGINT Parent data definition ID. 16 -
RELATION_TYPE VARCHAR Type of the relation. 4000 -
DISP_ORDER INT Display order. 6 -
STATUS INT Status 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
NOTES VARCHAR Note for this object. 4000 -

D.1.20 VT_ENT_DEFS_MAP

Discover the specifics of the VT_ENT_DEFS_MAP database table.

Description: This table depicts the relationship between an entity and a transaction.

Database table name: VT_ENT_DEFS_MAP

Primary Key: MAP_ID

Database Column Name Database Column Type Description Length
CREATE_TIME DATETIME Date/time creation of this object. 6
UPDATE_TIME TIMESTAMP Date value. 6
MAP_ID (PK) BIGINT ID for map. 16
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255
LABEL VARCHAR Name for the map. 4000
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000
DESCRIPTION VARCHAR Description of the object. 4000
DESC_RBKEY VARCHAR Resource bundle key for the description. 4000
ENTITY_DEF_ID_1 BIGINT Parent entity definition ID of object 1. 16
ENTITY_DEF_ID_2 BIGINT Parent entity definition ID of object 2. 16
RELATION_TYPE VARCHAR Type of the relation. 4000
DISP_ORDER INT Display order. 6
NOTES VARCHAR Note for this object. 4000

D.1.21 VT_DATA_DEF

Discover the specifics of the VT_DATA_DEF database table.

Description: This table contains the definition of data meta.

Database table name: VT_DATA_DEF

Primary Key: DATA_DEF_ID

Database Column Name Database Column Type Description Length Enum Values
CREATE_TIME DATETIME Date/time creation of this object. 6 -
UPDATE_TIME TIMESTAMP Date value. 6 -
DATA_DEF_ID (PK) BIGINT ID for data definition. 16 -
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255 -
LABEL VARCHAR Name for data definition. 4000 -
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000 -
DESCRIPTION VARCHAR Description of the object. 4000 -
DESC_RBKEY VARCHAR Resource bundle key for the description. 4000 -
DATA_DEF_KEY VARCHAR Key of the data. For example, "data", "key", "name", "auto-learning," and so on. 256 -
STATUS INT Status 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
DATA_DEF_TYPE INT Type of data definition. Whether it is dynamic or static. 5 -
IS_REQUIRED CHAR Is this data required by default. - -
IS_AUTO_CREATED CHAR Whether this auto created. - -
NOTES VARCHAR Note for this object. 4000 -

D.1.22 VT_DATA_DEF_ELEM

Discover the specifics of the VT_DATA_DEF_ELEM database table.

Description: This table provides the definition of elements in data meta.

Database table name: VT_DATA_DEF_ELEM

Primary Key: DATA_DEF_ELEM_ID

Database Column Name Database Column Type Description Length Enum Values
CREATE_TIME DATETIME Date/time creation of this object. 6 -
UPDATE_TIME TIMESTAMP Date value. 6 -
DATA_DEF_ELEM_ID (PK) BIGINT ID for data definition element. 16 -
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255 -
DEF_KEY VARCHAR Key to identify this data (for example, Transaction.billingAddress.adressLine1, Transaction.amount, and so on). The destination element's keys is different from those of the source element. Within the same data definition, this key has to be unique. 256 -
LABEL VARCHAR Name for column. 4000 -
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000 -
DESCRIPTION VARCHAR Description of the object. 4000 -
DESC_RBKEY VARCHAR Resource bundle key for the description. 4000 -
DATA_DEF_ID BIGINT Parent data definition ID (data_def_id from vt_data_def). 16 -
DATA_ROW INT Row for this data element. - -
DATA_COL INT Column for this data element (starting from 1). This corresponds to the 10 data fields in the VT_TRX_DATA and VT_ENTITY_ONE_PROFILE table for destination elements. For other profile types like "key" and "name", this value determines the sort order for corresponding keygen and namegen scheme. - -
IS_ENCRYPTED CHAR Is this data element encrypted. - -
DATA_TYPE INT Type of the data (numeric/alphanumeric types). - -
DATA_FORMAT VARCHAR Format of the data (for example, mm/YY for some dates). 4000 -
STATUS INT Status 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
IS_REQUIRED CHAR Is this data required by default. - -
NAME_GEN_SCHEME INT Name generation scheme. - -
NAME_GEN_PARAMS VARCHAR Static parameters to be passed to the Java class for name generation. 4000 -
IS_AUTO_CREATED CHAR Whether this auto created. - -
NOTES VARCHAR Note for this object. 4000 -

D.1.23 VT_DATA_DEF_MAP

Discover the specifics of the VT_DATA_DEF_MAP database table.

Description: This table defines the map between the Objects and the Data Definition.

Database table name: VT_DATA_DEF_MAP

Primary Key: MAP_ID

Database Column Name Database Column Type Description Length
CREATE_TIME DATETIME Date/time creation of this object. 6
UPDATE_TIME TIMESTAMP Date value. 6
MAP_ID (PK) BIGINT ID for map. 16
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255
LABEL VARCHAR Name for the map. 4000
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000
DESCRIPTION VARCHAR Description of the object. 4000
DESC_RBKEY VARCHAR Resource bundle key for the description. 4000
DATA_DEF_ID BIGINT Parent data definition ID. 16
PARENT_OBJ_TYPE INT Type of source object (Points to an enum of types, like 3 for entity, 1 for transaction definition, and so on.) 5
PARENT_OBJECT_ID BIGINT Parent to which datadef belongs to (entity_def_id , trx_def_id). 16
RELATION_TYPE VARCHAR Type of the relation ("data", "name," and so on). 4000
NOTES VARCHAR Note for this object. 4000

D.1.24 VT_DATA_DEF_TRANS

Discover the specifics of the VT_DATA_DEF_TRANS database table.

Description: This table provides the translation from one element to another, for example input transaction to normalized transaction data or transaction to entity.

Database table name: VT_DATA_DEF_TRANS

Primary Key: ELEM_MAP_ID

Database Column Name Database Column Type Description Length Enum Values
CREATE_TIME DATETIME Date/time creation of this object. 6 -
UPDATE_TIME TIMESTAMP Date value. 6 -
ELEM_MAP_ID (PK) BIGINT ID for data definition element. 16 -
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255 -
LABEL VARCHAR Name for this data map. 4000 -
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000 -
DESCRIPTION VARCHAR Description of the object. 4000 -
DESC_RBKEY VARCHAR Resource bundle key for the description. 4000 -
TRANS_SCHEME INT Scheme for translation. The value points to an enum of different types of translation schemes. - -
TRANS_PARAMS VARCHAR Static parameters to be passed to the Java class for translation. 4000 -
SRC_OBJ_TYPE INT Type of source object. The value points to an enum for different types of source objects. For example, 3 for entity, 2 for transaction Input, and so on. 5 -
SRC_OBJ_ID BIGINT Source object Id (mostly trx_def_id of the corresponding input transaction definition). 16 -
DEST_OBJ_TYPE INT Type of destination object. The value points to an enum for different types of destination objects, like 3 for entity, 5 for transaction profile, and so on. 5 -
DEST_OBJ_ID BIGINT Destination object ID (map_id from vt_trx_ent_defs_map which denotes the particular relationship type). 16 -
RELATION_TYPE VARCHAR Type of the relation. 4000 -
STATUS INT Status 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
NOTES VARCHAR Note for this object. 4000 -

D.1.25 VT_ELEM_DEF_TRANS

Discover the specifics of the VT_ELEM_DEF_TRANS database table.

Description: This table provides the translation from one element to another, for example input transaction to normalized transaction data or transaction to entity.

Database table name: VT_ELEM_DEF_TRANS

Primary Key: DEST_MAP_ID

Database Column Name Database Column Type Description Length Enum Values
CREATE_TIME DATETIME Date/time creation of this object. 6 -
UPDATE_TIME TIMESTAMP Date value. 6 -
DEST_MAP_ID (PK) BIGINT ID for data definition element. 16 -
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255 -
LABEL VARCHAR Name for this data map. 4000 -
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000 -
DESCRIPTION VARCHAR Description of the object. 4000 -
DESC_RBKEY VARCHAR Resource bundle key for the description. 4000 -
TRANS_SCHEME INT Scheme for translation. - -
TRANS_PARAMS VARCHAR Static parameters to be passed to the Java class for translation. 4000 -
TRANS_ID BIGINT Translation ID (corresponding elem_map_id from vt_data_def_trans). 16 -
DEST_ELEMENT_ID BIGINT Destination data element ID (corresponding destination's data_def_elem_id from vt_data_def_elem). 16 -
STATUS INT Status 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
NOTES VARCHAR Note for this object. 4000 -

D.1.26 VT_TRANS_SRC_ELEM

Discover the specifics of the VT_TRANS_SRC_ELEM database table.

Description: This table contains the source columns for translation.

Database table name: VT_TRANS_SRC_ELEM

Primary Key: SRC_ELEM_ID

Database Column Name Database Column Type Description Length Enum Values
CREATE_TIME DATETIME Date/time creation of this object 6 -
UPDATE_TIME TIMESTAMP Date value 6 -
SRC_ELEM_ID (PK) BIGINT ID for data definition element. 16 -
GLOBAL_ID VARCHAR Unique identifier which is used in import and export feature. 255 -
LABEL VARCHAR Name for this data map. 4000 -
LABEL_RBKEY VARCHAR Resource bundle key for the name. 4000 -
DESCRIPTION VARCHAR Description of the object. 4000 -
DESC_RBKEY VARCHAR Resource bundle key for the description. 4000 -
TRANS_SCHEME INT Scheme for translation. - -
TRANS_PARAMS VARCHAR Static parameters to be passed to the Java class for translation. 4000 -
DEST_MAP_ID BIGINT Destination map ID. 16 -
SRC_ELEMENT_ID BIGINT Source data element ID. 16 -
SORT_ORDER INT Row for this data element - -
STATUS INT Status 2
  • STATUS_ACTIVE
  • STATUS_DISABLED
  • STATUS_DELETED
NOTES VARCHAR Note for this object 4000 -

D.1.27 VT_TRX_LOGS

Discover the specifics of the VT_TRX_LOGS database table.

Description: This table provides the transaction log.

Database table name: VT_TRX_LOGS

Primary Key: LOG_ID

Database Column Name Database Column Type Description Length
LOG_ID (PK) BIGINT Log ID. 16
CREATE_TIME DATETIME Date/time of this transaction. 6
UPDATE_TIME TIMESTAMP Last update time for this object. 6
USER_ID BIGINT ID of the user. 16
REQUEST_ID VARCHAR ID of the login session. 256
EXT_TRX_ID VARCHAR External transaction ID. 255
TRX_DEF_ID BIGINT Transaction definition ID. 16
TRX_TYPE INT Transaction type 3
STATUS INT Status of the transaction (where applicable) 5
SCORE INT Score for this transaction -
RULE_ACTION VARCHAR Action 256
TRX_FLAG INT Flagging this transaction 3
POST_PROCESS_STATUS INT Status of the post processing 5
POST_PROCESS_RESULT INT Status of the post processing 5
TRX_DATA VARCHAR Transaction data as name value pair. 4000
DATA1 VARCHAR Data one 256
DATA2 VARCHAR Data two 256
DATA3 VARCHAR Data three 256
DATA4 VARCHAR Data four 256
DATA5 VARCHAR Data five 256
DATA6 VARCHAR Data six 256
DATA7 VARCHAR Data seven 256
DATA8 VARCHAR Data eight 256
DATA9 VARCHAR Data nine 256
DATA10 VARCHAR Data ten 256

D.1.28 VT_TRX_DATA

Discover the specifics of the VT_TRX_DATA database table.

Description: This table contains the data associated with the transaction.

Database table name: VT_TRX_DATA

Primary Key: TRX_DATA_ID

Database Column Name Database Column Type Description Length
TRX_DATA_ID (PK) BIGINT Transaction data ID. 16
TRX_ID BIGINT ID of the transaction. 16
DATA_DEF_ID BIGINT Data definition ID. 16
ROW_ORDER INT Row order 6
CREATE_TIME DATETIME Date/time when this object was created 6
UPDATE_TIME TIMESTAMP Last update time for this object 6
DATA1 VARCHAR Data one 4000
DATA2 VARCHAR Data two 4000
DATA3 VARCHAR Data three 4000
DATA4 VARCHAR Data four 4000
DATA5 VARCHAR Data five 4000
DATA6 VARCHAR Data six 4000
DATA7 VARCHAR Data seven 4000
DATA8 VARCHAR Data eight 4000
DATA9 VARCHAR Data nine 4000
DATA10 VARCHAR Data ten 4000
NUM_DATA0 BIGINT Numeric data 0 38
NUM_DATA1 BIGINT Numeric data 1 38
NUM_DATA2 BIGINT Numeric data 2 38

D.1.29 VR_RULE_LOGS

Discover the specifics of the VR_RULE_LOGS database table.

Description: Stores information about rule executions, including their actions, scores, and alert templates.

Database table name: VR_RULE_LOGS

Primary Key: RULE_LOG_ID

Database Column Name Database Column Type Description Length
RULE_LOG_ID (PK) BIGINT Auto generated rule log ID. 16
CREATE_TIME   Time when the rule log was created.  
RULE_MAP_ID BIGINT Refers to the rule information. It references VCRYPT_PROFILE_RULE_MAP#PROFILE_RULE_MAP_ID. 16
SCORE   Score generated by the rule.  
ACTION_LIST   List of actions resulting from the execution of rules for the request or user activity.  
ALERT_TEMPL_ID_LIST   Alert templates for generating alerts.  

D.1.30 VCRYPT_ALERT

Discover the specifics of the VCRYPT_ALERT database table.

Description: Stores the generated alerts, including their contents, source, and context. Use the REQUEST_ID and SESS_ACTION_MAP_ID in this table to look up for alerts generated for a specific session/context or user activity.

Database table name: VCRYPT_ALERT

Primary Key: ALERT_ID

Database Column Name Database Column Type Description Length
ALERT_ID (PK) BIGINT Auto generated alert ID. 16
SESS_ACTION_MAP_ID BIGINT Correlation with the session action map for user activity. 16
ALERT_TYPE INT Refers to the type of alert, with possible values from the alert.type.enum property. 10
CREATE_TIME TIMESTAMP Time when the alert was created. 6
REQUEST_ID VARCHAR Request ID for the session. 256
TRX_LOG_ID BIGINT ID of the custom activity used for processing the rules. 16
PROFILE_RULE_MAP_ID BIGINT ID of the rule that generated the alert. It references VCRYPT_PROFILE_RULE_MAP#PROFILE_RULE_MAP_ID. 16
RUNTIME_TYPE INT User or custom activity type, with possible values from the profile.type.enum property. 6
USER_ID VARCHAR Auto-generated unique user identifier used to correlate users with an external user store. 256
USER_LOGIN_ID VARCHAR Refers to the user login ID. 256
REMOTE_IP_ADDR BIGINT The IP address of the client, which is stored in LONG format. For example, the IP address 123.221.111.101 is saved as 2078109541. 15
ALERT_MESSAGE VARCHAR Text of the alert message. 4000

D.2 Using Geo-Location Data

The OAA/OARM database schema includes tables that map IP address ranges to location data including city, state, and country.

The relevant tables are VCRYPT_IP_LOCATION_MAP, VCRYPT_CITY, VCRYPT_STATE, and VCRYPT_COUNTRY.

Many tables contain IP addresses, and VCRYPT_IP_LOCATION_MAP contains foreign keys to each of VCRYPT_CITY, VCRYPT_STATE, and VCRYPT_COUNTRY.

In OAA/OARM, IP addresses are stored as long numerals. The following example shows how to join a table containing an IP address to the VCRYPT_IP_LOCATION_MAP.

SELECT ...
FROM vcrypt_tracker_usernode_logs logs
      INNER JOIN vcrypt_ip_location_map loc ON (
             logs.remote_ip_addr >= loc.from_ip_addr AND logs.remote_ip_addr <=
 loc.from_ip_addr
      )

For user input and display purposes, you will typically want to use the standard four-part IP address. The following example shows how to display a numeric IP address as a standard IP, where ipField is the field or parameter containing the numeric IP address you want to display. 

…
to_char(to_number(substr(to_char(ipField, 'XXXXXXXX'), 1, 3), 'XX')) || '.' ||
      to_char(to_number(substr(to_char(ipField, 'XXXXXXXX'), 4, 2), 'XX')) || '.'
 ||
      to_char(to_number(substr(to_char(ipField, 'XXXXXXXX'), 6, 2), 'XX')) || '.'
 ||
      to_char(to_number(substr(to_char(ipField, 'XXXXXXXX'), 8, 2), 'XX'))
...

The following listing shows how to convert a standard IP address to the long numeric format.

…
to_number(substr(ipField, 1, instr(ipField, '.')-1))*16777216 +
      to_number(substr(ipField, instr(ipField, '.', 1, 1)+1, instr(ipField, '.',
 1, 2)-instr(ipField, '.', 1, 1)-1))*65536 +
      to_number(substr(ipField, instr(ipField, '.', 1, 2)+1, instr(ipField, '.',
 1, 3)-instr(ipField, '.', 1, 2)-1))*256 +
      to_number(substr(ipField, instr(ipField, '.', 1, 3)+1))

D.3 Building OAA/OARM Custom User Activity Reports

You can build custom user activity reports based on data in the OAA/OARM database schema.

Topics

D.3.1 Retrieving Entities and Custom User Activities Information

You can obtain the Custom User Activity Definition key and Entity Definition keys.

Perform the following steps:
  1. Log in to the OAA Administration console.
  2. In the OAA Administration UI console, click the Application Navigation hamburger menu on the top left.
  3. Under Adaptive Risk Management, click Custom Activities.
    The Custom Activities Definition Search page is displayed.
  4. Specify criteria in the Search Filter to locate the custom user activity definition you are interested in and press Enter.
    The Search Results table displays a summary of the custom user activities definitions that match the search criteria.
  5. Click the Edit icon in the row for the custom user activity definition you are interested in to view more details.
    The Edit Custom Activity page appears.
  6. Note down the Name for this activity. This is the Custom User Activity Definition Key or the transaction definition key.
    This definition key value is used to map the client/external custom user activity data to custom activity definitions in Oracle Advanced Risk Manager (OARM) server.

    This value is sent while making the API call for creating or updating the custom user activity data in the OARM Server.

  7. On the Custom User Activity Definition Details or the Describe Activity page, click Next.
    A list of actors (entities) for the selected custom user activity is displayed.
  8. Note down the lists of names in the Actor Name column on the left.
  9. Note the Type for each of those actors. That is the Actor or Entity Definition Key of the entities.
    The definition key is the unique identifier for an actor or entity definition.

D.3.2 Discovering Actor or Entity Data Mapping Information

To discover the actor data mapping information you will need to generate a report.

Perform the following procedures:

D.3.2.1 Overview of Data Types

Learn about the data types and their descriptions.

The following table lists the data type and their descriptions.

Table D-1 Information about Data Types

Data Type Description
1 Represents String data
2 Represents Numeric data. Data stored is equal to (Original value * 1000).
3 Date type data. Store the data in "'YYYY-MM-DD HH24:MI:SS TZH:TZM" format and also retrieve it using same format.
4 Boolean data. Stored as strings. "True" represents TRUE and "False" represents FALSE.
D.3.2.2 Discovering Actor or Entity Data Details

To obtain the actor/entity data detail, such as Data Type, Row, and Column Mappings, you will need to construct your report.

Perform the following steps to generate the report.

  1. Log in to the OAA Administration console.
  2. In the OAA Administration UI console, click the Application Navigation hamburger menu on the top left.
  3. Under Adaptive Risk Management, click Custom Activities.

    The Custom Activities Definition Search page is displayed.

  4. Click the Edit icon in the row for the custom user activity definition you are interested in to view more details.

    The Edit Custom Activity page appears.

  5. On the Custom User Activity Definition Details or the Describe Activity page, click Next.

    A list of actors (entities) for the selected custom user activity is displayed.

  6. Note the Type for each of those actors. That is the Actor or Entity Definition Key of the entities.

    The definition key is the unique identifier for an actor or entity definition.

  7. Click the Edit icon for the respective actor to view more details.

    The Edit Actor page appears. It lists the actor and the data elements contained within it.

  8. On the Edit Actor page, note down the Instance Name, and click Ok.
  9. Do one of the following to obtain details of how entity data is mapped.
    1. Click the Map icon for the respective actor to view more details.

      The Select or provide Source Data for Actor attributes page appears. It describes the data items contained within that definition, as well as its source data and mapping information to the model in the OARM server.

    2. Obtain Entity Data mapping using the SQL query.
      SELECT label,
  data_row,
  data_col,
  data_type
FROM vt_data_def_elem
WHERE status =1
AND data_def_id =
  (SELECT data_def_id
  FROM vt_data_def_map
  WHERE relation_type   ='data'
  AND parent_obj_type   =3
  AND parent_object_id IN
    (SELECT entity_def_id
    FROM vt_entity_def
    WHERE entity_def_key=<Entity/Actor Definition Key>
    AND status =1
    )
  )
ORDER BY data_row ASC,
  data_col ASC;
D.3.2.3 Building Entity Data SQL Queries and Views

Learn how to create a SQL query and view based on information that reflects the data of a specific actor/entity.

The SQL query in Discovering Actor or Entity Data Mapping Information returns a list of the actor/entity's data fields, together with data type and row and column position. Using this information you will create a SQL query and view that reflects the data of a specific actor/entity.

Note:

EntityRowN denotes an entity data row. You would have three EntityRowN items, if your entity had three different data_row values from the aforementioned SQL query. The aliases must be named EntityRow1, EntityRow2, and so forth. As illustrated below, you must also take care of the corresponding joins. 
SELECT ent.ENTITY_ID,
    ent.EXT_ENTITY_ID,
    ent.ENTITYNAME,
    ent.ENTITY_KEY,
    ent.ENTITY_TYPE,
    EntityRowN<row>.DATA<col> <column_name>,
    (EntityRowN<row>.NUM_DATA<col>/ 1000.0) <numeric_column_name>,
    to_timestamp_tz(EntityRowN<row>.DATA<col>, 'YYYY-MM-DD HH24:MI:SS TZH:TZM') <date_column_name>,
    ent.CREATE_TIME,
    ent.UPDATE_TIME,
    ent.EXPIRY_TIME,
    ent.RENEW_TIME
  FROM 
    VT_ENTITY_DEF entDef,
    VT_ENTITY_ONE ent
    LEFT OUTER JOIN VT_ENTITY_ONE_PROFILE EntityRowN
          ON (EntityRowN.ENTITY_ID = ent.ENTITY_ID
          AND EntityRowN.ROW_ORDER = <row>
          AND EntityRowN.EXPIRE_TIME IS NULL)
    LEFT OUTER JOIN VT_ENTITY_ONE_PROFILE EntityRowN+1
        ON (EntityRowN+1.ENTITY_ID = ent.ENTITY_ID
          AND EntityRowN+1.ROW_ORDER = <row+1>
        AND row1.EXPIRE_TIME IS NULL)
  WHERE 
        ent.ENTITY_DEF_ID = entDef.ENTITY_DEF_ID and 
        entDef.ENTITY_DEF_KEY=<Entity Definition Key>

D.3.3 Discovering Custom User Activity Data Mapping Information

To discover custom user activity data mapping information, such as data type, row and column mappings you will need to generate a report.

To obtain the entity data and mapping details using SQL queries, perform the following steps:

Note:

You can also obtain the data mapping information from the OAA Administration console as described in Discovering Actor or Entity Data Details.
  1. Use the following SQL query to obtain a list of customer user activities to entity definition mapping IDs.
    SELECT map_id
FROM 
vt_trx_ent_defs_map, vt_trx_def
WHERE 
vt_trx_ent_defs_map.trx_def_id = vt_trx_def.trx_def_id
AND vt_trx_def.trx_def_key = <Transaction Definition Key>
  2. Use the following SQL query to obtain details of all custom user activity data fields, together with data type and row and column position.
    SELECT label, data_row, data_col, data_type
FROM vt_data_def_elem
WHERE status=1
AND data_def_id =
  (SELECT data_def_id
  FROM vt_data_def_map
  WHERE relation_type='data'
  AND parent_obj_type=1
  AND parent_object_id IN
    (SELECT trx_def_id
        FROM vt_trx_def
        WHERE trx_def_key=<Custom_User_Activity_Key>
        AND status=1
    )
  )
ORDER BY data_row ASC,
  data_col ASC;

D.4 Creating Custom Report Example

You can create custom reports on data in the OAA/OARM database schema.

Example 1

This query result will show a list of sessions with user id, login id, auth status, and location. You must first create the two date parameters, fromDate and toDate. The query will look like the following: 

SELECT s.request_id, s.create_time, s.user_id, s.user_login_id, country.country_name, statea.state_name, city.city_name
 FROM vcrypt_tracker_usernode_logs s
      INNER JOIN vcrypt_ip_location_map loc ON s.base_ip_addr = loc.from_ip_addr
      INNER JOIN vcrypt_country country ON loc.country_id = country.country_id
      INNER JOIN vcrypt_state statea ON loc.state_id = statea.state_id
      INNER JOIN vcrypt_city city ON loc.city_id = city.city_id

WHERE (:fromDate IS NULL OR s.create_time >= :fromDate)
AND (:toDate IS NULL OR s.create_time <= :toDate)
ORDER BY s.create_time DESC

Example 2

Using the OAA/OARM schema, you can generate a custom report for custom user activities. This query result will show a list of custom user activities, request id, status, transaction information for this specific type of transaction, and the creation and modification dates for each key type.

SELECT trx.LOG_ID,
    trx.USER_ID,
    trx.REQUEST_ID,
    trx.EXT_TRX_ID,
    trx.TRX_TYPE,
    trx.STATUS,
    trx.SCORE,
    trx.RULE_ACTION,
    trx.POST_PROCESS_STATUS,
    trx.POST_PROCESS_RESULT,
    TransactionDataRowN1.NUM_DATA0 NUM_DATA0,
    trx.CREATE_TIME,
    trx.UPDATE_TIME
  FROM VT_TRX_DEF trxDef, VT_TRX_LOGS trx
  LEFT OUTER JOIN VT_TRX_DATA TransactionDataRowN1
  ON (TransactionDataRowN1.TRX_ID = trx.LOG_ID
  AND TransactionDataRowN1.ROW_ORDER = 0)
  WHERE (:fromDate IS NULL OR trx.create_time >= :fromDate)
  AND (:toDate IS NULL OR trx.create_time <= :toDate)
  AND trx.TRX_DEF_ID = trxDef.TRX_DEF_ID and
  trxDef.TRX_DEF_KEY=<Custom_User_Activity_Key>