→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mastj25
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
mastj25
Engaged Sweeper
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

View solution in original post

2 REPLIES 2
mastj25
Engaged Sweeper
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
Lansweeper Alumni
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