System and Audit Tables

[ 1 Access Control Rule Table ] [ 2 Access Control Rule System Mapping Table ] [ 3 Audit Table ] [ 4 External System Table ] [ 5 External System Authentication Table ] [ 6 External System Role Table ] [ 7 External System Role Mapping Table ] [ 8 Security Table ]

Access Control Rule Table

This table contains all the rules of ACLs (access control rules) either from security or project team within CAM.

Query the table by: select * from access_control_rule limit 100

^Use the other Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id for the access control rule system mapping table. Is a mandatory field.	PK
object_entity_id	Id of the object entity (doc, folder etc)	FK on the object’s table eg document
group_id	Id of the group	FK on groups
grant_access	Allows to grant access to the user or group
role_id	Id of the role	FK on role
is_implicit	Is an implicit security role rule?
project_team_id	Id of the project team	FK on project_team
is_deleted	Is the record deleted?
extSystemInstanceId	The external system id (iManage, ND etc)	FK on externalsystem
createdDate	Displays the audit creation date. Is mandatory.
createdByUserId	The user id of the person who created the record.	FK on user
camCreatedDate	The date the record is created in CAM
camCreatedBy	The user who created in CAM	FK on user

Access Control Rule System Mapping Table

This table contains all the mappings of ACLs (access control rules) either from security or project team within CAM.

Query the table by: select * from access_control_rule_system_mapping limit 100

^Use the other Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id for the access control rule system mapping table. Is a mandatory field.	PK
access_control_rule_id	The id of the access control rule.	FK on access control rule
is_deleted	Is the record deleted?
extSystemInstanceId	The external system id (iManage, ND etc)	FK on externalsystem
createdDate	Displays the audit creation date. Is mandatory.
createdByUserId	The user id of the person who created the record.	FK on user
camCreatedDate	The date the record is created in CAM
camCreatedBy	The user who created in CAM	FK on user

Audit Table

This table contains all the audit information within CAM.

Query the table by: select * from audit limit 100

^Use the audit Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id for the audit table. Is a mandatory field.	PK
docId	It is a mandatory field if a document is set.	FK on document table
folderId	It is a mandatory field if a folder is set.	FK on folder table
userId	Displays the user Id associated to the user. Displays the user Id associated to the user.	FK on user table
extDocId	The external document id (filled if a document was added/changed/updated/deleted)
extFolder	The external folder id (filled if a folder/workspace was added/changed/updated/deleted)
extUserId	The external userid (filled if a user or security was added/changed/updated/deleted)
objectType	Displays the object type.
actionCodeId	The id of the particular action done on the object. Is a mandatory field.
actionCodeName	The name of the particular action done on the object. Is a mandatory field.
logType	The type of the log.
applicationName	Name of the CAM application module making the modification/addition/deletion.
ipAddress	If the ip address is filled from an external system or user.
clientCode	If a client is changed, this is the client code.
MatterCode	If a matter is changed, this is the matter code.
actionUserName	If a action has a specific user name passed, this will be filled.
ticketId
comments	Any comments left on the audit.
docNumber	If a document was added/changed/updated/deleted, the docnumber is filled.
hasJournal	Set to 1 if a journal is with the object.
version	Version of the object.
metadata	Any metadata added/changed/updated/deleted
createdDate	Displays the audit creation date. Is mandatory.
createdByUserId	The user id of the person who created the record.
camCreatedDate	The date the record is created in CAM
camCreatedBy	The user who created in CAM
extSystemId	The external system id (iManage, ND etc)	FK on externalsystem
subscriberId	Displays the tenant subscriber Id.
runId	The ID of the job run
actionDate	Date the action on the record was taken
extContainerId	If the configured External System is iManage this column displays the database name, followed by the folder number (e.g. demo!1234), in case of NetDocuments, this displays the 12 digit folder number (e.g. 4816-5122-8805). It is a mandatory field.	FK on externalsystem
extContainerName	If the configured External System is iManage, this column displays the database name, followed by the document number and version (e.g. demo!463.1), in case of NetDocuments, this displays the document name.	FK on externalsystem

External System Table

This table contains all the external system information within CAM.

Query the table by: select * from external_system_nt limit 100

^Use the other Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id of each external system.	PK
name	Name of the external system. E.g. iManage, NetDocuments, O365 etc
nickname	A nickname. Typically most people will set this the same as the name.
description	Description of the external system.
type	The system type. Can be 0,1,2
base_url	The url of the cloud based system.
icon_path	Path of where the icons are stored for the system. Is blank if not set (uses CAM default icons).
libraries	If a particular library within the API is used. Most commonly is blank.
active	Is actively used or not. 0 is active, 1 is not.
is_deleted	Marks if the system is deleted from CAM. 0 is not deleted. 1 is deleted.
created_user_id	The user id who created the external system connection.
last_modified_user_id	The user id who last modified the external system connection.
created_date	The date created
last_modified_date	The date last modified.

External System Authentication Table

This table contains all the external system authentication method information within CAM.

Query the table by: select * from external_system_auth limit 100

^Use the other Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id of each external system authentication row.	PK
systemUniqueId	The external system unique id. Each tenant or instance will have a separate id. They look like: 91b692a0-c09d-00e8-9e62-0dfac7e82c00:~imanage:~Worksite2022	FK on externalsystem
system	Type of the external system role. E.g. iManage, Office365 etc
camrepoName	Name of the CAM instance that connects to this external system. E.g. Worksite2022
healthy	Marks if the system has errored recently. If it has, it is unhealthy (0). Healthy is =1
errorMessage	If an error occurred with the system, the last error displays here. For example, Unable to authenticate with IManage server. Attempts(10) Consumed. Please contact system administrator.(Root Cause : 403 Forbidden)

External System Role Table

This table contains all the external role information within CAM.

Query the table by: select * from external_system_role limit 100

^Use the other Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id of each external system role.	PK
ext_id	The external system id from the external system table.	FK on externalsystem
name	Name of the external system role. E.g. Read, Read/Write, VESA etc
code	Code of the role. E.g. read_write, VESA, member etc
is_deleted	Marks if the role is deleted from CAM. 0 is not deleted. 1 is deleted.
created_user_id	The user id who created the external system role.
last_modified_user_id	The user id who last modified the external system role.
created_date	The date created
last_modified_date	The date last modified.

External System Role Mapping Table

This table contains all the mappings between external roles and CAM roles.

Query the table by: select * from external_system_role_mapping limit 100

^Use the other Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id of each external system role mapping.	PK
security_role_id	Id of the security role that is to be mapped.
external_system_instance_id	The external system id of each instance. For example, a firm may have two instances of iManage Work on their servers.	FK on externalsystem
external_system_role_id	The id of the external system role from the external_system_role table.
is_deleted	Marks if the role mapping is deleted from CAM. 0 is not deleted. 1 is deleted.
created_user_id	The user id who created the external system role mapping.
last_modified_user_id	The user id who last modified the external system role mapping.
created_date	The date created
last_modified_date	The date last modified.

Security Table

This table contains all the security information within CAM.

Query the table by: select * from security limit 100

^Use the security Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	Displays the row number id of the entry in the security table.	PK
objectid	Contains the id of the object security is being applied to. For example a document, workspace, folder, team.
extuserorgroupId	Displays the user Id or groupId of the external user or group.
extuserorgroupName	Displays the user name or group name of the external user or group
extuserorgrouptype	The type or user or group. 1= group, 2= user
roleId	The id of the role for the user in CAM
isLatest	Flag to indicate if the security has been recently updated and this is the latest record
objectType	Displays the object type.
accesslevelid	The id of the accesslevel. 0= no access, 1= read only, 2= read/write, 3=full access.
accessLevel	Displays the access level assigned to the user (full access/read-write/read only/no-access)
extContainerId	Id of the external system container
extContainerName	Name of the external container
metadata	Any assigned metadata to go with the security application. E.g. clientid=123
repositoryId	Displays the repository name
createddate	The date the security was created
editedDate	The date edited
createdbyUserid	The user id of the user who created the security
createdbyuser	The user who created the security
lastchangeuserid	The userid of the user that last changed the record
lastchangeuser	The user that last changed the record
camcreateddate	The date cam had created the security
camcreatedby	The person in cam that created
camediteddate	The date last edited in CAM
cameditedby	The person that last edited in CAM
subscriberId	Displays the tenant subscriber Id
extsystemid	Displays the external systemid	FK on externalsystem
docIdHash	Displays the hash from the table