Windows 10 Pro

[dancarman74]

I'm attempting to create a report containing all Windows 10 Pro operating systems, as we need to upgrade them to Enterprise. I've tried a number of SQL variables related to the OS but so far have had no luck returning a successful report. What's the best way to find all Windows 10 Pro machines on my network?

[brandon_jones]

Try this and see if it gives you the information you need.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblComputersystem.Lastchanged,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

dc74 wrote:
I'm attempting to create a report containing all Windows 10 Pro operating systems, as we need to upgrade them to Enterprise. I've tried a number of SQL variables related to the OS but so far have had no luck returning a successful report. What's the best way to find all Windows 10 Pro machines on my network?

[dancarman74]

Brandon wrote:
Try this and see if it gives you the information you need.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblComputersystem.Lastchanged,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

dc74 wrote:
I'm attempting to create a report containing all Windows 10 Pro operating systems, as we need to upgrade them to Enterprise. I've tried a number of SQL variables related to the OS but so far have had no luck returning a successful report. What's the best way to find all Windows 10 Pro machines on my network?

This gives me all of our Windows machines, I need the report to give me only those running Windows 10 Pro.

[brandon_jones · Accepted solution]

This should now only show Windows 10 pro.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblComputersystem.Lastchanged,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption = 'Microsoft Windows 10 Pro' And
tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

dc74 wrote:

Brandon wrote:
Try this and see if it gives you the information you need.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblComputersystem.Lastchanged,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

dc74 wrote:
I'm attempting to create a report containing all Windows 10 Pro operating systems, as we need to upgrade them to Enterprise. I've tried a number of SQL variables related to the OS but so far have had no luck returning a successful report. What's the best way to find all Windows 10 Pro machines on my network?

This gives me all of our Windows machines, I need the report to give me only those running Windows 10 Pro.

[dancarman74]

Brandon wrote:
This should now only show Windows 10 pro.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblComputersystem.Lastchanged,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption = 'Microsoft Windows 10 Pro' And
tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

dc74 wrote:

Brandon wrote:
Try this and see if it gives you the information you need.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblComputersystem.Lastchanged,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

dc74 wrote:
I'm attempting to create a report containing all Windows 10 Pro operating systems, as we need to upgrade them to Enterprise. I've tried a number of SQL variables related to the OS but so far have had no luck returning a successful report. What's the best way to find all Windows 10 Pro machines on my network?

This gives me all of our Windows machines, I need the report to give me only those running Windows 10 Pro.

Yeah that did it, thanks for the quick assistance!