Report for New software installs - modifying the Software Change report

[pej-sbs]

Running Lansweeper Classic v12 - I'm trying to modify the "Software: Changes in the last 7 days report" so that it only shows NEW installs.  I see a few older posts but they didn't show how to filter in the SQL.

I asked Copilot to update the code and its suggestion seems to work, but comparing the existing Change report to the modified version shows the new one is missing some items.  Is the approach shown here reasonable?  The main change is the EXISTS clauses which I've bold-faced.

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
And Exists(Select 1 From tblSoftwareHist sh1
Where sh1.softid = tblSoftwareUni.SoftID And sh1.Action = '1') And
Not Exists(Select 1 From tblSoftwareHist sh2
Where sh2.softid = tblSoftwareUni.SoftID And sh2.Action = '2')
Group By tsysOS.Image,
tblAssets.AssetID,
tblAssets.AssetName,
tblSoftwareHist.Action,
tblSoftwareUni.softwareName,
tblSoftwareHist.softwareVersion,
tblSoftware.MsStore,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareHist.Installdate,
tblSoftwareHist.Lastchanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname,
tblAssets.SP,
tblAssets.Firstseen,
tblAssets.Lastseen
Order By tblAssets.AssetName,
tblSoftwareHist.Lastchanged Desc,
Software

[pej-sbs]

Thanks!  That's a lot more understandable.  I'll run it side by side for a few days to see how things look.

[KevinA-REJIS]

This is a report I have, might be what you need:

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 tblSoftwareUni.SoftwarePublisher
  Is Not Null And tblState.Statename = 'Active'
Order By tblSoftwareUni.Added,
  Software,
  tblAssets.AssetName