‎01-22-2019 10:37 AM
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblVmwareGuest.Ipv4Address,
tblAssets1.AssetName As Host,
tblAssets1.IPAddress As HostIP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID = tblVmwareInfo.AssetID
Where tblState.Statename = 'Active'
Order By tblVmwareGuest.Ipv4Address,
tblAssets.AssetName
‎11-21-2019 07:48 PM
‎09-17-2019 09:44 AM
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblVmwareGuest.Ipv4Address,
case when tblAssets1.AssetName is not null then tblAssets1.AssetName else hvasset.assetname end As Host,
case when tblAssets1.IPAddress is not null then tblAssets1.IPAddress else hvasset.IPAddress end As HostIP,
case when tblassets1.assetname is not null then 'VMware' else 'HyperV' end as Type,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysAssetTypes.AssetTypeIcon10 As icon
From tblAssets
left outer Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID = tblVmwareInfo.AssetID
OUTER apply
(SELECT TOP 1 *
FROM tblhypervguest js
WHERE js.Name = tblassets.AssetName
ORDER BY js.Lastchanged DESC) js
left outer join tblassets AS hvasset ON hvasset.AssetID = js.AssetID
Where tblState.Statename = 'Active' and ( hvasset.assetid is not null or tblAssets1.assetid is not null)
Order By tblVmwareGuest.Ipv4Address,
tblAssets.AssetName
‎03-28-2019 09:14 PM
‎03-06-2019 07:57 PM
‎02-25-2019 01:56 PM
‎02-22-2019 06:00 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now