Custom Report - Relationships based

[mtterry13]

I have multiple environments that I'm supporting. I have created an asset type called "Application Environments". I have then attached various Assets (Windows/Linux). I would like a report that shows each Application Environment with each of the children with the Childs IP, Description, Name, and OS.

Any assistance would be helpful.

Ideally

Applications Environment - X
Child 1 Name , OS, IP, Description
Child 2 Name , OS, IP, Description
Child 3 Name , OS, IP, Description
Child 4 Name , OS, IP, Description
Applications Environment - Y
Child 1 Name , OS, IP, Description
Child 2 Name , OS, IP, Description
Child 3 Name , OS, IP, Description
Child 4 Name , OS, IP, Description

etc.

Thanks in advance.

[mtterry13]

I'm not super talented with SQL.. I came up with this though..

Select
(select AssetName from lansweeperdb.dbo.tblAssets where AssetID = TAP.AssetID) as Environment,
(select AssetName from lansweeperdb.dbo.tblAssets where AssetID = TAC.AssetID) as Server,
(select IPAddress from lansweeperdb.dbo.tblAssets where AssetID = TAC.AssetID) as ServerIP,
(select Caption from lansweeperdb.dbo.tblOperatingsystem where AssetID = TAC.AssetID) as WindowsOperatingSystem,
AR.Comments as Comments
from lansweeperdb.dbo.tblAssetRelations AS AR
FULL JOIN lansweeperdb.dbo.tblAssets TAP on AR.ParentAssetID = TAP.AssetID
FULL JOIN lansweeperdb.dbo.tblAssets TAC on AR.ChildAssetID = TAC.AssetID
FULL JOIN lansweeperdb.dbo.tblOperatingsystem OS on AR.ChildAssetID = OS.AssetID
where TAP.AssetName like'%%'
ORDER by
Environment;

Changing the Like for the Asset Prefix's im using for my custom assets. EDIT : Included OS column.