Duplicate rows in "Software: Changes in the last 24 hours" & "Software: Changes in the last 7 days"

[ErikT]

We noticed that from v9.5 the built-in reports “Software: Changes in the last 24 hours“ and “Software: Changes in the last 7 days“ contain many duplicate rows due to an incomplete inner join on tblSoftware.

Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID

Solution:
Adjust the concerning inner join by joining on tblSoftwareHist.softid = tblSoftware.softID as well in both reports

Inner Join tblSoftware On tblSoftwareHist.softid = tblSoftware.softID And
      tblAssets.assetid = tblSoftware.AssetID

For your convenience, we've included the corrected reports below as a workaround. When fixed, this will be mentioned as LAN-13127 in our changelog.

Software: Changes in the last 24 hours

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() - 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  tblSoftwareHist.Lastchanged Desc,
  Software

Software: Changes in the last 7 days

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

[ThierryC]

Hello,

We also have a problem with the dblsoftware and tblsoftwarehist table which have millions of repeated entries.

The problem with the pach Fixed: LAN-15308 Software history is repeated after every scan for MS Store Apps. is not yet fixed.

We have passed the latest Lansweeper patches but this does not correct the problem. Now we have to clean the DB by hand because the Lansweeper DB reached 10GB in a month.

It would be nice to correct this problem at the source.

[Obi_1_Cinobi]

Hello there!

Unfortunately, there are currently multiple issues with software scanning in Lansweeper version 10.6. Our development team has been able to fix these, and these fixes will be released in Lansweeper version 11, which should be coming soon. We thank you for your patience.

[Adri]

I think this issue still exists although in a different form
we had a blow out of our SQL tempdb space earlier this week and when investigating it pointed to Lansweeper software changes in the last 24 hours and 7 days, both reports were hitting the max of 1000000 lines 
we did identify a device that had an enormous amount of changes in the history, after deleting the device things got back to "normal" again 

Yesterday I downloaded the 7 day report and compared it with the standard report in Lansweeper v 10.6.2.0 (they are different and deliver different results 

I have found that there are situations store apps are left with the default ms-resource:AppStoreName or ms-resource:AppName where they appear to be different store apps, not helpful when it comes to identifying what it is  and it seems to upset the logic of those 2 reports

a sample for one of my assets

When running the latest version of he 7 days report I get 570 lines for that asset 
of which 525 are related to ms-resource:AppStoreName, lot of double ups, list is much longer

the downloaded version of the report produces 74 lines of which 15 are for ms-resource:AppStoreName 

I have added Hist and UNi softid to the report

perhaps somebody at lansweeper can have a look into this? 
thanks,

adri

[Obi_1_Cinobi]

Hello there!

We are very aware of this software scanning issue. Our development team has been able to fix this issue, and a fixe will be released in Lansweeper version 11, which should be coming soon. We thank you for your patience.

[rader]

Hi ErikT,

Looks like the report still show duplicates and the LAN-13127 should be LAN-13120 per the changelog.

What I have noticed is that the duplication seems to stem from the LastChanged field seconds numbers.

It would seem more reasonable to either limit to the minutes time-frame or maybe just to the Version field.

This would limit the results to a more manageable and morning readable volume as it was before.

Ideas?

[rader]

@ErikT 

Finally got the report edited as per your recommendation and it's exactly what I was looking for. Good to learn about the Format command. The seconds in all the reports were bugging me to no end.

Now my reports are more manageable to read quickly. Side note I sort on the Software column to scan thru the report quicker than sorting by asset. Easier to pick up on the outlier software installation.

[ErikT · Accepted solution]

@rader ,
In the below report,  the LastChanged date is limited to show the day, month, year with hours and minutes only.  

This can be further changed to show the LastChanged field in day, month, year only if needed.

Change:
  Format(tblSoftwareHist.Lastchanged, 'dd/MM/yyyy hh:mm') As [Last Changed],
Into:
  Format(tblSoftwareHist.Lastchanged, 'dd/MM/yyyy' ) As [Last Changed],

 
Report with the LastChanged date limited to show the day, month, year with hours and minutes only.

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,
  Format(tblSoftwareHist.Lastchanged, 'dd/MM/yyyy hh:mm') As [Last Changed],
  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

[Obi_1_Cinobi]

Hello there!

Thanks for your comment. We have asked @ErikT to update the QTS with the latest information.