→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dancarman74
Engaged Sweeper II
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?
1 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.


View solution in original post

4 REPLIES 4
brandon_jones
Champion Sweeper III
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?


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.
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.


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!