asked on

Change from GC query to LDAP query in VB script

We have a VB script that was written years ago that creates some reports based on group membership. This has worked fine until this quarter. When I run the report it only pulls info from Universal groups and not the Global groups. The difference is that since the last time we ran the script, we have migrated from W2K3 DC's to W2K8 DC's - and my guess is that the GC changed. Here is the offending line of code:

objCommand.CommandText = "<GC://dc=xyz,dc=com>;(&(objectCategory=group)(managedBy=" & strManagerDN & "));name,info,distinguishedName,description,member;subtree"
Set objRecordSet = objCommand.Execute

My guess is that if I pull this data using an LDAP query instead of a GC query it will work properly. I am not a scripting expert, nor do I pretend to be. I need help. Any ideas?

Hundet

ASKER CERTIFIED SOLUTION

rlandquist

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hundet

ASKER

Here is the rest of the script:

******************

On Error Resume Next

set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
objExcel.Visible = True
set objWorkbook = objExcel.Workbooks.Open("\\file01\departments\is\audit info\Q1-2011\All AD Group Managers.xls")
set objWorksheet = objWorkbook.Worksheets("AD Group Managers")

ADS_SCOPE_SUBTREE = 2
xlHAlignLeft = -4131
xlLandscape = 2
ADS_UF_ACCOUNTDISABLE = 2

set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 10000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Size Limit")= 10000 '10000 records to return in recordset
objCommand.Properties("Time Limit")= 3600 'domain controller will run the search for a max of 3600 seconds
objCommand.Properties("Timeout")= 3600 'client waits 3600 seconds before terminating a search request

I=1
Do Until objWorksheet.cells(I,1).value = ""

strManagerDN = objWorksheet.cells(I,1).value
arrManagerDN = split( strManagerDN , "," )
strManager = replace( arrManagerDN(0) , "CN=" , "" )
strDept = replace( arrManagerDN(1) , "OU=" , "" )

Set objGroupMgr = GetObject("LDAP://" & strManagerDN )
strMgrTitle = objGroupMgr.Title

If InStr( strDept , "NetAdmin" ) Then
strDept = "IT"
End If

set objWorkbookNew = objExcel.Workbooks.add
set objWorkSheetNew = objWorkbookNew.Worksheets("Sheet1")
objWorkSheetNew.Name = "Mar2011-" & strManager
objWorkbookNew.SaveAs("\\file01\departments\is\audit info\Q1-2011\" & strManager & " - 1st Quarter Group Report.xls")
objWorkSheetNew.PageSetup.Orientation = xlLandscape

set objRangeforColumnWidth = objExcel.Range("A1")
objRangeforColumnWidth.ColumnWidth = "15.00"
set objRangeforColumnWidth = objExcel.Range("B1")
objRangeforColumnWidth.ColumnWidth = "15.00"
set objRangeforColumnWidth = objExcel.Range("C1")
objRangeforColumnWidth.ColumnWidth = "10.00"
set objRangeforColumnWidth = objExcel.Range("D1")
objRangeforColumnWidth.ColumnWidth = "25.00"
set objRangeforColumnWidth = objExcel.Range("E1")
objRangeforColumnWidth.ColumnWidth = "50.00"

objCommand.CommandText = "<GC://dc=xyz,dc=com>;(&(objectCategory=group)(managedBy=" & strManagerDN & "));name,info,distinguishedName,description,member;subtree"
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
x=1

While Not objRecordSet.EOF

objWorkSheetNew.cells(x,1).value = "Group Name: " & objRecordSet.fields("name")
objWorkSheetNew.cells(x,1).font.bold = true
x=x+1
arrGroupDescriptions = objRecordSet.Fields("description")
If IsArray(objRecordSet.Fields("description")) Then
For Each item in arrGroupDescriptions
objWorkSheetNew.cells(x,1).value = "Group Description: " & item
objWorkSheetNew.cells(x,1).font.bold = true
Next
End If
x=x+1

strNotes = objRecordSet.fields("info")
strNotes = UCase(strNotes)

If InStr(strNotes,"INTERNAL") Then
strGroupClasification = "Classification: [ ] Restricted [ ] Confidential [ X ]Internal [ ] Public"
ElseIf InStr(strNotes,"CONFIDENTIAL") Then
strGroupClasification = "Classification: [ ] Restricted [ X ] Confidential [ ]Internal [ ] Public"
ElseIf InStr(strNotes,"RESTRICTED") Then
strGroupClasification = "Classification: [ X ] Restricted [ ] Confidential [ ]Internal [ ] Public"
ElseIf InStr(strNotes,"PUBLIC") Then
strGroupClasification = "Classification: [ ] Restricted [ ] Confidential [ ]Internal [ X ] Public"
Else
strGroupClasification = "Classification: [ X ] Restricted [ ] Confidential [ ]Internal [ ] Public"
End If

objWorkSheetNew.cells(x,1).value = strGroupClasification
objWorkSheetNew.cells(x,1).font.bold = true
x=x+1

objWorkSheetNew.cells(x,1).value = "Display Name"
objWorkSheetNew.cells(x,1).font.bold = true
objWorkSheetNew.cells(x,2).value = "User ID"
objWorkSheetNew.cells(x,2).font.bold = true
objWorkSheetNew.cells(x,3).value = "Dept"
objWorkSheetNew.cells(x,3).font.bold = true
objWorkSheetNew.cells(x,4).value = "Title"
objWorkSheetNew.cells(x,4).font.bold = true

x=x+1
strupperrow = x
arrGroupMembers = objRecordSet.Fields("member")
If IsArray(objRecordSet.Fields("member")) Then
For Each strGroupMemberDN in arrGroupMembers

set objMember = GetObject("LDAP://" & strGroupMemberDN)
objWorkSheetNew.cells(x,1).value = objMember.DisplayName
objWorkSheetNew.cells(x,2).value = objMember.samAccountName
objWorkSheetNew.cells(x,3).value = objMember.Department
objWorkSheetNew.cells(x,4).value = objMember.Title
x=x+1
Next
End If
strlowerrow = (x-1)
objWorkSheetNew.cells(x,1).value = ""
x=x+1

set objRangeMembers = objExcel.Range( "A" & strupperrow , "E" & strlowerrow )
set objRangeSort = objExcel.Range( "A" & strupperrow )
objRangeMembers.Sort(objRangeSort)

objRecordSet.MoveNext

WEnd

objWorkSheetNew.PageSetup.CenterHeader = strDept & " - " & strManager
objWorkSheetNew.PageSetup.CenterFooter = "XYZ,Inc. Confidential"
objWorkSheetNew.PageSetup.LeftFooter = "&D"
objWorkSheetNew.PageSetup.RightFooter = "Page &P of &N"

x=x+2
objWorkSheetNew.cells(x,1).value = "Prepared by: John Doe - IT"
objWorkSheetNew.cells(x,1).font.bold = true
objWorkSheetNew.cells(x+1,1).value = "Date: " & Date
objWorkSheetNew.cells(x+1,1).font.bold = true
objWorkSheetNew.cells(x+2,1).value = "Signature: "
objWorkSheetNew.cells(x+2,1).font.bold = true

objWorkSheetNew.cells(x+5,1).value = "Approved by: " & strManager & " - " & strMgrTitle
objWorkSheetNew.cells(x+5,1).font.bold = true
objWorkSheetNew.cells(x+6,1).value = "Date: "
objWorkSheetNew.cells(x+6,1).font.bold = true
objWorkSheetNew.cells(x+7,1).value = "Signature: "
objWorkSheetNew.cells(x+7,1).font.bold = true

Set objRange = objExcel.Range("A" & x , "E" & (x+2) )
objRange.Interior.ColorIndex = 15
Set objRange2 = objExcel.Range("A" & (x+5) , "E" & (x+7) )
objRange2.Interior.ColorIndex = 15

objWorkSheetNew.UsedRange.HorizontalAlignment = xlHAlignLeft
objWorkSheetNew.UsedRange.Font.Size = 8
objWorkbookNew.save

I=I+1
Loop

Wscript.echo "Completed!"

Wscript.quit

hundet

ASKER

Any other thoughts - anyone???

rlandquist

objCommand.CommandText = "<LDAP://dc=xyz,dc=com>;(&(objectCategory=group)(managedBy=" & strManagerDN & "));name,info,distinguishedName,description,member;subtree"

hundet

ASKER

rlandquist - thanks, but I had tried that. When I replace the GC with LDAP, it doesn't even return any of the group members (global or universal). When I go back to GC, at least it returns the Universal group members.

Am I missing an LDAP bind command or something?

SOLUTION

prashanthd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hundet

ASKER

Have not been able to get this script fixed. I've given up and sent this over to a developer at our corporate headquarters.

hundet

ASKER

I never found an answer to this question.