Skip to Content
0

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

Jun 27, 2017 at 08:00 AM

605

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Joe Peters Jun 27, 2017 at 04:31 PM
0

It's a hack, but works:

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Yes - definitely a Hack, but I can work with this, and it is the best solution to my need at this moment.

0

ok a little add on to remove AD groups select si_name,si_aliases from ci_systemobjects where si_aliases like '%secwinad%' and si_kind='user'

0
Manikandan Elumalai Jun 27, 2017 at 02:00 PM
0

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

Show 4 Share
10 |10000 characters needed characters left characters exceeded

My "problem" with this approach is, that in the current setup, there is a mix of Enterprise users. Windows AD users and SAP users, I am currently only interested in those using Windows AD

My reason is, I would like to get at list of these Windows AD users, so I can check them against the Windows AD. to see if they are Active, and if not then also Disable them in BO.

Obviously a SAP or Enterprise user does not necessarily also exists in the Windows AD, and therefore I could end up disabling a user that was not intended.

0

to note AD users that are inactive or disabled in AD cannot logon to BI the authentication never goes through BI it's direct to AD. So there is no need to locate and disable BI users unless you created active enterprise aliases for them.

When using an external directory it is expected that the disabling/deleting of unused accounts is performed entirely in that external directory.

-Tim

1

Hi Tim

The "problem" is that if a user is disabled in Windows AD, the user still counts as an active user in the BI Platform, at least to the LMBI tool. So it might be that the user cannot logon, but they still counts as a license, since they are not automatically disabled in the BI Platform as well.

//Thomas

0

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'

0
Manikandan Elumalai Jun 27, 2017 at 02:35 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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,

0
Dell Stinnett-Christy Jun 29, 2017 at 01:55 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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

0