cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ErikT
Lansweeper Tech Support
Lansweeper Tech Support
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
1 ACCEPTED SOLUTION
ErikT
Lansweeper Tech Support
Lansweeper Tech Support

@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

View solution in original post

8 REPLIES 8
ThierryC
Engaged Sweeper

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.

chapuisat_0-1697187094627.png

 

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
Lansweeper Tech Support
Lansweeper Tech Support

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
Engaged Sweeper III

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 

Adri_0-1697147861627.png

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

Adri_1-1697148154044.png

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

Adri_2-1697148309416.png

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

Adri_0-1697171407911.png

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
Lansweeper Tech Support
Lansweeper Tech Support

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
Champion Sweeper III

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.

Software_ Changes in the last x days microseconds.png

 

 

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
Champion Sweeper III

@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
Lansweeper Tech Support
Lansweeper Tech Support

@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
Lansweeper Tech Support
Lansweeper Tech Support

Hello there!

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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now