Go Back   TechArena Community > Technical Support > Computer Help > Windows Server > Windows Server Help
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Extract data from AD and save in TXT file.

Windows Server Help


Reply
 
Thread Tools Search this Thread
  #1  
Old 21-07-2007
Tom Ja
 
Posts: n/a
Extract data from AD and save in TXT file.

Can any one help me out. advance thanks.
I am not very much expert in script I know basic script.
I have question about script. Infect I want extract some information from
Active directory and save in TXT file
Question.
I want script which takes IDs from the file (TXT file). Through this script
search this (ID) in Active Directory. If user’s ID exist in AD then save
the users Name ,Last name ,Address, Email address in a file xyz.TXT .if
users NOT exist in AD then print ID and name “ID NOT EXIST.” and go for next
users's ID from the TXT file till end of TXT file.
I hope you will understand my question. I will be waiting to hear from you
in great anxiety
Please help me out in this regards.
Tom

Reply With Quote
  #2  
Old 21-07-2007
Richard Mueller [MVP]
 
Posts: n/a
Re: Extract data from AD and save in TXT file.

Tom Ja wrote:

> Can any one help me out. advance thanks.
> I am not very much expert in script I know basic script.
> I have question about script. Infect I want extract some information from
> Active directory and save in TXT file
> Question.
> I want script which takes IDs from the file (TXT file). Through this
> script
> search this (ID) in Active Directory. If user?s ID exist in AD then save
> the users Name ,Last name ,Address, Email address in a file xyz.TXT .if
> users NOT exist in AD then print ID and name ?ID NOT EXIST.? and go for
> next
> users's ID from the TXT file till end of TXT file.
> I hope you will understand my question. I will be waiting to hear from you
> in great anxiety
> Please help me out in this regards.


The FileSystemObject can be used to read names from a text file, I assume
one name per line. I also assume you have the NT names (pre-Windows 2000
logon names) of the users. If so, you can use the NameTranslate object to
convert the NT names to the Distinguished Names (required to bind to the
objects). Then you can bind to each user object, retrieve any attribute
values desired, and output.

It wouldn't make sense to create one xyz.txt file for each user. By far the
easiest would be to have the script simply echo to the command prompt. You
can redirect the output to a text file. For information on using
NameTranslate, see this link:

http://www.rlmueller.net/NameTranslateFAQ.htm

For documentation on the attributes corresponding to fields in ADUC, see
this link:

http://www.rlmueller.net/UserAttributes.htm

An example VBScript program could be similar to:
===============
Option Explicit

Const ForReading = 1
' Constants for NameTranslate.
Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_NT4 = 3
Const ADS_NAME_TYPE_1779 = 1

' Specify the text file of user names.
strFilePath = "c:\Scripts\UserList.txt"

' Open the file for read access.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)

' Determine DNS domain name from RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")

' Use the NameTranslate object to find the NetBIOS domain name
' from the DNS domain name.
Set objTrans = CreateObject("NameTranslate")
objTrans.Init ADS_NAME_INITTYPE_GC, ""
objTrans.Set ADS_NAME_TYPE_1779, strDNSDomain
strNetBIOSDomain = objTrans.Get(ADS_NAME_TYPE_NT4)
' Remove trailing backslash.
strNetBIOSDomain = Left(strNetBIOSDomain, _
Len(strNetBIOSDomain) - 1)

' Read NT names from text file.
Do Until objFile.AtEndOfStream
strNTName = Trim(objFile.ReadLine)
' Skip blank lines.
If (strNTName <> "") Then
' Use Set method to specify NT name.
' Trap error if name not found.
On Error Resume Next
objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strNTName
If (Err.Number <> 0) Then
On Error GoTo 0
Wscript.Echo "User " & strNTName _
& " not found in Active Directory"
End If
On Error GoTo 0
' Use Get method to retrieve Distinguished Name.
strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)
' Bind to user object in AD.
Set objUser = GetObject("LDAP://" & strUserDN)
' Output values desired. This outputs:
' NT Name;Common Name;first name;last name;street
address;city;state;email
Wscript.Echo objUser.sAMAccountName _
& ";" objUser.Name _
& ";" & objUser.givenName _
& ";" & objUser.sn _
& ";" & objUser.streetAddress _
& ";" & objUser.l _
& ";" & objUser.st _
& ";" & objUser.mail
End If
Loop
objFile.Close
================
You would run this VBScript program at a command prompt with the cscript
host. For example, if the VBScript is saved in the file ListUsers.vbs, you
could run the program and redirect the output to a text file with the
following command:

cscript //nologo ListUsers.vbs > report.txt

This assumes you are in the directory where the file ListUsers.vbs is saved.
Otherwise you must also specify the path. The file report.txt is created in
the same directory. I chose to output one line per user, with the fields
delimited by semicolons (since some values could have embedded commas). This
creates a file that can be read into a spreadsheet program. You can select
other attributes and change the format.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--


Reply With Quote
  #3  
Old 23-07-2007
Tom Ja
 
Posts: n/a
Re: Extract data from AD and save in TXT file.

Thanks for reply and I am very happy and appreciate with prompt response.
I tried this script but it shows me error.
Error is:
“ C:\scripts\listusers.vbs (10, 1) Microsoft VBScript runtime error:
Variable is undefined: ‘strFilePath’ “
I checked variable is defined and file path is also ok in line 10, 1 .File
is existing (UserList.txt) format of file is:
User1
User2
User3
User4
User5
Could you please help me out in this regards. I can not understand this
error as I told you earlier I am not very much expert.
Once again thanks for replying and thanks in advance.
Tom.


"Richard Mueller [MVP]" wrote:

> Tom Ja wrote:
>
> > Can any one help me out. advance thanks.
> > I am not very much expert in script I know basic script.
> > I have question about script. Infect I want extract some information from
> > Active directory and save in TXT file
> > Question.
> > I want script which takes IDs from the file (TXT file). Through this
> > script
> > search this (ID) in Active Directory. If user’s ID exist in AD then save
> > the users Name ,Last name ,Address, Email address in a file xyz.TXT .if
> > users NOT exist in AD then print ID and name “ID NOT EXIST.” and go for
> > next
> > users's ID from the TXT file till end of TXT file.
> > I hope you will understand my question. I will be waiting to hear from you
> > in great anxiety
> > Please help me out in this regards.

>
> The FileSystemObject can be used to read names from a text file, I assume
> one name per line. I also assume you have the NT names (pre-Windows 2000
> logon names) of the users. If so, you can use the NameTranslate object to
> convert the NT names to the Distinguished Names (required to bind to the
> objects). Then you can bind to each user object, retrieve any attribute
> values desired, and output.
>
> It wouldn't make sense to create one xyz.txt file for each user. By far the
> easiest would be to have the script simply echo to the command prompt. You
> can redirect the output to a text file. For information on using
> NameTranslate, see this link:
>
> http://www.rlmueller.net/NameTranslateFAQ.htm
>
> For documentation on the attributes corresponding to fields in ADUC, see
> this link:
>
> http://www.rlmueller.net/UserAttributes.htm
>
> An example VBScript program could be similar to:
> ===============
> Option Explicit
>
> Const ForReading = 1
> ' Constants for NameTranslate.
> Const ADS_NAME_INITTYPE_GC = 3
> Const ADS_NAME_TYPE_NT4 = 3
> Const ADS_NAME_TYPE_1779 = 1
>
> ' Specify the text file of user names.
> strFilePath = "c:\Scripts\UserList.txt"
>
> ' Open the file for read access.
> Set objFSO = CreateObject("Scripting.FileSystemObject")
> Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)
>
> ' Determine DNS domain name from RootDSE object.
> Set objRootDSE = GetObject("LDAP://RootDSE")
> strDNSDomain = objRootDSE.Get("defaultNamingContext")
>
> ' Use the NameTranslate object to find the NetBIOS domain name
> ' from the DNS domain name.
> Set objTrans = CreateObject("NameTranslate")
> objTrans.Init ADS_NAME_INITTYPE_GC, ""
> objTrans.Set ADS_NAME_TYPE_1779, strDNSDomain
> strNetBIOSDomain = objTrans.Get(ADS_NAME_TYPE_NT4)
> ' Remove trailing backslash.
> strNetBIOSDomain = Left(strNetBIOSDomain, _
> Len(strNetBIOSDomain) - 1)
>
> ' Read NT names from text file.
> Do Until objFile.AtEndOfStream
> strNTName = Trim(objFile.ReadLine)
> ' Skip blank lines.
> If (strNTName <> "") Then
> ' Use Set method to specify NT name.
> ' Trap error if name not found.
> On Error Resume Next
> objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" & strNTName
> If (Err.Number <> 0) Then
> On Error GoTo 0
> Wscript.Echo "User " & strNTName _
> & " not found in Active Directory"
> End If
> On Error GoTo 0
> ' Use Get method to retrieve Distinguished Name.
> strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)
> ' Bind to user object in AD.
> Set objUser = GetObject("LDAP://" & strUserDN)
> ' Output values desired. This outputs:
> ' NT Name;Common Name;first name;last name;street
> address;city;state;email
> Wscript.Echo objUser.sAMAccountName _
> & ";" objUser.Name _
> & ";" & objUser.givenName _
> & ";" & objUser.sn _
> & ";" & objUser.streetAddress _
> & ";" & objUser.l _
> & ";" & objUser.st _
> & ";" & objUser.mail
> End If
> Loop
> objFile.Close
> ================
> You would run this VBScript program at a command prompt with the cscript
> host. For example, if the VBScript is saved in the file ListUsers.vbs, you
> could run the program and redirect the output to a text file with the
> following command:
>
> cscript //nologo ListUsers.vbs > report.txt
>
> This assumes you are in the directory where the file ListUsers.vbs is saved.
> Otherwise you must also specify the path. The file report.txt is created in
> the same directory. I chose to output one line per user, with the fields
> delimited by semicolons (since some values could have embedded commas). This
> creates a file that can be read into a spreadsheet program. You can select
> other attributes and change the format.
>
> --
> Richard Mueller
> Microsoft MVP Scripting and ADSI
> Hilltop Lab - http://www.rlmueller.net
> --
>
>
>

Reply With Quote
  #4  
Old 23-07-2007
Richard Mueller [MVP]
 
Posts: n/a
Re: Extract data from AD and save in TXT file.

My mistake. I included the "Option Explicit" statement (recommended), but
did not include the Dim statements to declare the variables. Nothing wrong
with strFilePath, it was just the first variable encountered that was not
declared in a Dim statement. Add the following statements after the "Option
Explicit":

Dim strFilePath, objFSO, objFile, objRootDSE
Dim strDNSDomain, objTrans, strNetBIOSDomain
Dim strNTName, strUserDN, objUser

In addition, I see I missed a "&" concatenation symbol in the Wscript.Echo
statement. It should be:

Wscript.Echo objUser.sAMAccountName _
& ";" & objUser.Name _
& ";" & objUser.givenName _
& ";" & objUser.sn _
& ";" & objUser.streetAddress _
& ";" & objUser.l _
& ";" & objUser.st _
& ";" & objUser.mail

I missed the "&" character in the second line. I hope this helps.

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

"Tom Ja" <TomJa@discussions.microsoft.com> wrote in message
news:0C8E2E54-9ACD-454B-9CBD-8BB33165ADD5@microsoft.com...
> Thanks for reply and I am very happy and appreciate with prompt response.
> I tried this script but it shows me error.
> Error is:
> ? C:\scripts\listusers.vbs (10, 1) Microsoft VBScript runtime error:
> Variable is undefined: ?strFilePath? ?
> I checked variable is defined and file path is also ok in line 10, 1 .File
> is existing (UserList.txt) format of file is:
> User1
> User2
> User3
> User4
> User5
> Could you please help me out in this regards. I can not understand this
> error as I told you earlier I am not very much expert.
> Once again thanks for replying and thanks in advance.
> Tom.
>
>
> "Richard Mueller [MVP]" wrote:
>
>> Tom Ja wrote:
>>
>> > Can any one help me out. advance thanks.
>> > I am not very much expert in script I know basic script.
>> > I have question about script. Infect I want extract some information
>> > from
>> > Active directory and save in TXT file
>> > Question.
>> > I want script which takes IDs from the file (TXT file). Through this
>> > script
>> > search this (ID) in Active Directory. If user?s ID exist in AD then
>> > save
>> > the users Name ,Last name ,Address, Email address in a file xyz.TXT
>> > .if
>> > users NOT exist in AD then print ID and name ?ID NOT EXIST.? and go for
>> > next
>> > users's ID from the TXT file till end of TXT file.
>> > I hope you will understand my question. I will be waiting to hear from
>> > you
>> > in great anxiety
>> > Please help me out in this regards.

>>
>> The FileSystemObject can be used to read names from a text file, I assume
>> one name per line. I also assume you have the NT names (pre-Windows 2000
>> logon names) of the users. If so, you can use the NameTranslate object to
>> convert the NT names to the Distinguished Names (required to bind to the
>> objects). Then you can bind to each user object, retrieve any attribute
>> values desired, and output.
>>
>> It wouldn't make sense to create one xyz.txt file for each user. By far
>> the
>> easiest would be to have the script simply echo to the command prompt.
>> You
>> can redirect the output to a text file. For information on using
>> NameTranslate, see this link:
>>
>> http://www.rlmueller.net/NameTranslateFAQ.htm
>>
>> For documentation on the attributes corresponding to fields in ADUC, see
>> this link:
>>
>> http://www.rlmueller.net/UserAttributes.htm
>>
>> An example VBScript program could be similar to:
>> ===============
>> Option Explicit
>>
>> Const ForReading = 1
>> ' Constants for NameTranslate.
>> Const ADS_NAME_INITTYPE_GC = 3
>> Const ADS_NAME_TYPE_NT4 = 3
>> Const ADS_NAME_TYPE_1779 = 1
>>
>> ' Specify the text file of user names.
>> strFilePath = "c:\Scripts\UserList.txt"
>>
>> ' Open the file for read access.
>> Set objFSO = CreateObject("Scripting.FileSystemObject")
>> Set objFile = objFSO.OpenTextFile(strFilePath, ForReading)
>>
>> ' Determine DNS domain name from RootDSE object.
>> Set objRootDSE = GetObject("LDAP://RootDSE")
>> strDNSDomain = objRootDSE.Get("defaultNamingContext")
>>
>> ' Use the NameTranslate object to find the NetBIOS domain name
>> ' from the DNS domain name.
>> Set objTrans = CreateObject("NameTranslate")
>> objTrans.Init ADS_NAME_INITTYPE_GC, ""
>> objTrans.Set ADS_NAME_TYPE_1779, strDNSDomain
>> strNetBIOSDomain = objTrans.Get(ADS_NAME_TYPE_NT4)
>> ' Remove trailing backslash.
>> strNetBIOSDomain = Left(strNetBIOSDomain, _
>> Len(strNetBIOSDomain) - 1)
>>
>> ' Read NT names from text file.
>> Do Until objFile.AtEndOfStream
>> strNTName = Trim(objFile.ReadLine)
>> ' Skip blank lines.
>> If (strNTName <> "") Then
>> ' Use Set method to specify NT name.
>> ' Trap error if name not found.
>> On Error Resume Next
>> objTrans.Set ADS_NAME_TYPE_NT4, strNetBIOSDomain & "\" &
>> strNTName
>> If (Err.Number <> 0) Then
>> On Error GoTo 0
>> Wscript.Echo "User " & strNTName _
>> & " not found in Active Directory"
>> End If
>> On Error GoTo 0
>> ' Use Get method to retrieve Distinguished Name.
>> strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)
>> ' Bind to user object in AD.
>> Set objUser = GetObject("LDAP://" & strUserDN)
>> ' Output values desired. This outputs:
>> ' NT Name;Common Name;first name;last name;street
>> address;city;state;email
>> Wscript.Echo objUser.sAMAccountName _
>> & ";" objUser.Name _
>> & ";" & objUser.givenName _
>> & ";" & objUser.sn _
>> & ";" & objUser.streetAddress _
>> & ";" & objUser.l _
>> & ";" & objUser.st _
>> & ";" & objUser.mail
>> End If
>> Loop
>> objFile.Close
>> ================
>> You would run this VBScript program at a command prompt with the cscript
>> host. For example, if the VBScript is saved in the file ListUsers.vbs,
>> you
>> could run the program and redirect the output to a text file with the
>> following command:
>>
>> cscript //nologo ListUsers.vbs > report.txt
>>
>> This assumes you are in the directory where the file ListUsers.vbs is
>> saved.
>> Otherwise you must also specify the path. The file report.txt is created
>> in
>> the same directory. I chose to output one line per user, with the fields
>> delimited by semicolons (since some values could have embedded commas).
>> This
>> creates a file that can be read into a spreadsheet program. You can
>> select
>> other attributes and change the format.
>>
>> --
>> Richard Mueller
>> Microsoft MVP Scripting and ADSI
>> Hilltop Lab - http://www.rlmueller.net
>> --
>>
>>
>>



Reply With Quote
  #5  
Old 30-07-2007
Member
 
Join Date: Jul 2007
Posts: 1
Bundle of thanks

Bingoooooo?
This scripts works fine. Bundle of thanks.
I got 2 last question.

Q1. In this script my out put is repeating some records bellow is sample of my out put file:
User adm-1 not found in Active Directory
;DC=abc;;;;;
User adm-2 not found in Active Directory
;DC=abc;;;;;
u3;CN=u3;User3;Luser3;add333;;
u4;CN=u4;u4;sadfsadfdsafsadfsfdsafdsa;;;
User 222 not found in Active Directory
u4;CN=u4;u4;sadfsadfdsafsadfsadfdsafdsafdsa;;;
User adm-3 not found in Active Directory
u4;CN=u4;u4;sadfsadfdsafsadfsadfdsafdsafdsa;;;

Q2. This script allow me to extract data from AD but if I want to extract data form (ADSI attribute) then this script does not work.
For example if I want to take adminDisplayName (ADSI attribute) from TXT file and search it, redirect out put file.
I tried to change strNTName variable and replace with adminDisplayName (ADSI attribute) but does not wok may be too early for me because I am new.
I hope you understand my question.
Once again bundle of thanks.
Tom
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Windows Server > Windows Server Help
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Extract data from AD and save in TXT file."
Thread Thread Starter Forum Replies Last Post
script to extract data from text file and put into excel format tdubb Windows Server Help 19 17-11-2010 04:19 PM
How to extract data from web databases spuff Tips & Tweaks 7 09-10-2010 11:33 PM
How to extract data from a Barracuda 7200.7? clos_man Hardware Peripherals 6 23-05-2009 08:31 AM
Extract data from one workbook to another Laler Windows Software 3 18-05-2009 04:56 PM
How to extract data from AD? Asgar Active Directory 2 31-10-2008 04:49 AM


All times are GMT +5.5. The time now is 07:48 PM.