Writing SQL Queries for JDBC Connectors

[bburrell (Bakhari Burrell)]

The purpose of this blog is to demonstrate SQL queries that can be used to aggregate accounts and entitlements in a JDBC source connector. Finishing the setup of your JDBC database connector will require you to write a handful of SQL database queries, which will be discussed in this blog post.

This article will include the following processes:

• Writing an Account Query
• Writing a Group Query
• Discovering Account Schema
• Creating the Entitlement Schema
• Aggregation

Prerequisites

1. You will need to obtain read access to the SQL database (for testing queries)
2. You will need basic SQL query knowledge.
3. Your Source Connector IS NOT an authoritative source
4. Complete the JDBC source connector setup through the JDBC Integration document

Although the ideas here might work for ANY type of SQL database, this article assumes you are using a Microsoft SQL database.

Account SQL Query

Create a new JDBC source connector and configure the connection settings according your environment. Then, move to the “Query Settings” page.

We are going to write an Account SQL Query to get the data we need.

1. Open Microsoft SQL Server Management Studio and navigate to the SQL Database you will be pulling data from
2. Once inside the database, navigate to the table you want to pull data from. (It’ll more than likely be multiple tables, but we will just start with one here. )
3. Create a New Query

• Here’s a basic Query you can use to view what’s in the table: SELECT * FROM Team ORDER BY TeamName ASC
• Team is the name of the table in the database.
• TeamName is the name of the column you will sort by.

4. This query will get you started with the necessary research to determine what columns you need to bring into SailPoint IDN. You will have to test a lot of different SQL Queries in order to get the data that you need
5. Review this Query below in order to see the account data we are pulling in from the sandbox database. The names for tables and databases have been changed for security reasons.

USE WAREBOLT 
SELECT  ISNULL (ship.Name, 'null') AS 'Shipping Account', ISNULL (le.Name, 'null') AS 'ZBA Name', ISNULL (lr.Name, 'null') AS 'Ledgers', ISNULL (rsid.Name, 'null') AS 'Recording', ISNULL(rg.Name, 'null') AS 'Role Group',  iuser.FirstName, iuser.LastName, iuser.Email, iuser.UserID
 
  FROM User_DEV (NOLOCK) iuser
  LEFT JOIN ShippingAccountUserRel (NOLOCK) shiprel ON iuser.UserID = shiprel.UserID
  LEFT JOIN ShippingAccount (NOLOCK) ship ON shiprel.ShippingAccountID = ship.ShippingAccountID
 
 LEFT JOIN LedgerBankZBAUserRel (NOLOCK) zba ON iuser.UserID = zba.UserID
  LEFT JOIN Ledger (NOLOCK) le ON zba.LedgerID = le.LedgerID
 
  LEFT JOIN RoleGroupLedgerRoleRel (NOLOCK) rglr ON le.LedgerID = rglr.LedgerID
  LEFT JOIN LedgerRole (NOLOCK) lr ON rglr.LedgerRoleID = lr.LedgerRoleID
 
  LEFT JOIN RecordingServiceUserRel (NOLOCK) rs ON iuser.UserID = rs.UserID
  LEFT JOIN RecordingServiceCredential (NOLOCK) rsc ON rs.RecordingServiceCredentialID = rsc.RecordingServiceCredentialID
  LEFT JOIN RecordingService (NOLOCK) rsid ON rsc.RecordingServiceID = rsid.RecordingServiceID
 
  LEFT JOIN UserRoleGroupRel (NOLOCK) iurg ON iuser.UserID = iurg.UserID
  LEFT JOIN RoleGroup (NOLOCK) rg ON iurg.RoleGroupID = rg.RoleGroupID
  
  WHERE iuser.UserID IS NOT NULL AND NOT iuser.UserID = '1' AND NOT iuser.UserID = '494' AND NOT iuser.UserID = '495' ANd NOT iuser.UserID = '499' AND NOT iuser.UserID = '500' AND NOT iuser.UserID = '501'
  
  ORDER BY iuser.UserID ASC

6. There’s a lot happening in this query, but here are the basics:

• We are selecting specific columns from specific tables that we want to see aggregated over to SailPoint IDN
• We are replacing NULL rows with a “null” string in the rows in order to prevent NULL errors that will come up in SailPoint IDN if you don’t do some type of NULL checking. This could probably be fixed by having the team/owner of the database clean up their data.
• We are joining several tables from this database that are considered “entitlements.” We want to see these entitlements tied to the user in SailPoint IDN. This is how this is accomplished.
• There are multiple disparate tables being joined together as all the data we want to see is not in one neat table
• At the end, we are preventing certain IDs from showing up in our result set since these IDs are not users we need

7. Here is what that table looks like: This is just a sample of what the table looks like. The query above will return a LOT of rows. Nearly 500,000 actually. Because it returns every possible combination from the Left Join. We do this because we don’t want any users left out of our Account Query, even if the user only has one of the roles/groups/columns.

				Shipping Account	ZBA Name	Ledgers	Recording	Role Group	FirstName	LastName	Email	UserID
			1	null	null	null	null	BSC	Chris	Cuttle	[email protected]	506
			2	null	null	null	null	Admin 4	Bryan	Bus	[email protected]	507
			3	null	null	null	null	Admin 4	Ganene	Nielson	[email protected]	508
			4	null	null	null	Simplifile	RM	Jan	Corrado	[email protected]	509
			5	null	THD West	THD Type 2	null	Admin 3	Jennifer	Dumas	[email protected]	510

8. This looks really good. This means our query is working. We have to do the Account Query this way in order to tie the groups to the user in IDN.
9. Let’s pop the query into the Account SQL Query portion of our SailPoint IDN screen and then click “Save”.
10. Click “Test connection” to make sure everything is working. DO NOT AGGREGATE YET. We still have a few more queries to write.
11. After you’ve got a successful test connection, make sure to follow the “Discover the Schema” and “Account Schemas” steps from the documentation.
12. Once you get a successful Test Connection, it’s time to work on our Group Query and Entitlement Types

Group SQL Query

Something important to note about Group Queries and the Entitlement Types: you will need a brand new Entitlement Type for every column you want to bring in from your SQL Database.

This is to make sure that Entitlement aggregations work correctly and are tied to the correct User Account.

1. The first step is to create a new Entitlement Type for every column you want to bring in from your SQL Database.
2. This information is in the SailPoint JDBC documentation in detail, but here’s a quick startup:
   1. Navigate to your Source > click the “Import Data” tab
   2. Click on “Entitlement Types”
   3. Click “Create Entitlement Type” in the top right corner
      
   4. Name your Entitlement the exact same name as the column you are bringing in from your SQL Database. This must match!
   5. Click “Save”
   6. The TYPE will more than likely be a string, but select what is appropriate for your data.
   7. Make sure the Entitlement check box is CHECKED.
   8. If the user is allowed to have multiple selections from this column, then make sure “Multi-Valued” checkbox is checked.
   9. Save and done
   10. Do this for every column you are bringing in as an Entitlement
   11. The Entitlement ID and Name will be the Attribute Name/exact Column name from the database.
3. Once all your Entitlement Types have been created, let’s tie them to the User Account. The instructions are more detailed in the SailPoint JDBC documentation, but here’s a quick start up:
   1. Navigate to your Source > Click “Account Schema”
   2. Here we will add the new Entitlement Types we created as Attributes on the Account
   3. Click “Add New Attribute”
   4. Make sure the Name matches exactly the name of the Column from your SQL database
   5. Add a description
   6. Under Type, select the Entitlement Type you created that matches the name of the Attribute you are creating
   7. Make sure the checkboxes match those of the Entitlement Type (Multivalued and Entitlement)
   8. Here’s an example of what it should look like:
      
      
4. In order for every Entitlement Type we created to pull in the columns from our SQL database, we need to write SQL query for them
   1. Edit the source Configuration by navigating to the Source and clicking “Edit Configuration”
   2. Click “Query Settings”
   3. You should see the new Entitlement Types you created here:
      
      
   4. Let’s use Shipping Account as our example
   5. Open that Entitlement Type.
   6. What you will see here is pretty much identical to the setting up the account query and connection settings. You will need to enter the username and password combo for your SQL database.
      • You will also need to enter your Database URL
      • Also enter the Driver Class
      • Click Save and go to Query Settings
   7. Your Query Settings are where we enter the Group SQL Query that will pull the column we need from our database.
   8. In this example, we are use Shipping Account. Here’s what our Shipping Account Query looks like:

USE WAREBOLT
SELECT ship.Name AS 'Shipping Account', iuser.UserID
  
  FROM User_DEV (NOLOCK) iuser
LEFT JOIN ShippingAccountUserRel (NOLOCK) shiprel ON iuser.UserID = shiprel.UserID
  LEFT JOIN ShippingAccount (NOLOCK) ship ON shiprel.ShippingAccountID = ship.ShippingAccountID
	
  WHERE ship.Name IS NOT NULL
  
  ORDER BY iuser.UserID ASC

9. This query does several things:
   • This Query only selects the UserID and Shipping Account name (selecting the UserID for sorting purposes)
   • Several tables are joined in this Query since a lot of the information needed is spread across multiple tables (This is why you need to be able to test your queries. You’re data won’t always be in one nice organized table.)
   • NULL values are removed from the results
   • The Query sorts the results by the USER ID
10. That’s all you have to do for the Group SQL Query
11. Save, and repeat the process for every Entitlement Type you added
12. Once complete, Test Connection to verify everything is working as intended.

Aggregation

This is the easy part.

1. Navigate to your Source > Import Data tab
2. Select “Account Aggregation”
3. Click “Start” on the Manual Aggregation button
4. If everything is configured properly, both accounts AND entitlements should aggregate from your SQL Database and they should be tied together.
5. There’s no need to run an Entitlement Aggregation using this method since the Entitlements will be brought in with the Accounts.