cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
AlexVlogic
Engaged Sweeper
I created a report to list Custom Asset type with relation but only Windows asset relation listed. I want list also Linux and Vmware guest asset type in report but not works.
Any idea ?

Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
ChildAsset.Custom7 As [Tag Scope de vague],
ChildAsset.AssetTypeIcon10 As Icon2,
ChildAsset.AssetName As hyperlink_name_ChildAsset,
'/asset.aspx?AssetID=' + Cast(ChildAsset.AssetID As nvarchar(10)) As
hyperlink_ChildAsset,
ChildAsset.Description1 As [Active Directory Description],
ChildAsset.Description As Description,
ChildAsset.OSname As OS,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tsysAssetRelationTypes.RelationTypeIcon10 As Icon3,
tsysAssetRelationTypes.ReverseName As RelationType,
ChildAsset.Domain As hyperlink_name_ChildAssetDomain,
'/Report/report.aspx?det=Web50getdomain&title=Computers in domain ' +
ChildAsset.Domain + '&@domain=' + ChildAsset.Domain As
hyperlink_ChildAssetDomain,
ChildAsset.Custom2 As Scope,
ChildAsset.Custom6 As Strategy,
ChildAsset.Custom3 As [Fiscal year],
ChildAsset.Custom4 As [Migration status],
ChildAsset.Custom5 As [MIgration Date completed],
ChildAsset.AssetTypename As [Server OS],
ChildAsset.FQDN As FQDN,
ChildAsset.IPAddress As ChildAssetIPAddress,
Case
When tblAssetRelations.EndDate < GetDate() Then '#dddddd'
Else '#ffffff'
End As backgroundcolor,
ChildAsset.Comments As Comments,
ChildAsset.Custom1 As Environnement,
ChildAsset.Custom17 As [NSX Tag Infra],
ChildAsset.Custom18 As [NSX Tag App],
ChildAsset.Custom19 As [NSX Tag Env],
ChildAsset.Custom20 As [NSX Tag Tiers]
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,
tsysOS.OSname,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tblAssets.IPAddress,
tblAssetCustom.DNSName,
tblAssetCustom.Custom1,
tblAssetCustom.Custom2,
tblAssetCustom.Custom3,
tblAssetCustom.Custom4,
tblAssetCustom.Custom5,
tblAssetCustom.Custom6,
tblAssetCustom.Custom7,
tblAssetCustom.Custom20,
tblAssetCustom.Custom19,
tblAssetCustom.Custom18,
tblAssetCustom.Custom17,
tblAssets.FQDN,
tblAssets.Description,
tblAssetCustom.Comments,
tblADComputers.Description As Description1
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID)
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
Left Outer Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
Order By Case
When tblAssetRelations.EndDate Is Null Then 1
Else 0
End Desc,
tblAssets.AssetName
0 REPLIES 0