List of local admins exclude xxx and zzz

[EStarshinov]

Help please whith report

[EStarshinov]

It works. Thanks!

[Daniel_B · Accepted solution]

These might be caused by users from different domains with the same username or by user membership in multiple groups. The following report lists the user domain as well and filters out duplicates by using the Select Distinct clause:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tblUsersInGroup.Username,
  tsysOS.Image As icon,
  tblUsersInGroup.Domainname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Where tblUsersInGroup.Username Not In ('xxx', 'zzz') And
  tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  tblUsersInGroup.Username

[EStarshinov]

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblUsersInGroup.Username,
tsysOS.Image As icon
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Where tblUsersInGroup.Username Not In ('xxx', 'zzz') And
tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
tblUsersInGroup.Username

Daniel, this report has duplicates.

[Daniel_B]

Apologies, there was a mistake in the report. The report in my last post was updated. It shouldn't list duplicates any more.

[EStarshinov]

Thanks? it work!
But now i have duplicate rows in my excel report:

X057 Domain Win XP aKodrovS 1bloomber
X057 Domain Win XP aBodrovS 1bloomber
X057 Domain Win XP aKodrovS 1bloomber
X057 Domain Win XP aKodrovS 1bloomber
X057 Domain Win XP aKodrovS 1bloomber
X057 Domain Win XP aKodrovS user
X057 Domain Win XP aKodrovS user
X057 Domain Win XP aKodrovS user
X057 Domain Win XP aKodrovS user
X057 Domain Win XP aKodrovS user

How fix it in query?

[Daniel_B]

You can find users which are member of the local administrators group in tblUsersInGroup. In order to exclude certain usernames, use a NOT IN statement. Please find an example below.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tblUsersInGroup.Username,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblUsersInGroup On tblAssets.AssetID = tblUsersInGroup.AssetID
Where tblUsersInGroup.Username Not In ('xxx', 'zzz') And
  tblUsersInGroup.Admingroup = 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  tblUsersInGroup.Username