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
LVL 1
fireguy1125Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jsdrayCommented:
need to remove the quotes to import as CSV
0
Mike KlineCommented:
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
0
fireguy1125Author Commented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Mike KlineCommented:
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
0
Life1430Commented:
Chris has a great GUI tool for all these stuffs

Cjwdev | AD Info - Active Directory Reporting www.cjwdev.co.uk/Software/ADReportingTool/Info.html
0
fireguy1125Author Commented:
Unfortunately I can't use any other tools except AD and the Quest.
0
fireguy1125Author Commented:
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.
0
Life1430Commented:
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

0
Mike KlineCommented:
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
0
fireguy1125Author Commented:
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.
0
fireguy1125Author Commented:
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.
0
Mike KlineCommented:
I  was just saying that as en exmaple

user -empid instead of -employeeid in the dsget command.

Thanks


Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fireguy1125Author Commented:
thanks, but that only lists the users that have an employeeid, i need it to list users with and without an employee id
0
fireguy1125Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Active Directory

From novice to tech pro — start learning today.