modify code

[n3tw0rkadm1n1strat0r]

I have a script that reads from a file, and sorts the data out into a text file. Here is the format it reads from:

CN=Lastname\, Firstname,OU=Users,OU=sports group,DC=domain,DC=name, DC=com

This code will read the data and put it into a text file:

if Mid(strData,1,2)=" C" then
strTemp=Mid(strData, 5, InStr(1, strData,",",1)-5)

In the text file it looks like this:

Lastname\

How would I modify the code to make it "Lastname, Firstname"?

Thanks to anyone who can help...

 

[markdmac]

You clearly have the distinguished name of your users, so why not bind to the user object and extract the last name and first name and write that to your text file?

userDN = "CN=Lastname\, Firstname,OU=Users,OU=sports group,DC=domain,DC=name, DC=com"

Set objUser = GetObject("LDAP://" & userDN)
objUser.GetInfo
LastName = objUser.sn
FirstName = objUser.givenName

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[n3tw0rkadm1n1strat0r]

Because it's using "ldifde" to export the users...it exports using "ldifde" and then uses modify.vbs to sort out the names in the "ldf" file that "ldifde" generates. Here is the full modifying code:

Set objFileSystem = CreateObject("Scripting.fileSystemObject")
Set objInputFile = objFileSystem.OpenTextFile(strOutputFile, OPEN_FILE_FOR_READING)

' read everything in an array
inputData = Split(objInputFile.ReadAll, vbNewline)

For each strData In inputData
       if Mid(strData, 1,2)="me" and len(strdata)>10  then
       strTemp=Mid(strData, 12, InStr(1, strData,",",1)-12)

WScript.Echo strTemp

end if  

    if Mid(strData,1,2)=" C" then

strTemp=Mid(strData, 5, InStr(1, strData,",",1)-5)

WScript.Echo strTemp  
end if

Next

objInputFile.Close
Set objFileSystem = Nothing

WScript.Quit(0)

 

[n3tw0rkadm1n1strat0r]

Sorry here is the top half of the code that I didn't paste in right:

Dim objFileSystem, objInputFile
Dim strOutputFile, inputData, strData, strTemp

Const OPEN_FILE_FOR_READING = 1

' generate a filename base on the script name, here readfile.in
strOutputFile = "groups.ldf"

 

[markdmac]

I don't see your code writng back anywhere to the file. Is that intentional?

As I explained before you can just bind to the user object.

Dim objFileSystem, objInputFile
Dim strOutputFile, inputData, strData, strTemp

Const OPEN_FILE_FOR_READING = 1

' generate a filename base on the script name, here readfile.in
strOutputFile = "groups.ldf"

Set objFileSystem = CreateObject("Scripting.fileSystemObject")
Set objInputFile = objFileSystem.OpenTextFile(strOutputFile, OPEN_FILE_FOR_READING)

' read everything in an array
inputData = Split(objInputFile.ReadAll, vbNewline)

For each strData In inputData
	Set objUser = GetObject("LDAP://" & strData)
	objUser.GetInfo
	LastName = objUser.sn
	FirstName = objUser.givenName 
	strTemp = LastName & ", " & FirstName
	WScript.Echo strTemp
Next

objInputFile.Close
Set objFileSystem = Nothing

WScript.Quit(0)

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[n3tw0rkadm1n1strat0r]

It runs this after the ldife command which exports it:

objShell.Run("%comspec% /c cscript  Modify.vbs > ""Users.txt"""), 0, True

Do you know how to change it to lastname, firtname?

 

[n3tw0rkadm1n1strat0r]

I tried using what you gave me and I got errors trying to open the excel file...

 

[markdmac]

What excel file?

I have to ask, are you at all familiar with vbscritp? Since you are using a script to start with I assumed you would know what to do with the info I gave you.

In the above example I have given you strTemp in the format of LastName, FirstName. That information is just being echoed out to the screen. You would need to write it to a file for whatever your purpose is.

Please explain what you are actually trying to do, working with bits and pieces is getting us nowhere.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[n3tw0rkadm1n1strat0r]

Ok...I am familiar with vbscript. What I am doing is exporting members of a group from AD. I am using "ldifde.exe" to export the users to a .ldf file, because thats that only way ldifde can do it that I have found. It then uses the modify script to open the .ldf files, sort out all the crap from the only way ldifde can export, and then makes it look nicer. But it only sorts the last name, and I'm trying to make it "lastname, firstname" in the text file or excel file...it doesn't matter. The reason why I pasted this:

if Mid(strData,1,2)=" C" then
strTemp=Mid(strData, 5, InStr(1, strData,",",1)-5)

..in the first post was because that's what I thought sorted out the data, but my vbscript knowledge does not go that far with this...

I believe all I need to do is fix that one part...

Is that a little better?

 

[n3tw0rkadm1n1strat0r]

I know what you talking about with objUser...but will it recognize that through the .ldf file?

 

[markdmac]

I would eliminate the ldf file altogether.

'==========================================================================
'
' NAME: EnumGroupMembersToExcelWithSort.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: [URL unfurl="true"]http://www.TheSpidersParlor.com[/URL]
' COPYRIGHT (c) 2006 All Rights Reserved
' DATE  : 7/24/2006
'
' COMMENT: 
'
'
'    THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
'    ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED To
'    THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
'    PARTICULAR PURPOSE.
'
'    IN NO EVENT SHALL THE SPIDER'S PARLOR AND/OR ITS RESPECTIVE SUPPLIERS 
'    BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
'    DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
'    WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
'    ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
'    OF THIS CODE OR INFORMATION.
'
'==========================================================================



'On Error Resume Next
Dim x, xRow, xTab
Dim oRootDSE, oConnection, objCommand, ldstring, oRecordset, GCounter
Const xlAscending = 1
Const xlYes = 1
Const xlDescending = 2


Set x = CreateObject("excel.application")
x.Visible = true
Set newBook = x.Workbooks.Add
newBook.Worksheets(1).Activate


Set oRootDSE = GetObject("LDAP://rootDSE")
Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = oConnection

ldstring = "<LDAP://" & oRootDSE.get("defaultNamingContext") & ">;" 

objCommand.CommandText=ldstring & "(objectClass=group);name,distinguishedName"

Set oRecordSet = objCommand.Execute()
GCounter = 1
SheetCounter = 0
Do While Not oRecordSet.EOF
    'Account for the original 3 worksheets
    Do While SheetCounter < 3
    SheetCounter = SheetCounter + 1
    oRecordSet.MoveNext
    Loop
	'Create the necessary number of additional tabs in Excel
	newBook.Worksheets.Add
	oRecordSet.MoveNext
Loop



Set oRecordSet = objCommand.Execute()

Do While Not oRecordSet.EOF
	GroupName = oRecordSet.Fields("name")
	GroupPath = "LDAP://" & oRecordSet.Fields("distinguishedName")
	'Now call the Function to get the users.  Pass it the username	
	newBook.Worksheets(GCounter).Activate
	Wscript.Sleep 300
    
    newBook.Worksheets(GCounter).Cells(1,1).value="Last Name"
    newBook.Worksheets(GCounter).Cells(1,2).value="First Name"
    
    If Len(GroupName)> 31 Then 
	GroupName = Left(Trim(GroupName),30)
    End If
    newBook.Worksheets(GCounter).Name = GroupName
    newBook.Worksheets(GCounter).Cells(1,1).Font.Bold = True
    newBook.Worksheets(GCounter).Cells(1,2).Font.Bold = True
    
    xRow = 2
    Set objGroup = GetObject(GroupPath)
    For each objMember in objGroup.Members
    newBook.Worksheets(GCounter).Cells(xRow,1).value=objMember.sn
    newBook.Worksheets(GCounter).Cells(xRow,2).value=objMember.givenName
    newBook.Worksheets(GCounter).Columns("A:B").EntireColumn.AutoFit
    xRow = xRow + 1
    Next
    
    Wscript.Sleep 100
    'On Error Goto 0
    'Sort the data
    
    
	Set objRange = newBook.Worksheets(GCounter).UsedRange
	Set objRange2 = newBook.Worksheets(GCounter).Range("A1")
	Set objRange3 = newBook.Worksheets(GCounter).Range("B1")
	objRange.Sort objRange2,xlAscending,objRange3,,xlAscending,,,xlYes
	'expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header,
	    
	GCounter= GCounter + 1
	oRecordSet.MoveNext
Loop

The above will create an Excel spreadsheet and make a tab for each group. It will list the gorup members and sort them.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[n3tw0rkadm1n1strat0r]

Wow thank you! So do I only have to fill in the name of my group here:

If Len(GroupName)> 31 Then

or just here:

Set objGroup = GetObject(GroupPath)

 

[markdmac]

Neither, just run the script. This will enumerate all your groups and give you an Excel spreadsheet with each group on a seperate tab. It will list the members and sort them.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[n3tw0rkadm1n1strat0r]

Ahh ok...how would I specify 1 group? ...we have A LOT of groups in our directory...

 

[markdmac]

That would be a significanlty different script since this one is designed to enumerate all groups.

Basic code to do it is this:

On Error Resume Next
 
Set objGroup = GetObject _
  ("LDAP://cn=Scientists,ou=R&D,dc=NA,dc=fabrikam,dc=com")
objGroup.GetInfo
 
arrMemberOf = objGroup.GetEx("member")
 
WScript.Echo "Members:"
For Each strMember in arrMemberOf
    WScript.echo strMember
Next

You could combine the code from my original post with the above.

Have you tried running the script I posted? Even if you do have a lot of groups, it should only take a minute to run and you will then have documentation for all groups.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.