TECHNICAL NOTE

 

Fasoo FED Server Usage Logs

Administrators and users with appropriate permissions can access document usage logs in the Fasoo Enterprise DRM (FED) administrator console (e.g., https://fds.fasoo.com/fed5/wp/usagedoc).  Once the user sets search criteria, the user can export the results to a CSV file for further analysis.


Some customers want to import document usage logs into a SIEM or other tool for further analysis.  The sections below show SQL queries to generate usage log output for direct input into an analysis tool.


MySQL and MariaDB Queries

Run the following MySQL query to generate usage log output for direct input into an analysis tool.  Save the query to a file (e.g., Usage_log_MYSQL.sql) if you plan to run it programmatically. A sample is attached to this article.



SELECT 

         a.user_code AS 'User ID', 

         IFNULL(c.user_name, '') AS 'User name', 

         a.position_code AS 'Job title code', 

         a.position_name AS 'Job title name', 

         IFNULL(c.user_dept_name, '') AS 'Group name', 

         a.log_date AS 'Date used', 

         INET_NTOA(a.ip) AS 'IP address', 

         CASE a.client_os WHEN '2' THEN 'Mac' WHEN '1' THEN 'Windows' ELSE a.client_os END AS 'OS', 

         a.current_content_name AS 'File name', 

         IFNULL(b.content_name, '') AS 'Original file name', 

         IFNULL(b.content_code, '') AS 'Content ID', 

         CASE a.product WHEN '2' THEN 'FED-R' WHEN '1' THEN 'FED-N' WHEN '16' THEN 'FED-N' WHEN '4' THEN 

        'FED-E' ELSE a.product END AS 'File type', 

         CONCAT(COALESCE(b.system_code, " "), (CASE WHEN b.sec_level_name = 'N/A' THEN '' ELSE

          b.sec_level_name END)) AS 'Class/System', 

         a.owner_name AS 'Owner name', 

         a.owner_code AS 'Owner ID', 

         CASE d.name WHEN 'VIEW' THEN 'View' WHEN 'SECURE_SAVE' THEN 'Edit' WHEN 'CREATE_NEW' THEN 'Create'

         WHEN 'MOD_ACL' THEN 'Change permission' WHEN 'MOD_SECLEVEL' THEN 'Change class' WHEN 'SAVE' THEN

        'Decrypt' WHEN 'MOD_OWNER' THEN 'Change Owner' WHEN 'SECURE_PRINT' THEN 'Print with watermark' 

         WHEN 'SAVE_API' THEN 'System Decryption' WHEN 'CREATE_NEW_API' THEN 'System Encryption' WHEN

        'BLOCK_PRINTING_DETECTED_FILE' THEN 'Block Printing Detected File' ELSE d.name END AS 'Action', 

         (CASE WHEN a.usage_result=1 THEN CASE WHEN a.log_type=5 THEN 'Success (Provisional license)' ELSE

        'Success' END ELSE 'Failure' END) AS 'Result', 

         b.etc1, b.etc2, b.etc3, b.etc4, b.etc5 

FROM fut_log a 

LEFT OUTER JOIN fut_content b ON a.content_id = b.content_id 

LEFT OUTER JOIN fut_user c ON a.user_id = c.user_id 

LEFT OUTER JOIN fut_report_infomation d ON a.purpose = d.code WHERE d.divide_type = '1'

ORDER BY a.log_id DESC

 


Send MySQL Query Output to File

If your SIEM or other analysis tool does not allow you to run a SQL query to import document usage logs directly, you can use a PowerShell script to generate a CSV file to import.


Start a PowerShell session and run the following to install the SqlServer module from the PowerShell Gallery onto your PC or server.


PowerShell

Install-Module -Name SqlServer
Install-Module -Name SimplySql
Import-Module SimplySql -Force 
Install-Module -Name MySQLCmdlets
Import-Module MySQLCmdlets -Force 


After you install the SqlServer module, create a PowerShell script (e.g., usageLogs.ps1) using parameters and the example below to run a SQL script and generate a CSV file to import into your analysis tool.


Parameter

Setting

#SERVER#

The Fasoo database server name or IP address

#INSTANCE#

The Fasoo database instance, if one exists

#PORT#

The Fasoo database server port

#FED_DB#

FED database name

#FED_DB_User#

FED database user name; not needed if using integrated security

#FED_DB_Password#

FED database user password; not needed if using integrated security

#SQL_SCRIPT#

SQL script to generate usage logs

#OUTPUT#

Output of SQL script, typically a CSV

PowerShell

 

Open-MySqlConnection 

        -Server #SERVER#

        -Database #FED_DB# 

        -Username #FED_DB_User# 

        -Password #FED_DB_Password#

        -SSLMode None

PowerShell Example

 

Invoke-Sqlcmd

        -ServerInstance "192.168.1.2\PW4000765762S,1434"

        -Database "FED5" 

        -Username "fasoo" 

        -Password "Fasoo2023!" 

        -TrustServerCertificate

        -InputFile "C:\Fasoo\Usage_log_MSSQL.sql" | Export-Csv -Path "C:\Fasoo\FasooLogs.csv"

 

 PowerShell Example with Integrated Security

 

Invoke-Sqlcmd 

        -ServerInstance "192.168.1.2\PW4000765762S,1434"

        -Database "FED5" 

        -Username "fasoo" 

        -Password "Fasoo2023!" 

 

 

 

 

Schedule Script

If your SIEM or other analysis tool does not allow you to run a SQL query to directly import document usage logs, you can use a PowerShell script to generate a csv file to import.   Below is an example of scheduling a task in Task Scheduler using PowerShell.


PowerShell Example

 

$Action=New-ScheduledTaskAction -Execute PowerShell.exe -WorkingDirectory C:\Fasoo
 -Argument "C:\Fasoo\usageLogs.ps1"

$Time=New-ScheduledTaskTrigger -At 12:00 -Daily

Register-ScheduledTask -TaskName "Schedule Fasoo Usage Logs" -Trigger $Time -Action $Action
 -RunLevel Highest 

 

 

Sample scripts and this tech note are attached.