cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
servicedesk
Champion Sweeper II
Hello

My company have over 3000 assets and several Sites/IPLocations.

Due the end of support of Windows 7 for 2020 wants to migrate to Windows 10 and would be super handly have report with a count of Windows 7, Windows 10 by IP Location.

This way we could have an idea of how many devices we need to migrate per site. Once the project start we can also check time to time this same report and review the evolution of migration, for example month to month.


Thank you
4 REPLIES 4
servicedesk
Champion Sweeper II
AZHockeyNut that's perfect. Thanks a lot you are amazing 🙂
AZHockeyNut
Champion Sweeper III
how about this?


Select Top 1000000
tsysOS.OSname As OS,
tsysIPLocations.IPLocation,
count (tblassets.assetid) as Installs
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.State = 1 and (tsysos.OSname like '%win%7%' or tsysos.OSname like '%win%10%')
group by iplocation, tsysOS.OSname

servicedesk
Champion Sweeper II
Thank you, but i'm looking for something like:

SiteA - 1000 W7
SiteB - 200 W7
SiteC - 100 W7


Any ideas?
AZHockeyNut
Champion Sweeper III
Might be a few more columns than you want but there is a report I have that will give you that data. I added a where clause to filter in win 7 and 10.


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname As OS,
tblOperatingsystem.Caption,
tblAssets.SP As SP,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.State = 1 and (tsysos.OSname like '%win%7%' or tsysos.OSname like '%win%10%')
Order By OS,
tblAssets.AssetName,
tblOperatingsystem.Caption