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.,  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.

Microsoft SQL Queries

Since Microsoft SQL Server does not have a built-in function to convert IP addresses from decimal format to standard dotted decimal notation, create a function shown below to convert an IP address.

CREATE FUNCTION dbo.ipIntToString 

    @ip bigINT 




    DECLARE @o1 bigINT, 

        @o2 bigINT, 

        @o3 bigINT, 

        @o4 bigINT 


    IF ABS(@ip) > 4294967295 

        RETURN '' 


    SET @o1 = @ip / 16777216 


    IF @o1 = 0 

        SELECT @o1 = 255, @ip = @ip + 16777216 

    ELSE IF @o1 < 0 


        IF @ip % 16777216 = 0 

            SET @o1 = @o1 + 256 



            SET @o1 = @o1 + 255 

            IF @o1 = 128 

                   SET @ip = @ip + 2147483648 


                   SET @ip = @ip + (16777216 * (256 - @o1)) 





        SET @ip = @ip - (16777216 * @o1) 



    SET @ip = @ip % 16777216 

    SET @o2 = @ip / 65536 

    SET @ip = @ip % 65536 

    SET @o3 = @ip / 256 

    SET @ip = @ip % 256 

    SET @o4 = @ip 



        CONVERT(VARCHAR(4), @o1) + '.' + 

        CONVERT(VARCHAR(4), @o2) + '.' + 

        CONVERT(VARCHAR(4), @o3) + '.' + 

        CONVERT(VARCHAR(4), @o4) 


Run the following Microsoft SQL Server query to generate usage log output for direct input into an analysis tool.  Save the query to a file (e.g., Usage_log_MSSQL.sql) if you plan to run it programmatically (a sample is attached to this article).  You will use the IP conversion function created earlier to convert IP address formats to dotted decimal notation.

Add FOR JSON PATH (in red) at the end of the query if you want to generate a JSON file as output.  If you want to generate a CSV file, remove FOR JSON PATH or leave it commented.


         a.user_code AS 'User ID', 

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

         a.position_code AS 'Job title code', 

         a.position_name AS 'Job title name', 

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

         a.log_date AS 'Date used', 

         dbo.ipIntToString(a.ip) AS 'IP address', 

         CASE CONVERT(varchar(15), a.client_os) WHEN '2' THEN 'Mac' WHEN '1' THEN 'Windows' ELSE                  CONVERT(varchar(15),     a.client_os) END AS 'OS', 

         a.current_content_name AS 'File name', 

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

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

         CASE CONVERT(varchar(15), a.product) WHEN '2' THEN 'FED-R' WHEN '1' THEN 'FED-N' WHEN '16'           THEN 'FED-N'  WHEN '4' THEN 'FED-E' ELSE CONVERT(varchar(15), a.product) END AS 'File type', 

         CONCAT(ISNULL(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 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 END AS 'Action', 

         CASE a.purpose_fail_reason WHEN '103' THEN 'Failed' WHEN '101' THEN 'Failure' WHEN '0' THEN              'Success' WHEN '301'     THEN 'Success (Provisional permission)' 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

/* remove comment below for JSON output */



Microsoft SQL Query Output to File

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 or JSON 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.


 Install-Module -Name SqlServer

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 or JSON file to import into your analysis tool.




The Fasoo database server name or IP address


The Fasoo database instance, if one exists


The Fasoo database server port


FED database name


FED database user name; not needed if using integrated security


FED database user password; not needed if using integrated security


SQL script to generate usage logs


Output of SQL script in CSV or JSON format




        -ServerInstance "#SERVER#\#INSTANCE#,#PORT#" 

        -Database "#FED_DB#" 

        -Username "#FED_DB_User#" 

        -Password "#FED_DB_Password#" 


        -InputFile "#SQL_SCRIPT#" | Export-Csv -Path "#OUTPUT#"


PowerShell Example



        -ServerInstance "\PW4000765762S,1434"

        -Database "FED5" 

        -Username "fasoo" 

        -Password "Fasoo2023!" 


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


 PowerShell Example with Integrated Security



        -ConnectionString "Data Source="\PW4000765762S,1434"; Initial Catalog="FED5";

        Integrated Security=True;"

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





Schedule Script

Schedule the PowerShell script to run in Windows Task Scheduler, SQL Server Agent, or another scheduler to query the Fasoo database and save the output to a CSV or JSON file to import into your SIEM or other analysis tool.   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.