|
|
|
|
|
This topic contains a partial MOVEit Transfer database schema. This schema is helpful if you are writing your own custom reports against MOVEit Transfer log records.
The MOVEit Transfer database includes a menu of "actions" (for example, sec_signon, folder_add, file_upload, and more) described in an Ipswitch community knowledgebase article titled MOVEit Transfer Database Actions.
log (
ID bigint(20) NOT NULL auto_increment,
LogTime datetime default NULL,
Action nvarchar(16) default NULL,
InstID int(11) NOT NULL default '0',
Username nvarchar(128) default NULL,
TargetID nvarchar(128) NOT NULL default '',
TargetName nvarchar(128) NOT NULL default '',
FolderID int(11) NOT NULL default '0',
FileID nvarchar(12) default NULL,
IPAddress nvarchar(16) default NULL,
Error int(11) NOT NULL default '0',
Parm1 nvarchar(2500) default NULL,
Parm2 nvarchar(2500) default NULL,
Parm3 nvarchar(2500) default NULL,
Parm4 nvarchar(2500) default NULL,
Message ntext,
AgentBrand nvarchar(1024) default NULL,
AgentVersion nvarchar(16) default NULL,
XferSize double default '0',
Duration double NOT NULL default '0',
FileName nvarchar(1024) default NULL,
FolderPath nvarchar(2500) default NULL,
ResilNode tinyint(4) NOT NULL default '0',
Cert ntext,
Hash nvarchar(40) default NULL,
VirtualFolderID int(11) NOT NULL default '0',
VirtualFolderPath nvarchar(2500) default NULL
)
TIP: To see a quick list of different actions and error codes in YOUR log, either run this query from a database query tool:
SELECT Action,Error,Count(*) FROM log GROUP BY Action,Error;
...or run a custom report with these values:
Fields: Action,Error,Count(*)
Tables: log
Group By: Action,Error
Sample output:
Action Error Count(*)
sec_signoff 0 317
sec_signon 0 582
sec_signon 2025 72
activesessions (
Username nvarchar(128) NOT NULL default '',
RealName nvarchar(64) default NULL,
InstID int(11) NOT NULL default '0',
IPAddress nvarchar(16) default NULL,
LastTouch datetime default NULL,
SessionID nvarchar(32) default NULL,
DMZInterface int(11) NOT NULL default '0',
ResilNode int(11) NOT NULL default '0'
)
files (
ID nvarchar(12) NOT NULL default '',
InstID int(11) NOT NULL default '0',
FolderID int(11) NOT NULL default '0',
FileSize double NOT NULL default '0',
OriginalFileTypeID int(11) NOT NULL default '0',
UploadStamp datetime default NULL,
UploadUsername nvarchar(128) NOT NULL default '',
UploadComment ntext,
UploadIP nvarchar(64),
UploadAgentBrand nvarchar(1024) default NULL,
UploadAgentVersion nvarchar(16) default NULL,
DownloadCount int(11) NOT NULL default '0',
MaxDownloads int(11) NOT NULL default '1',
OriginalFilename nvarchar(400),
Deleted int(11) NOT NULL default '0',
Thumbnail int(11) NOT NULL default '0',
UploadIntegrity int(11) NOT NULL default '0',
ParentID nvarchar(12) NOT NULL default '0',
Recipients ntext,
Name nvarchar(1024),
DeliveryRcpt int(11) NOT NULL default '0',
Attachments nvarchar(4000),
ReadStatus int(11) NOT NULL default '0'
)
folders (
ID int(11) NOT NULL default '0',
InstID int(11) NOT NULL default '0',
Name nvarchar(400),
Owner nvarchar(128) NOT NULL default '',
Description ntext,
ResponsePath nvarchar(1024),
SystemType int(11) NOT NULL default '0',
FolderType int(11) NOT NULL default '0',
FileType int(11) NOT NULL default '0',
CleanType int(11) NOT NULL default '0',
CleanTime int(11) NOT NULL default '30',
SenderReminderType int(11) NOT NULL default '0',
SenderReminderTime int(11) NOT NULL default '60',
SenderReminderLastDoneStamp datetime default NULL,
RecipientReminderType int(11) NOT NULL default '3',
RecipientReminderTime int(11) NOT NULL default '15',
RecipientReminderLastDoneStamp datetime default NULL,
Deleted int(11) NOT NULL default '0',
ResponseType int(11) NOT NULL default '1',
ResponseText ntext,
ResponseTime int(11) NOT NULL default '10',
NewTime int(11) NOT NULL default '7',
HideHistory int(11) NOT NULL default '1',
Thumbnails int(11) NOT NULL default '0',
ParentID int(11) NOT NULL default '0',
ParentInheritRights int(11) NOT NULL default '1',
PostUploadNotificationType int(11) NOT NULL default '0',
EnforceUniqueFilenames int(11) NOT NULL default '0',
FolderPath nvarchar(2500),
Quota double NOT NULL default '0',
CreateStamp datetime default NULL,
AllowFileOverwrite int(11) NOT NULL default '0',
FileMasks ntext,
FileMaskRule int(11) NOT NULL default '0',
SubfolderCleanTime int(11) NOT NULL default '0',
ResponseSubject nvarchar(255) NOT NULL default '',
LastChangeStamp datetime default NULL,
NoDownloadNotificationType int(11) NOT NULL default '0',
NoDownloadNotificationTime int(11) NOT NULL default '30'
)
folderuser (
ID int(11) NOT NULL auto_increment,
Username nvarchar(128) default NULL,
GroupID int(11) NOT NULL default '0',
FolderID int(11) NOT NULL default '0',
Relationship int(11) NOT NULL default '0',
InstID int(11) NOT NULL default '0',
OverrideFlag int(11) NOT NULL default '0'
)
groups (
ID int(11) NOT NULL auto_increment,
InstID int(11) NOT NULL default '0',
Name nvarchar(128) default NULL,
Description ntext,
MaxMemberQuota double NOT NULL default '0',
CanCreateTempUsers int(11) NOT NULL default '0',
AdminFolderAccess int(11) NOT NULL default '0',
DisplayProfile int(11) NOT NULL default '-1',
AllowAttachments int(11) NOT NULL default '0',
MaxMaxAttchSize double NOT NULL default '0',
AdminTempUserAccess int(11) NOT NULL default '1',
TempUsersInAddrBookExpansion int(11) NOT NULL default '0',
AdminMemberAccess int(11) NOT NULL default '1',
AdminMembershipAccess int(11) NOT NULL default '0',
AdminMemberPasswordAccess int(11) NOT NULL default '1',
)
groupuser (
ID int(11) NOT NULL auto_increment,
InstID int(11) NOT NULL default '0',
GroupID int(11) NOT NULL default '0',
Username nvarchar(128) default NULL,
Relationship int(11) NOT NULL default '0'
)
msgposts (
ID int(11) NOT NULL auto_increment,
InstID int(11) NOT NULL default '0',
FileID nvarchar(12) NOT NULL default '0',
FileTypeID int(11) NOT NULL default '0',
FileSize double NOT NULL default '0',
UploadStamp datetime default NULL,
UploadUsername nvarchar(128) NOT NULL default ''
)
newfiles (
FileID nvarchar(12) NOT NULL default '0',
FolderID int(11) NOT NULL default '0',
Username nvarchar(128) NOT NULL default '',
InstID int(11) NOT NULL default '0'
)
NOTE: The users table schema has changed recently to include the LoginName field. Custom reports, or other processes using the users table and listing out usernames should now use LoginName instead of Username.
users (
Username nvarchar(128) NOT NULL default '',
LoginName nvarchar(128) NOT NULL default '',
InstID int(11) NOT NULL default '0',
RealName nvarchar(128) default NULL,
Password nvarchar(80) default NULL,
Email ntext,
Notes ntext,
LastLoginStamp datetime default NULL,
PasswordChangeStamp datetime default NULL,
Permission int(11) NOT NULL default '0',
Deleted int(11) NOT NULL default '0',
Status nvarchar(16) NOT NULL default 'active',
StatusNote ntext,
PassHistory ntext,
UseCustomHostPermits int(11) NOT NULL default '0',
MustChangePassword int(11) NOT NULL default '0',
ExemptFromPasswordAging int(11) NOT NULL default '0',
ReceivesNotification int(11) NOT NULL default '1',
CreateStamp datetime default NULL,
ExpireStamp datetime default NULL,
AuthMethod int(11) NOT NULL default '0',
LastChangeStamp datetime default NULL,
Quota double NOT NULL default '0',
DefaultFolder int(11) NOT NULL default '0',
TempPassword nvarchar(80) NOT NULL default '',
UserListLength int(11) NOT NULL default '10',
CanCreateTempUsers int(11) NOT NULL default '2',
FileListLength int(11) NOT NULL default '100',
MessagingSignature varchar(255) default '',
CreateUsername nvarchar(128) default '',
DenyMultiSignons int(11) NOT NULL default '0',
AllowAttachments int(11) NOT NULL default '2',
MaxAttchSize double NOT NULL default '0',
LangUser nvarchar(12) NOT NULL default 'en',
EmailFormat int(11) NOT NULL default '1',
AuthSourceID int(11) NOT NULL default '0',
FTPCertRequired int(11) NOT NULL default '0',
FTPCertPlusPW int(11) NOT NULL default '1',
SSHCertRequired int(11) NOT NULL default '0',
SSHCertPlusPW int(11) NOT NULL default '0',
AllowedInterfaces int(11) NOT NULL default '31',
ExpirationPolicy int(11) NOT NULL default '0',
DefaultMsgDelivRcpt int(11) NOT NULL default '0',
ChrootDefaultFolder int(11) NOT NULL default '0',
)
certinfo (
ID int(11) NOT NULL default '0',
Username nvarchar(128) default NULL,
InstID int(11) NOT NULL default '0',
CertType int(11) NOT NULL default '0',
DataType int(11) NOT NULL default '0',
Timestamp datetime default NULL,
CertData ntext
)
sysstats - see also Scheduled Tasks - SysStat - (
ID bigint(20) NOT NULL auto_increment,
StatTime datetime default NULL,
FilesDriveRootPath nvarchar(128) NOT NULL default '',
FilesDriveSpaceFree double NOT NULL default '0',
FilesDriveSpaceUsed double NOT NULL default '0',
FilesSpaceUsed double NOT NULL default '0',
DBDriveRootPath nvarchar(128) NOT NULL default '',
DBDriveSpaceFree double NOT NULL default '0',
DBDriveSpaceUsed double NOT NULL default '0',
DBSpaceUsed double NOT NULL default '0',
LogsDriveRootPath nvarchar(128) NOT NULL default '',
LogsDriveSpaceFree double NOT NULL default '0',
LogsDriveSpaceUsed double NOT NULL default '0',
LogsSpaceUsed double NOT NULL default '0',
FilesTotalDB int(10) NOT NULL default '0',
FilesSizeTotalDB double NOT NULL default '0',
CPUUsagePercentTotal tinyint(3) NOT NULL default '0',
CPUUsagePercentDMZ tinyint(3) NOT NULL default '0',
CPUUsagePercentISAPI tinyint(3) NOT NULL default '0',
CPUUsagePercentIIS tinyint(3) NOT NULL default '0',
CPUUsagePercentDB tinyint(3) NOT NULL default '0',
CPUUsagePercentDMZFTP tinyint(3) NOT NULL default '0',
CPUUsagePercentDMZSSH tinyint(3) NOT NULL default '0',
CPUUsagePercentSched tinyint(3) NOT NULL default '0',
CPUUsagePercentCentral tinyint(3) NOT NULL default '0',
CPUUsagePercentResil tinyint(3) NOT NULL default '0',
MemUsedTotal double NOT NULL default '0',
MemFreeTotal double NOT NULL default '0',
MemUsedDMZ double NOT NULL default '0',
MemUsedISAPI double NOT NULL default '0',
MemUsedIIS double NOT NULL default '0',
MemUsedDB double NOT NULL default '0',
MemUsedDMZFTP double NOT NULL default '0',
MemUsedDMZSSH double NOT NULL default '0',
MemUsedSched double NOT NULL default '0',
MemUsedCentral double NOT NULL default '0',
MemUsedResil double NOT NULL default '0',
VMSizeDMZ double NOT NULL default '0',
VMSizeISAPI double NOT NULL default '0',
VMSizeIIS double NOT NULL default '0',
VMSizeDB double NOT NULL default '0',
VMSizeDMZFTP double NOT NULL default '0',
VMSizeDMZSSH double NOT NULL default '0',
VMSizeSched double NOT NULL default '0',
VMSizeCentral double NOT NULL default '0',
VMSizeResil double NOT NULL default '0',
HandlesTotal int(10) NOT NULL default '0',
HandlesDMZ int(10) NOT NULL default '0',
HandlesISAPI int(10) NOT NULL default '0',
HandlesIIS int(10) NOT NULL default '0',
HandlesDB int(10) NOT NULL default '0',
HandlesDMZFTP int(10) NOT NULL default '0',
HandlesDMZSSH int(10) NOT NULL default '0',
HandlesSched int(10) NOT NULL default '0',
HandlesCentral int(10) NOT NULL default '0',
HandlesResil int(10) NOT NULL default '0',
ProcessesTotal int(10) NOT NULL default '0',
ThreadsTotal int(10) NOT NULL default '0',
ThreadsDMZ int(10) NOT NULL default '0',
ThreadsISAPI int(10) NOT NULL default '0',
ThreadsIIS int(10) NOT NULL default '0',
ThreadsDB int(10) NOT NULL default '0',
ThreadsDMZFTP int(10) NOT NULL default '0',
ThreadsDMZSSH int(10) NOT NULL default '0',
ThreadsSched int(10) NOT NULL default '0',
ThreadsCentral int(10) NOT NULL default '0',
ThreadsResil int(10) NOT NULL default '0',
SessionsTotal int(10) NOT NULL default '0',
SessionsActive int(10) NOT NULL default '0',
ResilNode tinyint(3) NOT NULL default '0'
)
Note: In the above listing, ResilNode is the web farm node for the application doing the DB insert.
Note: In the above listing, the following were for recording information about programs used in the now-deprecated Resiliency service: CPUUsagePercentResil, MemUsedResil, VMSizeResil, HandlesResil, and ThreadsResil.
To see a full database schema, if you have selected MySQL as your database engine, issue this command with the appropriate credentials from the appropriate path using the Windows command line.
D:\MySQL\Bin>mysqldump --user=root --password=mypass -d moveitdmz > d:\temp\dbdump.txt