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 Rest API for Scanning
by  Brian Smith   Go to last post Go to first unread
Last post: Today at 7:35:27 PM(UTC)
Lansweeper Language translation not working anymore
by  Florian  
Go to last post Go to first unread
Last post: Today at 6:03:19 PM(UTC)
Lansweeper Use current user for scanning credentials?
by  pryan67   Go to last post Go to first unread
Last post: Today at 6:01:33 PM(UTC)
Lansweeper Bluetooth Info
by  FrankSc  
Go to last post Go to first unread
Last post: Today at 5:42:59 PM(UTC)
Lansweeper Question about Scanning Assets and Password Encryption
by  FrankSc   Go to last post Go to first unread
Last post: Today at 5:23:05 PM(UTC)
Lansweeper How to enable HTTPS in ver 6.0.150.60
by  Duncan.Miles  
Go to last post Go to first unread
Last post: Today at 1:11:19 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: Yesterday at 3:22:29 PM(UTC)
Lansweeper MS Edge Chromium LanSweeper Extension development
by  Slim D  
Go to last post Go to first unread
Last post: 1/15/2021 11:39:29 AM(UTC)