CE10 groups and users export. How to. 2

[cmmrfrds]

How can I export the users that belong to a group. I did not see an export capability on the console. Can anybody give me some direction as to where this data resides and how to export.

 

[MJRBIM]

Do you want to export them out to a file/report, or are you looking to export them to another CE-10 environment?

 

[cmmrfrds]

I would like to export to a file. I need to take the ID's and put them in a table that I will use to filter by ID in a business view.

 

[KingfisherINC]

There is nothing built in, you would need to code somthing based around a query like this:

SELECT SI_GROUP_MEMBERS
FROM CI_SYSTEMOBJECTS
WHERE SI_PROGID = 'CrystalEnterprise.UserGroup'
AND SI_NAME = 'Crystal NT Users'

I assume you're trying to key something off of CE User ID variable?
Are you sure that the built in User / Group filtering functionality does not meet your needs?


Kingfisher [CECP]

 

[cmmrfrds]

Kingfisher, I recognize the data names from our older version but don't find the table name CI_SYSTEMOBJECTS in the new CE10 database. Can you give me some direction of where to find the database so I know where to connect.

 

[KingfisherINC]

It's a Crystal Query (you'll need to write some CSP/ASP/JSP) this information can not be directly accessed through a traditional SQL client.

Kingfisher [CECP]

 

[MJRBIM]

You can also run it through the Query tool on the CE Launchpad

 

[cmmrfrds]

Thank you all for the suggestions. mjrbim, can you suggest a query construction that would give me the groups and what users belong to the groups. The query kingfisher gave me works but only returns id's not names.

 

[mdwyer]

I don't think you can do this with a single query because the APS/CMS database does not support the kind of relational join you would need. You will need to write code, as mentioned above, using something like one of the following to drive an outer loop and calling the other as an inner query.
(These are from CE9.)

function getUsers( sortKey) {
	// Retrieve the PluginManager and use it to retrieve the Users plug-in.
	PluginMgr = IStore.PluginManager;
	UserPlugin = PluginMgr.Plugins("Desktop").Item("CrystalEnterprise.User"); // SAMPLES version
	UserPlugin = PluginMgr.Plugininfo("CrystalEnterprise.User"); // SDK REFERENCE version
	
	// Query the users
	var uQuery = "SELECT SI_ID, SI_NAME, SI_USERFULLNAME, SI_DESCRIPTION, " +
				" SI_NAMEDUSER, SI_USERGROUPS, SI_ALIASES" +
				" FROM CI_SYSTEMOBJECTS " +
				" WHERE SI_PROGID = 'CrystalEnterprise.User'" + 
				" ORDER BY " + sortKey;
	var uColl = IStore.Query( uQuery);
	return uColl;
}

function getGroups() {
	// Retrieve the PluginManager and use it to retrieve the Groups plug-in.
	PluginMgr = IStore.PluginManager;
	GroupPlugin = PluginMgr.Plugins("Desktop").Item("CrystalEnterprise.UserGroup"); // SAMPLES version
	GroupPlugin = PluginMgr.Plugininfo("CrystalEnterprise.UserGroup"); // SDK REFERENCE version
		
	// Query the user groups
	var gQuery = "SELECT SI_ID, SI_NAME FROM CI_SYSTEMOBJECTS " +
				" WHERE SI_PROGID = 'CrystalEnterprise.UserGroup'" + 
				" AND SI_ID > 134"; // exclude default groups: eg Everyone, Administrator
	var gColl = IStore.Query( gQuery);
	return gColl;
}

 

[cmmrfrds]

mdwyer, the functions you show. Would I add these to the repository? How do I invoke the functions in a Crystal Report? Thank you.

 

[mdwyer]

This is csp code that runs on the WCS server. It would produce HTML, or you could use the File System Object method to write to a file. I don't see this information you have requested going to a Crystal Report. There is another thread on theis board that looked at an XML approach that allowed the SYSTEMOBJECTS table to be read into an XML format which was then made available to CR using ODBC. I don't know whether that approach was ever made to work.

If you want to take on the File System Object method that lets your csp code write to a flat file, Google on that term or wait till I am back in the office Monday and can post some additional code.

 

[KingfisherINC]

cmmrfrds: You'll need to understand the basics of ASP and CSP to utilize these functions (or to write your own). Check out the CE_SDK.chm on the product CD.

Kingfisher [CECP]

 

[cmmrfrds]

The select statement looks like standard sql to me. If I had an OLEDB connection to the datasource, could I not do the query from VBA code. I am familiar with FSO, which I have used in some VBA apps.

 

[mdwyer]

The SQL appearance is deceptive. Only the basic format of SQL is supported. The data is stored in an object format and must be manipulated as such.

 

[cmmrfrds]

Thank you for the input. I was hoping there was an easier way to do this.

 

[khartman]

mdwyer,

Not being a coder, what would I have to wrap your code with in order to have it execute to even the HTML output? I would love to be able to get this information out of the system (we are using CE10 on a W2K server environment and IIS 5). This whole issue is coming about because we are shifting from Windows NT authentication to WinAD, and in testing I noticed that in disabling the WinNT authentication it removed all of the users which were mapped to the groups set up in CE. I am hoping to be able to develop a heirarchical listing of the the CE Groups so I know who I need to remap to that group.

--Ken

Kenneth Hartman
Manager, EIS
Hughes Network Systems

http://www.hns.com

 

[mdwyer]

I envisioned the code I suggested as part of a csp file, similar to what runs in ePortfolio. To get the html, you don't need a wrapper per se, but you do also need the code to logon to the CMS.