Generate a report of users in each group in Cascade

nssabol's Avatar

nssabol

06 Jan, 2015 10:21 PM

Hello there Tim,

Sorry to hit you up twice in a day.

Another (hopefully) quick question - is there an easy way to get a report (csv, xls, etc.) of all Cascade groups and their membership?

Thank you for all of your help and support,
-Neil

  1. Support Staff 1 Posted by Tim on 06 Jan, 2015 10:28 PM

    Tim's Avatar

    Hi Neil,

    I think something like this will work if you're using MySQL:

    select u.userName, (select group_concat(g.name) from cxml_group g, cxml_group_membership gm where g.name = gm.groupName and gm.userName = u.userName) groups from cxml_user u order by u.userName
    
    Let me know if that returns the data you're looking for. If you don't happen to be using MySQL, let me know which vendor you are using and I can try to find a working query for that as well.

    Thanks!

  2. 2 Posted by nssabol on 06 Jan, 2015 10:31 PM

    nssabol's Avatar

    Hi Tim,

    Many thanks for the prompt reply!

    We are actually using Oracle 10g for our production environment - any guidance you can provide for that database engine is much appreciated.

    Thank you again,
    -Neil

  3. Support Staff 3 Posted by Tim on 06 Jan, 2015 10:33 PM

    Tim's Avatar

    For Oracle, I was able to track down this query which appears to have been used by one of our other clients. Want to give it a shot?:

    select groupname, LISTAGG(USERNAME, ',') WITHIN GROUP (ORDER BY
    USERNAME) AS USERNAMES from cxml_group_membership group by GROUPNAME;
    
    Let me know if that does the trick.
  4. 4 Posted by nssabol on 06 Jan, 2015 11:30 PM

    nssabol's Avatar

    Hi Tim,

    Awesome, many thanks! I will work with our DBAs to execute this query. You can close this discussion – I will let you know if this does not work.

    Thanks again,
    -Neil

  5. Support Staff 5 Posted by Tim on 06 Jan, 2015 11:51 PM

    Tim's Avatar

    Sounds good, Neil! Take care.

  6. Tim closed this discussion on 06 Jan, 2015 11:51 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac