SELECT TOP (1000000) tsysOS.Image AS Icon, tblAssets.AssetID, tblAssets.AssetName,
   CASE tblSoftwareHist.Action WHEN '1' THEN '+' WHEN '2' THEN '-' END AS [+/-], tblSoftwareUni.softwareName AS Software,
   tblSoftwareHist.softwareVersion AS Version, (SELECT CASE tblSoftware.MsStore WHEN 0 THEN 'Desktop app' ELSE 'Microsoft Store app' END) as 'Type',
   tblSoftwareUni.SoftwarePublisher AS Publisher,
   tblSoftwareHist.Installdate, tblSoftwareHist.Lastchanged, tblAssets.Domain, tblAssets.Username, tblAssets.Userdomain, tblAssets.IPAddress, tblAssets.Description,
   tblAssetCustom.Manufacturer, tblAssetCustom.Model, tblAssetCustom.Location, tsysIPLocations.IPLocation,
   tsysOS.OSname AS OS, tblAssets.SP, tblAssets.Firstseen AS [Created at], tblAssets.Lastseen AS [Last successful scan]
   FROM tblAssets
   INNER JOIN tblSoftwareHist ON tblAssets.AssetID = tblSoftwareHist.AssetID
   INNER JOIN tblSoftwareUni ON tblSoftwareHist.softid = tblSoftwareUni.SoftID
   INNER JOIN tblSoftware ON tblSoftwareHist.softid = tblSoftware.softID AND tblAssets.AssetID = tblSoftware.AssetID
   INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
   INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
   LEFT OUTER JOIN tsysIPLocations ON tsysIPLocations.LocationID = tblAssets.LocationID
   WHERE tblSoftwareHist.Lastchanged > GETDATE() - 7 AND tblAssetCustom.State = 1
   ORDER BY tblAssets.AssetName, tblSoftwareHist.Lastchanged DESC, Software