External data correlation

Custom tables

A custom table contains one or more attributes that you want to have available on the appliance. By uploading data for that table from an existing table, you can relate the encoded and real names.

Before you define a custom table, 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.

Central manager experienced failure getting data from collector. Central manager experienced error in the last 24 hours uploading data from collector. It's logged in both the log named cmDataUpload.log and the following report

Supported and unsupported data types for custom tables

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

Archiving and restoring custom tables

From the Custom Table Builder, select a table to manage and then select Purge/Archive to open the Custom Table Data Purge page.

Click Archive to include the data in the custom table in the normal data archive. The selected custom table data is archived according to the date in SQLGUARD_TIMESTAMP column of the custom table.

You can archive data from either a collector or an aggregator.

Custom domains

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

Custom queries

A custom query accesses data from a custom domain. 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. For more information about using predefined database entitlement reports, see Running database entitlement reports. To see entitlement reports, log on the user portal, and go to the DB Entitlements tab.

LDAP import into custom tables

Enrich Guardium tables with LDAP data by using an LDAP server as a datasource. Retrieve and configure LDAP attributes that can be used to customize reports.

1. Open the Custom Table Builder.
2. To create a new table definition, click Manually Define and define the entity. For more information, see Manually defining a table definition.
3. From the Custom Table Builder screen, select your custom table definition and click LDAP Import.
4. Click to create a new LDAP datasource.
   • From the Configuration tab, specify the LDAP connection information and define the Base DN. The Base distinguished name (DN) specifies the starting point of the search in the LDAP tree. Optionally include a search filter in LDAP search syntax. For example, objectClass=person.
   • Click the Mapping tab to map the columns of the custom table to the LDAP fields.
   • Click the Schedule tab and add a schedule to import the LDAP data.
   • Click Save to save the configuration.
5. To import data, select the LDAP Host from the LDAP configurations screen and click Run Once Now or wait for the schedule that you defined.
   Tip: To view the imported LDAP data, click the LDAP datasource from the Custom Tables list, and click Edit Data.

The custom table now has LDAP data that can be used to create custom reports.

Uploading a table definition

Create a custom table by the uploading a table definition. You can access the table's metadata from the database server on which the metadata is defined.

1. From the Custom Table Builder, click Upload Definition to open the Import Table Structure page. It is not necessary to select an item.
2. Enter a description for the table in the Entity desc field. This is the name you use to reference the table when you create a custom query.
3. Enter the database table name for the table in the Table Name field. This is the name you use to create the table in the local database.
4. 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 that you define. 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; if needed, use a column alias.
5. Click Add Datasource to open the Select datasource window. From Select datasource, you can define where the external database is located, and the credentials that are needed to retrieve the table definition and content later in the process.
6. Use Select datasource to identify the database from where you will upload the table definition.
7. Click Retrieve to upload the table definition. The SQL statement runs and retrieves the table structure. The SQL request is sent to the external database from the Guardium system. Remember that only the definition is being uploaded. You can upload data later.

Manually defining a table definition

1. From the Custom Table Builder, click Manually Define to open the Define Entity panel.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. Click Done when you have added all columns for the table.

Modifying 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. From the Custom Table Builder, select the custom table to modify.
2. Click Modify to open the Modify Entity page.
3. For information about modifying a table definition, see Manually defining a table definition.
4. After you apply changes to a custom table, if any queries might be invalid due to changes to attributes from that table, the queries are displayed in the Query List page. Use Query List 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. From the Custom Table Builder, click Invalid Queries.
2. The queries are displayed in the Query List panel. Use Query List to choose and change queries.

Purging data from a custom table

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

1. From the Custom Table Builder, select a custom table to purge.
2. Click Purge to open the Custom Table Data Purge panel.
3. 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 that are deleted without checking the retention.
4. 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.
5. Click Run Once Now to run a schedule purge operation once.
6. Click Modify Schedule to open the standard Schedule Definition panel and schedule a purge operation.
7. Click Done to close the panel.

Uploading data to a custom table

1. From the Custom Table Builder, click on the name of the table whose data you want to upload.
2. Click Upload Data to open the Upload Data page.
3. 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.
   Important: Different SQL databases have unique syntax requirements. For example, compare the following SQL statements for uploading from an Oracle database and uploading from a Microsoft SQL Server database:

   # Oracle example
   select * from xxx WHERE col1 > TO_DATE('^FromDate^', 'YYYY/MM/DD HH24:MI:SS');
   
   #Microsoft SQL Server example
   select * from xxx WHERE col1 > CONVERT(DATETIME,'^FromDate^');

   To create a functional SQL statement for uploading data, test the statement outside of Guardium with the SQL interface of the specific database being used.

   Do not include any newline characters in the SQL statement.

   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^ where the value is equal to the maximum value of the ID column from the previous upload.
     Important:

     • When you use ^FromDate^ or ^ToDate^ do not specify an Id column name.
     • When you use ^fromID^, specify an Id Column name and Id column type.
4. If using ^fromID^ specify the ID column name and Id column type for the table defined within the datasource. The specified column name is used for tracking by ID.
5. 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.
6. To configure Overwrite Default Purge, select Per upload to purge data in the custom table before the upload. Select Per datasource to purge data for that datasource before the upload.
7. Check Default Purge (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.
8. 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 the following predefined custom tables: CM Buffer Usage Monitor, Enterprise No Traffic, S-TAP Changes, and S-TAP Info.
9. Click Add Datasource to open the Select Datasource page. From Select Datasource you can identify one or more databases from which to upload the table data. You can add multiple datasources to upload from multiple sources.
   Notes:

   • For a central manager, on the Import Data page, you can select Include default source. If this box is checked, upload data iterates through all online registered managed units.
   • When adding a datasource, the application cannot be scheduled to run without specifying the user name and password of the selected datasource.
10. You can click Check/Repair to compare the schema of the custom table to the schema of the meta-data. 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.
11. Click Verify Datasources to test the external database connection. An acknowledgment screen will appear.
12. Click Apply.
13. 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.

Maintaining custom tables

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).

create index a1 on CUSTOM.aaa (id,`System`(255),aaa(255),qqq(255));

create index a1 on CUSTOM.aaa (id,`System`(100),aaa(100),qqq(100));

Scheduling 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.

Creating 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
   • Comply > Custom Reporting > 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 these steps. You will need to use the Join Condition to define the relationship between the entities. For each additional entity:
   Note: When data level security is on, internal entities added to the custom domain cannot belong to different domains with filtering policies.
   1. 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.
   2. 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.
   3. 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.
      Note: Known limitation: After an entity is added, the Detail window in the GUI always displays it as an inner join. However, the correct join condition is saved in the table.
   4. Click Add Field Pair. Add Field Pair can be used to add more attributes pairs of these two entities to the join condition.
   5. Repeat the steps for any additional join operations.
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.

Modifying 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.

Removing 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.

Cloning 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.

Linking 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-Report Builder. For more information about defining a query, see Using the Query-Report Builder. Use the Custom Query Builder to build queries against data from custom domains, which contain one or more custom tables.

1. From the Custom Query Builder, select a custom domain from the list. The list of queries/reports under this domain opens.
2. To view, modify or clone an existing query, select it from the Query Name list.

Bidirectional interface to and from InfoSphere Discovery

Both IBM® Guardium and InfoSphere® Discovery can identify and classify sensitive data, such as social security numbers or credit card numbers.

The data is transferred via CSV files.

Follow these steps:

• Export from Guardium
• Export Classification Data from 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
3. Select ClassificationDataImport and click Upload Data.
4. 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.
5. After defining the CSV as Datasource, click Add in Datasource list screen.
6. In Upload data screen click Verify Datasource and then Apply.
7. Click Run Once Now to load the data from the CSV.
8. 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.
9. 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

The following table provides examples of CSV interface signatures used in the bidirectional transfer between Guardium and InfoSphere Discovery.