To generate logs on the appliance for SecureAuth IdP, create the logging database. You will need to create SQL Server database files and then connect the SQL Server to SecureAuth IdP.
Information about maintaining log files is at the end of this document.
- SQL Server installed on a server
Create the SQL Server database
- Download the loggingScripts.zip file containing the CreateLoggingDatabase.sql and CreateLoggingDatabaseUser.sql files.
Create the SQL Server database, reporting tables, and stored procedures. See the following code example for Create logging database.
Change the file path to the location of the database files.
Create user and roles, and grant permissions for stored procedures. See the following code example for Create logging database user.
Replace [PasswordPlaceHolder] with the preferred password.
OPTIONAL: Create chart stored procedures. See the following code example for Drop / create charts.
Connect the SQL Server database to SecureAuth IdP
In this section, create a Logging database / user before or after you update the SecureAuth IdP appliance. After you process the update and updatewebconfig function, specify a connection string in the Logs tab .
- Go to the Logs tab.
- In the Logs Option section, verify the Log Instance ID value is unique and represents the realm. This the value used for the Realm column in each audit log event record.
In the Log Database section, set the following.
Data Source Set to the server machine name / SQL server instance name. Integrated Security To use SQL authentication, set to False. Persist Security Info Set to False. Generate Connection String Click to generate a connection string and populate the Connection String field. Test Connection Click to test the connection.
To apply changes to the web.config files for all realms, click Save to all realms.
Do not click the Save button with all realm check boxes selected. The Log Instance ID must be different on each realm.
Maintaining log files
The log database continues to grow unless a backup or truncate procedure is implemented. The log database continues to grow until you put in place a backup or truncate procedure.
In this section, you can use any of the following commands to truncate the log table and shrink the actual database (.mdf) and log (.ldf) files .
Truncating a table removes ALL rows. This is not reversible and with no rollback, it takes less time to process and has no overhead.
Deleting uses the WHERE parameter. The processing time is proportional to the size of the table.
Truncate the log table
Truncating the log table removes all rows from the table.
- In SQL Management Studio, connect as a db user with either sysadmin or dbowner permissions .
Drop all foreign keys (FKs) that point to the table using the following command in Drop foreign keys (FKs).
To truncate the log table, use the following command in Truncate log table.
Recreate the foreign keys using the following command in Recreate foreign keys (FKs).
Delete records from the Log table that are older than X amount of days
You can delete records older than a set number of days. As an example, use the following command to delete all records over 30 days old in Delete records.
Shrink the database files
The following command shrinks the database to the last allocated extent.