|
|
|
|
|
A partial MOVEit DMZ database schema is listed below. This schema will be helpful if you are writing your own custom reports against MOVEit DMZ log records.
log (
ID bigint(20) NOT NULL auto_increment,
LogTime datetime default NULL,
Action varchar(16) default NULL,
InstID int(11) NOT NULL default '0',
Username varchar(128) default NULL,
TargetID varchar(128) NOT NULL default '',
TargetName varchar(128) NOT NULL default '',
FolderID int(11) NOT NULL default '0',
FileID varchar(12) default NULL,
IPAddress varchar(16) default NULL,
Error int(11) NOT NULL default '0',
Parm1 varchar(4096) default NULL,
Parm2 varchar(4096) default NULL,
Parm3 varchar(4096) default NULL,
Parm4 varchar(4096) default NULL,
Message text,
AgentBrand varchar(1024) default NULL,
AgentVersion varchar(16) default NULL,
XferSize double default '0',
Duration double NOT NULL default '0',
FileName varchar(1024) default NULL,
FolderPath varchar(4096) default NULL,
ResilNode tinyint(4) NOT NULL default '0',
Cert text,
Hash varchar(40) default NULL,
VirtualFolderID int(11) NOT NULL default '0',
VirtualFolderPath varchar(4096) default NULL
)
HINT: 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 varchar(128) NOT NULL default '',
RealName varchar(64) default NULL,
InstID int(11) NOT NULL default '0',
IPAddress varchar(16) default NULL,
LastTouch datetime default NULL,
SessionID varchar(32) default NULL,
DMZInterface int(11) NOT NULL default '0',
ResilNode int(11) NOT NULL default '0'
)
files (
ID varchar(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 varchar(128) NOT NULL default '',
UploadComment text,
UploadIP text,
UploadAgentBrand varchar(24) default NULL,
UploadAgentVersion varchar(8) default NULL,
DownloadCount int(11) NOT NULL default '0',
MaxDownloads int(11) NOT NULL default '1',
OriginalFilename text,
Deleted int(11) NOT NULL default '0',
Thumbnail int(11) NOT NULL default '0',
UploadIntegrity int(11) NOT NULL default '0',
ParentID varchar(12) NOT NULL default '0',
Recipients text,
Name text,
DeliveryRcpt int(11) NOT NULL default '0',
Attachments text,
ReadStatus int(11) NOT NULL default '0'
)
folders (
ID int(11) NOT NULL default '0',
InstID int(11) NOT NULL default '0',
Name text,
Owner varchar(128) NOT NULL default '',
Description text,
ResponsePath text,
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 text,
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 text,
Quota double NOT NULL default '0',
CreateStamp datetime default NULL,
AllowFileOverwrite int(11) NOT NULL default '0',
FileMasks text,
FileMaskRule int(11) NOT NULL default '0',
SubfolderCleanTime int(11) NOT NULL default '0',
ResponseSubject varchar(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 varchar(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 varchar(128) default NULL,
Description text,
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 varchar(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 varchar(12) NOT NULL default '0',
FileTypeID int(11) NOT NULL default '0',
FileSize double NOT NULL default '0',
UploadStamp datetime default NULL,
UploadUsername varchar(128) NOT NULL default ''
)
newfiles (
FileID varchar(12) NOT NULL default '0',
FolderID int(11) NOT NULL default '0',
Username varchar(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 varchar(128) NOT NULL default '',
LoginName varchar(128) NOT NULL default '',
InstID int(11) NOT NULL default '0',
RealName varchar(128) default NULL,
Password varchar(32) default NULL,
Email text,
Notes text,
LastLoginStamp datetime default NULL,
PasswordChangeStamp datetime default NULL,
Permission int(11) NOT NULL default '0',
Deleted int(11) NOT NULL default '0',
Status varchar(16) NOT NULL default 'active',
StatusNote text,
PassHistory text,
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 varchar(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 varchar(128) default '',
DenyMultiSignons int(11) NOT NULL default '0',
AllowAttachments int(11) NOT NULL default '2',
MaxAttchSize double NOT NULL default '0',
LangUser varchar(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 varchar(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 text
)
sysstats - see also Scheduled Tasks - SysStat - (
ID bigint(20) NOT NULL auto_increment,
StatTime datetime default NULL,
FilesDriveRootPath varchar(128) NOT NULL default '',
FilesDriveSpaceFree double NOT NULL default '0',
FilesDriveSpaceUsed double NOT NULL default '0',
FilesSpaceUsed double NOT NULL default '0',
DBDriveRootPath varchar(128) NOT NULL default '',
DBDriveSpaceFree double NOT NULL default '0',
DBDriveSpaceUsed double NOT NULL default '0',
DBSpaceUsed double NOT NULL default '0',
LogsDriveRootPath varchar(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