You are viewing limited content. For full access, please sign in.

Question

Question

Directory Server database

asked on March 20, 2017

Hi,

We would like to do license usage auditing. Is there a SQL query that can extract all license holders, whether retrieval or full from the SQL database of Directory Server?

 

Thanks

0 0

Answer

SELECTED ANSWER
replied on March 20, 2017 Show version history

In Directory Server, the license assignments are stored separate from the SID to username mapping, so you need to perform a join.

The following query will bring back all licensed users in Directory Server (not any assigned in Forms or the Laserfiche Server), substituting the license type name for the license type GUID:

SELECT [directory_objects].[name]
	,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 'Submitter'
	End)
	,[directory_objects].[sid]
From [directory_objects]
Inner Join [user_licenses] on [directory_objects].[sid]=[user_licenses].[sid]

Edited Nov 2022: dde75261-70e3-49f5-84bc-d8ef36f1aa56 is the GUID for the deprecated license type assigned to forms-only users. Participant licenses that allow submitting to Forms and browsing the repository have unique GUIDs on each primary license

2 0
replied on August 9, 2018

This is exactly what I need but for License Manager. (LF version 9.2.1 and LM 8.3, I think)

 

Is there a similar thing that I can do? Will I have to check the License Manager .db file? Or is there other way?

0 0
replied on February 5, 2020

The SQL above returns the sid in binary form. Example:

0x0106000000000009ADDCBA00D088399C09A5B248800F641B942ED62A0B000000

Is there a way to resolve this to the Active Directory SID?  I am trying to root out the source of these annoying DS messages:

LFS received an unrecognized or unexpected error from LFDS. Service Call=GetUnsignedTokenEx(token=<a:ApiVersion xmlns:a="http://laserfiche.com/namespace/lm">2.0</a:ApiVersion><a:,database=<a:DBMSType xmlns:a="http://laserfiche.com/namespace/lm">0</a:DBMSType><a:Flags ,sidString=S-1-9-12246189-2621016272-1219667209-459542400-718679700-126,identityProviderName=,isLocal=false); LFDS error code=95

 

without having to go and research the AD shown above, along with many others.  We have already tried Cast, Convert and Decompress...

 

0 0
replied on April 14, 2020

Below is an incomplete c# code snippet that may help with the conversion:

using System;
using System.Data;
using System.Linq;
using System.Security.Principal;


public class ConversionExample
{

    private string ConvertHexSidToStringSid(string hexSID)
    {
        // Check if hexSID has is prefixed with '0x'. Remove it if it does.
        if (hexSID.StartsWith("0x"))
            hexSID = hexSID.Substring(2);
        // Convert the hex string to a byte array.
        byte[] sidBytes = Enumerable.Range(0, hexSID.Length).
    Where(x => x % 2 == 0).
    Select(x => Convert.ToByte(hexSID.Substring(x, 2), 16))
    .ToArray();
        // Return the string SID
        return new SecurityIdentifier(sidBytes, 0).ToString();
    }
}

It converts from a hex string to a byte array, then uses the C# library System.Security.Principal to do the conversion from the byte array to the to the SID and casts it to a string.

I'm not aware of a way to do this without the C# library System.Security.Principal, but there were multiple posts online when I referenced this conversion, so there may be some options.

0 0
replied on August 23, 2022

Had to convert SIDs for users as part of a conversion of a client from Windows Accounts to SAML accounts. Not C# versed, so I spent some time vetting out another solution. I wanted to do this in powershell, but ended up borrowing some vbscript code. 

Save this code to a file, i.e. "convertsids.vbs" and execute from a CMD prompt with the hexsid as the single parameter: 

cscript /nologo convertsids.publish.vbs 010500000000000515000000FF869BA35EB1DC6FAA3E7C2717FB0C00

output will be: hexSID, stringSID, i.e.: 

010500000000000515000000FF869BA35EB1DC6FAA3E7C2717FB0C00,S-1-5-21-2744878847-1876734302-662453930-850711

 

vbscript code

Dim Arg
Set Arg = WScript.Arguments
hexSID = Arg(0)
strSID = HexStrToSID(hexSID)
WScript.Echo hexSID & "," & strSID

Function HexStrToSID(strSid) 
'converts a raw SID hex string to the according SID string (SDDL)
    Dim i, data, offset
    ReDim data(Len(strSid)/2 - 1) 
    For i = 0 To UBound(data) 
        data(i) = CInt("&H" & Mid(strSid, 2*i + 1, 2)) 
    Next 
    HexStrToSID = "S-" & data(0) & "-" & Byte6ToLong(data(2), data(3), data(4), data(5), data(6), data(7))

    blockCount = data(1)
    For i = 0 To blockCount - 1
        offset = 8 + 4*i
        HexStrToSID = HexStrToSID & "-" & Byte4ToLong(data(offset+3), data(offset+2), data(offset+1), data(offset))
    Next
End Function 

Function OctetToHexStr(var_octet)
'converts pure binary data to a string with the according hexadecimal values
    OctetToHexStr = ""
    For n = 1 To lenb(var_octet)
        OctetToHexStr = OctetToHexStr & Right("0" & hex(ascb(midb(var_octet, n, 1))), 2)
    Next
End Function

Function Byte4ToLong(ByVal b1, ByVal b2, ByVal b3, ByVal b4)
'converts 4 bytes to the according lang integer value
    Byte4ToLong = b1
    Byte4ToLong = Byte4ToLong * 256 + b2
    Byte4ToLong = Byte4ToLong * 256 + b3
    Byte4ToLong = Byte4ToLong * 256 + b4
End Function


Function Byte6ToLong(ByVal b1, ByVal b2, ByVal b3, ByVal b4, ByVal b5, ByVal b6)
'converts 6 bytes to the according lang integer value
    Byte6ToLong = b1
    Byte6ToLong = Byte6ToLong * 256 + b2
    Byte6ToLong = Byte6ToLong * 256 + b3
    Byte6ToLong = Byte6ToLong * 256 + b4
    Byte6ToLong = Byte6ToLong * 256 + b5
    Byte6ToLong = Byte6ToLong * 256 + b6
End Function

 

2 0
replied on November 3, 2022

We added this to the case statement for Participant licenses: 57bf6faf-9269-1c97-4370-ab3d6984d2c5.

Where is the user profile data stored? I would like to add that to the view.

0 0

Replies

replied on March 20, 2017 Show version history

We use this query:
 

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

It returns named user and participants :
DOC\apappas    apappas@xxxxxxx    named user    0    9    Pappas, Andrew    1

I am not sure what the user_type of 2 is ( we don't have any! )
Participant license seem to be a user_type of 4 when added from AD, and 3 when manually added. Although I don't know this for sure.

Hope that helps!

(we are using Avante,  I don't know if RIO would be a different query )

Andrew

 

2 0
replied on March 20, 2017

Thanks for that ... however this seems to be for Forms whereas I require it for Directory Server. Am I right?

0 0
replied on March 20, 2017

This is pulling from the forms database but does return a list of all fully named users configured in the repository as well as forms participants.   Being on Avante we don't have Directory Server and this query seems to work well for our license audit/checks.   Hopefully a better query will be posted specific to Directory Server.   I know I would use it once we make the jump to RIO.

0 0
replied on March 20, 2017

oh ok, I will give a go and let you know. thanks

0 0
replied on March 20, 2017

Good man! That works like a treat! Thank you do much

0 0
replied on March 21, 2017

Thank you to both of you. Appreciate Andrew's reply however the Directory Server was the exact one I needed. his will help me to be able to report of users and licenses. Thank you once again!

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.