Database Plugin Details
The database plugin enables Directory Monitor to write events that occur on a specified directory to a database of your choice.
The following relational database types are supported:
Microsoft SQL Server (2005/2008/2012/2014/2016/2017)
MYSQL 5.x
PostgeSQL 8.x+
Oracle 10g+
SQLite 3.x
To configure the plugin, add or edit a directory and browse to the Database tab. The instructions assume you are evaluating the software or you have already purchased the plugin or a PRO unlock key.
Enable saving to a database for the directory you are adding/editing.
Previously used connection information will appear in the drop-down list to quickly populate the fields. These will only be visible to your user and not shared by anyone else on the system, even in Shared-User Mode.
Choose the appropriate database provider that you will be using. TCP/IP allows you to provide a port, Name Pipes are for inter-process communication with a locally installed database.
Provide the server name or address and port number (if enabled) to connect to.
Select whether to use NTLM authentication, disabling this option will allow you to enter a username and password.
Warning: If you are using Directory Monitor as a background service then be careful when selecting "Use Windows authentication", the installed service user may not be authorized to access the database the same way your currently logged in use can.
Provide a username and password if necessary to access the database.
The database drop-down list will automatically be populated with non-system databases on the server if you can successfully connect to it. To create a new database simply type a new name and click the Create button.
Always make sure you test your settings to ensure you can successfully connect to the database server. If anything goes wrong, the error message that appears will usually tell you exactly what you need to fix.
The database that is created (regardless of provider) will have the following schema:
Here are some sample queries you can use to get the data from a specific database provider.
Microsoft SQL Server
SELECT e.EventTime AS [EventTime],
et.Name AS [EventType],
d.Path AS [Directory],
e.Path AS [FullePath],
CASE WHEN LEN(e.Path) > 0 THEN RIGHT(e.Path, CHARINDEX('\', REVERSE(e.Path)) - 1) ELSE '' END AS [FileName],
e.OldPath AS [OldFullPath],
CASE WHEN LEN(e.OldPath) > 0 THEN RIGHT(e.Path, CHARINDEX('\', REVERSE(e.OldPath)) - 1) ELSE '' END AS [OldFileName],
u.Name AS [UserName],
p.Name AS [ProcessName]
FROM [EventInfo] e
JOIN [EventType] et ON et.EventTypeId = e.EventTypeId
JOIN [Directory] d ON d.DirectoryId = e.DirectoryId
LEFT JOIN [UserInfo] u ON u.UserId = e.UserId
LEFT JOIN [ProcessInfo] p ON p.ProcessId = e.ProcessId
ORDER BY e.EventTime
MySQL
SELECT e.EventTime EventTime,
et.Name EventType,
d.Path Directory,
e.Path FullePath,
CASE WHEN LENGTH(e.Path) > 0 THEN RIGHT(e.Path, LOCATE('\\', REVERSE(e.Path)) - 1) ELSE '' END FileName,
e.OldPath OldFullPath,
CASE WHEN LENGTH(e.OldPath) > 0 THEN RIGHT(e.Path, LOCATE('\\', REVERSE(e.OldPath)) - 1) ELSE '' END OldFileName,
u.Name UserName,
p.Name ProcessName
FROM EventInfo e
JOIN EventType et ON et.EventTypeId = e.EventTypeId
JOIN Directory d ON d.DirectoryId = e.DirectoryId
LEFT JOIN UserInfo u ON u.UserId = e.UserId
LEFT JOIN ProcessInfo p ON p.ProcessId = e.ProcessId
ORDER BY e.EventTime;
PostgreSQL
SELECT event_info."event_time" EventTime,
event_type."name" EventType,
directory."path" Directory,
event_info."path" FullePath,
CASE WHEN LENGTH(event_info."path") > 0
THEN RIGHT(event_info."path", POSITION('\' in REVERSE(event_info."path")) - 1)
ELSE '' END FileName,
event_info."old_path" OldFullPath,
CASE WHEN LENGTH(event_info."old_path") > 0
THEN RIGHT(event_info."path", POSITION('\' in REVERSE(event_info."old_path")) - 1)
ELSE '' END OldFileName,
user_info."name" UserName,
process_info."name" ProcessName
FROM event_info
JOIN event_type ON event_type."event_type_id" = event_info."event_type_id"
JOIN directory ON directory."directory_id" = event_info."directory_id"
LEFT JOIN user_info ON user_info."user_id" = event_info."user_id"
LEFT JOIN process_info ON process_info."process_id" = event_info."process_id"
ORDER BY event_info."event_time"
Oracle
SELECT e.EventTime EventTime,
et.Name EventType,
d.Path Directory,
e.Path FullePath,
SUBSTR(e.Path, INSTR(e.Path, '\', -1) + 1) FileName,
e.OldPath OldFullPath,
SUBSTR(e.OldPath, INSTR(e.OldPath, '\', -1) + 1) OldFileName,
u.Name UserName,
p.Name ProcessName
FROM EVENTINFO e
JOIN EVENTTYPE et ON et.EventTypeId = e.EventTypeId
JOIN DIRECTORY d ON d.DirectoryId = e.DirectoryId
LEFT JOIN USERINFO u ON u.UserId = e.UserId
LEFT JOIN PROCESSINFO p ON p.ProcessId = e.ProcessId
ORDER BY e.EventTime
Testing
A good way to test the database plugin is to watch the Activity Log tab on the main screen. This tab will display warnings or errors that may be occurring while trying to connect and insert records in the database. For even more detailed information about what is happening inside the plugins you can select "Everything" in the logging options:
