Select Distinct Top 1000000 tblAssets.AssetID, tblAssets.AssetName, tblAssets.Domain, tblAssets.Username, tblAssets.Userdomain, Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon, tblAssets.IPAddress, tsysIPLocations.IPLocation, tblAssetCustom.Manufacturer, tblAssetCustom.Model, tsysOS.OSname As OS, tblAssets.SP, tblSoftwareUni.softwareName As Software, tblSoftware.softwareVersion As Version, tblSoftwareUni.Added, tblAssets.Lastseen, tblAssets.Lasttried From tblAssets Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID Inner Join tblState On tblState.State = tblAssetCustom.State Left Join tsysOS On tsysOS.OScode = tblAssets.OScode Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID Right Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID Left Join tblSoftwareHist On tblSoftwareHist.softid = tblSoftwareUni.SoftID Where tblSoftwareUni.Added > GetDate() - 7 And SubString(tblSoftwareUni.softwareName, 1, 10) Not In (Select SubString(tblSoftwareUni.softwareName, 1, 10) From tblSoftwareUni Where tblSoftwareUni.Added < GetDate() - 7) And tblSoftwareUni.SoftwarePublisher Is Not Null And tblState.Statename = 'Active' Order By tblAssets.Domain, tblAssets.AssetName