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?
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.
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…
You really have two options in order to get this information in a useable format:
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).
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)
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());
}
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.
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)…