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: 8
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 Is there a limit to the number of incoming mail domains?
by  DrewT   Go to last post Go to first unread
Last post: Today at 1:33:44 PM(UTC)
Lansweeper License renewal - but why
by  mrusso  
Go to last post Go to first unread
Last post: Yesterday at 5:01:47 PM(UTC)
Lansweeper Deployment Package Error Message
by  Brandon   Go to last post Go to first unread
Last post: Yesterday at 2:04:25 PM(UTC)
Lansweeper Asset Type Mail Server
by  MarkPayton  
Go to last post Go to first unread
Last post: Yesterday at 1:03:54 PM(UTC)
Lansweeper Upgrade Win 10 build to version 2004
by  Jean-FB   Go to last post Go to first unread
Last post: 10/28/2020 7:34:29 PM(UTC)
Lansweeper Uptime only shows Standby
by  Gst4r  
Go to last post Go to first unread
Last post: 10/28/2020 4:19:33 PM(UTC)
Lansweeper Excepciones
by  Pablo   Go to last post Go to first unread
Last post: 10/27/2020 7:35:21 PM(UTC)
Lansweeper Help desk API
by  Skylar@Hennig  
Go to last post Go to first unread
Last post: 10/27/2020 5:01:18 PM(UTC)