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
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
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
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?
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...
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.
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
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.
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
Thanks for that ... however this seems to be for Forms whereas I require it for Directory Server. Am I right?
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.
oh ok, I will give a go and let you know. thanks
Good man! That works like a treat! Thank you do much
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!