Report request

[mastj25]

I need to create a report based off the all Servers canned report, that adds in the location, Model, Hyper-v, and custom3 field. I am basing the field names off of the default asset view for a single item in Lansweeper, not sure exactly how they correlate to the table's names. Any help would be appreciated, my first attempts have yielded results that show the same asset in every location.

[mastj25 · Accepted solution]

Thank you so much! That is almost exactly what I need, and the missing information is my fault for not being cleared. For location I need the IPLocation in the tsysIPLocations table, and I believe I just got it. Thanks again!

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblComputersystem.Lastchanged,
tsysOS.Image As icon,
tHost.AssetName As [Hyper-V host],
tblAssetCustom.Model,
tblAssetCustom.Custom3,
tsysIPLocations.IPLocation
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join TblHyperVGuestNetwork
On tblAssets.Mac = TblHyperVGuestNetwork.MacAddress
Left Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
TblHyperVGuestNetwork.HyperVGuestID
Left Join tblAssets tHost On tHost.AssetID = tblHyperVGuest.AssetID
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Daniel_B]

Hyper-V guests are correlated to their hosts through their MAC address in the table tblHyperVGuestNetwork, which is related to tblHyperVGuest. This information gets updated through rescanning the Hyper-V hosts. The report below lists the data you need:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tblComputersystem.Lastchanged,
  tsysOS.Image As icon,
  tHost.AssetName As [Hyper-V host],
  tblAssetCustom.Location,
  tblAssetCustom.Model,
  tblAssetCustom.Custom3
From tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join TblHyperVGuestNetwork
    On tblAssets.Mac = TblHyperVGuestNetwork.MacAddress
  Left Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
    TblHyperVGuestNetwork.HyperVGuestID
  Left Join tblAssets tHost On tHost.AssetID = tblHyperVGuest.AssetID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName