How to export AuditLog to the database.

How to export AuditLog to the database.

This article describes how to export AuditLog to the database by adding a new appender to log4net.config file.
For new appender, refer to the Appender.txt  file attached to the article.
  1. Create a table in the database for logging AuditLog to the database.

2. On the computer running Spotfire Server, open a command line as an administrator and change the directory to the location of the config.bat file (config.sh on Linux). The default location is <server installation dir>/tomcat/bin or <server installation dir>/tomcat/spotfire-bin(for version 10.3 and higher)

3. On the command line, export the service configuration that you want to modify from Spotfire Server by using the export-service-config command.

Example for default configuration:
config export-service-config --capability=WEB_PLAYER --deployment-area=Production

4. Locate the log4net.config file under "<Server installation>\tomcat\bin\config\root".
NOTE: For the versions later than 10.3, you can find it under "<Server installation>\tomcat\spotfire-bin".

  a) Make the changes in the below section to the appender before adding it to log4net.config file.

          <connectionString value="
               Data Source=[hostname/IP address of db_server];
               Initial Catalog=Database_name;
               User ID=username;
               Password=password"
            />

 b) Mention the table name(Table_name) in the INSERT query as created in the database. Locate the below section in the appender.

      <commandText value="
            INSERT INTO Table_name
            ([hostName],[level],[sessionId],[ipAddress],[userName],
            [operation],[analysisId],[argument],[status],[timeStamp])
            VALUES (@hostName,@level,@sessionId,@ipAddress,@userName,
           @operation,@analysisId,@argument,@status,@timeStamp)"
        />

 NOTE: Please make sure you add all the columns in the same order to the table for logging the AuditLog data to the database

5. Define the new appender in log4net.config file i.e "AuditLogAdoNetAppender" for particular Web player service that logs to the  database, next to the old file appender AuditLog. Make sure you add it to 'log4net.config' file and not the one with instance-id.

6. Add a reference to the new appender to the logger.

 a) Locate the following section in the log4net.config file:
      <logger name="WebLogger.WebAuditLog" additivity="false"
            <appender-ref ref="AuditLog"/>
            <level value="INFO" />
      </logger>

 b) Add the appender reference <appender-ref ref="AuditLogAdoNetAppender"/> to the logger to enable the newly created log to database appender. The section should then look like this:

      <logger name="WebLogger.WebAuditLog" additivity="false">
             <appender-ref ref="AuditLog"/>
            <appender-ref ref="AuditLogAdoNetAppender"/>
            <level value="INFO" />
      </logger>

Please refer to the Final Log4net.txt file to check the exact format of Audit log appender with reference section in the Log4net.config file.

7. Save the file and import the configuration using the following command:

config import-service-config --config-name= <Serviceconfiguration>

8. Apply the configuration to Web Player services via the Spotfire Administration web interface  (Nodes & Services > Web Player Services > Edit > Select the new configuration that you just configured) 

9. Restart the Node manager service. Doc:Log to Database example
https://docs.tibco.com/pub/spotfire_server/7.8.2/doc/html/TIB_sfire_server_admin_help/GUID-465EDB5D-22F5-4F17-B158-9755BEEC75EB.html
 

Doc: export-service-config:

https://docs.tibco.com/pub/spotfire_server/10.5.0/doc/html/TIB_sfire_server_tsas_admin_help/GUID-5462A06E-20CC-4F42-BCB8-0A66C8EC1C18.html

Doc: import-service-config:
https://docs.tibco.com/pub/spotfire_server/10.5.0/doc/html/TIB_sfire_server_tsas_admin_help/GUID-6D37ADC8-824A-4FC6-9028-94A0CB0061B5.html

Doc: set-service-config :
https://docs.tibco.com/pub/spotfire_server/10.5.0/doc/html/TIB_sfire_server_tsas_admin_help/GUID-CA326706-EA48-47D6-9F4B-F9E93208710C.html