We noticed that the Software Change Report (both 24 hours and 7 days) did not reflect software removals. We were able to resolve this issue by removing the following table from the query: tblSoftware
Here is our query for the 7 day report. The only change needed for the 24 hour report is to change the Where tblSoftwareHist.Lastchanged > GetDate() - 7 to Where tblSoftwareHist.Lastchanged > GetDate() - 1
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,
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 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
