SQL Query for deactivated accounts

[Tia_Brott-Silke]

I need a query to detect the difference between (deactivated) user accounts because they were "deleted" vs. because they reached the lockout period specified in the password policy. Is there a setting in useraccount for "deleted" or do I need to join to another object?

[Chris_Arpajian · Accepted solution]

Hi Tia,

When users are locked within OnBase, you can see this via hsi.useraccount.disablelogin.  When users are deleted (inactive) or active , this is one of the flags in hsi.useraccount.licenseflag where you have to look and see if the 2nd bit is turned on or off.

I'm less familiar with Oracle in regards to the syntax for bitwise but I think it would look something like this to see which users are in a deleted/deactivated state:

select username, usernum from hsi.useraccount where bitand(licenseflag, 2) = 2;

Feel free to try this out but if you have further questions regarding this or the syntax of the SQL, you may want to check with your first line of support.