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


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
1 REPLY 1
Bruce_B
Lansweeper Alumni
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.