cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
GP
Engaged Sweeper
I go this query which generates a report of all assets with Windows and Linux, with IP. for some reason it shows duplicates on some of the assets. Can you help how to remove duplicates. Thanks,

Select Top 100000 tsysIPLocations.IPLocation,
tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Username,
tblADusers.Name,
tblADusers.email,
tblAssets.Userdomain,
(Case
When tblAssets.Assettype = -1 Then tblOperatingsystem.Caption
Else tblLinuxSystem.OSRelease
End) As [OS Release],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Firstseen,
tblAssets.Lastseen,
(Case
When tblAssets.Assettype = -1 Then (Select Top 1 TP2.Name
From tblProcessor TP2 Where TP2.AssetID = TP1.AssetID)
Else (Select Top 1 TLP2.Manufacturer + ' ' + TLP2.Family + ' ' +
TLP2.CurrentSpeed From tblLinuxProcessors TLP2
Where TLP2.AssetID = tblAssets.AssetID And
TLP2.Status Like '%Enabled%')
End) As Processor,
(Case
When tblAssets.Assettype = -1 Then (Select Count(TP3.WIN32_PROCESSORid)
From tblProcessor TP3 Where TP3.AssetID = TP1.AssetID)
Else (Select Count(TLP3.ProcessorID) From tblLinuxProcessors TLP3
Where TLP3.AssetID = TLP1.AssetID And TLP3.Status Like '%Enabled%')
End) As ProcessorCount,
tblAssets.Memory,
tblAssetCustom.Serialnumber,
tblAssetCustom.Warrantydate,
tblAssetCustom.State,
tblAssets.Assettype,
tblAssets.IPAddress
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username
Left Join tblLinuxSystem On tblLinuxSystem.AssetID = tblAssets.AssetID
Left Join tblProcessor TP1 On TP1.AssetID = tblAssets.AssetID
Left Join tblLinuxProcessors TLP1 On TLP1.AssetID = tblAssets.AssetID And
TLP1.Status Like '%Enabled%'
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssetCustom.State = 1 And (tblAssets.Assettype = -1 Or
(tblAssets.Assettype = 11 And Exists(Select tblLinuxSystem.AssetID
From tblLinuxSystem
Where tblLinuxSystem.AssetID = tblAssets.AssetID)))
Order By tblAssets.IPAddress
0 REPLIES 0