Notification

Icon
Error

Report containing asset user relation + asset to asset relation

Posted: Friday, October 18, 2019 1:26:17 PM(UTC)
ArjanvA

ArjanvA

Member Original PosterPosts: 3
0
Like
Good afternoon,

I'm trying to build a report on a certain hardware we have. What i'm tring to achieve is that we have an overview of all of these devices and the Simcard theyre holding. (filtered on manufacturer and model)
The overview must include some hardware specifics of the parent asset as well as some specifics of the child asset (asset to asset relation) as well as the current owner (asset to user relation).
I've attempted to create this but fail to get hardware that does not have a asset to asset relation or asset to user relation. It must have both to come up in the results. I've been trying to wrap my head around the joins but cannot get it to work. Does anyone have experience with this and able to point me in the right direction?

What I have so far:


Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber As scanner_serial,
  tblAssetCustom.Custom7 As scanner_imei,
  tblAssetCustom.PurchaseDate As scanner_purchase_date,
  tblAssetCustom.Warrantydate As scanner_warranty_date,
  tsysAssetRelationTypes.RelationTypeIcon10 As Icon3,
  tsysAssetRelationTypes.ReverseName As RelationType,
  ChildAsset.AssetName As hyperlink_name_ChildAsset,
  tblAssetRelations.Comments As RelationComments,
  tblAssetRelations.StartDate As RelationStartDate,
  tblAssetRelations.EndDate As RelationEndDate,
  ChildAsset.AssetTypeIcon10 As Icon2,
  '/asset.aspx?AssetID=' + Cast(ChildAsset.AssetID As nvarchar(10)) As
  hyperlink_ChildAsset,
  '/Report/report.aspx?det=Web50getdomain&title=Computers in domain ' +
  ChildAsset.Domain + '&@domain=' + ChildAsset.Domain As
  hyperlink_ChildAssetDomain,
  ChildAsset.AssetTypename As ChildAssetType,
  Case
    When tblAssetRelations.EndDate < GetDate() Then '#dddddd'
    Else '#ffffff'
  End As backgroundcolor,
  tblADusers.Displayname
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblAssetRelations On
    tblAssets.AssetID = tblAssetRelations.ParentAssetID
  Inner Join tsysAssetRelationTypes On tblAssetRelations.Type =
    tsysAssetRelationTypes.RelationTypeID
  Inner Join (Select Top 1000000 tblAssets.AssetID,
        tblAssets.AssetName,
        tsysAssetTypes.AssetTypeIcon10,
        tblAssets.Domain,
        tsysAssetTypes.AssetTypename,
        tblAssets.IPAddress
      From tblAssets
        Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
        Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
          tblAssets.Assettype
      Where tblAssetCustom.State = 1) As ChildAsset On
    tblAssetRelations.ChildAssetID = ChildAsset.AssetID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Inner Join tblAssetUserRelations On
    tblAssets.AssetID = tblAssetUserRelations.AssetID
  Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
Where tblAssetCustom.Manufacturer = 'Zebra' And tblAssetCustom.Model = 'TC57HO'
  And tblState.Statename = 'Active'
Order By Case
    When tblAssetRelations.EndDate Is Null Then 1
    Else 0
  End Desc,
  RelationEndDate Desc,
  RelationStartDate Desc,
  tblAssets.AssetName
Bruce.B
#1Bruce.B Member Administration Posts: 537  
posted: 10/22/2019 7:35:09 PM(UTC)
If you want results that have just an asset<->asset relation or just an asset<->user relation you'll need to change your joins of the asset/user relation tables to left joins. You may need to modify your where clause afterwards though if you get too many results.

Active Discussions

Lansweeper Physical Disk Size
by  RC62N   Go to last post Go to first unread
Last post: Yesterday at 5:18:38 PM(UTC)
Lansweeper AD User report
by  GMcCartney  
Go to last post Go to first unread
Last post: Yesterday at 12:07:22 PM(UTC)
Lansweeper PC Build Report Missing Software
by  Andy.S   Go to last post Go to first unread
Last post: Yesterday at 10:28:40 AM(UTC)
Lansweeper Creat report ALL office (ms officce,libreoffice,openoffice)
by  RC62N  
Go to last post Go to first unread
Last post: 11/15/2019 9:58:33 PM(UTC)
Lansweeper How to Report on Dynamic Asset Groups
by  Rob-CD   Go to last post Go to first unread
Last post: 11/15/2019 11:49:22 AM(UTC)
Lansweeper Custom All Assets Report Help
by  Matt Fuhrman  
Go to last post Go to first unread
Last post: 11/14/2019 3:55:25 PM(UTC)
Report Center Calculate your network's computing power
by  GBInnovation   Go to last post Go to first unread
Last post: 11/13/2019 12:11:18 PM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  JacobH  
Go to last post Go to first unread
Last post: 11/12/2019 5:28:37 PM(UTC)