Notification

Icon
Error

Windows 10 Pro

Posted: Friday, April 9, 2021 7:37:51 PM(UTC)
dc74

dc74

Member Original PosterPosts: 5
0
Like
This issue has been solved! Click here to view the solution
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
#1Brandon Member Posts: 139  
posted: 4/9/2021 8:12:17 PM(UTC)
Try this and see if it gives you the information you need.

Quote:
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


Originally Posted by: dc74 Go to Quoted Post
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?


dc74
#2dc74 Member Original PosterPosts: 5  
posted: 4/9/2021 8:15:19 PM(UTC)
Originally Posted by: Brandon Go to Quoted Post
Try this and see if it gives you the information you need.

Quote:
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


Originally Posted by: dc74 Go to Quoted Post
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
#3Brandon Member Posts: 139  
posted: 4/9/2021 8:19:29 PM(UTC)
This should now only show Windows 10 pro.

Quote:
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


Originally Posted by: dc74 Go to Quoted Post
Originally Posted by: Brandon Go to Quoted Post
Try this and see if it gives you the information you need.

Quote:
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


Originally Posted by: dc74 Go to Quoted Post
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.


dc74
#4dc74 Member Original PosterPosts: 5  
posted: 4/9/2021 8:42:31 PM(UTC)
Originally Posted by: Brandon Go to Quoted Post
This should now only show Windows 10 pro.

Quote:
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


Originally Posted by: dc74 Go to Quoted Post
Originally Posted by: Brandon Go to Quoted Post
Try this and see if it gives you the information you need.

Quote:
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


Originally Posted by: dc74 Go to Quoted Post
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!

Active Discussions

Lansweeper Using tblO365User report for devices Out of warranty
by  QueryLSTech   Go to last post Go to first unread
Last post: Yesterday at 5:15:37 PM(UTC)
Lansweeper Duplicate assets, random monitor unique keys
by  kloosterd  
Go to last post Go to first unread
Last post: Yesterday at 1:00:34 PM(UTC)
Lansweeper Scanning - nothing appears in the queue
by  LS IT Admins   Go to last post Go to first unread
Last post: Yesterday at 11:08:22 AM(UTC)
Lansweeper Broken scanning of AD
by  LS IT Admins  
Go to last post Go to first unread
Last post: Yesterday at 10:59:35 AM(UTC)
Lansweeper New ticket auto-assignment & default state
by  Brandon   Go to last post Go to first unread
Last post: 5/13/2021 5:21:31 PM(UTC)
Lansweeper Automatic Follow-Up for Tickets
by  Francis Lee Mondia - Endace  
Go to last post Go to first unread
Last post: 5/12/2021 11:06:51 PM(UTC)
Lansweeper Can't see devices on Lansweeper
by  vqT4cDoP9iXyMZwoDUWU   Go to last post Go to first unread
Last post: 5/12/2021 8:33:21 PM(UTC)
Lansweeper LAPS managed password
by  SystemAdmin  
Go to last post Go to first unread
Last post: 5/12/2021 6:08:42 PM(UTC)