A 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:
A.1 Viewing the Details of Database Tables
Learn about the specifics of each database table.
Topics
- VCRYPT_TRACKER_NODE
- VT_USER_DEVICE_MAP
- VT_SESSION_ACTION_MAP
- VT_USER_GROUPS
- V_FPRINTS
- V_FP_NV
- V_FP_MAP
- VCRYPT_COUNTRY
- VCRYPT_STATE
- VCRYPT_CITY
- VCRYPT_ISP
- VCRYPT_IP_LOCATION_MAP
- VT_TRX_DEF
- VT_TRX_INPUT_DEF
- VT_ENTITY_DEF
- VT_TRX_ENT_DEFS_MAP
- VT_ENT_DEFS_MAP
- VT_DATA_DEF
- VT_DATA_DEF_ELEM
- VT_DATA_DEF_MAP
- VT_DATA_DEF_TRANS
- VT_ELEM_DEF_TRANS
- VT_TRANS_SRC_ELEM
- VT_TRX_LOGS
- VT_TRX_DATA
A.1.1 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 | Id for the User group | 16 | - |
| GROUP_NAME | TEXT | Name of the group | 4000 | - |
| DESCRIPTION | TEXT | Description for this group | 4000 | - |
| CREATE_TIME | DATETIME | Date/time creation of this user | 6 | - |
| UPDATE_TIME | TIMESTAMP | Last update time | 6 | - |
| USERGROUP_TYPE_CODE | INT | Type of the User group | - | - |
| USERGROUP_STATUS_CODE | INT | Status of the User group | 2 |
|
| NOTES | TEXT | Note | 4000 | - |
A.1.2 VCRYPT_TRACKER_USERNODE_LOGS
Discover the specifics of the VCRYPT_TRACKER_USERNODE_LOGS database table.
Description: This table logs all the activities for the nodes.
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 | Log Id of the user for a given node | 16 |
| REQUEST_ID | TEXT | Id of the request. This is used to co-relate the post authentication request. | 256 |
| EXT_SESSION_ID | TEXT | External session Id | 512 |
| CLIENT_DEVICE_ID | TEXT | Id of the device which is generated by the application. | 256 |
| REMOTE_IP_ADDR | BIGINT | The IP address from where the client connected | 15 |
| BASE_IP_ADDR | BIGINT | This is the base IP address for quick search | 15 |
| NODE_ID | BIGINT | Id of the nodeId. | 16 |
| TRACKER_NODE_HISTORY_ID | BIGINT | Id of the Tracker Node History (if available). | 16 |
| USER_ID | TEXT | User Id of the user if available. | 256 |
| USER_LOGIN_ID | TEXT | Login Id of the user if available. | 256 |
| USER_GROUP_ID | TEXT | GroupId of the user if available. | 256 |
| USER_SUB_GROUP_ID | TEXT | Sub GroupId of the user if available. | 256 |
| AUTH_STATUS | INT | Status of the authentication. | 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 | Type of the client used by the user for authentication | 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 | Post Authentication score | 8 |
| PREAUTH_ACTION | TEXT | Pre Authentication action | 256 |
| POST_ACTION | TEXT | Post Authentication action | 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 | - |
A.1.3 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 | TEXT | 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 | TEXT | Cache | 4000 |
A.1.4 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 nodeId. | 16 |
| REQUEST_ID | TEXT | 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 | TEXT | 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 |
A.1.5 VT_SESSION_ACTION_MAP
Discover the specifics of the VT_SESSION_ACTION_MAP database table.
Description: This table maintains the actions for each session.
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 | TEXT | Id of the request. This is used to co-relate the post authentication request. | 256 |
| TRX_ID | BIGINT | Id for the transaction for this log | 16 |
| RUNTIME_TYPE | INT | Type of runtime | 6 |
| ACTION | TEXT | Actions for this runtime and session | 256 |
| ORIGINAL_ACTION | TEXT | This was the original action, which got overridden finally | 256 |
| OVERRIDE_REASON | INT | Override reason | - |
| ACTION_LIST | TEXT | List of action. | 256 |
| SCORE | INT | Score for this runtime and runtime. The same score will appear for all the rows for this transaction | - |
| IS_FINAL_ACTION | CHAR | Is this final action | - |
A.1.6 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 UserGroup | 16 | - |
| EXT_USERGROUP_ID | VARCHAR | External User group Id | 255 | - |
| DESCRIPTION | TEXT | 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 userList | 16 | - |
| USERGROUP_STATUS_CODE | INT | Status of the User group | 2 |
|
| NOTES | TEXT | Note | 4000 | - |
A.1.7 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 | TEXT | Hash value for the fingerprint | 512 |
| DATA_VALUE | TEXT | Data value for the fingerprint | 4000 |
A.1.8 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 | TEXT | Value of the attribute | 256 |
A.1.9 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 | TEXT | value of the attribute | 256 |
A.1.10 VCRYPT_COUNTRY
Discover the specifics of the VCRYPT_COUNTRY database table.
Description: This table represents the country object.
Database table name: VCRYPT_COUNTRY
Primary Key: COUNTRY_ID
| Database Column Name | Database Column Type | Description | Length |
|---|---|---|---|
| COUNTRY_ID (PK) | BIGINT | Id for this country | 16 |
| COUNTRY_CODE | VARCHAR | Code of the country | 64 |
| COUNTRY_NAME | TEXT | Name of the country | 4000 |
| CREATE_TIME | DATETIME | 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 | TEXT | Notes for this country | 4000 |
A.1.11 VCRYPT_STATE
Discover the specifics of the VCRYPT_STATE database table.
Description: This table represents state or region of the country.
Database table name: VCRYPT_STATE
Primary Key: STATE_ID
| Database Column Name | Database Column Type | Description | Length |
|---|---|---|---|
| STATE_ID (PK) | BIGINT | Id for this state | 16 |
| COUNTRY_ID | BIGINT | Id for the country to which this state belongs to | 16 |
| STATE_CODE | VARCHAR | Code for the state or region | 64 |
| STATE_NAME | TEXT | Name of the state | 4000 |
| CREATE_TIME | DATETIME | Date/time for this log | 6 |
| UPDATE_TIME | TIMESTAMP | Last update time for this object | 6 |
| NOTES | TEXT | Notes for this state | 4000 |
A.1.12 VCRYPT_CITY
Discover the specifics of the VCRYPT_CITY database table.
Description: This table represents the city object.
Database table name: VCRYPT_CITY
Primary Key: CITY_ID
| Database Column Name | Database Column Type | Description | Length |
|---|---|---|---|
| CITY_ID (PK) | BIGINT | Id for this city | 16 |
| STATE_ID | BIGINT | Id for the state to which this city belongs to. | 16 |
| CITY_CODE | VARCHAR | Code for the city | 64 |
| CITY_NAME | TEXT | Name of the city | 4000 |
| CREATE_TIME | DATETIME | Date/time for this log. | 6 |
| UPDATE_TIME | TIMESTAMP | Last update time for this object. | 6 |
| LATITUDE | VARCHAR | Latitude | 20 |
| LONGITUDE | VARCHAR | Longitude | 20 |
| TIMEZONE | VARCHAR | Time Zone | 20 |
| NOTES | TEXT | Notes for this city | 4000 |
A.1.13 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 | TEXT | Name of the ISP | 4000 |
| CREATE_TIME | DATETIME | Date/time for this log | 6 |
| UPDATE_TIME | TIMESTAMP | Last update time for this object | 6 |
A.1.14 VCRYPT_IP_LOCATION_MAP
Discover the specifics of the VCRYPT_IP_LOCATION_MAP database table.
Description: This table provides the mapping of the IP range to city, state, and country.
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 | The from IP address | 15 |
| TO_IP_ADDR | BIGINT | The to IP address | 15 |
| CREATE_TIME | DATETIME | Date/time for this log | - |
| UPDATE_TIME | TIMESTAMP | Last update time for this object | - |
| COUNTRY_ID | BIGINT | Id for the country to which this state belongs to | 16 |
| STATE_ID | BIGINT | Id for the state to which this IP range belongs to | 16 |
| CITY_ID | BIGINT | Id for the city to which this IP range belongs to | 16 |
| METRO_ID | BIGINT | Id of the metro for this IP | - |
| 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 | Connection type | - |
| CONNECTION_SPEED | INT | Connection speed | - |
| TOP_LEVEL_DOMAIN | VARCHAR | Top level domain | 25 |
| SEC_LEVEL_DOMAIN | VARCHAR | Second level domain | 128 |
| ASN | VARCHAR | ASN | 25 |
| CARRIER | VARCHAR | Carrier | 128 |
| ZIP_CODE | VARCHAR | Zip code | 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 the region | 16 |
| PHONE_AREA | VARCHAR | Phone area code | 10 |
| IS_SPLIT | CHAR | Is the IP split. If so, in some queries, we might have to do additional checks. | - |
| 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 |
A.1.15 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 | TEXT | Name for transaction | 4000 | - |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 | - |
| DESCRIPTION | TEXT | Description of the object | 4000 | - |
| DESC_RBKEY | TEXT | Resource bundle key for the description | 4000 | - |
| TRX_DEF_KEY | TEXT | 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 |
|
| NOTES | TEXT | Note for this object | 4000 | - |
A.1.16 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 | TEXT | Name for transaction. | 4000 | - |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 | - |
| DESCRIPTION | TEXT | Description of the object | 4000 | - |
| DESC_RBKEY | TEXT | Resource bundle key for the description | 4000 | - |
| TRX_DEF_KEY | TEXT | Key name to be used for the transaction e.g 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 |
|
| NOTES | TEXT | Note for this object | 4000 | - |
A.1.17 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 | TEXT | Name for entity. For example address, customer | 4000 | - |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 | - |
| DESCRIPTION | TEXT | Description of the object | 4000 | - |
| DESC_RBKEY | TEXT | Resource bundle key for the description | 4000 | - |
| ENTITY_DEF_KEY | TEXT | Key of the entity. E.g. address, merchant, etc | 256 | - |
| STATUS | INT | Status | 2 |
|
| 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 | TEXT | 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 | TEXT | Static parameters to be passed to the Java class for name generation. For example is a delimiter of ',' | 4000 | - |
| NOTES | TEXT | Note for this object | 4000 | - |
A.1.18 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 | TEXT | Name for the map. | 4000 | - |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 | - |
| DESCRIPTION | TEXT | Description of the object | 4000 | - |
| DESC_RBKEY | TEXT | 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 | TEXT | Type of the relation | 4000 | - |
| DISP_ORDER | INT | Display order | 6 | - |
| STATUS | INT | Status | 2 |
|
| NOTES | TEXT | Note for this object | 4000 | - |
A.1.19 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 | TEXT | Name for the map | 4000 |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 |
| DESCRIPTION | TEXT | Description of the object | 4000 |
| DESC_RBKEY | TEXT | 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 | TEXT | Type of the relation | 4000 |
| DISP_ORDER | INT | Display order | 6 |
| NOTES | TEXT | Note for this object | 4000 |
A.1.20 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 | TEXT | Name for data definition. | 4000 | - |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 | - |
| DESCRIPTION | TEXT | Description of the object | 4000 | - |
| DESC_RBKEY | TEXT | Resource bundle key for the description | 4000 | - |
| DATA_DEF_KEY | TEXT | Key of the data. For example, "data", "key", "name", "auto-learning," and so on. | 256 | - |
| STATUS | INT | Status | 2 |
|
| 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 | TEXT | Note for this object | 4000 | - |
A.1.21 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 | TEXT | 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 | TEXT | Name for column | 4000 | - |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 | - |
| DESCRIPTION | TEXT | Description of the object | 4000 | - |
| DESC_RBKEY | TEXT | 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 | TEXT | Format of the data (for example, mm/YY for some dates) | 4000 | - |
| STATUS | INT | Status | 2 |
|
| IS_REQUIRED | CHAR | Is this data required by default | - | - |
| NAME_GEN_SCHEME | INT | Name generation scheme | - | - |
| NAME_GEN_PARAMS | TEXT | Static parameters to be passed to the Java class for name generation | 4000 | - |
| IS_AUTO_CREATED | CHAR | Whether this auto created | - | - |
| NOTES | TEXT | Note for this object | 4000 | - |
A.1.22 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 | TEXT | Name for the map | 4000 |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 |
| DESCRIPTION | TEXT | Description of the object | 4000 |
| DESC_RBKEY | TEXT | 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 | TEXT | Type of the relation ("data", "name," and so on). | 4000 |
| NOTES | TEXT | Note for this object | 4000 |
A.1.23 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 | TEXT | Name for this data map | 4000 | - |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 | - |
| DESCRIPTION | TEXT | Description of the object | 4000 | - |
| DESC_RBKEY | TEXT | 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 | TEXT | 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 | TEXT | Type of the relation | 4000 | - |
| STATUS | INT | Status | 2 |
|
| NOTES | TEXT | Note for this object | 4000 | - |
A.1.24 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 | TEXT | Name for this data map | 4000 | - |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 | - |
| DESCRIPTION | TEXT | Description of the object | 4000 | - |
| DESC_RBKEY | TEXT | Resource bundle key for the description | 4000 | - |
| TRANS_SCHEME | INT | Scheme for translation | - | - |
| TRANS_PARAMS | TEXT | 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 |
|
| NOTES | TEXT | Note for this object | 4000 | - |
A.1.25 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 | TEXT | Name for this data map | 4000 | - |
| LABEL_RBKEY | TEXT | Resource bundle key for the name | 4000 | - |
| DESCRIPTION | TEXT | Description of the object | 4000 | - |
| DESC_RBKEY | TEXT | Resource bundle key for the description | 4000 | - |
| TRANS_SCHEME | INT | Scheme for translation | - | - |
| TRANS_PARAMS | TEXT | 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 |
|
| NOTES | TEXT | Note for this object | 4000 | - |
A.1.26 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 | TEXT | 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 | TEXT | 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 | TEXT | Transaction data as name value pair. | 4000 |
| DATA1 | TEXT | Data one | 256 |
| DATA2 | TEXT | Data two | 256 |
| DATA3 | TEXT | Data three | 256 |
| DATA4 | TEXT | Data four | 256 |
| DATA5 | TEXT | Data five | 256 |
| DATA6 | TEXT | Data six | 256 |
| DATA7 | TEXT | Data seven | 256 |
| DATA8 | TEXT | Data eight | 256 |
| DATA9 | TEXT | Data nine | 256 |
| DATA10 | TEXT | Data ten | 256 |
A.1.27 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 | TEXT | Data one | 4000 |
| DATA2 | TEXT | Data two | 4000 |
| DATA3 | TEXT | Data three | 4000 |
| DATA4 | TEXT | Data four | 4000 |
| DATA5 | TEXT | Data five | 4000 |
| DATA6 | TEXT | Data six | 4000 |
| DATA7 | TEXT | Data seven | 4000 |
| DATA8 | TEXT | Data eight | 4000 |
| DATA9 | TEXT | Data nine | 4000 |
| DATA10 | TEXT | Data ten | 4000 |
| NUM_DATA0 | BIGINT | Numeric data 0 | 38 |
| NUM_DATA1 | BIGINT | Numeric data 1 | 38 |
| NUM_DATA2 | BIGINT | Numeric data 2 | 38 |
A.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))A.3 Building OAA/OARM Custom User Activity Reports
You can build custom user activity reports based on data in the OAA/OARM database schema.
A.3.1 Retrieving Entities and Custom User Activities Information
You can obtain the Custom User Activity Definition key and Entity Definition keys.
A.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:
A.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 A-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. |
A.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.
- Log in to the OAA Administration console.
- In the OAA Administration UI console, click the Application Navigation hamburger menu on the top left.
- Under Adaptive Risk Management, click Custom Activities.
The Custom Activities Definition Search page is displayed.
- 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.
- 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.
- 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.
- 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.
- On the Edit Actor page, note down the Instance Name, and click Ok.
- Do one of the following to obtain details of how entity data is mapped.
- 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.
- 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;
- Click the Map icon for the respective actor to view more details.
A.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>A.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.- 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> - 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;
A.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 DESCExample 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>