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 | ||||||||
|---|---|---|---|---|---|---|---|---|
|
...
| title | Document |
|---|
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
...
N/A
...
isFavorite
...
N/A
...
isLocked
...
N/A
...
isOfficalLocked
...
N/A
...
isQueueforIndexing
...
N/A
...
isSigned
...
Applies to NetDocuments only.
...
repositoryName
...
Displays the repository name
Expand
| title | Audit |
|---|
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. Displays the docid.
folderId
It is a mandatory field if a folderid 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
camCreatedDate
camCreatedBy
extSystemId
subscriberId
Displays the tenant subscriber Id.
runId
actionDate
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 | Security |
|---|
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
| title | Folder |
|---|
Folder Table
Column
Description
extid
Displays the row number of the entry in the folder table. It is a mandatory field.
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.
folderType
Displays the folder type (e.g. folder/saved search/workspace etc). See chart below: FolderTypeids. It is a mandatory field.
folderdescription
Displays the folder name
parentFolder
Displays the parent folder name.
parentFolderId
Displays the parent folder Id associated to the parent folder. This is auto-generated by CAM.
workspaceId
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)
documentNumber
N/A
version
N/A
indexNumber
N/A
orderedById
N/A
orderingDate
N/A
isDeleted
N/A
importReference
Displays the folder Id
isDeletedToken
N/A
folderStatusId
N/A
folderStatus
N/A
owner
Displays the folder owner name
ownerId
Displays the folder Id associated to the folder. This is auto-generated by CAM.
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
matterSubType
This value is updated based on the Auroradb column mapping in the metadata tab.
areaOfLaw
N/A
classCode
N/A
deptCode
N/A
subClassCode
N/A
comments
N/A
matterShortName
N/A
matterFamilyNumber
N/A
matterStatusId
N/A
sourceId
N/A
nblsNumber
N/A
startDate
N/A
closeDate
N/A
retentionStartDate
N/A
retentionEndDate
N/A
filedDate
N/A
hasUnorderedDocuments
N/A
templateId
N/A
location
N/A
responsibleLawyerId
N/A
originatingLawyerId
N/A
billingLawyerId
N/A
officeCode
N/A
countryCode
N/A
juristicationCodeId
N/A
juristicationCode
N/A
courtCode
N/A
judgeCode
N/A
onLegalHold
N/A
legalHoldStartDate
N/A
legalHoldEndDate
N/A
guId
N/A
importedById
N/A
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
c1-c45
Displays the custom metadata fields
n1-n10
Displays the custom metadata fields
d1-d10
Displays the custom metadata fields
runId
N/A
objectId
N/A
createdDate
Displays the folder creation date
createdBy
Displays the user name who created the folder
isLatest
Flag to indicate if the folder was recently updated
subscriberId
Displays the tenant subscriber Id
system
Displays the external system
repositoryId
Displays the repository name
writeDate
Displays
targetDatabase
N/A
targetId
N/A
isApproved
N/A
isArchived
N/A
isAutoversioning
N/A
isEcho
N/A
isExternal
N/A
isExternalUser
N/A
isFavorite
Applies for workspaces
isLocked
N/A
isOfficalLocked
N/A
isQueueforIndexing
N/A
isSigned
N/A
repositoryName
N/A
alternateFolderId
N/A
| title | Metadata |
|---|
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
| title | Users |
|---|
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
| title | Groups |
|---|
Groups Table
Column
Description
id
Displays the row number of the entry in the groups table
groupId
Displays the group Id
group_Member
Display the list of the user added into group
Note: Group members synchronize to iManage once a day if any changes to the group members will reflect in next day sync.
groupName
Displays the group name
N/A
office
N/A
groupType
Displays the group type (Internal/External)
isExternal
Flag indicating if the group is marked as external
subscriberId
Displays the tenant subscriber Id
system
Displays the external system
repositoryId
Displays the repository name
documentDBName
Displays the database name
writeDate
Displays the entry date in CAM
flag
created_on
Displays the group creation date
created_by
Displays the user name who created the group
modified_on
Displays the last update date
modified_by
N/A
metadata
N/A
sourceId
Displays the database name
isCabinetGroup
repositoryName
Displays the repository name
| title | User_groups |
|---|
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
| title | Folder Type Ids |
|---|
Folder_Type_Ids Table
Use these Id’s to search for specific folder results in Analytics, or use them in Request Workflow queries specifying specific folder id's.
Folder Type
FolderTypeId
Workspace
1
Folder
2
iManage_Tab
3
iManage_Search_Folder
4
iManage_Category_Folder
5
iManage_Folder_Shortcut
6
iManage_Document_Shortcut
7
ND_Filter
8
ND_Saved_Search
9
iManage_My_Matters
10
iManage_My_Favorites
11
Office_Channel
12
Office_Site
13
Office_Team
14
Office_Onedrive
15
Office_Private_Channel
16
HighQ_Site
17
18
| title | External System |
|---|
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. ) 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
| Child pages (Children Display) | ||||
|---|---|---|---|---|
|