cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
abdul_hammoud
Engaged Sweeper
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

1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

3 REPLIES 3
RCorbeil
Honored Sweeper II
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
Engaged Sweeper
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
Honored Sweeper II
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.