→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mjbliss
Engaged Sweeper II
Hi,

All my user accounts in AD have the description "User Account" set, this is to differentiate them from Consulting Accounts and Service Accounts, they also have "Company" populated under the Company field.

I'm trying to create a query that lets me know which accounts do not have Employee ID populated, I thought the below query would do the trick but the results still show users that have something other than "User Account" in the description field.

The filtering of users that DO HAVE employee ID populated works as expected.

Any reason why the And tblADusers.Description = 'User Account') doesn't seems to work?

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Office,
tblADusers.EmployeeID,
tblADusers.Company,
tblADusers.Description
From tblADusers
Where (tblADusers.EmployeeID Like '' And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account') Or
(tblADusers.EmployeeID Is Null)
3 REPLIES 3
Esben_D
Lansweeper Employee
Lansweeper Employee
Looking at your where clause I can see the problem.

You're basically displaying two groups of users in your query:
  1. EmployeeID is empty, company is 'company' and description is 'User Account'
  2. EmployeeID is NULL
This means that if a user has NULL for EmployeeID, it will be displayed regardless of any other fields.

I would use the following for your situation:
Where (tblADusers.EmployeeID Like '' And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account') Or
(tblADusers.EmployeeID Is Null And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account')


Edit: Seems like you figured it out while I was posting 😉
mjbliss
Engaged Sweeper II
I checked this morning and somehow a consultant name appeared on my list, so I didnt quite have it. Ive replaced my code with yours and its working perfectly now. Thank you.

Charles.X wrote:
Looking at your where clause I can see the problem.

You're basically displaying two groups of users in your query:
  1. EmployeeID is empty, company is 'company' and description is 'User Account'
  2. EmployeeID is NULL
This means that if a user has NULL for EmployeeID, it will be displayed regardless of any other fields.

I would use the following for your situation:
Where (tblADusers.EmployeeID Like '' And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account') Or
(tblADusers.EmployeeID Is Null And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account')


Edit: Seems like you figured it out while I was posting 😉


mjbliss
Engaged Sweeper II
This seems to have done the trick;

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Office,
tblADusers.EmployeeID,
tblADusers.Company,
tblADusers.Description
From tblADusers
Where (tblADusers.EmployeeID Like '' And tblADusers.Company = 'Ugu' And
tblADusers.Description = 'User Account') Or
(tblADusers.EmployeeID Is Null And tblADusers.Description = 'User Account')

mjbliss wrote:
Hi,

All my user accounts in AD have the description "User Account" set, this is to differentiate them from Consulting Accounts and Service Accounts, they also have "Company" populated under the Company field.

I'm trying to create a query that lets me know which accounts do not have Employee ID populated, I thought the below query would do the trick but the results still show users that have something other than "User Account" in the description field.

The filtering of users that DO HAVE employee ID populated works as expected.

Any reason why the And tblADusers.Description = 'User Account') doesn't seems to work?

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Office,
tblADusers.EmployeeID,
tblADusers.Company,
tblADusers.Description
From tblADusers
Where (tblADusers.EmployeeID Like '' And tblADusers.Company = 'Company' And
tblADusers.Description = 'User Account') Or
(tblADusers.EmployeeID Is Null)