Using Secure Data Share to find users with more than one account on a source

Identity Security Cloud (ISC) ISC Show and Tell
, ,
1

Before Secure Data Share (SDS), the primary way to search your identity data was to use the SailPoint search interface. Search uses Elastic Search Query Language, which is not a common language and has several limitations. For example, to find users with more than one account on a source, you had to run a search query using the v3 search aggregation endpoint. The query looks like this:

{
	"query": {
		"query": "*"
	},
	"indices": [
		"identities"
	],
	"aggregationsDsl": {
		"accounts": {
			"nested": {
				"path": "accounts"
			},
			"aggs": {
				"source_name": {
					"terms": {
						"field": "accounts.source.name.exact",
						"min_doc_count": 2,
						"size": 1000                        
					},
					"aggs": {
						"identities": {
							"terms": {
								"field": "_id",
								"min_doc_count": 2,
								"size": 1000
							},
							"aggs": {
								"accounts": {
									"top_hits": {}
								}
							}
						}
					}
				}
			}
		}
	}
}

And it produces a huge result of JSON data that is hard to parse into a readable report.

Now that SailPoint offers Secure Data Share, you can query your tenant data using SQL. Using SQL, the query to find duplicate accounts is much simpler.

SELECT IDENTITY.display_name, IDENTITY_ACCOUNTS.id as IDENTITY_ID, IDENTITY_ACCOUNTS.source_id, SOURCE.name as SOURCE_NAME, COUNT(*) as ACCOUNTS
FROM IDENTITY_ACCOUNTS
LEFT JOIN IDENTITY
ON IDENTITY_ACCOUNTS.id = IDENTITY.id
LEFT JOIN SOURCE
ON IDENTITY_ACCOUNTS.source_id = SOURCE.id
GROUP BY IDENTITY_ACCOUNTS.id, IDENTITY.display_name, IDENTITY_ACCOUNTS.source_id, SOURCE.name
HAVING COUNT(*) > 1;

And the result is not only readable, but it can be downloaded as a CSV file!

image
image2068Ă—686 110 KB

To top things off, SDS uses your Snowflake account, if you have one, as the data warehouse. Snowflake can be connected to many different BI tools, such as Tableau and PowerBI. These tools can make it much easier to build reports on your tenant data than it was to use search.

Search is still a free and valuable tool for querying your tenant data, but SDS makes reporting easier than ever before. Check out the SDS docs here to learn more.

You can also watch a video presentation on this topic here:

4 Likes
2

Hi @colin_mckibben

This looks really great and will definitely be helpful for reporting purposes.
I will look forward to use the snake flow account. Only one question I have for now, do we have the documentation about the tables that will be made available for read only purposes here ?

But again looks like a great platform for colleagues who have the db experience.

Thank you
Kind regards
Vikas.

3

Here’s the table diagram for identity data

And here’s the diagram for audit data

1 Like