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.
| Table of Contents | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Expand
| title | Audit |
|---|
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
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.
| title | Document |
|---|
Document Table
This table contains all the documents within CAM.
Query the table by: select * from document limit 100
^Use the document Query type
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
| title | External System |
|---|
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
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.
| title | External System Authentication |
|---|
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
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)
| title | External System Role |
|---|
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
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.
| title | External System Role |
|---|
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
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.
| title | Folder |
|---|
Folder Table
This table contains all the workspaces and folders within CAM.
Query the table by: select * from folder limit 100
^Use the folder Query type
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
| title | Groups |
|---|
Groups Table
This table contains all the groups within CAM.
Query the table by: select * from groups limit 100
^Use the groups Query type
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
| title | Metadata |
|---|
Metadata Table
This table contains all the metadata within CAM.
Query the table by: select * from metadata limit 100
^Use the metadata Query type
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
The attributes of the metadata values
docSystemKey
Displays the metadata name
objectId
The id of the object of the metadata (matter, client are examples)
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 date the metadata was written to the system
uidCustomMetadata
Shows any custom metadata inputted within CAM to the field.
repositoryName
Displays the repository name
| title | Security |
|---|
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
id
Displays the row number id of the entry in the security table.
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
docIdHash
Displays the hash from the table
| title | Users |
|---|
User Table
This table contains all the users within CAM.
Query the table by: select * from user limit 100
^Use the users Query type
| Info |
|---|
Important: Don’t type users as the table name; this is an old antiquated table and you won’t get proper results. |
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
| title | User_groups |
|---|
User_Groups Table
This table contains all the user group information within CAM.
Query the table by: select * from user_groups limit 100
^Use the groups Query type
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
| Child pages (Children Display) | ||||
|---|---|---|---|---|
|