SQL Query to retrieve the email, username, display name and the License Type

asked on November 20, 2017 • Show version history

Hi all,

I am using Rio and I want to extract a list of users with Full Name and the license type they are assigned on LFDS (i.e Full or participant)

I have followed this thread: https://answers.laserfiche.com/questions/117487/Directory-Server-database#117524 and the LFDS license type is not OK [Please see below query run]. Can someone please helping it tweaking it.

SELECT TOP 1000 [username], [email], LicType =(CASE user_type
when 0 then 'repository user'
when 1 then 'named user'
when 2 then 'unknown'
when 3 then 'partcipant'
when 4 then 'participant'
ELSE NULL
END)
,[is_lfadmin],[user_id],[displayname],[is_activated]
FROM [LFForms].[dbo].[cf_users]
where is_activated = 1

0 0

replied on November 22, 2017

The query you are trying to use is to query the Forms DB and not the LFDS DB. Did you try the query provided by Brianna against the LFDS DB?

1 0

View 3 previous replies

replied on November 22, 2017

Hi Bert,

I have run the query that Brianna proposed but I am not having the Display Name of the AD user. Thanks to advise.

0 0

replied on November 22, 2017

The query that Brianna provided shows the AD user as Domain\Name. How do you want the name displayed?

0 0

replied on November 22, 2017

Hi Bert,

This should be like this:

Thanks to let me know how this can be achieved.

0 0

replied on November 23, 2017 • Show version history

The query you want to run on the LFDS DB would be like:

SELECT UserName = ([directory_objects].[name])
	,eMail = (SELECT [str_val]
				FROM [additional_claims]
				WHERE [additional_claims].[claim_id] = 1
				AND [additional_claims].[sid] = [directory_objects].[sid])
  	,LicType = (CASE [user_licenses].[type]
				WHEN '9bba0d89-9a13-455f-ada9-83cf071d46b9' THEN 'Full'
				WHEN '6e858768-cd5c-4e50-8fbe-aced7f67e25f' THEN 'Retrieval'
				WHEN 'dde75261-70e3-49f5-84bc-d8ef36f1aa56' THEN 'Participant'
				END)
    ,DisplayName = (SELECT [str_val]
					FROM [additional_claims]
					WHERE [additional_claims].[claim_id] = 12
					AND [additional_claims].[sid] = [directory_objects].[sid])
FROM [directory_objects]
INNER JOIN [user_licenses] ON [directory_objects].[sid]=[user_licenses].[sid]

3 0

replied on November 26, 2017

Hi Bert,

I tried your query:

SELECT UserName = ([directory_objects].[name])

,eMail = (SELECT [str_val]

FROM [additional_claims]

WHERE [additional_claims].[claim_id] = 1

AND [additional_claims].[sid] = [directory_objects].[sid])

,LicType = (CASE [user_licenses].[type]

WHEN '9bba0d89-9a13-455f-ada9-83cf071d46b9' THEN 'Full'

WHEN '6e858768-cd5c-4e50-8fbe-aced7f67e25f' THEN 'Retrieval'

WHEN 'dde75261-70e3-49f5-84bc-d8ef36f1aa56' THEN 'Participant'

END)

,DisplayName = (SELECT [str_val]

FROM [additional_claims]

WHERE [additional_claims].[claim_id] = 12

AND [additional_claims].[sid] = [directory_objects].[sid])

FROM [directory_objects]

INNER JOIN [user_licenses] ON [directory_objects].[sid]=[user_licenses]

I am having this error in SQL Server Management :

Msg 207, Level 16, State 1, Line 31

Invalid column name 'user_licenses'.

I have underlined the concerned error in the SQL query.

Thanks to advise how to correct this.

0 0

replied on November 26, 2017

Syntax error missed. Thanks a lot for your help

0 0

Question

Question

SQL Query to retrieve the email, username, display name and the License Type

Replies

Sign in to reply to this post.