customize report

[sisman]

Hi, what tables should I use to have customized Unauthorized Administrators report like this:

-upn : -display : -disabled : -hometel : -dept : -office

I wrote all this fields as used in dsget command.....

[Hemoco]

Select Distinct Top 1000000 dbo.tblUsersInGroup.Computername As Computer,
  dbo.tblUsersInGroup.Username, dbo.tblUsersInGroup.Lastchanged,
  tblADusers.Displayname, tblADusers.UPN, tblADusers.Department,
  tblADusers.Office
From dbo.tblUsersInGroup Inner Join
  dbo.tblComputers On dbo.tblUsersInGroup.Computername =
    dbo.tblComputers.Computername Inner Join
  tblADusers On tblADusers.Userdomain = dbo.tblComputers.Userdomain And
    tblADusers.Username = dbo.tblComputers.Username
Where Not (dbo.tblUsersInGroup.Username In (Select tsysadmins.AdminName
  From tsysadmins)) And dbo.tblUsersInGroup.Groupname Like 'adm%'
Order By dbo.tblUsersInGroup.Computername

[sisman]

Select Top 100 Percent dbo.tblUsersInGroup.Computername As Computer,
dbo.tblUsersInGroup.Username, dbo.tblUsersInGroup.Lastchanged,
tblADusers.Displayname, tblADusers.UPN, tblADusers.Department,
tblADusers.Office
From dbo.tblUsersInGroup Inner Join
dbo.tblComputers On dbo.tblUsersInGroup.Computername =
dbo.tblComputers.Computername Inner Join
tblADusers On tblADusers.Userdomain = dbo.tblComputers.Userdomain And
tblADusers.Username = dbo.tblComputers.Username
Where Not (dbo.tblUsersInGroup.Username In (Select tsysadmins.AdminName
From tsysadmins)) And dbo.tblUsersInGroup.Groupname Like 'adm%'
Order By dbo.tblUsersInGroup.Computername

[sisman]

1. But where I can find then data that domain user is disabled?
2. Now for each users it reports several rows, how can I fix it to report only one time for each user?

[Hemoco]

1. But where I can find then data that domain user is disabled?

You can't (with Lansweeper)

2. Now for each users it reports several rows, how can I fix it to report only one time for each user?

COuld you post your last query please.

[Hemoco]

don't use the tblusers table, this are the local users, not the active directory users.

[sisman]

this is query I made so far:

Select Top 100 Percent dbo.tblUsersInGroup.Computername As Computer,
dbo.tblUsersInGroup.Username, dbo.tblUsersInGroup.Lastchanged,
tblADusers.Displayname, tblADusers.UPN, tblADusers.Department,
tblADusers.Office, tblUsers.Disabled
From dbo.tblUsersInGroup Inner Join
dbo.tblComputers On dbo.tblUsersInGroup.Computername =
dbo.tblComputers.Computername Inner Join
tblADusers On tblADusers.Userdomain = dbo.tblComputers.Userdomain And
tblADusers.Username = dbo.tblComputers.Username Inner Join
tblUsers On dbo.tblComputers.Computername = tblUsers.Computername
Where Not (dbo.tblUsersInGroup.Username In (Select tsysadmins.AdminName
From tsysadmins)) And dbo.tblUsersInGroup.Groupname Like 'adm%'
Order By dbo.tblUsersInGroup.Computername

[sisman]

I found field -disabled in tblUsers.
But now for each users it reports several rows, how can I fix it to report only one time for each user?

[sisman]

Ok, so -homephone filed can not be found in any other table?
I ask because we need that field in report.

[Hemoco]

These are the the only AD fields available.
You need to link username and userdomain to table tblcomputers.