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

[abdul_hammoud]

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

[mfassler]

I need regular inventory by location.  While PC's have a location (set by Active Directory OU), it's still leaving out all docking stations and monitors, even though the information is in there.  How can those devices all be automatically bulk-set to match the connected computer?

[RCorbeil]

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.

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

[abdul_hammoud]

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 ?

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

[RCorbeil · Accepted solution]

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.

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.