Notification

Icon
Error

How to list Monitors with their connected PC and Monitor Asset ID

Posted: Thursday, February 18, 2021 10:07:15 PM(UTC)
A H

A H

Member Original PosterPosts: 3
0
Like
This issue has been solved! Click here to view the solution
Hello,
I have the following simple query that is listing all monitors with their connected PC's.
However I can't get in the list the Asset ID of the monitor ? I need the Name, serial number and Asset ID for both the monitor and the PC.




Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID As pc_asset_id,
tblAssets.AssetName As pc_asset_name,
tblMonitor.AssetID As monitor_asset_id,
tblMonitor.MonitorManufacturer As [Manufacturer Monitor],
tblMonitor.MonitorModel,
tblMonitor.SerialNumber,
tblMonitor.ManufacturedDate,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tblAssetCustom.Custom1 As asset_tag,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblMonitor On tblMonitor.AssetID = tblAssets.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
Where tblAssetCustom.State = 1
Order By pc_asset_name
RC62N
#1RC62N Member Posts: 522  
posted: 2/19/2021 6:06:01 PM(UTC)
Monitor data is collected and stored in more than one location in the database. If you want the AssetID value for a monitor, you need to pull the monitor data from tblAssets, not tblMonitor. The data recorded in each can be slightly different, depending on the table's data source.

Either of these will get you started pulling the monitor data from tblAssets. The result is the same, only one filters by the asset type descriptive name while the other skips that and just uses the AssetType numeric value. (You can "SELECT * FROM tSysAssetTypes" to see the full list.) Either way, because you're linking tblAssets to tblAssets, you need to use tblAssetRelations for the asset-to-asset connection.
Code:
SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  monitor.AssetID,
  monitor.AssetName
FROM
  tblAssets
  INNER JOIN tSysAssetTypes AS aType ON aType.AssetType = tblAssets.AssetType AND aType.AssetTypename = 'Windows'
  INNER JOIN tblAssetRelations ON tblAssetRelations.ParentAssetID = tblAssets.AssetID
  INNER JOIN tblAssets AS monitor ON monitor.AssetID = tblAssetRelations.ChildAssetID
  INNER JOIN tSysAssetTypes AS mType ON mType.AssetType = monitor.AssetType AND mType.AssetTypename = 'Monitor'
ORDER BY
  tblAssets.AssetName,
  monitor.AssetName


SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  monitor.AssetID,
  monitor.AssetName
FROM
  tblAssets
  INNER JOIN tblAssetRelations ON tblAssetRelations.ParentAssetID = tblAssets.AssetID
  INNER JOIN tblAssets AS monitor ON monitor.AssetID = tblAssetRelations.ChildAssetID AND monitor.AssetType = 208  -- monitor
WHERE
  tblAssets.AssetType = -1  -- Windows
ORDER BY
  tblAssets.AssetName,
  monitor.AssetName

You should be able to build up your query using either of those as a base.
A H
#2A H Member Original PosterPosts: 3  
posted: 2/22/2021 4:20:36 PM(UTC)
Thank you this is great. So in conclusion there was no need for the tblMonitor table. But what if I wanted to include monitors EVEN if they had no relationship ? I modified it to look something like this would this work ?

Code:
Select Top 1000000 tblAssets.AssetName As pc_asset_name,
  tblAssets.AssetID As pc_asset_id,
  monitor.AssetName As monitor_asset_name,
  monitor.AssetID As monitor_asset_id,
  tblAssetCustom.Custom1 As monitor_asset_tag,
  tblAssetCustom.Serialnumber As monitor_serial_number
From tblAssets
  Inner Join tblAssetRelations On tblAssetRelations.ParentAssetID =
    tblAssets.AssetID
  Inner Join tblAssets As monitor On
    monitor.AssetID = tblAssetRelations.ChildAssetID And monitor.Assettype = 208
  Inner Join tblAssetCustom On monitor.AssetID = tblAssetCustom.AssetID
Where (tblAssets.Assettype = -1 Or tblAssets.Assettype = 208)
Order By pc_asset_name,
  monitor_asset_name
RC62N
#3RC62N Member Posts: 522  
posted: 2/23/2021 3:33:58 PM(UTC)
If you want to pull a list of monitors and their associated computers rather than computers and their associated monitors, that changes things. First, you'll want to make the monitors the main focus. Second, you'll want to LEFT JOIN rather than INNER JOIN to the computers. INNER JOIN will automatically filter out results where there is nothing to join to, i.e. a monitor with no associated computer.
Code:
SELECT
  pc.AssetID AS [PC AssetID],
  pc.AssetName AS [PC AssetName],
  monitor.AssetID AS [Monitor AssetID],
  monitor.AssetName AS [Monitor AssetName],
  monitorCustom.Model AS [Monitor Model],
  monitorCustom.Serialnumber As [Monitor Serial]
FROM
  tblAssets AS monitor
  LEFT JOIN tblAssetRelations ON tblAssetRelations.ChildAssetID = monitor.AssetID
  LEFT JOIN tblAssets AS pc ON pc.AssetID = tblAssetRelations.ParentAssetID AND pc.AssetType = -1
  INNER JOIN tblAssetCustom as monitorCustom ON monitorCustom.AssetID = monitor.AssetID
WHERE
  monitor.AssetType = 208
ORDER BY
  pc.AssetName,
  monitor.AssetName,
  monitorCustom.SerialNumber

Active Discussions

Lansweeper USB printers with toner levels
by  vstevo   Go to last post Go to first unread
Last post: Today at 3:15:35 PM(UTC)
Lansweeper Changing picture in Assets, New Location error.
by  jmani  
Go to last post Go to first unread
Last post: Today at 2:44:27 PM(UTC)
Lansweeper Showing all assets without a department
by  Andy.S   Go to last post Go to first unread
Last post: Yesterday at 8:30:43 AM(UTC)
Lansweeper Patch Volume Across Fleet
by  darren.kimber  
Go to last post Go to first unread
Last post: Yesterday at 8:29:33 AM(UTC)
Lansweeper Count by Department
by  Brandon   Go to last post Go to first unread
Last post: 4/21/2021 7:42:00 PM(UTC)
Lansweeper Adding computer type to Windows 10 report
by  Brandon   Go to last post Go to first unread
Last post: 4/19/2021 6:38:45 PM(UTC)
Report Center Hardware Inventory
by  Cori  
Go to last post Go to first unread
Last post: 4/16/2021 4:05:02 PM(UTC)