cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Eichelberger
Engaged Sweeper
Is there a way to only show people who are in AD multiple times? Basically if their first and last names are duplicated in separate AD accounts.

Is Lansweeper case sensitive? We have some users who have one account in all caps and another in lowercase.

I'm using this to list everyone in AD:

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname,
tblADusers.Description,
tblADusers.Office,
tblADusers.Telephone,
tblADusers.Fax,
tblADusers.Mobile,
tblADusers.Street,
tblADusers.City,
tblADusers.C,
tblADusers.Zip,
tblADusers.Country,
tblADusers.Countrycode,
tblADusers.UPN,
tblADusers.Title,
tblADusers.Department,
tblADusers.Company,
tblADusers.email,
tblADusers.OU,
tblADusers.Lastchanged,
Max(tblCPlogoninfo.logontime) As lastlogon
From tblADusers
Left Join tblCPlogoninfo On tblCPlogoninfo.Username = tblADusers.Username And
tblCPlogoninfo.Domain = tblADusers.Userdomain
Group By tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname,
tblADusers.Description,
tblADusers.Office,
tblADusers.Telephone,
tblADusers.Fax,
tblADusers.Mobile,
tblADusers.Street,
tblADusers.City,
tblADusers.C,
tblADusers.Zip,
tblADusers.Country,
tblADusers.Countrycode,
tblADusers.UPN,
tblADusers.Title,
tblADusers.Department,
tblADusers.Company,
tblADusers.email,
tblADusers.OU,
tblADusers.Lastchanged
Having tblADusers.Lastname <> ' '
Order By tblADusers.Userdomain,
tblADusers.Lastname


Thank you.
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please use the report below for the information you are after.
Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname,
tblADusers.Description,
tblADusers.Office,
tblADusers.Telephone,
tblADusers.Fax,
tblADusers.Mobile,
tblADusers.Street,
tblADusers.City,
tblADusers.C,
tblADusers.Zip,
tblADusers.Country,
tblADusers.Countrycode,
tblADusers.UPN,
tblADusers.Title,
tblADusers.Department,
tblADusers.Company,
tblADusers.email,
tblADusers.OU,
tblADusers.Lastchanged,
SubQuery2.Lastlogon
From tblADusers
Inner Join (Select Top 1000000 tblADusers.Firstname,
tblADusers.Lastname,
Count(tblADusers.Lastchanged) As Count
From tblADusers
Group By tblADusers.Firstname,
tblADusers.Lastname) SubQuery1 On SubQuery1.Firstname = tblADusers.Firstname
And SubQuery1.Lastname = tblADusers.Lastname
Left Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As Lastlogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery2 On SubQuery2.Username = tblADusers.Username
And SubQuery2.Domain = tblADusers.Userdomain
Where tblADusers.Lastname <> '' And SubQuery1.Count > 1
Order By tblADusers.Lastname,
tblADusers.Firstname

View solution in original post

3 REPLIES 3
Hemoco
Lansweeper Alumni
There's nothing wrong with the report itself. Could you try running it again. If the issue persists, access the error page on the machine hosting your Lansweeper web console. This should produce a better error message.
Eichelberger
Engaged Sweeper
I attached the error message I got.
Hemoco
Lansweeper Alumni
Please use the report below for the information you are after.
Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname,
tblADusers.Description,
tblADusers.Office,
tblADusers.Telephone,
tblADusers.Fax,
tblADusers.Mobile,
tblADusers.Street,
tblADusers.City,
tblADusers.C,
tblADusers.Zip,
tblADusers.Country,
tblADusers.Countrycode,
tblADusers.UPN,
tblADusers.Title,
tblADusers.Department,
tblADusers.Company,
tblADusers.email,
tblADusers.OU,
tblADusers.Lastchanged,
SubQuery2.Lastlogon
From tblADusers
Inner Join (Select Top 1000000 tblADusers.Firstname,
tblADusers.Lastname,
Count(tblADusers.Lastchanged) As Count
From tblADusers
Group By tblADusers.Firstname,
tblADusers.Lastname) SubQuery1 On SubQuery1.Firstname = tblADusers.Firstname
And SubQuery1.Lastname = tblADusers.Lastname
Left Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As Lastlogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery2 On SubQuery2.Username = tblADusers.Username
And SubQuery2.Domain = tblADusers.Userdomain
Where tblADusers.Lastname <> '' And SubQuery1.Count > 1
Order By tblADusers.Lastname,
tblADusers.Firstname