Query in script library to get a list of users getting an error

Question

Hi, 
 
 I'm trying to write a query in a script library to get a number of accounts in ADSAccountInADSGroupTotal that are members of a certain group. Then have a top(nn) clause to limit the number for moving them to another group. 
 
 I am using this: 
 
 Dim colObjects As IColDbObject = Nothing
	Dim currentObject As ISingleDbObject = Nothing
	Dim f As ISqlFormatter = Connection.SqlFormatter	
	Dim uidObject As String
	
	colObjects = Connection.CreateCol("ADSAccountInADSGroupTotal")
	
	colObjects.Prototype.WhereClause = f.Paging("select uid_ADSAccount from ADSAccountInADSGroupTotal Where UID_ADSGroup = '" & Current_UID_ADSGroup & "'","UID_ADSAccount",1,10) 
	
	colObjects.Prototype("UID_ADSAccount").IsDisplayItem = True
	colObjects.Load() 
 (in case the code tag doesn't work: 
 Dim colObjects As IColDbObject = Nothing Dim currentObject As ISingleDbObject = Nothing Dim f As ISqlFormatter = Connection.SqlFormatter Dim uidObject As String colObjects = Connection.CreateCol("ADSAccountInADSGroupTotal") colObjects.Prototype.WhereClause = f.Paging("select uid_ADSAccount from ADSAccountInADSGroupTotal Where UID_ADSGroup = '" & Current_UID_ADSGroup & "'","UID_ADSAccount",1,10) colObjects.Prototype("UID_ADSAccount").IsDisplayItem = True colObjects.Load() 
 And have been getting this error when doing a Test Script: 
 Exception has been thrown by the target of an invocation. Loading list of 'Active Directory user accounts: assignments to groups' objects failed. Error during execution of statement: select UID_ADSGroup, UID_ADSAccount from ADSAccountInADSGroupTotal where (select * from (select *, row_number() over(order by UID_ADSAccount) as XVIRowNumber from (select uid_ADSAccount from ADSAccountInADSGroupTotal Where UID_ADSGroup = '416fdf4a-f3e5-4859-8c29-c48f940b48d9') as x) as y where XVIRowNumber between 1 and 10 order by XVIRowNumber) order by UID_ADSAccount, UID_ADSGroup Database error 1033: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. 
 
 I am using the paging method for the first time and couldn't find any doc for it. 
 This is my attempt to get a batch of uid_ADSAccounts for further processing. 
 If you know other ways to perform the query I am open to suggestions too? 
 Any help is appreciated. 
 Thank You, Todd

Markus Weiss-Ehlers · Accepted Answer

You cannot use the paging in the where clause. But you can do a manual select top 10. 
 Sample code 
 Public Sub CCC_Top10Test(uidGroup As String)
 Dim colObjects As IColDbObject = Nothing
 Dim currentObject As ISingleDbObject = Nothing
 Dim f As ISqlFormatter = Connection.SqlFormatter

 Dim uidObject As String

 colObjects = Connection.CreateCol("ADSAccountInADSGroupTotal")

 colObjects.Prototype.WhereClause = String.Concat("UID_ADSAccount in (Select Top 10 UID_ADSAccount From ADSAccountInADSGroupTotal Where ",
f.Comparison("UID_ADSGroup", uidGroup, ValType.String, CompareOperator.Equal, FormatterOptions.None), ")")

 colObjects.Prototype("UID_ADSAccount").IsDisplayItem = True
 colObjects.Load()

 For Each colElement As IColElem In colObjects
 uidObject = colElement.GetValue("UID_ADSAccount").String
 ' do something for this UID_ADSAccount 
 Next
End Sub

ToddFendt · Answer

Hi Markus, Thank you for the clarifying the paging. It is enlightening. I will need to change the query somehow as the whereclause in clause produces a list of UID_ADSAccount that then drives the main select to produce a record for all groups for that uid_ADSAccount. I am working on that. I also found the documentation on the classes used here in \SDK\Documentation, for anyone interested. They are CHM files. Thanks again Markus for the help!

Endpoint Privilege Management

Query in script library to get a list of users getting an error