Report that show databases with hyper-V

[ianadams]

I am looking for a report that will generate a list of all of the databases, with SQL instances, and what Hyper-V they are on. Been trying to figure it out, but I am a complete SQL noob and haven't been able to put it together.

[Bruce_B · Accepted solution]

The report below displays some basic sql server instance and database information for your hyper-v guest machines. It also lists their hosts. The report can be modified to display more database information.

Select Top 1000000 tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblSqlServers.serviceName As InstanceName,
  tblSqlDatabases.name As DatabaseName,
  tblAssetMacAddress.Mac,
  tblAssets1.AssetName As [Hyper-V Host]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Inner Join tblSqlDatabases On tblSqlServers.sqlServerId =
    tblSqlDatabases.sqlServerId
  Inner Join tblAssetMacAddress
    On tblAssets.AssetID = tblAssetMacAddress.AssetID
  Inner Join TblHyperVGuestNetwork On tblAssetMacAddress.Mac =
    TblHyperVGuestNetwork.MacAddress
  Inner Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
    TblHyperVGuestNetwork.HyperVGuestID
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID = tblHyperVGuest.AssetID
Order By tblAssets.Domain,
  tblAssets.AssetName

[ianadams]

I would be okay with ignoring platform as well if I could figure out how to pull the complete list of databases with the hyper-v they are on.

[ianadams]

So there is the Database inventory report:

Select Top 1000000 tblSqlDatabases.name As name1,
tblAssets.AssetName,
tblSqlDatabases.Lastchanged,
tblSqlDatabases.dataFilesSizeKb,
tblSqlServers.dataPath,
tblSqlServers.serviceName
From tblSqlDatabases
Inner Join tblSqlServers On tblSqlServers.sqlServerId =
tblSqlDatabases.sqlServerId
Inner Join tblAssets On tblAssets.AssetID = tblSqlServers.AssetID

And then there is the Hyper-V: Virtual Guest Machines report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblHyperVGuest.Name As Guest,
tblHyperVGuest.Memory,
tblHyperVGuest.Enabledstate,
tblHyperVGuest.Healthstate,
tblHyperVGuest.Lastchanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen,
tsysOS.Image As icon
From tblAssets
Inner Join tblHyperVGuest On tblAssets.AssetID = tblHyperVGuest.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Order By tblAssets.AssetName

So basically what I need to figure out is how to combine the two where tblAssests.AssetName in the database inventory is equal to tblHyperVGuest.name in the Hyper-V report.

[marrngtn]

I would like almost the same thing but regardless of platform.


Something like:

Host DB DB_Size