Query Builder Admin Tool collections

[carlos_castrilln]

Hello,

We would like to ask a BOE XI 3.1 SP3 CMS Repository (ORACLE 11.2) for Users that only have one alias and that alias is Enterprise.

Firts we would like to do that using the web admin tool Query Builder, but we have problems with SI_ALIASES property because it is a collection of objects with their own properties and we don't know how to filter for that properties in a SQL Query.

How can we peform these kind of queries?. Those queries that filter for the SI_ALIASES object properties. For example... si_name (alias name)

We supposed that it could be solve with a little java program using java sdk also.

There is not much information about these issues.

Does anyone have the same problem?. How do you solve it?

Thanks in advance,

Carlos Castrilló

[Former Member]

QUERY BUILDER BOBJ

Get All Webi reports

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Webi' And SI_INSTANCE=0

Get Full Client Reports

SELECT SI_ID, SI_NAME,SI_FILES FROM CI_INFOOBJECTS WHERE SI_KIND in( 'webi' ,'FullClient')

Get All reports

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Report' And SI_INSTANCE=0

Get All universes

Select SI_ID, SI_NAME, SI_WEBI, SI_KIND From CI_APPOBJECTS where SI_KIND ='Universe'

Get All Users

SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID='CrystalEnterprise.USER'

Get all groups

Select * from CI_SYSTEMOBJECTS Where SI_KIND='UserGroup'

get all folders

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_PROGID='CrystalEnterprise.Folder'

get all categories

SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND='Category'

get all personal categories

Select SI_ID, SI_NAME From CI_INFOOBJECTS Where SI_KIND='PersonalCategory'

[Former Member]

Hi Carlos!

You can try to filter on the number of si_aliases.si_total

Identify users with only 1 login alias (no SSO)

SELECT SI_ALIASES FROM CI_SYSTEMOBJECTS Where SI_KIND='User' and si_aliases.si_total=1

SELECT SI_USERFULLNAME, SI_ALIASES, SI_EMAIL_ADDRESS, SI_FORCE_PASSWORD_CHANGE, SI_NAME, SI_ID, SI_USERGROUPS, SI_DESCRIPTION, SI_LASTLOGONTIME, SI_PASSWORDEXPIRE FROM CI_SYSTEMOBJECTS Where SI_KIND='User' and si_email_address='' and si_aliases.si_total>1

ciao Hakan