vba - Programming to query 100000 users in AD -


i working on project query ad, have script that, script failing after 1000 users, while users querying around 150.000 users.

here code:

below script , can tell me on line 

sub usersynchquery(byref res apiresult, byref orespds apidataset, byref sldapserver string, byref sldapport string, byref sldapbase string, byref susername string, byref spassword string, byref sslpprimary string, byref sslpsecondary string, byref sextension string, byref sconfiggroup string, byref sfilter string )  trace( "called usersynchquery entered" )  dim odsp object dim odsrs object  on error resume next set odsp = createobject("adodb.connection")  odsp.provider = "adsdsoobject"   odsp.open( "ads provider", susername, demung( spassword ))    if err.number <> 0         trace("error: failed instantiate ado object. " & err.number & " " & err.description)         res.code = "failed"         res.reason = "failed instantiate ado object"         exit sub end if  on error goto 0      dim sroot      'holds root of ldap object     sroot = "ldap://" & sldapserver & ":" & sldapport & "/" & sldapbase  dim squery string dim sselect string  sselect = ads_column_dn & "," & ads_column_username & "," & ads_column_lastname & "," & ads_column_firstname & "," & ads_column_email & ","  if len(sslpprimary) > 0     sselect = sselect & sslpprimary & "," end if if len(sslpsecondary) > 0     sselect = sselect & sslpsecondary & "," end if if len(sextension) > 0     sselect = sselect & sextension & "," end if if len(sconfiggroup) > 0     sselect = sselect & sconfiggroup & "," end if  sselect = sselect & ads_column_memberof  squery = "select " & sselect & " '" & sroot & "' " & sfilter  trace( "query string: " & squery )  on error resume next set odsrs = odsp.execute(squery)    if err.number <> 0         trace("error: query failed. " & err.number & " " & err.description)         res.code = "failed"         res.reason = "query failed"         exit sub end if  on error goto 0  '// before can fill in dataset, must initialize  '// number of columns orespds.initialize(msg_user_query_resp_num_cols)       dim nrow     dim srsusername     dim srslastname     dim srsfirstname     dim srsemail     dim srsdn     dim srsslpprimary     dim srsslpsecondary     dim srsextension     dim srsconfiggroup      nrow = 0      until odsrs.eof      srsusername = odsrs.fields(ads_column_username).value     srslastname = odsrs.fields(ads_column_lastname).value     srsfirstname = odsrs.fields(ads_column_firstname).value     srsemail = odsrs.fields(ads_column_email).value     srsdn = odsrs.fields(ads_column_dn).value       trace("----------- found user -----------")     trace("username: " & srsusername)     trace("last name: " & srslastname)     trace("first name: " & srsfirstname)     trace("email: " & srsemail)     trace("dn: " & srsdn)     if len(sslpprimary) > 0         srsslpprimary = odsrs.fields(sslpprimary).value         trace("sslpprimary: " & srsslpprimary)     end if     if len(sslpsecondary) > 0         srsslpsecondary = odsrs.fields(sslpsecondary).value         trace("sslpsecondary: " & srsslpsecondary)     end if     if len(sextension) > 0         srsextension = odsrs.fields(sextension).value         trace("sextension: " & srsextension)     end if     if len(sconfiggroup) > 0         srsconfiggroup = odsrs.fields(sconfiggroup).value         trace("sconfiggroup: " & srsconfiggroup)     end if      if( isnull( srsusernamme ) or isnull( srslastname ) or isnull( srsfirstname ) or isnull( srsdn ) )         trace( "error: ignoring user due missing information" )     else         'we need build list of groups needs         'to include indirect group membership         'be result of assigning group member of         'another group.          dim arrgroups         dim dictgroupnamesbydn          set dictgroupnamesbydn = createobject("scripting.dictionary")          arrgroups = odsrs.fields(ads_column_memberof).value          if isnull( arrgroups )              trace("--->no groups found")         else             processgroupmembership( dictgroupnamesbydn, arrgroups )                      end if            'now assing roles user based on         'the nested groups retrieved.          dim sapplications string         sapplications = ""          'we use opportunity build         'workgroup membership up.          dim sworkgroup string         sworkgroups = ""          dim scn string         dim sdn string          dim keys         keys = dictgroupnamesbydn.keys          each key in keys             sdn = key             scn = dictgroupnamesbydn.item(key)              sworkgroups = sworkgroups & scn & ";"              if scn = cim_agent_application_group_name                 sapplications = sapplications & "agent;"             end if              if scn = cim_resman_application_group_name                 sapplications = sapplications & "resman;"             end if              if scn = cim_configman_application_group_name                 sapplications = sapplications & "conman;"             end if              if scn = cim_ivault_application_group_name                 sapplications = sapplications & "ivault;"             end if              if scn = cim_decman_application_group_name                 sapplications = sapplications & "dmweb;"             end if              if scn = cim_qim_application_group_name                 sapplications = sapplications & "qim;"             end if              if scn = cim_sysman_application_group_name                 sapplications = sapplications & "sysman;"             end if         next           trace("roles: " & sapplications)         trace("workgroups: " & sworkgroups)           orespds.addrow         orespds.setfield(nrow,msg_user_query_resp_username, srsusername)         orespds.setfield(nrow,msg_user_query_resp_lastname, srslastname)         orespds.setfield(nrow,msg_user_query_resp_firstname, srsfirstname)          if not isnull(srsemail)             orespds.setfield(nrow,msg_user_query_resp_email, srsemail)         end if          orespds.setfield(nrow,msg_user_query_resp_dn, srsdn)         orespds.setfield(nrow,msg_user_query_resp_applications, sapplications)         orespds.setfield(nrow,msg_user_query_resp_workgroups, sworkgroups)          if len(sslpprimary) > 0             if isnull( srsslpprimary )                 trace("warning: " & sslpprimary & " value not populated")             else                 orespds.setfield(nrow,msg_user_query_resp_slpprimary, srsslpprimary)             end if         end if          if len(sslpsecondary) > 0             if isnull( srsslpsecondary )                 trace("warning: " & sslpsecondary & " value not populated")             else                 orespds.setfield(nrow,msg_user_query_resp_slpsecondary, srsslpsecondary)             end if         end if          if len(sextension) > 0             if isnull( srsextension )                 trace("warning: " & sextension & " value not populated")             else                 orespds.setfield(nrow,msg_user_query_resp_extension, srsextension)             end if         end if          if len(sconfiggroup) > 0             if isnull( srsconfiggroup )                 trace("warning: " & sconfiggroup & " value not populated")             else                 orespds.setfield(nrow,msg_user_query_resp_configgroups, srsconfiggroup)             end if         end if          nrow = nrow + 1     end if      odsrs.movenext     loop       'clean on error resume next      odsp = nothing     odsrs = nothing      on error goto 0 end sub

the variable of ldap server, ldap port, username, password , search base both user , group entered via application , working far.

error have once reaches 1000 users:

the size limit request exceeded.

if removed line odsrs.movenext give "overflow" error.

i did reading , this closest come with.

the ldap administrative limits balance active directory operational capabilities , performance. these limits prevent specific operations adversely affecting performance of server. limits make server resilient denial of service attacks.

as part of limits there maxpagesize setting controls number of records can returned ldap query. default number 1,000 records , if have more that, error “the size limit request exceeded”.

to workaround, set page size option, instructs domain controller process number of records , return them client before continuing search.

objcommand.properties("page size") = 1000

where objcommand name of command object.

see complete example here.


Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more