cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
marck1024
Engaged Sweeper III
Lansweeper includes a report showing Windows 10 computers not on the latest feature update. But this report is always showing a lot of computers because we have test users on the newest build at all times while the regular users are delayed by 8 months.

I put together a report that calculates the version that most computers are on and then shows computers on older builds than that. This helps us catch computers that are getting behind.


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname As OS,
tblAssets.OScode + '.' + tblAssets.BuildNumber As Build,
tblAssets.Version As [OS Version],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Left Join (Select A.Version,
Row_Number() Over (Order By A.Version Desc) As RowNumber
From (Select Distinct A.Version
From tblAssets A
Inner Join tsysOS B On A.OScode = B.OScode
Inner Join (Select Top 1 A.Version,
Count(1) As Total
From tblAssets A
Inner Join tsysOS B On A.OScode = B.OScode
Where B.OSname = 'Win 10'
Group By A.Version
Order By Total Desc) C On 1 = 1
Where A.Version <= C.Version And B.OSname = 'Win 10') A) Versions On
tblAssets.Version = Versions.Version
Where tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1 And
(Versions.RowNumber >= 2 Or tblAssets.Version Is Null)
Order By tblAssets.Lastseen Desc
4 REPLIES 4
saulob
Engaged Sweeper II
Yes. I used the default install.

That's my error.

Is there any alternative for that?

Thanks
marck1024
Engaged Sweeper III
DISCALIMER: I do not work work Lansweeper. I'm just an end user.

Lansweeper has documented the process of moving from SQL Compact Edition to SQL Server. You'd need to be comfortable installing and setting up SQL Server Express, first.

Then you would follow these instructions to move your database over.

https://www.lansweeper.com/knowledgebase/moving-your-database-from-sql-compact-to-sql-server/
saulob
Engaged Sweeper II
Error

[ Token line number = 1,Token line offset = 753,Token in error = Over ]

I was unable to save it 😞
marck1024
Engaged Sweeper III
Are you using SQL Compact for the database? It looks like ROW_NUMBER() is not supported in SQL Compact.

I didn't realize that. I always opt to install applications like this into a separately managed instance of SQL Server Express (which is free) over using the included database option.

Actually, the admin that originally installed Lansweeper in our environment did use SQL Compact and I migrated it over to SQL Server Express. We noticed an immediate performance improvement doing that despite that fact that SQL Express is running on the same system that SQL Compact was. We got a nice performance boost with a software change only.