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": [
        ...
    ]
}

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