→ 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: 
DaveR
Engaged Sweeper
Hey,

I would like to use that report as it pretty much contain everything i need for a request i just received. However, i miss the OS caption in the report. Would you please lend me a hand as i am pretty new to SQL.

Here is the report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblVmwareInfo.Version,
tblVmwareGuest.Name As Guest,
tblVmwareGuest.Memory,
tblVmwareGuest.IsRunning As State,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblVmwareGuest.lastchanged
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Order By tblAssets.AssetName,
Guest


Thank you for your help, this report is almost perfect, only adding a colum with the OScaption and this would be awsome! i dont want to filter by OS, only showing it.
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Please use the following report:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblVmwareInfo.Version,
tblVmwareGuest.Name As Guest,
tblOperatingsystem.Caption As [os guest],
tblVmwareGuest.Memory,
tblVmwareGuest.IsRunning As State,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblVmwareGuest.lastchanged
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Left Join tblVmwareGuestNetwork On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Left Join tblAssets tblAssets1
On tblAssets1.Mac = tblVmwareGuestNetwork.MacAddress
Left Join tblOperatingsystem
On tblAssets1.AssetID = tblOperatingsystem.AssetID
Order By tblAssets.AssetName,
Guest

View solution in original post

2 REPLIES 2
DaveR
Engaged Sweeper
Thanks!

Work Perfectly.
Hemoco
Lansweeper Alumni
Please use the following report:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblVmwareInfo.Version,
tblVmwareGuest.Name As Guest,
tblOperatingsystem.Caption As [os guest],
tblVmwareGuest.Memory,
tblVmwareGuest.IsRunning As State,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblVmwareGuest.lastchanged
From tblAssets
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Left Join tblVmwareGuestNetwork On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Left Join tblAssets tblAssets1
On tblAssets1.Mac = tblVmwareGuestNetwork.MacAddress
Left Join tblOperatingsystem
On tblAssets1.AssetID = tblOperatingsystem.AssetID
Order By tblAssets.AssetName,
Guest