Analytics Schema and Tables

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.

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.

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

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.

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)

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.

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.

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

id

Displays the id of the entry in the groups table in CAM

groupId

Displays the group Id

groupName

Displays the group name

email

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

id

Displays the id of the entry in the group_members table in CAM

groupid

Displays the id of the group

userid

Displays the id of the user

extgroupid

Contains the id of the external system group

extuserid

Contains the id of the external system user

extcontainerid

Contains the id of the external system container

extcontainername

Contains the name of the external system container.

Metadata

Displays in JSON format any metadata applied to the group membership. Comma separated.

CreatedDate

Date the group membership was created

EditedDate

Date the group membership was last edited

CreatedbyUserid

User id of the person who created the group membership

CreatedByUser

The user who created the group membership

LastChangeUserid

The userid of the person who last edited the group membership

LastChangeUser

The user who last edited the group membership

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

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

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

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

email

Displays the user email Id

office

Displays the office to which the user belongs

title

Displays the title currently associated with the user

phone

Displays phone number currently associated with the user

location

Displays the location currently associated with the user

isExternal

Is this an external user?

userType

Displays the user type (Internal/External)

userContainerType

Displays the container type

extContainerId

Displays the external container id

ExtContainerName

Displays the name of the external container

isCamUser

Displays whether the user is an active CAM user

extId

Displays the external id associated with the user

metadata

Any metadata added/changed/updated/deleted

createdDate

Displays the user creation date

editedDate

Displays the last date the user was edited

createdByUserId

Displays the user id that created the user

createdByUser

Displays the user name that created the user

lastChangeUserId

Displasy the user id that last changed the user

lastChangeUserId

Displasy the user id that last changed the user

camCreateDate

Displays the date in which the user was created in CAM

camCreatedBy

Displays the user that created the user record in CAM.

camEditedDate

Displays the last date the user was edited in cam

camEditedBy

Displays the user that last edited the user record in CAM

extSystemId

Displays the id of the external system.

subscriberId

The tenant subscription id