Report OS version count by IP Location

[servicedesk]

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

[servicedesk]

AZHockeyNut that's perfect. Thanks a lot you are amazing 🙂

[AZHockeyNut]

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]

Thank you, but i'm looking for something like:

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


Any ideas?

[AZHockeyNut]

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