‎01-09-2019 09:33 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.NrProcessors As [Socket Count],
tblVmwareInfo.numCpuCores As [Core Count],
tblAssets.Processor As [CPU Model],
tblVmwareInfo.BiosVersion,
tblAssets.Memory As RAM,
tblVmwareInfo.Version As [ESXi Version],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Warrantydate As [Warranty Expiration]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%ESXi%Server%'
‎03-18-2019 08:32 PM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.NrProcessors As [Socket Count],
tblVmwareInfo.numCpuCores As [Core Count],
Count.count As [Win Guest Count],
tblAssets.Processor As [CPU Model],
tblVmwareInfo.BiosVersion,
tblAssets.Memory As RAM,
tblVmwareInfo.Version As [ESXi Version],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Warrantydate As [Warranty Expiration]
From tblAssets
Left Join (Select Distinct Top 1000000 Count(tblAssets.AssetID) As count,
tblAssets1.AssetName As Host,
tblVmwareGuest.GuestFullName
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
Left Join tblVmwareVcenters On
tblAssets.AssetID = tblVmwareVcenters.AssetID And tblAssets1.AssetID =
tblVmwareVcenters.AssetID
Where tblVmwareGuest.GuestFullName Like '%indows%'
Group By tblAssets1.AssetName,
tblVmwareGuest.GuestFullName) As Count On
Count.Host = tblAssets.AssetName
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%ESXi%Server%'
Order By [Win Guest Count] Desc
‎01-15-2019 12:26 AM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.NrProcessors As [Socket Count],
tblVmwareInfo.numCpuCores As [Core Count],
Count.count As [Win Guest Count],
tblAssets.Processor As [CPU Model],
tblVmwareInfo.BiosVersion,
tblAssets.Memory As RAM,
tblVmwareInfo.Version As [ESXi Version],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Warrantydate As [Warranty Expiration]
From tblAssets
Left Join (Select Distinct Top 1000000 Count(tblAssets.AssetID) As count,
tblAssets1.AssetName As Host,
tblVmwareGuest.GuestFullName
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
Left Join tblVmwareVcenters On
tblAssets.AssetID = tblVmwareVcenters.AssetID And tblAssets1.AssetID =
tblVmwareVcenters.AssetID
Where tblVmwareGuest.GuestFullName Like '%indows%'
Group By tblAssets1.AssetName,
tblVmwareGuest.GuestFullName) As Count On
Count.Host = tblAssets.AssetName
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%ESXi%Server%'
‎01-14-2019 06:14 PM
Left Join (Select Distinct Top 1000000 Count(tblAssets.AssetID) As count, ) As Count On
tblAssets1.AssetName As Host
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
Left Join tblVmwareVcenters On
tblAssets.AssetID = tblVmwareVcenters.AssetID And tblAssets1.AssetID =
tblVmwareVcenters.AssetID
Group By tblAssets1.AssetName
Count.Host = tblAssets.AssetName
‎01-14-2019 11:55 AM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.NrProcessors As [Socket Count],
tblVmwareInfo.numCpuCores As [Core Count],
tblAssets.Processor As [CPU Model],
tblVmwareInfo.BiosVersion,
tblAssets.Memory As RAM,
tblVmwareInfo.Version As [ESXi Version],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Warrantydate As [Warranty Expiration],
Count.count
From tblAssets
Left Join (Select Distinct Top 1000000 Count(tblAssets.AssetID) As count,
tblAssets1.AssetName As Host
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
Left Join tblVmwareVcenters On
tblAssets.AssetID = tblVmwareVcenters.AssetID And tblAssets1.AssetID =
tblVmwareVcenters.AssetID
Group By tblAssets1.AssetName) As Count On
Count.Host = tblAssets.AssetName
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%ESXi%Server%'
‎01-14-2019 03:36 PM
Charles.X wrote:
Ty the query below:Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.NrProcessors As [Socket Count],
tblVmwareInfo.numCpuCores As [Core Count],
tblAssets.Processor As [CPU Model],
tblVmwareInfo.BiosVersion,
tblAssets.Memory As RAM,
tblVmwareInfo.Version As [ESXi Version],
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Warrantydate As [Warranty Expiration],
Count.count
From tblAssets
Left Join (Select Distinct Top 1000000 Count(tblAssets.AssetID) As count,
tblAssets1.AssetName As Host
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
Left Join tblVmwareVcenters On
tblAssets.AssetID = tblVmwareVcenters.AssetID And tblAssets1.AssetID =
tblVmwareVcenters.AssetID
Group By tblAssets1.AssetName) As Count On
Count.Host = tblAssets.AssetName
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Where tsysAssetTypes.AssetTypename Like '%ESXi%Server%'
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now