To find the all users who has secuirty keyword configured

[ANIL_PATEL]

I want to find out all users KID that have security keyword setup and the keyword and values for it.  Is there a way to find out through database SQL query?

example:

User's KID = patela123

Security Key word = Department

Keyword Value for Department Keyword = MCIT

Any suggestions?

Thanks

Anil Patel

[Joel_Moore1 · Accepted solution]

Hi Anil,

I believe this query should help you obtain this information:

SELECT 

ua.username AS 'KID'

,kt.keytype AS 'Security Keyword'

,sc.keyvaluechar AS 'Security Value' 

FROM 

hsi.useraccountseckeys sc

JOIN hsi.keytypetable kt ON sc.keytypenum = kt.keytypenum

JOIN hsi.useraccount ua ON sc.usernum = ua.usernum 

Please let me know if this is helpful.

Best,

Joel

[ANIL_PATEL · Accepted solution]

Joel

Thanks you so much for your help, it works.

I just need to make some change in quotation mark and join the values as

SELECT ua.username as KID, kt.keytype AS SecurityKeyword, sc.keyvaluechar AS SecurityValue FROM hsi.useraccountseckeys sc

JOIN hsi.keytypetable kt ON sc.keytypenum = kt.keytypenum

JOIN hsi.useraccount ua ON sc.usernum = ua.usernum

Appreciates for your help

Have a great day

Thanks

Anil Patel

[Joel_Moore1 · Accepted solution]

Hi Anil,

I believe this query should help you obtain this information:

SELECT 

ua.username AS 'KID'

,kt.keytype AS 'Security Keyword'

,sc.keyvaluechar AS 'Security Value' 

FROM 

hsi.useraccountseckeys sc

JOIN hsi.keytypetable kt ON sc.keytypenum = kt.keytypenum

JOIN hsi.useraccount ua ON sc.usernum = ua.usernum 

Please let me know if this is helpful.

Best,

Joel

[ANIL_PATEL · Accepted solution]

Joel

Thanks you so much for your help, it works.

I just need to make some change in quotation mark and join the values as

SELECT ua.username as KID, kt.keytype AS SecurityKeyword, sc.keyvaluechar AS SecurityValue FROM hsi.useraccountseckeys sc

JOIN hsi.keytypetable kt ON sc.keytypenum = kt.keytypenum

JOIN hsi.useraccount ua ON sc.usernum = ua.usernum

Appreciates for your help

Have a great day

Thanks

Anil Patel

[ANIL_PATEL · Accepted solution]

Joel

One more question:

If security keyword is set on OnBase User group level, how can I find the similar information from user group level?

Thanks

Anil

[Joel_Moore1]

Hi Anil,

It's a very similar query - we happen to store the Security Keywords for User Accounts and User Groups in separate tables. This should get you what you're after:

SELECT ug.usergroupname AS UserGroup, kt.keytype AS SecurityKeyword, gsc.keyvaluechar AS SecurityValue

FROM 

hsi.usergroupseckeys gsc

JOIN hsi.keytypetable kt ON gsc.keytypenum = kt.keytypenum

JOIN hsi.usergroup ug ON ug.usergroupnum = gsc.usergroupnum