→ 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: 
BWaring
Engaged Sweeper
Hello,

I am looking for a report that will show server with the following information:

Name
IP Address
Physical / Virtual

The physical / virtual could be make or model i guess as that would be distinguishable.

I did find a forum post for this already (http://www.lansweeper.com/Forum/yaf_postsm25352_Query-Server-Type-and-virtual---physical.aspx#post25352) however when I paste the code into the report creator I get the following error displayed:

'Invalid SELECT statement. Unknown object name: "tblComputers".: Unexpected token "tblComputers" at line 0, column -1'

Any help would be greatly appreciated.

Many Thanks,

Ben
1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
The query in the referenced post won't work because I believe the Lansweeper DB schema has gone through several changes since that time. As far as I am aware, there isn't a 100% full proof method to determine if the asset is virtual within Lansweeper. The query below borrows from the filter I use and it works for us.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
(Case When tblAssetCustom.Model Like '%Virtual%' Then 'Virtual'
Else 'Physical' End) As Type
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 2

View solution in original post

2 REPLIES 2
BWaring
Engaged Sweeper
Thanks for the reply Mike, that was exactly what I was after.

Thank you very much 🙂
MikeMc
Champion Sweeper II
The query in the referenced post won't work because I believe the Lansweeper DB schema has gone through several changes since that time. As far as I am aware, there isn't a 100% full proof method to determine if the asset is virtual within Lansweeper. The query below borrows from the filter I use and it works for us.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
(Case When tblAssetCustom.Model Like '%Virtual%' Then 'Virtual'
Else 'Physical' End) As Type
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 2