Notification

Icon
Error

Monitors by relation including tblMonitor data

Posted: Wednesday, March 9, 2016 9:57:10 PM(UTC)
esr

esr

Member Alpha Tester Original PosterPosts: 69
0
Like
This issue has been solved! Click here to view the solution
There are days I can't SQL my way out of a paper bag. Today appears to be one of those....

Quick & basic monitor report also finds the build in screens on Laptops, which I don't want. So, I've got a report using AssetRelations & AssetRelationTypes that works perfectly- the counts are exactly right.

I'd like to add data from tblMonitor as well so I can include MonitorModel and ManufactureDate.

I'm linking tblMonitor from tblAssets1, which is tied to the relation data, matching tblAssets1.AssetID to tblMonitor.MonitorID- Again, the counts are right, but only a portion of the tblMonitor data appears. I get the same results in SMS. I think it's just a join issue, but was unable to resolve it myself.

The query below includes the tblMonitor items and screenshot of results-

Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tsysAssetRelationTypes.Name As Relation,
  tblAssets1.AssetName As Monitor,
  tblMonitor.ManufacturedDate,
  tblMonitor.MonitorModel
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetRelations On tblAssetRelations.ParentAssetID =
    tblAssets.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetRelations.Type
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
    tblAssetRelations.ChildAssetID
  Inner Join tsysAssetTypes tsysAssetTypes1 On tsysAssetTypes1.AssetType =
    tblAssets1.Assettype
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblMonitor On tblMonitor.MonitorID = tblAssets1.AssetID
Where tsysAssetRelationTypes.Name = 'connected to' And
  tsysAssetTypes1.AssetTypename = 'monitor' And tblADComputers.OU Like '%HQN%'
Order By tblAssets.AssetName
esr attached the following image(s):
Screenshot - 3_9_2016  Wed , 3_51_24 PM.jpg
Screenshot - 3_9_2016  Wed , 3_50_44 PM.jpg
Susan.A
#1Susan.A Member Administration Posts: 1,536  
posted: 3/18/2016 3:39:45 PM(UTC)
The AssetID of the monitor asset in tblAssets doesn't actually correspond with the MonitorID in tblMonitor, which is why you're getting inaccurate results. You can try something like this to link the tables instead:
Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tsysAssetRelationTypes.Name As Relation,
  tblAssets1.AssetName As Monitor,
  tblMonitor.ManufacturedDate,
  tblMonitor.MonitorModel
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetRelations On tblAssetRelations.ParentAssetID =
    tblAssets.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetRelations.Type
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
    tblAssetRelations.ChildAssetID
  Inner Join tsysAssetTypes tsysAssetTypes1 On tsysAssetTypes1.AssetType =
    tblAssets1.Assettype
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblMonitor On 'mon:' + tblMonitor.MonitorManufacturer + ':' +
    tblMonitor.SerialNumber = tblAssets1.AssetUnique
Where tsysAssetRelationTypes.Name = 'connected to' And
  tsysAssetTypes1.AssetTypename = 'monitor' And tblADComputers.OU Like '%HQN%'
Order By tblAssets.AssetName

Active Discussions

Lansweeper Assigned Assets
by  anpatterson03   Go to last post Go to first unread
Last post: Yesterday at 11:50:46 PM(UTC)
Lansweeper Troubleshooting Helpdesk
by  brodiemac-too  
Go to last post Go to first unread
Last post: Yesterday at 8:46:21 PM(UTC)
Lansweeper Make agents regular users
by  JP-CPC   Go to last post Go to first unread
Last post: Yesterday at 7:32:54 PM(UTC)
Lansweeper Lansweeper updates without stopping whole IIS service
by  Hstr  
Go to last post Go to first unread
Last post: Yesterday at 10:32:17 AM(UTC)
Lansweeper Active Directory Groups not scanned properly
by  SCH   Go to last post Go to first unread
Last post: Yesterday at 7:10:23 AM(UTC)
Lansweeper Ticket Content Default Value
by  CPG  
Go to last post Go to first unread
Last post: 7/28/2021 8:45:12 PM(UTC)
Lansweeper New status to mimic Closed
by  chris.anderson   Go to last post Go to first unread
Last post: 7/28/2021 7:14:27 PM(UTC)
Lansweeper Change Management - Voting and Tracking
by  brodiemac-too  
Go to last post Go to first unread
Last post: 7/28/2021 2:48:01 PM(UTC)