External Data Correlation

Custom Tables

A custom table contains one or more attributes that you want to have available on the Guardium appliance. For example, you may have an existing database table relating encoded user names to real names. In the network traffic, only the encoded names will be seen. By defining a custom table on the Guardium appliance, and uploading data for that table from the existing table, you will be able to relate the encoded and real names.

Before defining a custom table, first verify that the data you need from the existing database is a supported data type. A data type is supported if it is taken as one of the following SQL type by the underlying JDBC driver: INTEGER, BIGINT, SMALLINT, TINYINT, BIT, BOOLEAN, DECIMAL, DOUBLE, FLOAT, NUMERIC, REAL, CHAR, VARCHAR, DATE, TIME, TIMESTAMP. The following table summarizes some of the supported and unsupported data types for uploading to a custom table.

Supported and Unsupported Data Types for Custom Tables

Use this table to see what supported and unsupported data types exist for certain databases.

Custom Table Archive and Restore

The Custom Table Builder screen has a button called Purge/Archive.

The Custom Table Data Purge screen has a checkbox for Archive. Checking this box results in the data of the custom table being included in the normal data archive.

This custom table data is archived according to the date in SQLGUARD_TIMESTAMP column of the custom table.

The data of the custom table can be archived from a collector or an aggregator.

The data of the custom table archived from a collector can be restored to any collector or aggregator managed by the same Central Manager as the source collector (the metadata must be present).

The data of the custom table archived from an aggregator can be restored to any aggregator managed by the same Central Manager as the source aggragator.

If the archive file to be restored to a Guardium system does not have the metadata, then the data of the custom table is not restored.

If the structure of the custom table has changed between the time of archive and the time of restore in a way that results in an SQL error (for example, columns removed or type changed), then a warning message appears on the aggregation/archive activity report and the data is not restored.

If a custom table is set to be purged by the default purge, then the restored data will be kept for the number of days specified on the restore screen.

If the custom table is set to overwrite data when it uploads, then restored data will be deleted at the time an upload is performed.

Custom Domains

A custom domain contains one or more custom tables. If it contains multiple tables, you define the relationships between tables when defining the custom domain.

Custom Queries

A custom query accesses data from a custom domain. You use the Custom Query Builder to create queries against custom domains. Custom queries can then be used like any other query to generate reports or audit tasks, populate groups, or to define aliases.

Database Entitlement Reports

DB Entitlement Reports use the Custom Domain feature to create links between the external data on the selected database with the internal data of the predefined entitlement reports. See topic, Link External Data to Internal Data, on this subject. See Database Entitlement Reports for further information on how to use predefined database entitlement reports. To see entitlement reports, log on the user portal, and go to the DB Entitlements tab.

Create a Custom Table

Upload a Table Definition

Creating a custom table can be accomplished by the uploading of a table definition by accessing its metadata from the database server on which the metadata is defined.

1. Open the Custom Table Builder.
2. Click Upload Definition to open the Import Table Structure panel. It is not necessary to select an item
3. Enter a description for the table in the Entity Desc field. This is the name you will use to reference the table when creating a custom query.
4. Enter the database table name for the table in the Table Name field. This is the name you will use to create the table in the local database.
5. Enter a valid SQL statement for the table in the SQL Statement field. The result set returned by the SQL statement must have the same structure as the custom table defined. For example, if the custom table contains all columns from the table named my_table, enter  select * from my_table.
   Note:

   Do not include any newline characters in the SQL statement. All columns must be explicitly named; making use of a column alias if necessary.

6. Click Add Datasource to open the Datasource Finder in a separate window. This will allow us to define where the external database is located, and the credentials needed to retrieve the table definition and content later in the process.
7. Use the Datasource Finder to identity the database from which the table definition will be uploaded.
8. Click Retrieve to upload the table definition. This will execute the SQL Statement and retrieve the table structure. The SQL request is sent to the external database from the Guardium system. Remember that only the definition is being uploaded and you can upload data later.

Manually Define a Table Definition

1. Open the Custom Table Builder.
2. Click Manually Define to open the Define Entity panel.
3. Enter a description for the table in the Entity Desc field. This is the name you will use to reference the table when creating a custom query.  Use of the special characters \$|&;'`" are not allowed in the entity description.
4. Enter the database table name for the table in the Table Name field. This is the name you will use to create the table in the local database.
5. For each column in the table to be defined:
   • Enter a name in the Column Name box. This will be the name of the column in the database table.
   • Enter a name in the Display Name box. This is the name you will use to reference the attribute in the Custom Domain Builder and the Custom Query Builder.
   • Select a data type (Text, Date, Integer, Float, or TimeStamp).
   • For a Text attribute, enter the maximum number of characters in the Size box. (The Size box is not available for other data types.)
   • If uniqueness is to be enforced on the column, check the Unique box.
   • If the attribute being defined corresponds to a group type, select that group type from the Group Type list.
   • Click Add to add the column.
6. Use the Entity Key drop-down list to identify which column will be used as the entity key. The Entity Key is used in the query builder when selecting count.
7. If additional changes are made after the Add button, such as deletion of a column, or changing an attribute, Click Apply to save any changes.
8. Click Done when you have added all columns for the table.

Modify a Table Definition

If you modify the definition of a custom table, you may invalidate existing reports based on queries using that table. For example, an existing query might reference an attribute that has been deleted, or whose data type has been changed. When applying changes to a custom table, if any queries have been built using attributes from that table, the Queries are displayed in the Query List panel. Note: You can also use the Modify to view and validate the table structures that were imported.

1. Open the Custom Table Builder.
2. Choose a custom table by clicking on the entity label and highlighting it.
3. Click Modify to open the Modify Entity panel.
4. See Defining a Table Manually for assistance.
5. When applying changes to a custom table, if any queries could be invalided due to modification to attribute from that table, the queries are displayed in the Query List panel. Use the Query List panel to choose and change queries. You do not have to make all changes immediately as you can always come back and use the Check for Invalid Queries option.

Invalid Queries

If you modify the definition of a custom table, you may invalidate existing reports based on queries using that table. For example, an existing query might reference an attribute that has been deleted, or whose data type has been changed. It is a good idea to check for invalid queries after the table modification process.

1. Open the Custom Table Builder.
2. Click Invalid Queries.
3. The queries are displayed in the Query List panel. Use the Query List panel to choose and change queries.

Purge Data from Custom Table

Data can be purged from custom tables on the Guardium server on demand, or on a scheduled basis.

1. Open the Custom Table Builder.
2. Choose a custom table by clicking on the table name and highlighting it
3. Click Purge to open the Custom Table Data Purge panel.
4. Click Purge All to purge now.
   Note: Run once now purge will look at the RESTORED_DATA table for retention. Purge ALL will purge all records deleted without checking the retention.
5. In the Configuration panel, enter the age of the data to be purged, as a number of days, weeks or months prior to the purge operation date.
6. Click Run Once Now to run a schedule purge operation once.
7. Click Modify Schedule to open the standard Schedule Definition panel and schedule a purge operation.
8. Click Done to close the panel.

Upload Data to a Custom Table

1. Open the Custom Table Builder.
2. Choose a custom table by clicking on the name of the table and highlighting it
3. Click Upload Data to open the Import Data panel.
4. In the SQL Statement box, enter a valid SQL statement for the table. The result set returned by the SQL statement must have the same structure as the custom table defined. For example, if the custom table contains all columns from the table named my_table, enter  select * from my_table. The following fields, which are internal to Guardium, are available for use within SQL statements:
   • ^FromDate?^ and ^ToDate?^ where the value is equal to the previous upload date and the current upload date respectively.
   • ^fromID^ and ^toID^ where, when used with Id Column Name consist of the maximum value of the Id Column from the previous upload and the maximum value of the current upload respectively.
   Note: Do not include any newline characters in the SQL statement.
5. Specify, if needed, a column name in the Id Column Name (from the table defined within the datasource) will be used and allow for tracking by ID and be used in conjunction with the internal Guardium fields ^fromID^ and ^toID^.
6. In the DML command after upload box, enter a DML command (an update or delete SQL statement) with no semicolon, to be executed after uploading the data. Note: Do not include any newline characters in the SQL statement.
7. Check the Overwrite per upload box if you wish to have data purged in the custom table before the upload. Check the Overwrite per datasource if you wish to have data for that datasource purged before the upload from it
8. Check the default purge button (in the Upload Custom Data screen) to be part of the Default Custom Table Purge Job purge object which has an initial default age of 60 days. To add a purge schedule for this table, go to initial Custom Table Builder page, select a Custom Table and click Purge to open a Custom Table Data Purge configuration screen.
9. Check the Use default schedule box only if uploading tables from previous versions of Guardium. This check box only appears in a Central Manager view and only for predefined custom tables CM Buffer Usage Monitor, Enterprise No Traffic, S-TAP® Changes and S-TAP Info.
10. Click Add Datasource to open the Datasource Finder in a separate window. Use this window to identify one or more databases from which the table data will be uploaded. You may add multiple datasources to upload from multiple sources. Note: For a Central Manager, in the Import Data page there is a read only check box called Include default source. If this check box is checked, upload data will iterate through all online registered managed units. Note: When adding a datasource, the application can not be scheduled to run without specifying the user name and password of the selected datasource.
11. You can click Check/Repair to compare the schema of the custom table to the schema of the meta-data. For central management environments: In a central management environment, the custom table definition resides on the central manager, and the custom table may not exist on the local (managed unit) database. Click the Check/Repair button to check if the custom table exists locally, and create one if it does not.
12. Click Verify Datasources to test the external database connection. An acknowledgement screen will appear.
13. Click Apply.
14. To upload data to this custom table, do one of the following:
    • Click Run Once Now to upload data manually.
    • Check Modify schedule to configure the schedule.

Maintain Custom Table

The other selection in the Maintain Custom Table menu is Manage Table Index. Click Insert to open Table Index Definition. The pop-up screen suggests columns in the table to add to indexes based on columns used on custom domains as Join conditions. Select the columns and save. Indexes will be created (or re-created).

Schedule Custom Data Uploads

Once a custom table definition is in place, data can be uploaded to custom tables on the Guardium appliance on a scheduled basis.

1. Open the Custom Table Builder.
2. Choose a custom table by clicking on the entity label and highlighting it.
3. Click Upload Data to open the Import Data panel.
4. Mark the Use Default Schedule check box to upload this table using the default schedule. Otherwise, this custom table uses its own upload data schedule.
5. Click Modify Schedule to open the standard Schedule Definition panel and modify the schedule.
6. Click Done when you are finished.

Create a Custom Domain

After defining one or more custom tables, define a custom domain so that you can perform query and reporting tasks using the custom data. The information collected is organized into domains, each of which contains a different type of information relating to a specific area of concern: data access, exceptions, policy violations, etc. There is a separate query builder tool for each domain. Custom domains allow for user defined domains and can define any tables of data uploaded to the Guardium appliance. See Custom Domains. The usage for these custom entitlement (privileges) domains are for entitlement reports which are found if logged in as a user. To see these reports, go to the user tab, DB Entitlements.

1. Open the Custom Domain Builder by navigating to one of the following:
   • Comply > Custom Reporting > Custom Domain Builder
   • Reports > Report Configuration Tools > Custom Domain Builder
   • Setup > Tools and Views > Custom Domain Builder
2. Click Domains to open the Domain Finder panel.
3. Click New to open the Custom Tables Domain panel.
4. Enter a Domain Name. Typically, you will be including a single custom table in the domain, so you may want to use the same name for the domain.
5. The Available Entities box lists all custom tables that have been defined (and to which you have access). Select an entity. Optionally, click the (Filter) tool to open the Entity Filter and enter a Like value to select only the entities you want listed, and click Accept. This closes the filter window and returns you to the Custom Tables Domain panel, with only those entities matching the Like value listed in the Available Entities box. Select the entity you want to include.
6. Click the >> arrow button to move the entity selected in the Available Entities list to the Domain Entities list.
7. To add an entity to a domain that already has one or more tables, follow the procedure outlined. You will need to use the Join Condition to define the relationship between the entities.

   For each additional entity:

   • From the Domain Entities box, select an entity. All of the attributes of that entity will become available in the field drop-down list of the Domain Entities box. Select the attribute from that list that will be used in the join operation.
   • From the Available Entities list, select the entity you want to add. All of the attributes of that entity will become available in the field dropdown list of the Available Entities box. Select the attribute from that list that will be used in the join operation.
   • Select = (the equality operator) if you want the join condition to be equal (e.g., domainA.attributeB = domainC.attributeD). Select outer join if you want the join condition to be an outer join using the selected attributes.
   • Click Add Field Pair. Add Field Pair can be used to add more attributes pairs of these two entities to the join condition.
   • Repeat the steps for any additional join operations.
   Note: When data level security is on, internal entities added to the custom domain cannot belong to different domains with filtering policies.
8. Select the Timestamp attribute for the custom domain entity.
   Note: At least one entity with a timestamp must be used, since a timestamp is required to save a custom domain.
9. Click Apply.

Modify a Custom Domain

The goal is to create a linkage between external data and the internal data.  

1. Open the Custom Domain Builder.
2. Choose the Custom Domain that you wish to clone
3. Click Modify to open the Custom Tables Domain panel.
4. See Open Custom Domain Builder and Linking External Data to Internal Data for assistance.
5. Click Apply to save the changes.

Remove a Custom Domain

1. Open the Custom Domain Builder.
2. Choose the Custom Domain that you wish to clone.
3. Click Domains to open the Domain Finder panel.
4. Click Delete to remove the custom domain.

Clone a Custom Domain

1. Open the Custom Domain Builder.
2. Choose the Custom Table that is in the domain you wish to clone.
3. Click Domains to open the Domain Finder panel.
4. Click Clone to open the Custom Tables Domain panel.
5. Change the Domain Name to reflect the new domain.
6. See Open Custom Domain Builder and Linking External Data to Internal Data for assistance.
7. Click Apply to save the changes.

Link External Data to Internal Data

The goal is to create a linkage between external data and the internal data.  

1. Open the Custom Domain Builder.
2. Choose the Custom Table that has your external data.
3. Click Domains to open the Domain Finder panel.
4. Click Modify to open the Custom Tables Domain panel.
5. Click the Filter icon next to the Available Entities.
6. Un-check the Custom box for the filter and optionally fill in a Like condition to filter entity names and click Accept.
7. Select an entity from the Available Entities that you would like to link with your external data.
8. Select the field that will be used to join data with your external data.
9. Highlight the table from the Domain Entities that contains your external data
10. Select the field that will be used to join data with the internal data.
11. Click the Add Field Pair to add the relationship.
12. Click the double arrow >> to add the internal table to the Domain Entities list.
13. Click Apply to save the changes.

Working with Custom Queries

This section describes how to open the Custom Query Builder. See Building Queries and Building Reports for assistance in defining a query and building a report. Use the Custom Query Builder to build queries against data from custom domains, which contain one or more custom tables.

1. Open the Custom Query Builder by navigating to Comply > Custom Reporting > Custom Query Builder.
2. Select a custom domain from the list.
3. Click Search to open the Query Finder
4. To view, modify or clone an existing query, select it from the Query Name list, or select a report using that query from the Report Title list.
5. To view all of the queries defined for a specific custom table, select that custom table from the Main Entity list and click the Search button (only the custom tables included in the selected custom domain will be listed).

Bidirectional Interface to and from InfoSphere Discovery

Both IBM Guardium and InfoSphere® Discovery have the capability to identify and classify sensitive data, such as Social Security Numbers or credit card numbers.

The data will be transferred via CSV files.

Follow these steps:

• Export from Guardium
• Export Classification Data from IBM Guardium to InfoSphere Discovery

1. As an admin user in the Guardium application, go to Tools > Report Building >Classifier Results Tracking > Select a Report > Export Sensitive Data to Discovery.
   Note: Add this report to the UI pane (it is not by default).
2. Click the Customize icon on the Report Result screen and specify the search criteria to filter the classification results data to transfer to Discovery.
3. Run the report and click Download All Records.
4. Save as CSV and import this file to Discovery according to the InfoSphere Discovery instructions.

Import to Guardium

Import Classification Data from InfoSphere Discovery to IBM Guardium

1. Export the classification data as CSV from InfoSphere Discovery based on InfoSphere Discovery instructions.
2. Open the Custom Table Builder by navigating to either of the following:

   • Comply > Custom Reporting > Custom Table Builder
   • Reports > Report Configuration Tools > Custom Table Builder

   Select ClassificationDataImport and click Upload Data.
3. In Upload Data screen, click Add Datasource, click New, define the CSV file imported from Discovery as new datasource (Database Type = Text).  
   Note: Alternatively you can load the data directly from Discovery database if you know how to access the Discovery database and Classification results data.
4. After defining the CSV as Datasource, click Add in Datasource list screen.
5. In Upload data screen click Verify Datasource and then Apply.
6. Click Run Once Now to load the data from the CSV.
7. Go to Report Builder, select the Classification Data Import report, Click Add to Pane to add it to your Portal and then navigate to the report.
8. Access the Report, click Customize to set the From/To dates and execute the report.

The report result has the classification data imported from InfoSphere Discovery. Double click to invoke APIs assigned to this report. The data imported from Discovery can be used for the following:

• Add new Datasource based on the result set.
• Add/Update Sensitive Data Group.
• Add policy rules based on datasource and sensitive data details.
• Add Privacy Set.

CSV Interface signature

Use the table for examples of CSV interface signatures used in the bidirectional transfer between IBM Guardium and InfoSphere Discovery.