→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
MrCount
Engaged Sweeper III
Hi,

my report shows duplicate entries.
I figured out that it is because some assets have more than 1 battery installed.
How can I edit the report, so that only one entry for each asset is shown?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom1,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Username,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPortableBattery
On tblAssets.AssetID = tblPortableBattery.AssetID
Where tblPortableBattery.CapacityMultiplier >= 0 And tblAssetCustom.State = 1
Order By tblAssetCustom.Custom1 Desc,
tblAssets.AssetName
1 ACCEPTED SOLUTION
MrCount
Engaged Sweeper III
Got it working...

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom1,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Username,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPortableBattery
On tblAssets.AssetID = tblPortableBattery.AssetID
Where tblPortableBattery.CapacityMultiplier >= 0 And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom1,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Username,
tblAssets.Lastseen
Order By tblAssetCustom.Custom1 Desc,
tblAssets.AssetName

View solution in original post

1 REPLY 1
MrCount
Engaged Sweeper III
Got it working...

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom1,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Username,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPortableBattery
On tblAssets.AssetID = tblPortableBattery.AssetID
Where tblPortableBattery.CapacityMultiplier >= 0 And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Custom1,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Username,
tblAssets.Lastseen
Order By tblAssetCustom.Custom1 Desc,
tblAssets.AssetName

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now