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.


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 

) 

RETURNS CHAR(15) 

AS 

BEGIN 

    DECLARE @o1 bigINT, 

        @o2 bigINT, 

        @o3 bigINT, 

        @o4 bigINT 

 

    IF ABS(@ip) > 4294967295 

        RETURN '255.255.255.255' 

 

    SET @o1 = @ip / 16777216 

 

    IF @o1 = 0 

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

    ELSE IF @o1 < 0 

    BEGIN 

        IF @ip % 16777216 = 0 

            SET @o1 = @o1 + 256 

        ELSE 

        BEGIN 

            SET @o1 = @o1 + 255 

            IF @o1 = 128 

                   SET @ip = @ip + 2147483648 

            ELSE 

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

        END 

    END 

    ELSE 

    BEGIN 

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

    END 

 

    SET @ip = @ip % 16777216 

    SET @o2 = @ip / 65536 

    SET @ip = @ip % 65536 

    SET @o3 = @ip / 256 

    SET @ip = @ip % 256 

    SET @o4 = @ip 

 

    RETURN 

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

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

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

        CONVERT(VARCHAR(4), @o4) 

END


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.


SELECT 

         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 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 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 */

--FOR JSON PATH;  

 



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.


PowerShell

 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.


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 in CSV or JSON format

PowerShell

 

Invoke-Sqlcmd 

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

        -Database "#FED_DB#" 

        -Username "#FED_DB_User#" 

        -Password "#FED_DB_Password#" 

        -TrustServerCertificate 

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

 

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 

        -ConnectionString "Data Source="192.168.1.2\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.