[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ACTIVE DIRECTORY - How do i write a query to display a list of users?

Posted on 2006-04-03
17
Medium Priority
?
1,391 Views
Last Modified: 2012-06-27
Hi all, this is an urgent one....

I have a list of about 350 user IDs in a text file that look like this:

sschpase
sschpask
sschpcme
sschpcsr
sschpfa9
ssclgswa
sscmrrsg
sscwhadq etc.....

In Active Directory Users and Computers there is a section at the top for specific AD queries.  If i create a new query, then click Define Query i get the Find Common Queries dialog box.  If i then click on Custom Search from the drop down list and go to the second tab, i can "Enter LDAP Query".

I would like someone to tell me how i can write an LDAP query so AD only shows the users IDs that match the list that i have.  I am hoping that if someone can write a query based on the user IDs above, then i can amend it to display only the 350 users IDs from my list.

However - there may be a limit to the size of the AD LDAP query - anyone know?

I would really appreciate the help with this one - thanks in advance!!!!
0
Comment
Question by:mcompton69
  • 7
  • 7
  • 2
  • +1
17 Comments
 
LVL 71

Expert Comment

by:Chris Dent
ID: 16359055

It would be much easier to do in VbScript in my opinion. What are you looking to do with the list of users (or what do you want to see)?

Chris
0
 
LVL 1

Author Comment

by:mcompton69
ID: 16359133
Hi Chris

I just want to show a list of the users with the properties on the user IDs.  The reason being is that some of these users no longer exist (the ones that i correctly moved the userdata folder for) but some still exist.  So i just want a list of these users, if they exist (there should only be about 20 or so).

I dont know VB scripting or how to write LDAP queries so please talk down to me.

Thanks.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Author Comment

by:mcompton69
ID: 16360464
No, this is nothing to do with Word - i do not know how to write an LDAP query from scratch and so that page doesn't help.

Thanks anyway.
0
 
LVL 1

Author Comment

by:mcompton69
ID: 16360478
I suppose that the LDAP query i require is in effect a filter:  i only want to display the users that have the user ID x, y, z etc....
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 16360686

I had an attempt at setting an Or statement for the username, and it can be done, but you have to explicitly list the usernames in the query (it can be a wildcard list "*" if you have a very similar set of usernames):

(&(objectCategory=user)(|(samAccountName=user1)(samAccountName=user2)))

So it helps to an extent, but isn't the nicest format to write in. Basically what is say is:

If the object is a User And (&) the Username is either User1 Or (|) User1 then display it.

Would you like a VbScript to write the filter so you can just paste it in rather than attempting to write that for 350 users? What format do you have the user list in at the moment?

Chris
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 16360721

Er should say User1 Or (|) User2, which would make a lot more sense.

Chris
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 16360790

Being a bit slow, just noticed you already posted the format.

If it's a standard text file then just save this as a .vbs file, put the filename where it says <FileName> and run it; it'll give you a new file (imaginatively) called out.txt which has the query string in it:

Set objFileSystem = CreateObject("Scripting.FileSystemObject")

Set objFile = objFileSystem.GetFile("<FileName>")
Set objStream = objFile.OpenAsTextStream(1, 0)
strLDAPQuery = "(&(objectCategory=user)(|"
Do While Not objStream.AtEndOfStream
      strUserName = objStream.ReadLine

      strLDAPQuery =       strLDAPQuery & "(samAccountName=" & strUserName & ")"
Loop
Set objFile = Nothing

strLDAPQuery = strLDAPQuery & "))"

Set objFile = objFileSystem.OpenTextFile("out.txt", 2, True, 0)
objFile.WriteLine strLDAPQuery
Set objFile = Nothing

Set objFileSystem = Nothing
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 16360810

If you wanted to do something very specific with the user accounts then you may find it still better to go with VbScript entirely; it allows more flexibility than an LDAP query, but requires things to be more defined.

Hope that lot helps a little anyway.

Chris
0
 
LVL 1

Author Comment

by:mcompton69
ID: 16362348
Hi Chris, thanks for all your help so far.  The script worked perfectly, i got the out.txt file, however

1) I could not paste in all the text, there must be a limit to the maximum LDAP query size (damn! damn!!)
2) I pasted some of the query and got an error message: " The query filter [bla bla bla bla bla...] is not a valid query string" where bla is the query you sent.

So basically my method for solving this will not work, can you think of another way of acheiving this?

I have an idea.  If i had a script that moved all the user IDs that are in the list into the OU called test (for example), then i could use the OU as the filter and display only the user IDs that i want to.

Does that sound like a plan?  Do you think you could come up with that script Chris?  I feel i should be paying you something for asking even!

I have to go catch my train now, resume again at 8:30GMT+1
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 16362499

The script will be no problem, it's actually really quite straight-forward. Here we go:


' INPUT_FILE is the file name you want to get the list of usernames from

Const INPUT_FILE = "<FileName>"

' OU_NAME is the OU you want to put the users in. Don't include the domain portion of the DN.
' e.g. OU=Users,OU=Temp OU

Const OU_NAME = "OU=Users,OU=Temp OU"

Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_1779 = 1
Const ADS_NAME_TYPE_NT4 = 3

Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objNetwork = CreateObject("WScript.Network")

Set objFile = objFileSystem.GetFile(INPUT_FILE)
Set objFileStream = objFile.OpenAsTextStream(1, 0)

Set objRootDSE = GetObject("LDAP://RootDSE")
Set objOU = GetObject("LDAP://" & OU_NAME & "," & objRootDSE.Get("defaultNamingContext"))

Do While Not objFileStream.AtEndOfStream

      On Error Resume Next

      strUserName = objNetwork.UserDomain & "\" & objFileStream.ReadLine

      Set objNameTranslate = CreateObject("NameTranslate")
      objNameTranslate.Init ADS_NAME_INITTYPE_GC, ""
      objNameTranslate.Set ADS_NAME_TYPE_NT4, strUserName
      If Err.Number <> 0 Then
            ' Couldn't find the user in AD
      Else
            strDN = objNameTranslate.Get(ADS_NAME_TYPE_1779)

            Set objUser = GetObject("LDAP://" & strDN)
            objOU.MoveHere objUser.ADSPath, VbNullString
            Set objUser = Nothing
      End If

      On Error Goto 0

      Set objNameTranslate = Nothing
Loop

Set objOU = Nothing
Set objRootDSE = Nothing

Set objFileStream = Nothing
Set objFile = Nothing

Set objNetwork = Nothing
Set objFileSystem = Nothing
0
 
LVL 85

Accepted Solution

by:
oBdA earned 1600 total points
ID: 16365304
If I got you right, you have a list of 350 user names, and you want to know which of these (about 20) are still present in your AD?
That's rather simple; save this as usercompare.cmd (or whatever.cmd), adjust the file names at the beginning, and run it.

@echo off
setlocal
set PotentialUserFile=userlist.txt
set ExistingUserFile=out.txt
if exist "%ExistingUserFile%" del "%ExistingUserFile%"
for /f %%a in ('type "%PotentialUserFile%"') do (
  echo Processing %%a ...
  net user %%a /domain >nul 2>nul && echo %%a >>"%ExistingUserFile%"
)
0
 
LVL 1

Author Comment

by:mcompton69
ID: 16371897
Hi oBda

I tried your answer first as it seemed shorter, but it does not work:

I renamed the text file with the usenames in to usercompate.cms
Pasted in the commands above before all the usernames [ *adjust* the names at the beginning doesn't mean anything unless you tell me how to adjust them]

I then removed the echo command to debug it and this is the result:

C:\Documents and Settings\ssmopcsp\Desktop>setlocal
C:\Documents and Settings\ssmopcsp\Desktop>set PotentialUserFile=userlist.txt
C:\Documents and Settings\ssmopcsp\Desktop>set ExistingUserFile=out.txt
C:\Documents and Settings\ssmopcsp\Desktop>if exist "out.txt" del "out.txt"
C:\Documents and Settings\ssmopcsp\Desktop>for /F %a in ('type "userlist.txt"') do (
echo Processing %a ...
 net user %a /domain   1>nul 2>nul  && echo %a  1>>"out.txt"
)
The system cannot find the file specified.

C:\Documents and Settings\ssmopcsp\Desktop>pause
Press any key to continue . . .
0
 
LVL 85

Expert Comment

by:oBdA
ID: 16375527
That meant just the two variables at the script's beginning, "PotentialUserFile" (the path and name with the input file that holds the list with all the user names), and "ExistingUserFile" (the output file which will list all accounts found in PotentialUserFile that are still domain members).
If you're running the script from the folder where the PotentialUserFile is stored, you don't need to specify the path.
So you need two files: the script above, which you can save as usercompare.cmd, and the file with the user accounts (in the script above it's currently defined as "userlist.txt").
Then run the script, and it will create the output file (out.txt in the script above).
0
 
LVL 1

Author Comment

by:mcompton69
ID: 16382715
Crikey - It worked!

I ran everything from the desktop so didn't need to mess about with pathnames, just renamed the text file with the usernames in to userlist.txt, ran the file and after a few seconds, out popped out.txt which contained 33 users.

I didnt get round to testing your script Chris, but it would still not have solved the original problem entirely, so all points to oBdA.

Thanks a million both for your help.
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 16382749

No problems there, as long as you end up with something that does what you need :)

Chris
0
 
LVL 1

Author Comment

by:mcompton69
ID: 16382940
That is a refreshing attitude!!!! Good on you :D

From past experience, some people get a bit snotty when you dont give them points after they submit a solution :(

However, you are now the proud owner of 400 VIRTUAL points!  Wooow!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A hard and fast method for reducing Active Directory Administrators members.
Had a business requirement to store the mobile number in an environmental variable. This is just a quick article on how this was done.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question