BusinessObjects Board

XIR2 Query Builder Admin Reports

security CMC
#1

I have compiled a list of queries that will get some basic information out of the CMC. I’m trying to figure out how to run more complex queires to get all the reports that I need.

here is the initial list. feel free to add queries… Thanks

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’

mohammedq (BOB member since 2006-02-01)

Can I extract a list of all users in CMC into a report?
Web i report documentation
Query Builder
query to get
query to get instance of a report
Query for Security Groups within Folders
howto create a "report of Reports"?
Need query using Query builder to get List of Webi Reports
List of Scheduled Reports
Generate a list of LDAP Groups
Hidden tables in BOXI R2
count in CMC
#2

Good one, thanks :wink:

Sheshachala5 :india: (BOB member since 2004-01-09)

#3

these are great i wish others would post their ideas here.

LANFIELD :us: (BOB member since 2006-05-18)

#4

Hello

Very useful!!! great. I’m testing the query:
Get All Users
SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS WHERE SI_PROGID=‘CrystalEnterprise.USER’

…and trying to introduce some changes…
SELECT SI_ALIASES FROM CI_SYSTEMOBJECTS WHERE SI_PROGID=‘CrystalEnterprise.USER’

…but I’m not able to query those users that have secEnterprise disabled (SI_DISABLED=true)… :hb:

Any ideas?

Thanks in advanced.

my mail: alberto.moreno@mango.com

albertmoreno (BOB member since 2006-01-05)

#5

If you have Security Viewer, you can see the users that have been disabled.

As I have said before, Query Builder is intented for some quick and dirty adhoc queries only. It is not designed for you to extract a large amount of data. The HTML output is useless when you have too many rows of data returned.

Use the Security Viewer for user/group permission check. For report and other objects, you have to write code with SDK.

Hope this clarifies.

substring :us: (BOB member since 2004-01-16)

#6

the Security Viewer works fine. they did say its a sort of stop gap tool and will not carry on to the next version. i am assuming :shock: that means it will be built into the CMC somehow

LANFIELD :us: (BOB member since 2006-05-18)

#7

Who knows? It might be the prelude to the comeback of Supervisor. :wink:

substring :us: (BOB member since 2004-01-16)

#8

id rather not stay attached to old tools. I would rather see something better thena either past toolset.

LANFIELD :us: (BOB member since 2006-05-18)

#9

What I meant is not the comeback of the “old” supervior as it won’t work in the XI architecture anyway. I am hoping they will give us a supervisor-type of client tool to manage the system. CMC is very low-tech in these days and age.

substring :us: (BOB member since 2004-01-16)

#10

TRUE it was an attempt at the all web client with very limited gui client. and as we all know the rush inn the application development industry to the all web client was a little missguided at times. they originally had a fat client system with a small web client in the Crystal Info system.

LANFIELD :us: (BOB member since 2006-05-18)

#11

Is everyone doing this though the “Business Objects Business Intelligence platform - Query Builder” Its works, but there is no way I can adjust the formatting. Is there another way to run the SQL in like a webi doc?

Thanks a bunch!

jcbeckwith (BOB member since 2006-04-26)

#12

[Is everyone doing this though the “Business Objects Business Intelligence platform - Query Builder” Its works, but there is no way I can adjust the formatting. Is there another way to run the SQL in like a webi doc?]

That’s the point - there is no way to format the output, it is not meant for large dataset returns and NO you cannot run the sql in any of the reporting tools.[/quote]

blair :canada: (BOB member since 2006-11-17)

#13

[albertmoreno wrote: …but I’m not able to query those users that have secEnterprise disabled (SI_DISABLED=true)… ]

Unfortunately SI_DISABLED is a sub property of the ALIASES property of the user class so i cannot find a way to show only disabled users through a query, you’ll have t use the SDK. Good Luck.

mohammedq (BOB member since 2006-02-01)

#14

Where there’s a Will, there’s a way. Open Excel and copy and paste the Query Builder results from IE. If you are careful with your Query Builder queries and don’t include any fields that vary in verticle size, you can manipulate the output in Excel using =if(A6=“SI_NAME”,B6,"") style formulas in subsequent columns. Once you get the results you want, copy the fomlulas down the rows. Finally, copy and Paste-Special by Values into the next set of columns and sort to group them. Keep in mind that Query Builder is limited to 1000 objects returned.

PCGEEKUS

pcgeekus :us: (BOB member since 2003-10-16)

#15

Hi

        Thanx a ton.

Harish

harishreddy :india: (BOB member since 2006-05-11)

#16

Hi all,

Is it possible using Query builder to get the list of all users from a specific group ?

Thanks in advance

Regards

#17

Or U can build the Universe based on this tables
CMS_Aliases5
CMS_IdNumber5
CMS_InfoObjects5
CMS_RELATIONS5
CMC_VersionInfo

and build the reports in Required format…

any ideas is much apreciated on building the universe on Repository.
because we are planing to build the univese on Repository.

Thanks in advance,
Siva :roll_eyes:

cva :uk: (BOB member since 2006-05-20)

XI Repository (Schema Design)
#18

Hi Siva,

You can no longer query the reporsitory using standard SQL. You can do this only using sdk.

Regards
Sebastien

#19

Hi,

Can we create a universe that connects these CMC tables such as CI_INFOOBJECTS and reports off it? Or, is there a way to create Webi reports from tables such as CI_INFOOBJECTS?

Thanks.

ywmui (BOB member since 2006-05-01)

#20

Yes (but not easily). I have normalized and extracted the XI R2 metadata layer via a utility I wrote. Once you have the contents extracted, normalized, and loaded properly, creating an ad-hoc universe on top of it will allow much more flexibility in environment reporting, and allow you to report on just about anything you’d like.

crystal01 :us: (BOB member since 2006-08-30)