All Asset Report - Almost where I need it

[Niley]

Greetings. I have an all asset report almost where I need it but I need a few tweaks that I can't figure out.

I need the following items added to my report.

On the monitors that are listed in this report I want them to tie back to the computer they are associated with so that I can see the "IP Locations" and "Username" for the monitor. The Computer they report through should have this information.

The other item I need to see on this report is the "Asset Location" which is set by the "Thumbtack" location field.

Here is my SQL

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As [Asset Type],
tblAssets.Username,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.IPAddress As [IP address],
tsysIPLocations.IPLocation As [IP location],
tblState.Statename As State,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Date],
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.Custom1 As Asset#,
tblAssetCustom.Custom2 As [Deploy Date],
Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
End As [not affected by cleanup options],
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Order By tblAssets.AssetName

[Susan_A · Accepted solution]

I moved this from the Report Center to Report Requests & Questions. Please note that the Report Center is for posting ready-to-use reports, not questions. For the modified report you're after, please use the SQL query below.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  Coalesce(tblAssets.Username, MonitorQuery.Username) As Username,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssets.IPAddress As [IP address],
  Coalesce(tsysIPLocations.IPLocation, MonitorQuery.IPLocation) As
  [IP location],
  tblState.Statename As State,
  tblAssets.Lastseen,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Date],
  tblAssetCustom.Contact,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Custom1 As Asset#,
  tblAssetCustom.Custom2 As [Deploy Date],
  Case When tblAssetCustom.PreventCleanup = 1 Then 'yes' Else 'no'
  End As [not affected by cleanup options],
  tsysAssetTypes.AssetTypeIcon10 As icon,
  LocationQuery.Location
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join (Select Top 1000000 tblAssetRelations.ChildAssetID,
    tblAssets.AssetName As Location
  From tblAssetRelations
    Inner Join tblAssets On tblAssets.AssetID = tblAssetRelations.ParentAssetID
    Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
      tblAssetRelations.Type
  Where tsysAssetRelationTypes.Name = 'is located in') LocationQuery
    On LocationQuery.ChildAssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblAssets.AssetID,
    tblAssets1.Username As Username,
    tsysIPLocations.IPLocation
  From tblAssetRelations
    Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
      tblAssetRelations.Type
    Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
      tblAssetRelations.ParentAssetID
    Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets1.IPNumeric
      And tsysIPLocations.EndIP >= tblAssets1.IPNumeric
    Inner Join tblAssets On tblAssetRelations.ChildAssetID = tblAssets.AssetID
    Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Where tsysAssetRelationTypes.Name = 'connected to' And
    tsysAssetTypes.AssetTypename = 'monitor') MonitorQuery
    On MonitorQuery.AssetID = tblAssets.AssetID
Order By tblAssets.AssetName