cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
LoveLansweeperV
Engaged Sweeper
Hi,

We are using a report to identify servers with a specific software installed on them. We have a mixed environment of Vmware virtual servers and physical Dell servers. I'm trying to make this report only output physical servers and exclude virtual. Could anyone please help? Thanks!

Report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblOperatingsystem.Caption As OS,
tblAssets.Lastseen As [Last Successful Scan],
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As UptimeSinceLastReboot
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.SoftID
Where
tblSoftwareUni.SoftwareName =
'LogicMonitor') And
tblAssetCustom.State = 1 And tblAssets.AssetType = -1 And
tblComputersystem.DomainRole > 1
Order By tblAssets.Domain,
tblAssets.AssetName
3 REPLIES 3
KevinA-REJIS
Champion Sweeper II
Sorry, it should be single quotes on that: 'VMware%'
LoveLansweeperV
Engaged Sweeper
I get "Invalid column name 'VMware%'."
KevinA-REJIS
Champion Sweeper II
Try this:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblOperatingsystem.Caption As OS,
tblAssets.Lastseen As [Last Successful Scan],
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As UptimeSinceLastReboot
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.SoftID
Where
tblSoftwareUni.SoftwareName =
'LogicMonitor') And
tblAssetCustom.State = 1 And tblAssets.AssetType = -1 And
tblComputersystem.DomainRole > 1 And tblAssetCustom.Manufacturer Like "VMware%"
Order By tblAssets.Domain,
tblAssets.AssetName