Get Users With More Than One Account in the Same Source

Hi,

I’m trying retrieve identities with more than one account in the same source using Search in IdentityNow. What is the way to retrieve this information?

Thanks in advance

1 Like

Our documentation on using Search is quite thorough. Have you taken a look at it?
Search Overview - SailPoint Identity Services.

Thanks for your response @colin_mckibben

Yes, I’ve just reviewing the compass article, but only is useful for me
the query ‘accountCount’, but this query only retrieve all source’s accounts of an user, not the same source’s account

I did some digging and here is what I found is possible.

In my tenant, I have a source called “Employees” with three accounts. There are two accounts with the same name, “jack.ryan”.

The query I need to run to return identities that have more than one account in the “Employees” source is as follows:

@accounts(source.name:Employees) AND accountCount:>2

You can paste that directly into your IDN search bar, or you can use our search API with the following body:

{
    "query": {
        "query": "@accounts(source.name:Employees) AND accountCount:>2"
    }
}

Breaking this query down, @accounts is needed to specify a nested object within the Identity model, since you can’t directly query accounts. Each account within an identity has a field called source that is used to identify the source that account belongs to. We can filter our results to a single source by specifying source.name:<your source>. Finally, the Identity model provides counts for each of the nested objects it contains, as described here. By using the filter accountCount:>2, we can further filter out any identities that have more than 2 accounts. I use >2 here because each identity is automatically assigned an account within Identity Now, so you have to account for the IdentityNow source in each of your identities.

It turns out the previous query I submitted won’t work as expected. It can raise false positives for any identity that has an account in your target source and in any other source. It appears that a better approach is to use the /v3/search/aggregate endpoint with the following body:

{
    "query": {
        "query": "*"
    },
    "aggregationsDsl": {
        "accounts": {
            "nested": {
                "path": "accounts"
            },
            "aggs": {
                "source_id": {
                    "terms": {
                        "field": "accounts.source.id",
                        "min_doc_count": 2,
                        "size": 1000
                    },
                    "aggs": {
                        "identities": {
                            "terms": {
                                "field": "_uid",
                                "min_doc_count": 2
                            },
                            "aggs": {
                                "accounts": {
                                    "top_hits": {}
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

This will respond with a JSON object that contains the following object:

{
    "aggregations": {
        "accounts": {
            "doc_count": 8,
            "source_id": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                    {
                        "key": "2c9180887671ff8c01767b4671fb7d5e",
                        "doc_count": 3,
                        "identities": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": [
                                {
                                    "key": "identity#2c918085771b670d01771c567e700917",
                                    "doc_count": 2,
                                    "accounts": {
                                        "hits": {
                                            "total": 2,
                                            "max_score": 4.1795163,
                                            "hits": [
                                                {
                                                    "_nested": {
                                                        "field": "accounts",
                                                        "offset": 1
                                                    },
                                                    "_score": 4.1795163,
                                                    "_source": {
                                                        "id": "2c9180897865dbf10178698380c5390f",
                                                        "name": "jack.ryan",
                                                        "accountId": "E003",
                                                        "source": {
                                                            "id": "2c9180887671ff8c01767b4671fb7d5e",
                                                            "name": "Employees",
                                                            "type": "DelimitedFile"
                                                        },
                                                        "disabled": false,
                                                        "locked": false,
                                                        "privileged": false,
                                                        "manuallyCorrelated": false,
                                                        "entitlementAttributes": {},
                                                        "created": "2021-03-25T13:12:09.157Z"
                                                    }
                                                },
                                                {
                                                    "_nested": {
                                                        "field": "accounts",
                                                        "offset": 0
                                                    },
                                                    "_score": 4.1795163,
                                                    "_source": {
                                                        "id": "2c9180897865dbf1017869837ca93909",
                                                        "name": "jack.ryan",
                                                        "accountId": "E001",
                                                        "source": {
                                                            "id": "2c9180887671ff8c01767b4671fb7d5e",
                                                            "name": "Employees",
                                                            "type": "DelimitedFile"
                                                        },
                                                        "disabled": false,
                                                        "locked": false,
                                                        "privileged": false,
                                                        "manuallyCorrelated": false,
                                                        "entitlementAttributes": {},
                                                        "created": "2021-03-25T13:12:08.105Z"
                                                    }
                                                }
                                            ]
                                        }
                                    }
                                }
                            ]
                        }
                    },
                    {
                        "key": "2c91808c771b686101772a91dbd877aa",
                        "doc_count": 3,
                        "identities": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": [
                                {
                                    "key": "identity#2c918085771b670d01771c567e650913",
                                    "doc_count": 2,
                                    "accounts": {
                                        "hits": {
                                            "total": 2,
                                            "max_score": 4.1795163,
                                            "hits": [
                                                {
                                                    "_nested": {
                                                        "field": "accounts",
                                                        "offset": 2
                                                    },
                                                    "_score": 4.1795163,
                                                    "_source": {
                                                        "id": "2c9180877865dbf201786a95806069dd",
                                                        "name": "sheena.martin",
                                                        "accountId": "E005",
                                                        "source": {
                                                            "id": "2c91808c771b686101772a91dbd877aa",
                                                            "name": "Ubuntu",
                                                            "type": "DelimitedFile"
                                                        },
                                                        "disabled": false,
                                                        "locked": false,
                                                        "privileged": false,
                                                        "manuallyCorrelated": false,
                                                        "entitlementAttributes": {},
                                                        "created": "2021-03-25T18:11:25.920Z"
                                                    }
                                                },
                                                {
                                                    "_nested": {
                                                        "field": "accounts",
                                                        "offset": 1
                                                    },
                                                    "_score": 4.1795163,
                                                    "_source": {
                                                        "id": "2c9180877865dbf201786a956c9b69cc",
                                                        "name": "sheena.martin",
                                                        "accountId": "E006",
                                                        "source": {
                                                            "id": "2c91808c771b686101772a91dbd877aa",
                                                            "name": "Ubuntu",
                                                            "type": "DelimitedFile"
                                                        },
                                                        "disabled": false,
                                                        "locked": false,
                                                        "privileged": false,
                                                        "manuallyCorrelated": false,
                                                        "entitlementAttributes": {},
                                                        "created": "2021-03-25T18:11:20.859Z"
                                                    }
                                                }
                                            ]
                                        }
                                    }
                                }
                            ]
                        }
                    },
                    {
                        "key": "2c91808475b4334b0175e1dff1b563a4",
                        "doc_count": 2,
                        "identities": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": []
                        }
                    }
                ]
            }
        }
    },
...
...
...

This will return any identity that has duplicates in any source. I can’t find a way to limit it to just a single source, but it will point out any duplicates in all of the sources you have. In my environment, jack.ryan has duplicate accounts in the Employees source, and sheena.martin has duplicate accounts in the Ubuntu source.

6 Likes

Hi @colin_mckibben

Thanks for your response. The query works!

Thanks!

1 Like

UPDATE: The previous query may no longer work as the data model of identity search results has changed. It appears the _uid field is no longer available, having been replaced by _id. If you are experiencing issues using the above query, then try this query instead:

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

This produces the following results:

{
    "aggregations": {
        "accounts": {
            "doc_count": 782,
            "source_id": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets":
                    {
                        "key": "2c91808780f6c51601812ae95ccb3abd",
                        "doc_count": 4,
                        "identities": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": []
                        }
                    },
                    {
                        "key": "2c9180887671ff8c01767b4671fb7d5e",
                        "doc_count": 3,
                        "identities": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": [
                                {
                                    "key": "2c9180867b75ce33017b78198e8c0a06",
                                    "doc_count": 2,
                                    "accounts": {
                                        "hits": {
                                            "total": {
                                                "value": 2,
                                                "relation": "eq"
                                            },
                                            "max_score": 1,
                                            "hits": [
                                                {
                                                    "_index": "identities_v35_stg03_group1of2",
                                                    "_type": "_doc",
                                                    "_id": "2c9180867b75ce33017b78198e8c0a06",
                                                    "_nested": {
                                                        "field": "accounts",
                                                        "offset": 0
                                                    },
                                                    "_score": 1,
                                                    "_source": {
                                                        "id": "2c918083814931bf01816cd42009481c",
                                                        "name": "adam.archer",
                                                        "accountId": "E010",
                                                        "source": {
                                                            "id": "2c9180887671ff8c01767b4671fb7d5e",
                                                            "name": "Employees",
                                                            "type": "DelimitedFile"
                                                        },
                                                        "disabled": false,
                                                        "locked": false,
                                                        "privileged": false,
                                                        "manuallyCorrelated": false,
                                                        "entitlementAttributes": {
                                                            "groups": [
                                                                "DevRel"
                                                            ]
                                                        },
                                                        "created": "2022-06-16T14:04:10.121Z"
                                                    }
                                                },
                                                {
                                                    "_index": "identities_v35_stg03_group1of2",
                                                    "_type": "_doc",
                                                    "_id": "2c9180867b75ce33017b78198e8c0a06",
                                                    "_nested": {
                                                        "field": "accounts",
                                                        "offset": 1
                                                    },
                                                    "_score": 1,
                                                    "_source": {
                                                        "id": "2c918083814931bf01816cd4211a481f",
                                                        "name": "adam.archer",
                                                        "accountId": "E008",
                                                        "source": {
                                                            "id": "2c9180887671ff8c01767b4671fb7d5e",
                                                            "name": "Employees",
                                                            "type": "DelimitedFile"
                                                        },
                                                        "disabled": false,
                                                        "locked": false,
                                                        "privileged": false,
                                                        "manuallyCorrelated": false,
                                                        "entitlementAttributes": {
                                                            "groups": [
                                                                "DevRel"
                                                            ]
                                                        },
                                                        "created": "2022-06-16T14:04:10.394Z"
                                                    }
                                                }
                                            ]
                                        }
                                    }
                                }
                            ]
                        }
                    },
                    {
                        "key": "2c91808a7b5adbb6017b63c91b2d0f4f",
                        "doc_count": 3,
                        "identities": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": []
                        }
                    }
                ]
            }
        }
    },
    "hits": [
        ...
    ]
}
1 Like

Hi,

Is there anyway to filter the results only for particular source. Like i want only users having multiple accounts on AD?
I tried to put id in “accounts.source.id” but that does not seem to return result as expected.

Thanks
Yunus

Hi Yunus,

Yes, you can modify the query as follows:

{
    "query": {
        "query": "source.id:2c9180887671ff8c01767b4671fb7d5e"
    },
    "indices": [
        "identities"
    ],
    "aggregationsDsl": {
        "accounts": {
            "nested": {
                "path": "accounts"
            },
            "aggs": {
                "source_id": {
                    "terms": {
                        "field": "accounts.source.id",
                        "min_doc_count": 2,
                        "size": 1000
                    },
                    "aggs": {
                        "identities": {
                            "terms": {
                                "field": "_id",
                                "min_doc_count": 2
                            },
                            "aggs": {
                                "accounts": {
                                    "top_hits": {}
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Hi Colin,

Can’t seem to get this to work. If I have query * it returns users without two accounts in a single source. If I had the source id check to check for my desired source, I get no results but I know they exist.

Not sure what I am missing here.

what endpoint are you using? Please share the exact endpoint and payload.

Hello @colin_mckibben

I have a similar question as the thread owner.
I went through the earlier responses in this thread and using the provided modified query for a single source, I’m getting the following no valid response as shown below:

{
    "aggregations": {
        "accounts": {
            "doc_count": 0,
            "source_id": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
            }
        }
    }
}

I’m wondering if the value for “indices” of “identities” is possible for “source.id”. Reason being is even if I perform a “source.id” search on IDN tenant, there is no option to show for Identities. Only Access Profile and Entitlements.

Also, it is important to be able to search for a specific source only, as doing a wildcard search for all sources may return additional results not wanted.

More infomation as follows:

Endpoint: Active Directory

Request body:

{
    "query": {
        "query": "source.id:2c91808781a6c9e30181ad25d2124704"
    },
    "indices": [
        "identities"
    ],
    "aggregationsDsl": {
        "accounts": {
            "nested": {
                "path": "accounts"
            },
            "aggs": {
                "source_id": {
                    "terms": {
                        "field": "accounts.source.id",
                        "min_doc_count": 2,
                        "size": 1000
                    },
                    "aggs": {
                        "identities": {
                            "terms": {
                                "field": "_id",
                                "min_doc_count": 2
                            },
                            "aggs": {
                                "accounts": {
                                    "top_hits": {}
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Appreciate your reply. TIA. :slight_smile:

Regards,
Ming Zheng

The query above will only work when limiting by authoritative source. The reason for this is that the top level source object on an identity object is that identity’s authoritative source. If you need to restrict your query to a non-authoritative source, then you will need to use the following query.

{
    "query": {
        "query": "@accounts(source.id:2c918088814e6a610181686b56977fa8)"
    },
    "indices": [
        "identities"
    ],
    "aggregationsDsl": {
        "accounts": {
            "nested": {
                "path": "accounts"
            },
            "aggs": {
                "source_id": {
                    "terms": {
                        "field": "accounts.source.id",
                        "min_doc_count": 2,
                        "size": 1000
                    },
                    "aggs": {
                        "identities": {
                            "terms": {
                                "field": "_id",
                                "min_doc_count": 2
                            },
                            "aggs": {
                                "accounts": {
                                    "top_hits": {}
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

There are many other ways to narrow down your search, as documented in the searchable fields guide.

We recently assisted a customer with a search api aggregation that can be used to detect sources with multiple accounts. Based on that engagement we’ve updated the DSL query and wanted to provide an example and some clarification.

This aggregation consists of two term buckets which are group counts for each value found in the search aggregation - in this case the source bucket and duplicate accounts bucket.

The main question asked was can the list of accounts be paged if the number of buckets exceeds the max limit. Since this is a search aggregation instead of a query the results cannot be paged but you can increase the size of buckets up to 65536. In the example below the first size parameter is for the source buckets, the second size value is for the duplicate accounts buckets which is most likely what you would need to increase.

There was also some confusion on what the query portion of the search was for. This query determines which search documents (in this case Identities) will be considered in the aggregation, You can page the search results or set limit to 0 if you don’t want to see query results and only want to see aggregation buckets. This will not cause the aggregation results to page.

Updated example:

{
	"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": {}
								}
							}
						}
					}
				}
			}
		}
	}
}
1 Like