List of unused security user groups

[mathieu_cartel]

Hello all,

Asking for a client: they've been live for several years and they would like to perform a clean up of their security user groups. They need to obtain a list of the groups that exist in OS that are no longer used/referenced anywhere in metadata, workflows... Pre-built Security reports do not offer this. I'm guessing we need to put together a query to the DB but I don't know all the tables that reference a user group ID. Can someone help please?

[JackLacava]

Hey Mathieu,

I suspect deleting groups might not be a good idea because of references in historic data and audit tables, so after you've found the unused groups, maybe just rename them to X_HISTORIC_* and remove all members.

To locate fields, you might want to get clever with the API guide, which includes all schemas. If you look at the files contained in OneStreamAPIDetailsandDocumentation.zip, in the APIDetails folder there is an .hhc folder - that's a text file. I would search for things like "GroupUniqueID" to start with.

Hope this helps!

[mathieu_cartel]

Thanks Jack and sorry for the late answer. I didn't know we had the schema fully documented in there so I'll take a look. I took a quick look at the DB from the SQL Server client and I know for sure that the field isn't always called like that which is going to make things a little longer. Cheers!

[DanielWillis]

I think the Sec prefixed tables in the system database should give what you need