Notification

Icon
Error

Adding computer type to Windows 10 report - Windows 10 report that we would like to also show if asset is desktop or laptop.

Posted: Thursday, April 8, 2021 7:19:41 PM(UTC)
chenegh

chenegh

Member Original PosterPosts: 5
1
Like
We have a Windows 10 report that we would like to also show whether each asset is a desktop or a laptop. What do we need to add to the report to show these?

Here is the Windows 10 report we have:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblOperatingsystem.Lastchanged,
tsysOS.Image As icon,
tblOperatingsystem.Version As Build,
Case tblOperatingsystem.Version
When '10.0.14393' Then '1607'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
When '10.0.18363' Then '1909'
When '10.0.19041' Then '2004'
When '10.0.19042' Then '20H2'
Else '?'
End As Version,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.State
From tblAssets
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1
Order By Build,
tblAssets.AssetUnique
Brandon
#1Brandon Member Posts: 139  
posted: 4/8/2021 9:16:40 PM(UTC)
See if this will work for you:

Quote:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblOperatingsystem.Lastchanged,
tsysOS.Image As icon,
tblOperatingsystem.Version As Build,
Case tblOperatingsystem.Version
When '10.0.14393' Then '1607'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
When '10.0.18363' Then '1909'
When '10.0.19041' Then '2004'
When '10.0.19042' Then '20H2'
Else '?'
End As Version,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.State,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lasttried,
Case
When tblPortableBattery.AssetID Is Null Then 'Desktop'
Else 'Laptop'
End As [Desktop/Laptop]
From tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join lansweeperdb.dbo.tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tsysOS.OSname = 'Win 10' And tblAssets.Lastseen Is Not Null And
tblAssets.Lastseen <> '' And (tblAssetCustom.Model Is Null Or
tblAssetCustom.Model = '' Or tblAssetCustom.Model Not Like '%Virtual%') And
tblState.Statename = 'Active' And tsysAssetTypes.AssetTypename In ('Windows',
'Windows CE')
Order By tblAssets.Domain,
tblAssets.AssetName



Originally Posted by: chenegh Go to Quoted Post
We have a Windows 10 report that we would like to also show whether each asset is a desktop or a laptop. What do we need to add to the report to show these?

Here is the Windows 10 report we have:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblOperatingsystem.Lastchanged,
tsysOS.Image As icon,
tblOperatingsystem.Version As Build,
Case tblOperatingsystem.Version
When '10.0.14393' Then '1607'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
When '10.0.18363' Then '1909'
When '10.0.19041' Then '2004'
When '10.0.19042' Then '20H2'
Else '?'
End As Version,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.State
From tblAssets
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1
Order By Build,
tblAssets.AssetUnique


chenegh
#2chenegh Member Original PosterPosts: 5  
posted: 4/19/2021 5:58:13 PM(UTC)
Thanks! This works for the most part. It still seems to show Surface tablets as desktops on the report. Any way to correct that?
Brandon
#3Brandon Member Posts: 139  
posted: 4/19/2021 6:38:45 PM(UTC)
I edited the query. Surface Tablets now show up as laptops and not desktops.

Quote:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblBattery.Win32_Batteryid Is Null Then 'Desktop'
Else 'Laptop'
End As [Desktop/Laptop]
From tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Inner Join lansweeperdb.dbo.tblBattery On tblAssets.AssetID =
tblBattery.AssetID
Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or
tblAssetCustom.Model Not Like '%Virtual%') And
tblAssets.Lastseen Is Not Null And tblAssets.Lastseen <> '' And
tblState.Statename = 'Active' And tsysAssetTypes.AssetTypename In ('Windows',
'Windows CE')
Order By tblAssets.Domain,
tblAssets.AssetName


Originally Posted by: chenegh Go to Quoted Post
Thanks! This works for the most part. It still seems to show Surface tablets as desktops on the report. Any way to correct that?


Active Discussions

Lansweeper Creating a count
by  Brianne   Go to last post Go to first unread
Last post: Yesterday at 6:12:49 PM(UTC)
Lansweeper Some reports shows dublicated assets
by  Andy.S  
Go to last post Go to first unread
Last post: Yesterday at 9:46:37 AM(UTC)
Lansweeper Adding "multi-part" Conditions to Reports
by  Brandon   Go to last post Go to first unread
Last post: 5/13/2021 1:52:17 PM(UTC)
Report Center Microsoft Outlook email bug EX255650
by  Esben.D  
Go to last post Go to first unread
Last post: 5/12/2021 2:30:16 PM(UTC)
Lansweeper Patch Tuesday May 2021
by  Esben.D   Go to last post Go to first unread
Last post: 5/11/2021 8:13:06 PM(UTC)
Lansweeper Report showing only Wi-Fi Devices and MAC addresses
by  Andy.S  
Go to last post Go to first unread
Last post: 5/11/2021 2:23:24 PM(UTC)
Lansweeper Modifying Purchase Date / Yearly Refresh Report
by  Cripple.Zero  
Go to last post Go to first unread
Last post: 5/7/2021 7:06:47 PM(UTC)