→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
EStarshinov
Engaged Sweeper II
Help please whith report
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

6 REPLIES 6
EStarshinov
Engaged Sweeper II
It works. Thanks!
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper II
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
Lansweeper Alumni
Apologies, there was a mistake in the report. The report in my last post was updated. It shouldn't list duplicates any more.
EStarshinov
Engaged Sweeper II
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
Lansweeper Alumni
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