![gforchuk gforchuk](https://community.lansweeper.com/html/assets/User_Avatar.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-29-2014 12:01 AM
We currently are using an emailed report that shows us new windows server created in the last 14 days. I am looking to add to this report information regarding what VM is hosting the server but I cannot figure out the SQL to get this done. Has anyone done this already?
This is my SQL for the report we are using:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tsysOS.OSname,
tsysOS.Image As icon,
tblComputersystem.SystemType,
tblAssets.IPAddress,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Manufacturer,
tblAssets.Firstseen
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.Firstseen > GetDate() - 14 And tblAssetCustom.State = 1 And
tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
Order By tblAssets.AssetName
Any help on how to integrate the VMware information would be great.
Thank you,
Graham
This is my SQL for the report we are using:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tsysOS.OSname,
tsysOS.Image As icon,
tblComputersystem.SystemType,
tblAssets.IPAddress,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Manufacturer,
tblAssets.Firstseen
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.Firstseen > GetDate() - 14 And tblAssetCustom.State = 1 And
tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
Order By tblAssets.AssetName
Any help on how to integrate the VMware information would be great.
Thank you,
Graham
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
![Hemoco Hemoco](https://community.lansweeper.com/legacyfs/online/forums_avatars/9703.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-29-2014 03:00 PM
Please use the following report:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tsysOS.OSname,
tsysOS.Image As icon,
tblComputersystem.SystemType,
tblAssets.IPAddress,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Manufacturer,
tblAssets.Firstseen,
tblAssets1.AssetName As vmwarehost
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblVmwareGuestNetwork
On tblAssets.Mac = tblVmwareGuestNetwork.MacAddress
Inner Join tblVmwareGuest On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID = tblVmwareGuest.AssetID
Where tblAssets.Firstseen > GetDate() - 14 And tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tsysOS.OSname,
tsysOS.Image As icon,
tblComputersystem.SystemType,
tblAssets.IPAddress,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Manufacturer,
tblAssets.Firstseen,
tblAssets1.AssetName As vmwarehost
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblVmwareGuestNetwork
On tblAssets.Mac = tblVmwareGuestNetwork.MacAddress
Inner Join tblVmwareGuest On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID = tblVmwareGuest.AssetID
Where tblAssets.Firstseen > GetDate() - 14 And tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
2 REPLIES 2
![gforchuk gforchuk](https://community.lansweeper.com/html/assets/User_Avatar.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-31-2014 08:54 PM
Thank you very much for your help in this. That was exactly what I was looking for.
![Hemoco Hemoco](https://community.lansweeper.com/legacyfs/online/forums_avatars/9703.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-29-2014 03:00 PM
Please use the following report:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tsysOS.OSname,
tsysOS.Image As icon,
tblComputersystem.SystemType,
tblAssets.IPAddress,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Manufacturer,
tblAssets.Firstseen,
tblAssets1.AssetName As vmwarehost
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblVmwareGuestNetwork
On tblAssets.Mac = tblVmwareGuestNetwork.MacAddress
Inner Join tblVmwareGuest On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID = tblVmwareGuest.AssetID
Where tblAssets.Firstseen > GetDate() - 14 And tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tsysOS.OSname,
tsysOS.Image As icon,
tblComputersystem.SystemType,
tblAssets.IPAddress,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Manufacturer,
tblAssets.Firstseen,
tblAssets1.AssetName As vmwarehost
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblVmwareGuestNetwork
On tblAssets.Mac = tblVmwareGuestNetwork.MacAddress
Inner Join tblVmwareGuest On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID = tblVmwareGuest.AssetID
Where tblAssets.Firstseen > GetDate() - 14 And tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
![](/skins/images/0DB7630AB1C6BBE7D93A05B2832E6E89/responsive_peak/images/icon_anonymous_message.png)