Skip to Content

How to use QueryBuilder to find all users that has Authentication Type Windows AD?

I think the question says it clearly

How can I get at list of all the users that has the Windows AD Authentication Type.

I have been scoping through many examples, but not that is this specific.

I have also managed to sort of getting the information by making multiple queries, and then iterate though them in a Excel macro / VBA code.

But for my current particular need, I am looking for at way to get all the user objects for the users that uses Windows AD Authentication Type, but can't quite figure out how to, since the SI_KIND='USER' and the objects with SI_NAME = 'secWinAD' is at different places in the CI_SYSTEMOBJECTS

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Jun 27, 2017 at 04:31 PM

    It's a hack, but works:

    select si_name,si_aliases from ci_systemobjects where si_aliases like '%secwinad%'

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 27, 2017 at 02:00 PM

    You cannot directly access SI_ALIASES PROPERTY. try this

    SELECT SI_NAME,SI_ALIASES FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’

    You either use Win AD or Enterprise as primary authentication mechanism. You can also try this

    SELECT SI_NAME,SI_ALIASES FROM CI_SYSTEMOBJECTS WHERE SI_KIND=’USER’ and SI_ALIASES.SI_TOTAL>1

    Add comment
    10|10000 characters needed characters exceeded

    • In that case we would expect that the users are removed from the AD groups.

      I tested this and it seems to work from an earlier post with the si_kind used to only display users not AD groups.

      select si_name,si_aliases from ci_systemobjects where si_aliases like '%secwinad%' and si_kind='user'

  • Jun 27, 2017 at 02:35 PM

    I would suggest you to go ahead and try BI Platform Support tool for this. Alternatively you can get the user list based on each mapped AD group and start working on it.

    Thanks

    Mani

    Add comment
    10|10000 characters needed characters exceeded

    • That was also one of my go to options, but the idea of first using one tool to get the users, then a second to check them wheter they where still active in AD, and then if not, use a third tool to disable them i BI, seems a bit to complex to automate it propperly,

  • Jun 29, 2017 at 01:55 PM

    It's also possible to write some fairly simple Java or .NET code that will pull the user ID, Name, Authentication Type, and Last Login Date and output it to a file (I personally use .csv files as they're simple to write.) That way you have the information in a file, and not just on the screen, where it will be easier to analyze and track the users you've updated.

    -Dell

    Add comment
    10|10000 characters needed characters exceeded

    • Hi and thanks for the input.

      This is actually what I am doing, but for this particular job I was only interested in the ones with the AD Login, not necessarily all users, and I have learned that good practice it to filter your data up front.

      //Thomas