asked on

Query Active Directory for Active User Accounts

I need a query so that I can generate a list of active, user accounts only in AD, and have this made available in a single column in Excel. I'm presently using this query:

dsquery * "dc=company,dc=com" -Filter "(&(objectCategory=
person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2))" -limi
t 5000 > C:\userexport.txt

And it works, however generates results such as:

"CN=TestUser1,OU=Users,OU=Company,DC=COMPANY,DC=COM"

When I go to import into excel as comma values as separation it still sees the entire line as 1 column entry. I just need the username. Is there a better query, perhaps powershell, or a way to set it up within ADU&C as a saved query for Active users. I can run one this way for either all users including disabled, or one with just disabled users, but it doesn't seem to give an option for active users only

jsdray

need to remove the quotes to import as CSV

Mike Kline

You can pipe that into dsget

dsquery * "dc=company,dc=com" -Filter "(&(objectCategory=
person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2))" -limi
t 5000 | dsget user -samid

Are you open to doing this using other tools?

Thanks

Mike

fireguy1125

ASKER

I was just informed we have Quest Powertools ActiveRoles also available in our environment to do this, and I was asked to include an EmployeeID attribute in a column, and the UPN of the account, is this possible?

Mike Kline

I'd have to test using qeust cmdlets, adfind is a great free small query tool

http://www.joeware.net/freetools/tools/adfind/

Enabled users

adfind -default -bit -f "&(objectcategory=person)(objectclass=user)(!useraccountcontrol:AND:=2)" samaccountname userprincipalname employeeid -nodn -csv > enabledusers.csv

to find disabled users just remove the not (!) before useraccountcontrol

You can also add other columns but I just outputted three attributes.

Thanks

Mike

Sarang Tinguria

Chris has a great GUI tool for all these stuffs

Cjwdev | AD Info - Active Directory Reporting www.cjwdev.co.uk/Software/ADReportingTool/Info.html

fireguy1125

ASKER

Unfortunately I can't use any other tools except AD and the Quest.

fireguy1125

ASKER

I just need to know the correct attributes perhaps to join it to the dsquery utility for the UPN and EmployeeID, when i do it through the ADUC GUI it gives me the option of employee ID as present or not, but not both, i still need all AD accounts whether they have employee ID or not, so I can't have it filtered that way.

Sarang Tinguria

Add attribute as you need

dsquery user "DC=domain,DC=local" -limit 0 | dsget user -display -email - samaccountname -displayname>c:\extract\Users.csv

Open in new window

Mike Kline

Your filter is fine you just add the dsget (see my first response).

by the way if you only want to see users that do have an employeeid attribute expand your filter

"(&(objectCategory=
person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(employeeid=*))"

Thanks

Mmike

fireguy1125

ASKER

It doesn't seem to be working, I get the following:

C:\>dsquery * "dc=company,dc=com" -Filter "(&(objectCategory=
person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(employe
eid=*))" -limit 0 | dsget user -samid -employeeID > C:\test2.txt
dsget failed:'-employeeID' is an unknown parameter.
type dsget /? for help.

fireguy1125

ASKER

i don't want to have separate list of users with and users without employee ids, rather i'd like to have a list of showing username, upn, employeeID. Not all useres have an employee ID, in that case it should be blank, but I need all users to appear in the query.

ASKER CERTIFIED SOLUTION

Mike Kline

membership

This solution is only available to members.

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

Start Free Trial

fireguy1125

ASKER

thanks, but that only lists the users that have an employeeid, i need it to list users with and without an employee id

fireguy1125

ASKER

nevermind, i figured it out, this is exactly what I need. thanks so much!

dsquery * "dc=company,dc=com" -Filter "(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2))" -limit 0 | dsget user -samid -upn -empid > C:\userexport.txt