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 Check if Netbios is disabled over TCP/IP
by  Wesker305  
Go to last post Go to first unread
Last post: Yesterday at 5:43:02 PM(UTC)
Lansweeper Ticket Info Meter incorrect
by  pfalls   Go to last post Go to first unread
Last post: Yesterday at 4:32:21 PM(UTC)
Lansweeper OS: Not latest Build of Windows 10 report
by  RKCar  
Go to last post Go to first unread
Last post: Yesterday at 3:08:52 PM(UTC)
Lansweeper Windows Defender AV
by  Mikey!   Go to last post Go to first unread
Last post: Yesterday at 2:48:54 PM(UTC)
Lansweeper Allow Users and cc Users to edit ticket form field
by  eoinpryan  
Go to last post Go to first unread
Last post: Yesterday at 12:41:05 PM(UTC)
Lansweeper Mobile App
by  jdvuyk   Go to last post Go to first unread
Last post: Yesterday at 2:28:56 AM(UTC)
Lansweeper Duplicate Asset 1 Mac address, 1 domain\computer\1
by  jstrong71  
Go to last post Go to first unread
Last post: 11/12/2019 9:16:58 PM(UTC)