cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Emort
Engaged Sweeper
hello
I have a "positive" (1) tag and a "negative"(2) tag and the "default" (0) tag.
i want a report that shows only assets that are members of the Default tag and only if they are not members of the two others.
I have been fumbling around but where I’m I going wrong.
in advance ..Thanks

Select Top 1000000 tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblComputersystem.Lastchanged,
tsysOS.Image As icon,
tblComputersystem.AssetID,
tblAssetGroups.AssetGroup
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1 And
(Not tblAssetGroups.AssetGroupID = 1 And Not tblAssetGroups.AssetGroupID = 2)
Order By tblAssets.AssetName
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please try the report below for the information you are after.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblComputersystem.Lastchanged,
tsysOS.Image As icon,
tblAssetGroups.AssetGroup
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID From tblAssets
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where tblAssetGroupLink.AssetGroupID <> 0) and tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

View solution in original post

4 REPLIES 4
Emort
Engaged Sweeper
Work after Upgrade! thanks
Hemoco
Lansweeper Alumni
Could you try updating to this Lansweeper version first. There were some bugs in earlier versions of the report builder.
Emort
Engaged Sweeper
Hmm i retrieve a
Incorrect syntax near the keyword 'Inner'.
but i'm unsure what the incorrect syntax is, probably right in front of me! could you try posting it in "code" brackets" maybe it is a copy paste error, all thou i would think i compared it....
Hemoco
Lansweeper Alumni
Please try the report below for the information you are after.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblComputersystem.Lastchanged,
tsysOS.Image As icon,
tblAssetGroups.AssetGroup
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID From tblAssets
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where tblAssetGroupLink.AssetGroupID <> 0) and tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName