BusinessObjects Board

XIR2 Query Builder Admin Reports

security CMC
#25

I understand that I can “See” that information in the CMC but in order to do a security audit, I need to be able to export a list by user group to send to data owners for review…Security Viewer only allows an export of all users but not users by group. I was hoping there was a way to query this somehow…

sturgra (BOB member since 2006-11-08)

#26

You really have two options in order to get this information in a useable format:

  1. Hack your way through with Query Builder running two queries, one to retrieve all users, and the other to retrieve all groups. Copy and paste into Excel, mess with formatting, and get creative to join them. The Group SI_ID will correspond with the User SI_USERGROUPS value(s).

  2. Approach this programatically. Someone in BO tech support released a code snippet which gets you exactly what you’re looking for:

You could also look into 3rd party metadata tools if you want a more comprehensive solution. (we have one - contact me if interested)

Regards

Brian Yaremych
Analytics8

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

#27

You can bypass the limit of 1000 objects by :
Select Top N from …
with N > 1000 so for example
select Top 5000 from

RikDeclercq :belgium: (BOB member since 2006-09-28)

#28

Below is the jsp code to get all users by groups in HTML format:

IInfoObject group = null;
IInfoObject user = null;
String usernames="";
try{

IInfoObjects groups = infoStore.query("Select SI_ID, SI_NAME From "
             + "CI_SYSTEMOBJECTS Where SI_PROGID='CrystalEnterprise.UserGroup'");

IInfoObjects groups1=null;
IInfoObject group1=null;
Object[] memberUsers;
IInfoObjects result=null;
IInfoObject iObject=null;
if (groups.size() == 0)
{
out.println(“No available groups”);
}
else
{
// Retrieve all of the user groups that were returned by the
// query.

    for (int i = 0; i < groups.size(); i++)
    {
	usernames="";
        group = (IInfoObject) groups.get(i);

if(!((group.getTitle()).equals(“Everyone”)))
{
groups1 = infoStore.query("Select TOP 1* From CI_SYSTEMOBJECTS "
+ “Where SI_ID=” + group.getID());
group1 = (IInfoObject) groups1.get(0);
memberUsers = ((IUserGroup)group1).getUsers().toArray();
for (int j = 0; j < memberUsers.length; j++)
{
// Retrieve each group from the infoStore.
result = infoStore.query ("SELECT SI_NAME FROM "
+ “CI_SYSTEMOBJECTS WHERE SI_ID=” + memberUsers[j]);
iObject = (IInfoObject) result.get(0);
usernames = usernames + iObject.getTitle()+ " / ";
}
%>
<%=group.getTitle()%>
<%=usernames%>

<% }} } } catch (SDKException e) { throw new Error ("Failed to retrieve the groups. Exception caught: " + e.getMessage()); }

Hope this helps. :mrgreen:

Anand Gupta :india: (BOB member since 2008-03-03)

#29

In response to the posts in this thread asking about disabled users, we have a freeware tool available that might help (Account Enabler). Displays all users and their status and allows you to quickly switch between enabled and disabled state via a checkbox. Very simple, but it works and it’s free.

We also have a full client query builder and export tool available in the very near future.

JGreg
www.zoom-software.com

JGreg (BOB member since 2007-11-21)

#30

In the download section there is a excel file which gives you all the information about groups and users. The file name is BOXI_UserListV3.xls.

you can find it here :

This excel writes all the groups which a user belongs in to one cell. So you can not filter by group. But it is very easy to change the code.

Here is my version of this xls file. The only difference is, it writes each group which a user belongs in to a new cell.

Feel free to try both files and change it to your own needs.
BOXI_UserListV3_1.xls (56.0 KB)

bozgul (BOB member since 2006-11-06)

#31

Is there a setting in the register that can be manipulated to allow more than 1000 results?

Thanks
Bryan

bthomps01 :us: (BOB member since 2007-01-09)

#32

Yes, you can either change the registry setting at:

HKEY_LOCAL_MACHINE -> SOFTWARE -> Business Objects -> Suite 11.5 -> CMS -> Instances -> , called “'InfoStoreDefaultTopNValue”

or you can just specify

“select TOP ##### (5000, 10000, etc) column1, column2, …”

in your query to retrieve more than 1000 results.

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

#33

worked perfect – Thanks

Bryan

bthomps01 :us: (BOB member since 2007-01-09)

#34

reports by category

I’ve had a look to see if there’s a way of getting reports in a category through the Query Builder, and there doesn’t seem to be a way of doing this.

Example
What I’m expecting is to get reports by category but no luck …………

Report Name Category Name
ABC Report ----- X Universe
PQR Report ----- Y Universe

any help is much apreciated on this.

Thanks in advance,
Cva

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

#35

2 ways of going about this within QB: look at the si_documents field for a particular category object, this will hold the si_ids of reports in this category. OR you can look at the si_corporate_categories field for a given report object, this will hold the category si_id.

That said, if you want this information in a reportable format you’ll need to use the SDK or a 3rd party tool (system profiler, etc)…

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

#36

That 1000 limit in Query Builder can be changed.

Keith Crump (BOB member since 2002-08-15)

#37

Hello
please i need you help. do you have any idea how to get list of reports and groups which have access to those reports using query builder.
thanks for your help in advance

rekha (BOB member since 2008-06-08)

#38

Query Builder doesn’t show any security information so you’ll have to get this elsewhere, i.e. 3rd party software or the SDK. There may be something in the downloads section though I haven’t looked.

BoB LoblaW :us: (BOB member since 2007-10-23)

#39

how about making a query that will tell me when a user last logged on? i have a lot users but no real way of knowing when they are no longer users of reporting. would be nice to keep my user count to a realistic number.

kb0ogt :us: (BOB member since 2007-08-27)

#40 
select si_name, si_lastlogontime from ci_systemobjects where si_kind = 'user' and si_name = '<user name>'

BoB LoblaW :us: (BOB member since 2007-10-23)

#41

Try this to start…

select si_lastlogontime, si_name, si_id from ci_systemobjects
where si_kind = ‘user’

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

#42

ok so i get the below returned. i guess in need something a bit more refined that will tell me the users who have not logged on for the past 30 days and a easy way to delete them. i have 40,000 users and i have a strong feeling that only 2500 of those are active.

14/1000 top
Properties
SI_ID 370
SI_NAME atx1013

15/1000 top
Properties
SI_ID 374
SI_NAME atx1014
SI_LASTLOGONTIME 11/2/2007 9:15:53 AM

16/1000 top
Properties
SI_ID 378
SI_NAME atx1015
SI_LASTLOGONTIME 11/1/2007 4:17:57 PM

kb0ogt :us: (BOB member since 2007-08-27)

#43

In that case, I’m going to refer back to my previous post about the SDK and/or 3rd party tools. Deleting that many users by hand is pretty much impractical.

You can use Query Builder to identify which users have not logged in for x amount of time to verify your estimates:

select top 1000000 si_id, si_name from ci_systemobjects where si_kind = 'user' and si_lastlogontime < '2009.06.30'

The date clause can be anything you like and it’s in the form YYYY.MM.DD.

BoB LoblaW :us: (BOB member since 2007-10-23)

#44

any suggestions for a 3rd party tool to do this?

kb0ogt :us: (BOB member since 2007-08-27)