Overview
This article lists the SQL queries used for troubleshooting SSO/SAML related issues.
Information
The following queries are run to check the user profile and user properties of a user:
SELECT userid,
Username,
Firstname,
Lastname,
CASE WHEN userenabled=0 THEN 'Disabled'
WHEN userenabled=1 THEN 'Enabled'
ELSE 'Value not documented' END as user_Enabled,
CASE WHEN Federated=0 THEN 'Not Federated'
WHEN Federated=1 THEN 'Federated'
ELSE 'Value not documented'END as Federated,
CASE WHEN visible=0 THEN 'NOT Visible'
WHEN visible=1 THEN 'Visible'
ELSE 'Value not documented 'END as Is_User_Visible,
CASE WHEN Status= 0 THEN 'None'
WHEN Status= 1 THEN 'Awaiting moderation'
WHEN Status= 2 THEN 'Approved'
WHEN Status= 3 THEN 'Rejected'
WHEN Status= 4 THEN 'Awaiting validation'
WHEN Status= 5 THEN 'Validated'
WHEN Status= 6 THEN 'Registered'
WHEN Status= 7 THEN 'Invited'
ELSE 'Value not documented'END as User_Status,
CASE WHEN usertype=0 THEN 'Regular (internal) user'
WHEN usertype=1 THEN 'External (bridged) user (cannot log in to sbs)'
WHEN usertype=2 THEN 'Partner user'
ELSE 'Value not documented'END as User_Type,
CASE WHEN namevisible=0 THEN 'Not Visible'
WHEN namevisible=1 THEN 'Visible'
ELSE 'Value not documented'END as Name_Visible, email,
CASE WHEN emailvisible=0 THEN 'Not Visible'
WHEN emailvisible=1 THEN 'Visible'
ELSE 'Value not documented'END as email_visible,
to_timestamp(creationdate/1000) as CreationDate,
to_timestamp(modificationdate/1000) as Modification_Date,
to_timestamp(lastloggedin/1000) as LastLoggedin_Date,
to_timestamp(lastprofileupdate/1000) as
LastProfile_Update
FROM jiveuser
WHERE USERID=<INSERT USER ID>;
SELECT * FROM jiveuserprofile WHERE USERID=<INSERT USER ID>;
SELECT JEI.userid as user_id,
JEI.externalidentityid as external_identity_id,
(SELECT concat(firstname,' ',lastname) FROM jiveuser ju WHERE
ju.userid=jeI.userid) as User_Name,
(SELECT email FROM jiveuser ju WHERE ju.userid=jeI.userid) as user_email,
JEI.identityvalue as external_identify_value,
CASE WHEN JEI.TYPE=1 THEN 'SAML'
WHEN JEI.TYPE=2 THEN 'Open ID'
WHEN JEI.TYPE=3 THEN 'Facebook'
WHEN JEI.TYPE=4 THEN 'LDAP_DN'
ELSE 'Value not documented' END AS External_Idenity_Type_Name
FROM jiveexternalidentity JEI
WHERE USERID=<INSERT USER ID>;
Comments
0 comments
Please sign in to leave a comment.