Help desperately needed.
in [VbScript]
|
Prev: Populating ComboBox
Next: Vbscript to Search Excel
From: Gunna on 26 Jan 2009 06:08 Thats great thanks but its only half what i need. How do i output the info to a csv file? "Richard Mueller [MVP]" wrote: > > "Gunna" <Gunna(a)discussions.microsoft.com> wrote in message > news:00BC02EA-F8D9-4509-9274-0CAD57AD44A5(a)microsoft.com... > >I need a simple script that will export from a certain OU (including sub > >OUs) > > the sameaccountname and displayname values for all users into a CSV file. > > > > Sounds simple enought right, not for my with no vbscript skills. You'd > > think Google would help me but Google hates me. > > > > Any help would be great. > > You can use ADO in a VBScript program for this. See this link for details: > > http://www.rlmueller.net/ADOSearchTips.htm > > For example: > ========== > Option Explicit > Dim adoCommand, adoConnection, strBase, strFilter, strAttributes > Dim strQuery, adoRecordset, strName, strDisplay > > ' Setup ADO objects. > Set adoCommand = CreateObject("ADODB.Command") > Set adoConnection = CreateObject("ADODB.Connection") > adoConnection.Provider = "ADsDSOObject" > adoConnection.Open "Active Directory Provider" > adoCommand.ActiveConnection = adoConnection > > ' Specify the base of the query. > ' This is the full AdsPath of the OU. > strBase = "<LDAP://ou=West,dc=MyDomain,dc=com>" > > ' Filter on user objects. > strFilter = "(&(objectCategory=person)(objectClass=user))" > > ' Comma delimited list of attribute values to retrieve. > strAttributes = "sAMAccountName,displayName" > > ' Construct the LDAP syntax query. > strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" > adoCommand.CommandText = strQuery > adoCommand.Properties("Page Size") = 100 > adoCommand.Properties("Timeout") = 30 > adoCommand.Properties("Cache Results") = False > > ' Run the query. > Set adoRecordset = adoCommand.Execute > > ' Enumerate the resulting recordset. > Do Until adoRecordset.EOF > ' Retrieve values and display. > strName = adoRecordset.Fields("sAMAccountName").Value > strDisplay = adoRecordset.Fields("displayName").value > Wscript.Echo """" & strName & """,""" & strDisplay & """" > ' Move to the next record in the recordset. > adoRecordset.MoveNext > Loop > > ' Clean up. > adoRecordset.Close > adoConnection.Close > ========= > I quoted the values in case they have embedded commas. The double quote > character must be doubled in a quoted string. The string """" resolves to a > single double quote character. The string """,""" resolves to ",". > > As with most administrative scripts, this should be run at a command prompt > using cscript. The output should be redirected to a text file, the csv file. > For example, if the VBScript code is saved in file GetUsers.vbs, you could > use the following command to create report.csv: > > cscript //nologo GetUsers.vbs > report.csv > > If GetUsers.vbs is not in the current directory, include the full path to > the file. > > You can also use Joe Richards' free adfind utility for this. See this link: > > http://www.joeware.net/freetools/tools/adfind/index.htm > > I think the command would be similar to (one line): > > adfind -b "ou=West,dc=MyDomain,dc=com" -f > "(&(objectCategory=person)(objectClass=user))" -nodn -csv sAMAccountName > displayName > > -- > Richard Mueller > MVP Directory Services > Hilltop Lab - http://www.rlmueller.net > -- > > >
From: Monitor on 26 Jan 2009 06:31 Richard's detailed reply gave you 98% of what you need. You can easily put in the remaining 2% by looking at the WriteLine method of the File System Object. There is an example in the help file "script56.chm" which you can download from the Microsoft site. Unless, of course, you prefer to use this newsgroup as a free pizza delivery service, with the respondents doing all the work for out out of the goodness of their hearts. "Gunna" <Gunna(a)discussions.microsoft.com> wrote in message news:08E35BCA-4ACF-48C4-9401-934F6E858588(a)microsoft.com... > Thats great thanks but its only half what i need. How do i output the info > to a csv file? > > "Richard Mueller [MVP]" wrote: > > > > > "Gunna" <Gunna(a)discussions.microsoft.com> wrote in message > > news:00BC02EA-F8D9-4509-9274-0CAD57AD44A5(a)microsoft.com... > > >I need a simple script that will export from a certain OU (including sub > > >OUs) > > > the sameaccountname and displayname values for all users into a CSV file. > > > > > > Sounds simple enought right, not for my with no vbscript skills. You'd > > > think Google would help me but Google hates me. > > > > > > Any help would be great. > > > > You can use ADO in a VBScript program for this. See this link for details: > > > > http://www.rlmueller.net/ADOSearchTips.htm > > > > For example: > > ========== > > Option Explicit > > Dim adoCommand, adoConnection, strBase, strFilter, strAttributes > > Dim strQuery, adoRecordset, strName, strDisplay > > > > ' Setup ADO objects. > > Set adoCommand = CreateObject("ADODB.Command") > > Set adoConnection = CreateObject("ADODB.Connection") > > adoConnection.Provider = "ADsDSOObject" > > adoConnection.Open "Active Directory Provider" > > adoCommand.ActiveConnection = adoConnection > > > > ' Specify the base of the query. > > ' This is the full AdsPath of the OU. > > strBase = "<LDAP://ou=West,dc=MyDomain,dc=com>" > > > > ' Filter on user objects. > > strFilter = "(&(objectCategory=person)(objectClass=user))" > > > > ' Comma delimited list of attribute values to retrieve. > > strAttributes = "sAMAccountName,displayName" > > > > ' Construct the LDAP syntax query. > > strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" > > adoCommand.CommandText = strQuery > > adoCommand.Properties("Page Size") = 100 > > adoCommand.Properties("Timeout") = 30 > > adoCommand.Properties("Cache Results") = False > > > > ' Run the query. > > Set adoRecordset = adoCommand.Execute > > > > ' Enumerate the resulting recordset. > > Do Until adoRecordset.EOF > > ' Retrieve values and display. > > strName = adoRecordset.Fields("sAMAccountName").Value > > strDisplay = adoRecordset.Fields("displayName").value > > Wscript.Echo """" & strName & """,""" & strDisplay & """" > > ' Move to the next record in the recordset. > > adoRecordset.MoveNext > > Loop > > > > ' Clean up. > > adoRecordset.Close > > adoConnection.Close > > ========= > > I quoted the values in case they have embedded commas. The double quote > > character must be doubled in a quoted string. The string """" resolves to a > > single double quote character. The string """,""" resolves to ",". > > > > As with most administrative scripts, this should be run at a command prompt > > using cscript. The output should be redirected to a text file, the csv file. > > For example, if the VBScript code is saved in file GetUsers.vbs, you could > > use the following command to create report.csv: > > > > cscript //nologo GetUsers.vbs > report.csv > > > > If GetUsers.vbs is not in the current directory, include the full path to > > the file. > > > > You can also use Joe Richards' free adfind utility for this. See this link: > > > > http://www.joeware.net/freetools/tools/adfind/index.htm > > > > I think the command would be similar to (one line): > > > > adfind -b "ou=West,dc=MyDomain,dc=com" -f > > "(&(objectCategory=person)(objectClass=user))" -nodn -csv sAMAccountName > > displayName > > > > -- > > Richard Mueller > > MVP Directory Services > > Hilltop Lab - http://www.rlmueller.net > > -- > > > > > >
From: Richard Mueller [MVP] on 26 Jan 2009 09:15
Look at my first post in this thread, where I describe how you can redirect the output to a csv file. Alternatively, you can use the FileSystemObject to write the lines to a file, but that involves adding several lines to the program. -- Richard Mueller MVP Directory Services Hilltop Lab - http://www.rlmueller.net -- "Gunna" <Gunna(a)discussions.microsoft.com> wrote in message news:08E35BCA-4ACF-48C4-9401-934F6E858588(a)microsoft.com... > Thats great thanks but its only half what i need. How do i output the > info > to a csv file? > > "Richard Mueller [MVP]" wrote: > >> >> "Gunna" <Gunna(a)discussions.microsoft.com> wrote in message >> news:00BC02EA-F8D9-4509-9274-0CAD57AD44A5(a)microsoft.com... >> >I need a simple script that will export from a certain OU (including sub >> >OUs) >> > the sameaccountname and displayname values for all users into a CSV >> > file. >> > >> > Sounds simple enought right, not for my with no vbscript skills. You'd >> > think Google would help me but Google hates me. >> > >> > Any help would be great. >> >> You can use ADO in a VBScript program for this. See this link for >> details: >> >> http://www.rlmueller.net/ADOSearchTips.htm >> >> For example: >> ========== >> Option Explicit >> Dim adoCommand, adoConnection, strBase, strFilter, strAttributes >> Dim strQuery, adoRecordset, strName, strDisplay >> >> ' Setup ADO objects. >> Set adoCommand = CreateObject("ADODB.Command") >> Set adoConnection = CreateObject("ADODB.Connection") >> adoConnection.Provider = "ADsDSOObject" >> adoConnection.Open "Active Directory Provider" >> adoCommand.ActiveConnection = adoConnection >> >> ' Specify the base of the query. >> ' This is the full AdsPath of the OU. >> strBase = "<LDAP://ou=West,dc=MyDomain,dc=com>" >> >> ' Filter on user objects. >> strFilter = "(&(objectCategory=person)(objectClass=user))" >> >> ' Comma delimited list of attribute values to retrieve. >> strAttributes = "sAMAccountName,displayName" >> >> ' Construct the LDAP syntax query. >> strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" >> adoCommand.CommandText = strQuery >> adoCommand.Properties("Page Size") = 100 >> adoCommand.Properties("Timeout") = 30 >> adoCommand.Properties("Cache Results") = False >> >> ' Run the query. >> Set adoRecordset = adoCommand.Execute >> >> ' Enumerate the resulting recordset. >> Do Until adoRecordset.EOF >> ' Retrieve values and display. >> strName = adoRecordset.Fields("sAMAccountName").Value >> strDisplay = adoRecordset.Fields("displayName").value >> Wscript.Echo """" & strName & """,""" & strDisplay & """" >> ' Move to the next record in the recordset. >> adoRecordset.MoveNext >> Loop >> >> ' Clean up. >> adoRecordset.Close >> adoConnection.Close >> ========= >> I quoted the values in case they have embedded commas. The double quote >> character must be doubled in a quoted string. The string """" resolves to >> a >> single double quote character. The string """,""" resolves to ",". >> >> As with most administrative scripts, this should be run at a command >> prompt >> using cscript. The output should be redirected to a text file, the csv >> file. >> For example, if the VBScript code is saved in file GetUsers.vbs, you >> could >> use the following command to create report.csv: >> >> cscript //nologo GetUsers.vbs > report.csv >> >> If GetUsers.vbs is not in the current directory, include the full path to >> the file. >> >> You can also use Joe Richards' free adfind utility for this. See this >> link: >> >> http://www.joeware.net/freetools/tools/adfind/index.htm >> >> I think the command would be similar to (one line): >> >> adfind -b "ou=West,dc=MyDomain,dc=com" -f >> "(&(objectCategory=person)(objectClass=user))" -nodn -csv sAMAccountName >> displayName >> >> -- >> Richard Mueller >> MVP Directory Services >> Hilltop Lab - http://www.rlmueller.net >> -- >> >> >> |