dsq1.bat: common AD queries

Author: Robert Lawson
Environment: Windows Server, Active Directory
Description: These are the most common queries I use to gather information from Active Directory, and usually import results into Excel for auditing and/or reporting. I do save most common OU's to simply cut-and-paste, else I'll use ADSI-edit to get the OU. I simply remove the REM from query I want to use, then execute as such:
dsq1.bat>dsq1.txt
Uses:
- Stale passwords: report users who password is due to expire, call and educate before it expires. I did this for 1 year… no longer an issue
- Inactive computers: monitored for a year, removed manually. Then on negotiating with User Services, agreed to 9month and automatically remove.
- Mailbox store: audit that conventions were being followed: students, employees, non-named accounts, inactive…
- Group membership: quick way to get e-list of members
- Security: members of Domain Admin, Enterprise Admin..
- Ad hoc queries: total number of active students, employees….
Code:

rem file   : dsq1.bat
rem purpose: AD Query's
rem create : 25-dec-2006/r lawson
rem
rem notes
rem - un-REM query you want to use
rem - execute to verify no syntax error
rem - dsq1.cmd>dsq1.txt
rem - Import dsq1.txt into Excel for reporting

rem computers, in AV, inactive for 1 year
rem dsquery computer "DC=school,DC=edu" -inactive 52 -limit 0  dsget computer -samid -dn

rem all computer names
rem
rem dsquery computer "OU=AvServers,OU=location,DC=school,DC=edu" -limit 0  dsget computer -samid

rem computers, inactive for x weeks
rem dsquery computer "OU=Computer Accounts,DC=school,DC=edu" -limit 0 dsget computer -samid  -sid -desc -loc
rem dsquery computer "OU=Computer Accounts,DC=school,DC=edu" -o rdn -limit 0 -inactive 12
rem dsquery * "OU=Computers,OU=Calabasas,DC=school,DC=edu" -attr cn operatingSystem operatingSystemServicePack lastlogonTimeStamp -limit 0


rem users, in Calabasas campus, with disabled accounts
rem dsquery user "OU=Calabasas,DC=school,DC=edu" -disabled -limit 0

rem all users, inactive for 1 year
rem dsquery user "DC=school,DC=edu" -inactive 26 -limit 0  dsget user -samid -display -disabled -dn

rem all users, who have not changed pw for 60 days
rem dsquery user "DC=school,DC=edu" -stalepwd 60  -limit 0
rem users who's password will expire in 5 days (90-85)
rem dsquery user "OU=Staff,OU=Users,OU=location,DC=school,DC=edu" -stalepwd 83 -limit 0  dsget user -samid -email -empid -display -title -dept -tel -office -disabled -canchpwd -pwdneverexpires -acctexpires -mustchpwd

rem all email enabled users
rem dsquery * "DC=school,DC=edu" -filter "(&(objectCategory=Person)(objectClass=User)(homeMDB=*)) " -attr sAMAccountName displayName distinguishedName homeMDB userAccountControl -limit 0


rem all Users in an OU
dsquery * "OU=Students,DC=school,DC=edu" -filter "(&(objectCategory=Person)(objectClass=User))" -attr sAMAccountName displayName givenName description sn employeeID employeeNumber department homeMDB userAccountControl -limit 0

rem another form of users in an OU
rem dsquery user "OU=janitors,OU=location,DC=school,DC=edu" -limit 0  dsget user -samid -display -disabled -canchpwd -pwdneverexpires -acctexpires

rem single user, display info
rem dsquery user "CN=edummy,OU=Consultants And Temporary Users,OU=All AV Employees,OU=location,DC=school,DC=edu"   dsget user -samid -display -disabled -canchpwd -pwdneverexpires -acctexpires

rem get members of a group
rem dsquery group "OU=ADgroups,OU=location,DC=school,DC=edu" -name "grpUGClassOf2012"dsget group -members -expanddsget user -samid -fn -ln -title -dept -disabled -mustchpwd -pwdneverexpires

rem groups
rem dsquery * "OU=ADgroups,OU=location,DC=school,DC=edu" -filter "(&(objectCategory=Group)(objectClass=Group))" -limit 0 -attr sAMAccountName displayName name description mail member

rem users 1: AD attributes, LDAP filter
rem dsquery * "DC=school,DC=edu" -filter "(&(objectCategory=Person)(objectClass=User))" -limit 0 -attr sAMAccountName displayName employeeID employeeNumber userAccountControl delivContLength submissionContLength mdbUseDefaults mDBStorageQuota mDBOverHardQuotaLimit msExchHideFromAddressLists memberOf

rem Users 2: AD attributes
rem dsquery * "OU=Students,OU=location,DC=school,DC=edu" -limit 0 -attr sAMAccountName displayName employeeID employeeNumber telephoneNumber ciscoEcsbuDtmfId physicalDeliveryOfficeName createTimeStamp whenCreated department title userAccountControl msExchHideFromAddressLists mail protocolSettings homeMDB memberOf

rem Users 3: Query elements
rem dsquery user "OU=NewStudents,OU=Users,DC=school,DC=edu" -limit 0 dsget user -samid -email -empid -display -title -dept -tel -office -disabled -pwdneverexpires -acctexpires -mustchpwd


rem Users 3: Query elements, brief
rem dsquery user "OU=NewStudents,DC=school,DC=edu" -limit 0 dsget user -samid -fn -ln

rem computers with bogus container setting
rem dsquery * "OU=Computer Accounts,DC=school,DC=edu"  -limit 0 -attr sAMAccountName CN msDS-Approx-Immed-Subordinates  memberOf

rem find oddities with T/S (doesn't seem to work May 2006)
rem dsquery * "OU=Users,DC=school,DC=edu" -limit 0 -attr sAMAccountName displayName department title scriptPath TerminalServicesProfilePath TerminalServicesHomeDirectory TerminalServicesHomeDrive TerminalServicesWorkDirectory TerminalServicesInitialProgram