Notification

Icon
Error

Multiple Monitors - All Basic information of computer with attached monitors

Posted: Friday, August 21, 2020 9:19:07 PM(UTC)
RayPamps23

RayPamps23

Member Original PosterPosts: 11
0
Like
Hi Guys,
Anyone that can help me for my report, basically i want to display all basic info of my asset also the attached monitors per computer(on my case maximum monitor attached to a computer is 3)... having hard time to eliminate the duplicate entries
thanks in advance.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblADusers.Username,
tblADusers.Displayname,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Mac,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tsysOS.OSname,
tblOperatingsystem.Version As Build,
Case tblOperatingsystem.Version
When '10.0.10240' Then '1507'
When '10.0.10586' Then '1511'
When '10.0.14393' Then '1607'
When '10.0.15063' Then '1703'
When '10.0.16299' Then '1709'
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 '6.1.7601' Then 'Win7 SP1'
Else '?'
End As Version,
tblAssetCustom.Warrantydate,
tblAssets.Lastseen,
Stuff((Select ', ' + Cast(t2.MonitorModel As varchar(10)) From tblMonitor t2
Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') Monitors,
countMonitor.numberMonitors As [Number monitors],
Stuff((Select ', ' + Cast(t2.SerialNumber As varchar(10)) From tblMonitor t2
Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') SerialNumbers,
Stuff((Select ', ' + Cast(t2.MonitorManufacturer As varchar(10))
From tblMonitor t2
Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2,
'') MonitorManufacturer
From tblAssets
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblMonitor t1 On tblAssets.AssetID = t1.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain
Inner Join (Select tblMonitor.AssetID,
Count(tblMonitor.MonitorID) As numberMonitors
From tblMonitor
Group By tblMonitor.AssetID) countMonitor On countMonitor.AssetID =
tblAssets.AssetID
Where tblOperatingsystem.Caption Not Like '%Server%'
Order By tblAssets.AssetName

Active Discussions

Lansweeper Ticket Content Default Value
by  CPG   Go to last post Go to first unread
Last post: Yesterday at 8:45:12 PM(UTC)
Lansweeper New status to mimic Closed
by  chris.anderson  
Go to last post Go to first unread
Last post: Yesterday at 7:14:27 PM(UTC)
Lansweeper Active Directory Groups not scanned properly
by  cross_eur   Go to last post Go to first unread
Last post: Yesterday at 5:45:00 PM(UTC)
Lansweeper Change Management - Voting and Tracking
by  brodiemac-too  
Go to last post Go to first unread
Last post: Yesterday at 2:48:01 PM(UTC)
Lansweeper No incoming Mails after update 8.4.100.9
by  EDELL   Go to last post Go to first unread
Last post: Yesterday at 2:38:50 PM(UTC)
Lansweeper Anti-Virus on Mac
by  Ian.Prentice  
Go to last post Go to first unread
Last post: Yesterday at 9:30:32 AM(UTC)
Lansweeper how to scan intune managed win10 clients?
by  brodiemac-too   Go to last post Go to first unread
Last post: 7/27/2021 9:17:07 PM(UTC)
Lansweeper Sort by in Helpdesk
by  brodiemac-too  
Go to last post Go to first unread
Last post: 7/27/2021 9:15:05 PM(UTC)