Need SQL query to list users configured to use Integrated (AD)

[David_Whelan]

We are needing to review our user setup within OnBase and our Active Directory.  Is there a query you can provide that returns all OnBase users and if they are Integrated or not? 

Checking the database, it looks like the users USERACCOUNT table doesn’t list this information and I’m not finding it noted in the Database Reporting guide either.  Maybe one of the fields in the user table includes this information, but is a BIT value inside a single field?

I know we can run the users report to get all this written to a text report, but we’d prefer to not have to parse through all that to simply get this kind of result:

Username,AD,Standard

Dwhelan,1,0

Fred,0,1

John,1,0

Thanks!

David

[James_Perry · Accepted solution]

David, we use the following SQL query to get a list of all active users in our OnBase system. Those with AD accounts have a Security_ID (SID) listed. OnBase accounts have a blank Security_ID field.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT 	usernum AS 'User_Number'	, RTRIM(realname) AS 'User_Name'	, RTRIM(username) AS 'User_ID'	, disablelogin AS 'Disabled'	, lastlogon AS 'Last_Logon'	, lastpwchange AS 'Last_Pwd_Change'	, RTRIM(securityid) AS 'Security_ID'FROM hsi.useraccountWHERE 	(licenseflag & 2)<>2 --NOT Deactivated	AND (username NOT LIKE '%disabled%')ORDER BY 	disablelogin DESC, User_ID ASC

[David_Whelan]

Thank you very much!

[AdamShaneHyland]

As a follow up to what @Jim Perry  provided, the hsi.useraccount.securityid is the field associated with the user account which stores the SID from Active Directory.  This field is populated the first time the user authenticates via Directory Service Authentication (e.g. Active Directory).  Subsequent attempts to login will compare this field in order to authenticate the user into OnBase.

However, to add on to this, a user account in OnBase can authenticate using an OnBase username and password AND domain authentication when their OnBase user account is configured with the Authentication (Config | Users | Settings) setting Standard Logon.  When an OnBase user account is configured with the Standard Logon radio button selected, the user account will have a password associated.  This allows the user to login with their OnBase username and password using any client configured for OnBase credentials.  It also allows them to logon with Directory Service Authentication for any client configured for this method of authentication.  If the user account is configured with the Authentication setting Integrated Security Logon Only, then the user will ONLY be able to logon with Directory Service Authentication.

Assuming that all of your user accounts are configured for Directory Service Authentication AND you are provisioning users upon authentication (meaning the user account is created the first time they logon to the app), then looking at the presence of the security hsi.useraccount.securityid field is likely the most accurate way to determine if users are using Active Directory authentication.  You can further filter by disabled.

Best wishes.