SQl query to link User to their respective User Groups? OnBase

[Greg_Seim]

Cleaning up old data and I need a sql query that extracts the usernum, realname, lastlogon etc. and joins the User Groups they are assigned to.  The report builder included in config, spits out a .txt file that is unusable for the most part.

Thank you.

[aboucher]

Hi @Greg Seim,

I changed the tag for this community question from Community (Site) to General OnBase. For the best customer experience, please always tag your question with a specific product forum so that community members following that forum will be notified when you post your question.

Cheers!
~Alan

[Ryan_Wakefield]

@Alan Boucher ,

You might also add the tag for Database as that is where this really would get the traction that is needed.

[Ryan_Wakefield · Accepted solution]

@Greg Seim ,

As a note, this is for MSSQL (Microsoft SQL Server). So if you have Oracle, some edits might need to be made. Also, this is only returning the columns you mentioned. If you are needing additional data points, you can add them as you see fit.

--This gives you all of the core details about any User Account.select * from hsi.useraccount--This gives you all of the core details about any User Group.select * from hsi.usergroup--This table is what is used to tie what User Groups the User Account is assigned.select * from hsi.userxusergroup--This is the SQL query that combines the 3 tables above to rend what you are looking for.select ua.usernum,	ua.username,	ua.realname,	ua.emailaddress,	ua.lastlogon,	ug.usergroupnamefrom hsi.useraccount uainner join hsi.userxusergroup uxug on uxug.usernum=ua.usernuminner join hsi.usergroup ug on ug.usergroupnum=uxug.usergroupnumorder by username

[Greg_Seim]

Yes thank you!