cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Carlos_montes
Engaged Sweeper II
Hello!

i want to create a report with a list of assets that are not servers nor workstations(only laptops) and that have e.g a java version lower than 291 It has to include the list of assets (hostname, user that uses that machine).

Is posible to avoid duplicate rows?I mean it may show duplicate information like JAVA 32 bits and 64 for the same hostname.


Thanks for advance!!
2 REPLIES 2
Carlos_montes
Engaged Sweeper II
Thanks a lot, im newbie and im so out of practice of SQL code, looks working,
brandon_jones
Champion Sweeper III
Try this:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
From tblAssets
Inner Join tblBattery On tblAssets.AssetID = tblBattery.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join lansweeperdb.dbo.tblSoftware On tblAssets.AssetID =
tblSoftware.AssetID
Inner Join lansweeperdb.dbo.tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'java%' And
tblSoftware.softwareVersion Not Like '%291%' And tblAssetCustom.State = 1 And
tblBattery.DeviceID Is Not Null
Order By tblAssets.AssetName