dbarc
Use the dbarc program to configure the range of data Bravura Security Fabric will use during normal operation. It can partition and archive data in the database; for example, you can divide data into partitions for 2007+older, 2008, 2009, 2010+newer. This allows you to reduce the amount of data that Bravura Security Fabric queries regularly, which improves performance. You can also move the storage of chunks of data onto different devices with different performance characteristics.
The views created by dbarc can be used for exit traps and CGI programs when the relevant information is requested.
Reports always use the full table, with an option to set the date range to limit the information, rather than using views.
If the owner of the application objects is different to the database user used by the Database Service (iddb), then you must create a file with dbarc and execute it manually on the client.
Table partitions and views are grouped in categories:
Group | Description |
|---|---|
checkout | Privileged access checkout information |
pwhistory | Password reset history |
request | Workflow request information |
rtaudit | Auditing information |
sesslog | Report-related information |
sessmon | Session monitoring information |
userhistory | User and account definition information |
Caution
When dbarc.exe is executed for the first time the process can impact the performance of Bravura Security Fabric and take several hours to complete. Subsequent executions only take a few minutes.
Caution
Before running any code, including dbarc without the -f option, check with Support if the (dbarc-) rendered SQL is correct for your needs; send support@bravurasecurity.com the following:
- The planned SQL statements in a text file.
- The counts of rows resulting from one of the queries from the section above, as tab-separated text, with headers.
In different versions of the product dbarc may handle different tables; check the help string of the utility to see which tables are supported in your version:
util\dbarc -h
The following is a list of tables to consider when deciding how many days Bravura Security Fabric will keep data:
All products:
sesslog - The largest group because it retains data from all sessions into the product. This group in some cases can retain half of the used space into the database usage.
pwhistory - Check how many days is configured on the password policy allow reuse of old passwords after N days rule.
userhistory
rtaudit
Bravura Identity:
request
Bravura Privilege:
checkout
sessmon
Before running dbarc:
Test the results by running
dbarcwith its -file argument and the other arguments for each data group that has to be handled first.For example, to see how
dbarcwill try to remove any event log data older than a year, run the following from a command prompt:
<Program Files path>\Bravura Security\Bravura Security Fabric\<instance>\ util\ dbarc -delete sesslog -deletemaxage 366 -file reduceSesslog.sql
Always a backup the data before making any changes.
Replace the DELETE with SELECT * in the resulting queries, and before running the DELETEs. Save the results of the SELECTs to a file from which you can search and recover the removed data if needed at a later date.
Usage
dbarc.exe -listgroups dbarc.exe -list <group> dbarc.exe -split <group> -splitinterval <days> -splitadvance <num> dbarc.exe -drop <group> [-dropdate <YYYYMMDD>]|[-dropmaxage <days>] dbarc.exe -delete <group> -deletemaxage <days> dbarc.exe -hideolder <group> [-hidedate<YYYYMMDD|none> ]|[-hidemaxage <days|none>] dbarc.exe -extract <group> [-extractdir <directory>] dbarc.exe -extractreset <group> -extractresettime <YYYYMMDDHHMMSS> dbarc.exe -file <filename>
Argument | Description |
|---|---|
-delete <group> | Delete partition group content older than a specified age of data (-deletemaxage). |
-deletemaxage <days> | Delete content from partition group older than this number of days from current. |
-drop <group> | Drop partitions in the specified group either older than a specified date (-dropdate) or older than a specified age of data (-dropmaxage). |
-dropdate <YYYYMMDD> | Date value for dropping old data partition. |
-dropmaxage <days> | Drop partitions older than this number of days from current. |
-extract <group> | Extract data not previously extracted from the specified group to CSV files. |
-extractdir <directory> | Extract data to this directory. The default is \Logs\<instance>\archive_extract\. |
-extractreset <group> | Clear archivetime previously set by "extract". |
-extractresettime <YYYYMMDDHHMMSS> | Earliest archivetime value previously set by "extract" to reset. |
-file <filename> | Write commands to specified file instead of running them. |
-force | Allow operations that may be dangerous or time-consuming to be executed. |
-hidedate <YYYYMMDD> | Date value for hiding old data from full tables and put into views. Date can be none to disable hiding to retrieve full table content into views. |
-hidemaxage <days> | Hide partitions older than this number of days. Can be none to disable hiding. |
-hideolder <group> | Hide records older than the specified date from the core product for the selected group. |
-list <group> | List partitions in the specified group. |
-listgroups | Display a list of valid partition groups. |
-split <group> | Split partitions such that sufficient future partitions are reserved. This in intended for scheduled tasks. |
-splitadvance <N> | Number of intervals into the future to create partitions. |
-splitinterval <days> | Number of days between newly-split partitions. |
When splitting, dropping, hiding, extracting or resetting extract time for partitions in groups, you can specify all to have the operation affect all groups.
Examples
To create 30 partitions for the request group, with each partition 10 days apart from now:
dbarc -split request -splitadvance 30 -splitinterval 10
To create 6 partitions for all groups, with each partition 183 days apart from now:
dbarc -split all -splitadvance 6 -splitinterval 183
To cache the sesslog group into a view for information newer than June 15, 2013:
dbarc -hideolder sesslog -hidedate 20130615
To drop pwhistory group partition named and dated at ’20101215’:
dbarc -drop pwhistory -dropdate 20101215
To drop all group partitions older than 365 days.
dbarc -drop all -dropmaxage 365
To extract checkout group data into CSV files:
dbarc -extract checkout
To clear all groups of archivetime previously set by "extract" on June 15, 2013 at 01:00:00 or later:
dbarc -extractreset all -extractresettime 20130615010000
Important
When a large amount of rows need to be removed (the first time sesslog group data is removed), depending on the amount of data in the database and the indexes fragmentation, the process can take hours and affects the access to the UI. In a production environment, consider a downtime window to have it done.
Before performing these operations, ensure there is enough free space in the partitions where the data files are maintained by MSSQL. At least 2.5 times the size of the data files should be free before starting the process.
Ask your DBA to re-build indexes periodically specially for the tables on the sesslog group. These tables are the most used by the product and data is added every minute in there.