01-25-2018 05:24 AM - edited 03-14-2019 05:54 PM
Looking to automate export of user list for CUIC 10.x
The CUIC user list is stored on the informix database, not familiar with the schema.
I know the ICM database schema pretty well, but not sure about CUIC's internal database.
The goal is to be able to export the entire list of all CUIC users.
Solved! Go to Solution.
01-25-2018 05:36 AM
create a report definition and report from that definition and run and export the result.
your Query should be "Select * from cuicuser"
01-25-2018 05:36 AM
create a report definition and report from that definition and run and export the result.
your Query should be "Select * from cuicuser"
01-25-2018 05:38 AM
01-25-2018 05:45 AM
There are more David,
for CUIC collection:
SELECT * FROM CUICCOLLECTION
for CUIC collection Value:
SELECT * FROM cuiccollectionvalue
for CUIC valuelist:
SELECT * FROM CUICVALUELIST
and if you want to look at all the table which CUIC db holds:
select * from "informix".systables
Create definition for each of the table and you can see what columns they have.
01-28-2018 02:49 PM
@Chintan Gajjar wrote:
There are more David,
There are indeed.
COLLECTIONS
SELECT c.autoupdate
,c.collectionidentifier
,c.collectionorigin
,c.collectiontype
,c.description
,c.id
,c.isactive
,c.lastrefreshed
,c.name as Collection
,c.owner
,c.partition
,c.refreshperiod
,c.sharingpermission
,c.valuelistid
,c.version
,c.wildcardstring
,v.name as ValueList
,v.valuelistquery
,v.collectionquery
,d.name as Datasource
FROM cuiccollection c
JOIN cuicvaluelist v on c.valuelistid = v.id
JOIN cuicdatasource d on v.datasourceid = d.id
GROUP MEMBERS
select g.id
,g.groupfullname
,g.description
,gm.groupid
,u.name
from cuicgroup g
join cuicgroupmember gm on g.id = gm.groupid
join cuicuser u on gm.memberid = u.id
SCHEDULED JOBS
select
sr.scheduledreportname
,sr.userid
,u.firstname
,u.lastname
from cuicscheduledreports as sr
join cuicuser as u ON sr.userid = u.id
If you use the CLI you can execute "run sql" commands to see the schema. For example
run sql select first 1 * from cuic_data:cuicuser
Or for simple queries - like to get a list of reports
run sql select name from cuic_data:cuicreport
Don't forget the cuic_data: when running from the CLI.
Regards,
Geoff
01-29-2018 03:54 AM
Thanks Geoff, always learning something from you.
01-29-2018 08:15 AM
thanks, I will give that a try
01-29-2018 08:22 AM
thanks that worked!
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide