→ 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: 
ianadams
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
Bruce_B
Lansweeper Alumni
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

View solution in original post

4 REPLIES 4
Bruce_B
Lansweeper Alumni
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
Engaged Sweeper
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
Engaged Sweeper
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
Engaged Sweeper III
I would like almost the same thing but regardless of platform.


Something like:

Host DB DB_Size