Since May, we've been asked by nearly half of our 10 consortia members to help them pull a list of their users and assigned user groups from Security for auditing purposes. I wrote a bit of code for it the first time around, and I've been tweaking it since. I thought that it might come in handy here for other consortia if they need to audit active/inactive users for a particular organization (if they didn't already have something in use, anyway!). So, hopefully, this can come in use to someone else like it has for me!
Thanks,
Katie Hathaway
/* Katie Hathaway, TAMC Identify Users from a specific user group--both active and inactive. Written Summer 2023, modified 9/19/23 */ select * from T_METUSER --helpful select statements to view tables select * from T_METUSERGROUP select * from TX_USER_GROUP select * from TR_CONTROL_GROUP select * from TX_CONTROL_GROUP_USER_GROUP select * from ltr_user_info select a.userid as User_ID, a.fname as First_Name, a.lname as Last_Name, a.inactive as Inactive, d.UG_name as User_Group_Name, b.default_ind as Primary_User_Group, a.location as Tessitura_Staff_Indicator from T_METUSER a left join TX_USER_GROUP b on a.userid = b.userid left join TR_CONTROL_GROUP c on a.control_group = c.id left join T_METUSERGROUP d on b.UG_id = d.UG_id where d.UG_name like 'Am Song%' and a.inactive = 'N' --pulls by User Group Desc, change inactive status as needed --can also use: d.UG_id like 'P%' to pull by user group ID
Thanks for sharing!