- Created by Eric Schullek (Unlicensed) , last modified on Jan 26, 2023
You are viewing an old version of this page. View the current version.
Compare with Current View Page History
« Previous Version 26 Next »
To understand the Analytics queries to write, the user will need to understand the tables CAM Analytics supports and the metadata and values that can be selected from.
Audit Table
Column | Description |
|---|---|
id | The internal CAM id for the audit table. Is a mandatory field. |
docId | It is a mandatory field if a document is set. |
folderId | It is a mandatory field if a folder is set. |
userId | Displays the user Id associated to the user. Displays the user Id associated to the user. |
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) |
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. |
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. |
Document Table
Column | Description |
|---|---|
Id | The internal CAM id of the document row. This is auto-generated by CAM |
extId | Displays the row number of the entry in the document table. It is a mandatory field. |
extContainerId | 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 12 digit document number (e.g. 4816-5122-8805). It is a mandatory field. |
name | Name of the document. |
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. |
description | Displays the description of the document. |
number | Displays the document number for iManage. This does not apply for NetDocuments. It is a mandatory field. |
folder | This displays the folder name |
folderId | This displays the folder id This is auto-generated by CAM |
workspaceId | If the configured External System is iManage this column displays the database name, followed by workspace id. (e.g. demo.463.1), in case of NetDocuments, |
tabId | The id of the tab used to display the document on a sharepoint site or teams site. |
type | Displays the doc type (e.g. csv, BMP, PDF etc.) |
typeId | Displays the type Id. This is auto-generated by CAM |
etag |
|
ctag |
|
version | Displays the document version number. It is a mandatory field. |
sourceId | Displays the database or cabinet name |
hasAttachment | Flag to indicate if document has an attachment (in case of emails for example) |
isdeclared | Indicates if the document is marked as a record |
DeclaredDate | Date the document was declared, if isdeclared=1 |
indexNumber | Number of the index |
originalDocketId | If the document was part of a docket, includes that docket id. |
dateSent | Displays the message sent date (in case of email) |
dateReceived | Displays the message received date (in case of email) |
transcriptDate | Date the document was transcripted. |
FileName | Displays the file name (without extension) |
FileExtension | Displays the doc type (e.g. csv, BMP, PDF etc.) |
FilePath | The path of the file (e.g. if the file was stored in a fileshare) |
FileSize | The size of the file uploaded. Includes kb/mb/gb designations. |
dateFiled | The date the document was filed, e.g. 20/11/2022 |
sortorder | Allows to sort the documents in a particular order in the workspace. |
SortOrderModifiedDate | Date the sort order was modified, e.g. 20/11/2022 |
orderedById | Id of the ordering schema |
orderingDate | Date the ordering occurred, e.g. 20/11/2022 |
workProductId | If using iManage Work, this is the product id from there. |
isDeleted | Flag to indicate if document is deleted from the system. 1/0 |
importReference | Displays the document ID as a reference for import. |
isDeletedToken | If from a SQL based system, the metadata row could have a token as a GUID if the record was deleted. |
extLongId | The external system’s long id |
extShortId | The external system’s short id |
StatusId | Displays the document status id associated to document. This is auto generated by CAM. |
SecurityLevelId | Sets an id based on the security level set on the document. This is auto-generated by CAM |
checkedOutLocation | Displays the path where the file is checked out |
CheckedOutPath | The path of the checked out file |
checkedOutBy | Displays the user name who has checked out the file |
checkedoutbyid | The id of the person who checked out the document. This is auto-generated by CAM |
checkedOutDate | Displays the file checkout date |
checkedoutDueDate | Displays the due date for the checked out file. |
checkedoutComment | The comment left when a file is checked out. |
isOffline | Is the document an offline stored document? |
csJobId |
|
securityLevelId | Displays the security level ID associated to security. This is auto-generated by CAM |
securityLevel | Displays the document security (public/private/view) |
emailid | Introduced new column in document table. It contains id/msg_id from imanage and netdocs respectively. |
emailto | Displays the email recipient name |
emailfrom | Displays the email sender name |
emailcc | Displays the user name added in the cc field in the email |
emailBcc | Displays the user name added in the Bcc field in the email |
emailSubject | Displays the subject/title added in the subject field in the email. |
emailMessageId | This is an existing column. It contains the conversation ID, used to group emails. Note: emailMessageid later will be renamed as emailConversationId |
isExternalEmail | Is this an external email outside the firm? |
checksum | A checksum of changes in sql. |
extDocRefId | Id of the refiled external document |
targetContainerId | The id of the target container (netDocuments) |
targetContainerName | The name of the target container (netDocuments) |
authorId | Displays the author Id associated to the author. This is auto-generated by CAM |
author | Displays the document author name |
operator | Displays the name of the user who edited the document most recently |
operatorId | Displays the operator ID associated to the author. This is auto-generated by CAM |
clientCodeId | Displays the client code ID associated to client code. This is auto-generated by CAM |
clientCode | This value is updated based on the Auroradb column mapping in the metadata tab. |
matterCodeId | Displays the matter code ID associated to matter code. This is auto-generated by CAM |
matterCode | This value is updated based on the Auroradb column mapping in the metadata tab. |
practiceCodeId | Displays the practice code Id associated to practice code. This is auto-generated by CAM |
practiceCode | This value is updated based on the Auroradb column mapping in the metadata tab. |
industryCodeId | Displays the industry code Id associated to industry code. This is auto-generated by CAM |
industryCode | This value is updated based on the Auroradb column mapping in the metadata tab. |
docStatusCode | The document status code from the external system |
docStatusCodeid | The document status code id from the external system |
jurisdictionCode | Code of the jurisdiction |
jurisdictionCodeId | Id for the Code of the jurisdiction |
matterType | Displays the matter type |
matterTypeId | The id of the matter type |
matterSubType | The subtype of the matter |
matterSubTypeId | The id of the subtype |
class | The class of document (document, email, etc) |
classId | Displays the class Id associated to class. This is auto-generated by CAM |
classCodeId | Displays the class code Id associated to class code. This is auto-generated by CAM |
classCode | Code of the class. E.g. if class is document, typically the code is DOC |
subClassCode | This is updated for iManage only |
commentsId | Displays the comments Id associated to comments. This is auto-generated by CAM |
comments | Displays the comments added to the document |
isLatest | Flag to indicate if document updated recently |
isCheckedOut | Flag to indicate if document is checked out |
isApproved | Is the document approved? |
isArchived | Is the document archived? |
isAutoversioning | Is the document autoversioned, instead of manually versioned? |
GUID | A guid value from sql or source system. |
importedById | Id of the system imported by into CAM |
importedDate | Displays the date when the file was imported to CAM |
lastChangeDate | Displays the last update date |
lastChangeUserId | Displays the last change user id associated to the last change user. This is auto-generated by CAM. |
lastChangeUser | Displays the user name who last updated the document |
c1-c45 | Displays the custom metadata fields |
n1-n10 | Displays the custom metadata fields |
d1-d10 | Displays the custom metadata fields |
runId | The job id of the run. |
objectId | Id of the particular object reference type (matter,client etc) |
subscriberId | The subscriberid from Office365 |
system | Displays the external system name |
repositoryId | Displays the repository name |
createdBy | Displays the user name who created the file |
createdDate | Displays the file creation date |
writeDate | Displays the date the document was updated in CAM |
targetDatabase | The target database of a job to move a document/folder/workspace |
targetId | The id of the target database |
docId | Displays the auto generated document id. |
docIdHash | A hash reference of the docid |
isApproved | Flag to tell if the document/object is approved |
isArchived | Flag to tell if the document/object is archived |
isAutoversioning | Flag to show if the document has been autoversioned by the source. |
isEcho |
|
isExternal | Is an external stored document? |
isExternalUser | Is for an external user? |
isFavorite | Is a favorited document? |
isLocked | Is the document locked? |
isOfficialLocked | Is the document officially locked from the source? |
isQueueforIndexing | Is the document queued for indexing? |
isSigned | Is the document signed? Applies to NetDocuments only. |
repositoryName | Displays the repository name |
External System Table
Use the table name external_system_nt in your query.
Column | Description |
|---|---|
id | The internal CAM id of each external system. |
name | Name of the external system. E.g. iManage, Kira, 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
Use the table name external_system_auth in your query.
Column | Description |
|---|---|
id | The internal CAM id of each external system authentication row. |
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 |
system | Type of the external system role. E.g. iManage, HighQ, 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 HighQ server. Attempts(10) Consumed. Please contact system administrator.(Root Cause : 403 Forbidden) |
External System Role Table
Use the table name external_system_role in your query.
Column | Description |
|---|---|
id | The internal CAM id of each external system role. |
ext_id | The external system id from the external system table. |
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
Use the table name external_system_role_mapping in your query.
Column | Description |
|---|---|
id | The internal CAM id of each external system role mapping. |
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. |
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. |
Folder Table
Column | Description |
|---|---|
id | The unique internal id of the CAM folders/workspaces. |
extid | Displays the row number of the entry in the folder table. It is a mandatory field. For fileshare, this is the path |
name | Name of the folder. |
description | Description on the folder. |
extParentId | The id externally of the parent folder/workspace. |
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. Other systems, this is the path of the folder/workspace. |
extContainerName | If the configured External System is iManage this column displays the database name, followed by the folder name (e.g. demo!folder), in case of NetDocuments, this displays the folder name. It is a mandatory field. Other systems, this is the path of the folder/workspace. |
folderType | Displays the folder type (e.g. folder/saved search/workspace etc). See chart below: FolderTypeids. It is a mandatory field. |
parentId | Displays the parent folder Id associated to the parent folder. This is auto-generated by CAM. |
alternateparentid | The id of the alternate parent folder. |
extworkspaceId | Displays the workspace id associated to the workspace. If the configured External System is iManage this column displays the database name, followed by the workspace number (e.g. demo!1234), in case of NetDocuments, this displays the 12 digit workspace number (e.g. 4816-5122-8805) |
ctag |
|
etag |
|
documentNumber | The document number |
version | The document version |
indexNumber | The number of the index |
orderedById | Id the record is ordered by |
orderingDate | Date the record was ordered |
isDeleted | Is the folder/workspace deleted? |
importReference | Displays the folder Id from the Data Import |
isDeletedToken | If marked for deletion, this token is filled out |
extLongId | The long id version from the external system. For fileshare, this is the path. |
extShortId | The shorter id from the external system. For fileshare, this is the path. |
folderStatusId | Id of the folder status |
folderStatus | Status of the folder |
owner | Displays the folder owner name |
ownerId | Displays the folder Id associated to the folder. This is auto-generated by CAM. |
author | Displays the author of the folder/workspace. |
authorId | Displays the authorid of the folder/workspace. |
securityLevelId | Displays the security level Id associated to the security level. This is auto-generated by CAM. |
securityLevel | Displays the folder security level (inherit/view/private etc.) |
documentClass | This value is updated based on the Auroradb column mapping in the metadata tab. |
documentClassId | Displays the document class Id associated to the document class. This is auto-generated by CAM. |
clientCodeId | Displays the client code ID associated to client code. This is auto-generated by CAM |
clientCode | This value is updated based on the Auroradb column mapping in the metadata tab. |
matterCodeId | Displays the matter code ID associated to matter code. This is auto-generated by CAM |
matterCode | This value is updated based on the Auroradb column mapping in the metadata tab. |
practiceCodeId | Displays the practice code ID associated to practice code. This is auto-generated by CAM |
practiceCode | This value is updated based on the Auroradb column mapping in the metadata tab. |
industryCodeId | Displays the industry code ID associated to industry code. This is auto-generated by CAM |
industryCode | This value is updated based on the Auroradb column mapping in the metadata tab. |
matterType | Displays the matter type. |
matterTypeId | Displays the matter type id. |
matterSubType | Displays the matter subtype. |
mattersubtypeid | Displays the matter subtype id. |
areaOfLaw | The area of law categorization. For example, Intellectual Property |
areaOfLawId | The area of law id |
classCode | The fee earner class code |
classcodeid | The class code id |
deptCode | The department code |
deptCodeid | The department code id |
subClassCode | If the class has a subclass, this is the code for it. |
SubclassCodeid | The subclasscodeid |
comments | Comments on the folder/workspace |
matterStatusId | Id of the matter status |
MatterStatus | The status of the matter. For e.g. Open, Closed, Inactive, Opportunity |
sourceId | Id of the source record |
nblsNumber |
|
matteropenDate | Date the matter started |
mattercloseDate | Date the matter ended |
filedDate | NDate the folder was filed |
hasUnorderedDocuments | If the folder/workspace has unordered documents (blank documentorder), this is 1=Yes. |
templateId | The id of the template used. |
matterlocation | Location of the matter |
matterlocationid | Id of the matter location |
responsiblelawyer | The responsible lawyer of the matter. |
responsibleLawyerId | Id of the responsible lawyer |
originatinglawyer | The lawyer that originated the matter. |
originatingLawyerId | The id of the originating lawyer |
officeCode | The code of the office |
officecodeid | The id of the office |
countryCode | The code of the country |
countrycodeid | The id of the country |
jurisdictionCodeId | Id of the jurisdiction |
jurisdictionCode | The Jurisdiction of the matter |
courtCode | Code of the court |
courtcodeid | Id of the court |
judgename | Name of the judge |
onLegalHold | If the workspace or document is under a legal hold (in CAM or other system) |
legalHoldStartDate | Start of the legal hold |
legalHoldEndDate | End of the legal hold |
guid | A guid identifier for the record (unique) |
isLatest | Flag to indicate if the folder was recently updated |
isHipaa | Is the document or workspace dealing with a Hipaa procedure in medicine |
isCheckedOut | Is the folder/workspacechecked out? |
isDeclared | Is the folder/workspace declared? |
isApproved | Is the folder/workspace approved? |
isArchived | Is the folder/workspace archived? |
isAutoversioning | Is the folder/workspace auto versioned? |
isEcho |
|
isExternal | Is the folder/workspace external to the firm? |
isExternalUser | Is there an external user to the folder/workspace? |
isFavorite | Is the folder/workspace a favorite? |
isLocked | Is the folder/workspace locked? |
isOfficalLocked | Is the folder/workspace officially locked? |
isQueueforIndexing | Is the folder/workspace queued for indexing? |
isSigned | Is the folder/workspace signed? |
isOfficialVersion | Is the folder/workspace officially versioned (default) |
isShortcut | Is the folder/workspace a shortcut? |
importedById | Id of the person or system who imported the folder/workspace |
importedDate | Displays the date the folder was imported in CAM |
lastChangeDate | Displays the last update date |
lastChangeUserId | Displays the last change user id associated to the last change user. This is auto-generated by CAM. |
lastChangeUser | Displays the user name who last updated the document |
metadata | A JSON formatted row of all the metadata passed on the folder/workspace. Comma separated. |
c1-c45 | Displays the custom metadata fields |
n1-n10 | Displays the custom metadata fields |
d1-d10 | Displays the custom metadata fields |
createdDate | Displays the folder creation date |
createdBy | Displays the user name who created the folder |
EditedDate | The date last edited |
subscriberId | Displays the tenant subscriber Id |
extsystemid | Displays the external system id |
projectId | Displays the id of the project. From CAM’s Project import function in Directory tab |
ProfileEditDate | Date the profile was edited |
ProfileEditedUserId | The userid of the person who edited the profile |
profileEditedUser | The user that edited the profile |
camCreatedDate | If created by CAM in CAM, the date created. |
camCreatedBy | If created by CAM in CAM, the person that created (if applicable) |
camEditedDate | If using CAM, the date last edited |
camEditedBy | If using CAM, the person who last edited |
Groups Table
Column | Description |
|---|---|
id | Displays the id of the entry in the groups table in CAM |
groupId | Displays the group Id |
groupName | Displays the group name |
The email of the group | |
office | The office location of the group |
groupType | Displays the group type (Internal/External) Internal=0 External=1 |
groupContainerType | The type of container of the group |
extcontainerid | The id of the container (typically = the name) |
extcontainername | The container name where the group came from |
isCAMGroup | Is a group within CAM for CAM. 0= from any other ext system |
extid | The external id of the group |
Metadata | Displays in JSON format any metadata applied to the group. Comma separated. |
CreatedDate | Date the group was created |
EditedDate | Date the group was last edited |
CreatedbyUserid | User id of the person who created the group |
CreatedByUser | The user who created the group |
LastChangeUserid | The userid of the person who last edited the group |
LastChangeUser | The user who last edited the group |
camCreatedDate | If created in CAM, the created date |
camCreatedBy | If created in CAM, the user who created |
camEditedDate | If edited in CAM, the date edited |
camEditedBy | If edited in CAM, the user who edited |
extSystemId | The external system id |
subscriberid | The tenant subscriber id |
Metadata Table
Column | Description |
|---|---|
id | Displays the row number of the entry in the metadata table. |
columnName | Displays the column name mapped to the metadata in CAM |
name | Displays the metadata ID |
value | Displays the metadata desc |
parent | Displays the parent Id for the metadata |
attributes | N/A |
docSystemKey | Displays the metadata name |
objectId | N/A |
subscriberId | Displays the tenant subscriber Id |
system | Displays the external system name |
repositoryId | Displays the repository name in the external system |
sourceId | Displays the cabinet/database name |
lastEdited | Displays the last edited date and time |
createdDate | Displays the metadata created date |
createdBy | Displays the user name who created the metadata |
modifiedBy | Displays the user name who last modified the metadata |
writeDate | Displays the |
uidCustomMetadata |
|
repositoryName | Displays the repository name |
Security Table
Column | Description |
|---|---|
id | Displays the row number of the entry in the security table. |
documentId | Displays the document Id associated to the document. This is auto-generated by CAM. |
folderId | Displays the folder Id associated to the folder. This is auto-generated by CAM. |
userId | Displays the user Id associated to the user. This is auto-generated by CAM. |
userName | Displays the user name |
groupId | N/A |
groupName | N/A |
roleId | N/A |
denied | Displays if permission is denied to the user (0- allow /1-deny) |
accesspermissions | Displays the access permission code assigned to the user |
policyId | N/A |
expiryDate | Displays the security expiry date |
joinDate | Displays the security assigned date |
isLatest | Flag to indicate if security has been recently updated |
docId | N/A |
objectId | Displays the object Id associated to the object. This is auto-generated by CAM. |
objectType | Displays the object type. |
securityType | Displays the security type assigned (user/group) |
securityTypeId | Displays the security type Id associated to the security type. This is auto-generated by CAM |
securityTypeName | N/A |
access | Displays the access code associated to the user (3/2/1/0) |
accessLevel | Displays the access level assigned to the user (full access/read-write/read only/no-access) |
defaultSecurity | N/A |
runId | N/A |
subscriberId | Displays the tenant subscriber Id |
system | Displays the external system |
repositoryId | Displays the repository name |
documentDBName | Displays the database name |
writeDate | Displays |
createdDate | Displays the date the security was assigned to the user |
createdBy | N/A |
lastchangeDate | Displays the last update date |
lastchangeUserId | Displays the last change user id associated to the last change user. This is auto-generated by CAM. |
lastchangeUser | Displays the user name who last updated the security |
flag |
|
docIdHash | Displays the folder Id |
Users Table
Column | Description |
|---|---|
id | Displays the row number of the entry in the user table |
userId | Displays the user id associated to the user name. |
userName | Displays the user name |
emailId | Displays the user email Id |
office | N/A |
title | N/A |
phone | N/A |
location | N/A |
isExternal | N/A |
userType | Displays the user type (Internal/External) |
subscriberId | Displays the tenant subscriber Id |
system | Displays the external system name |
documentDBName | Displays the database name |
repositoryId | Displays the repository name |
writeDate | Displays |
flag | N/A |
createdOn | Displays the user creation date |
createdBy | N/A |
modifiedOn | Displays the last update date |
modifiedBy | Displays the user name who last updated the entry |
metadata | N/A |
sourceId | Displays the database name |
repositoryName | Displays the repository name |
User_Groups Table
Column | Description |
|---|---|
id | Displays the row number of the entry in the user_group table |
userId | Displays the user id associated to the user name. This is auto-generated by CAM. |
userSystemId | Displays the user name |
groupId | Displays the user id associated to the user name. This is auto-generated by CAM. |
groupSystemId | Displays the group name the user is associated to. |
subscriberId | Displays the tenant subscriber Id |
system | Displays the external system |
repositoryId | Displays the repository name |
documentDbName | Displays the database name |
flag |
|
created_on | Displays the date the user was added to the group |
created_by |
|
modified_on | Displays the last update date |
modified_by | N/A |
metadata | N/A |
sourceId | Displays the database name |
repositoryName | Displays the repository name |
- No labels