VMs and HOST inventory for Microsoft Licenses

[Nels]

Hello,

Can some please help with a custom report? I need below values in the report:

Virtual Machine Name | Host Server Name (name of physical server where the VM is running)| VM Power Status (ON or OFF) | VM OS Caption | Host OS Caption | VM Physical CPU Core Count | Host CPU Core Count

Any help would be greatly appreciated

[rader]

We use Hyper-V here so not sure if that's up the alley you're looking for, but here goes.

Hyper-V OS

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblProcessor.Name As Processor,
  tblProcessor.NumberOfCores As Physical,
  tblProcessor.NumberOfLogicalProcessors As Logical
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblHyperVGuest On tblAssets.AssetID = tblHyperVGuest.AssetID
  Inner Join tblOperatingsystem On
      tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

Hyper-V Guests and assigned Hosts

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress As [Guest IP],
  tHost.AssetName As [Host Computer Name],
  tHost.IPAddress As [Host IP],
  (Case
    When tblHyperVGuest.Enabledstate = 0 Then 'Unknown'
    When tblHyperVGuest.Enabledstate = 2 Then 'Enabled'
    When tblHyperVGuest.Enabledstate = 3 Then 'Disabled'
    When tblHyperVGuest.Enabledstate = 6 Then 'Saved'
    When tblHyperVGuest.Enabledstate = 32768 Then 'Paused'
    When tblHyperVGuest.Enabledstate = 32769 Then 'Suspended'
    When tblHyperVGuest.Enabledstate = 32770 Then 'Starting'
    When tblHyperVGuest.Enabledstate = 32771 Then 'Snapshotting'
    When tblHyperVGuest.Enabledstate = 32773 Then 'Saving'
    When tblHyperVGuest.Enabledstate = 32774 Then 'Stopping'
    When tblHyperVGuest.Enabledstate = 32776 Then 'Pausing'
    When tblHyperVGuest.Enabledstate = 32777 Then 'Resuming'
    Else 'Undocumented'
  End) As State
From tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join TblHyperVGuestNetwork On
      tblAssets.Mac = TblHyperVGuestNetwork.MacAddress
  Inner Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
      TblHyperVGuestNetwork.HyperVGuestID
  Left Join tblAssets tHost On tHost.AssetID = tblHyperVGuest.AssetID
Where tblAssetCustom.State = 1
Order By [Host Computer Name],
  tblAssets.AssetName

Hyper-V: VM Rebuild Parameters

Select Distinct Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  Ceiling(tblComputersystem.TotalPhysicalMemory / 1024 / 1024 / 1024) As
  [Memory in GB],
  tblComputersystem.NumberOfProcessors As [# Phy Processors],
  tblComputersystem.NumberOfLogicalProcessors As [# Log Processors],
  tblassets.IPAddress,
  tsysAssetRelationTypes.Name As RelationType,
  ChildAsset.AssetName As hyperlink_name_ChildAsset,
  '/asset.aspx?AssetID=' + Cast(ChildAsset.AssetID As nvarchar(10)) As
  hyperlink_ChildAsset,
  tblAssetRelations.Comments As [Using Backup Method],
  tblAssetRelations.StartDate As [Start Date],
  Case
    When tblAssetRelations.EndDate < GetDate() Then '#dddddd'
    Else '#ffffff'
  End As backgroundcolor
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join tblComputersystem On tblassets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetRelations On
      tblassets.AssetID = tblAssetRelations.ChildAssetID
  Inner Join tsysAssetRelationTypes On tblAssetRelations.Type =
      tsysAssetRelationTypes.RelationTypeID
  Inner Join (Select tblAssets.AssetID,
      tblAssets.Domain,
      tblAssets.IPAddress,
      tblAssets.AssetName,
      tsysAssetTypes.AssetTypename,
      tsysAssetTypes.AssetTypeIcon10
    From tblAssets
      Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
          tblAssets.Assettype
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID)
  ChildAsset On tblAssetRelations.ParentAssetID = ChildAsset.AssetID
Where tsysAssetRelationTypes.Name Like 'Backed Up To' And
  tsysassettypes.AssetTypename = 'Windows' And tblassetcustom.State = 1
Order By tblassets.AssetName,
  [Start Date] Desc

Chart: Hosts/VM's

Select Distinct Top 1000000 Case tblAssetCustom.Model
    When 'Guest' Then 'Virtual'
    When 'Virtual Machine' Then 'Virtual'
    Else 'Physical'
  End As Server,
  COUNT(*) As Total
From tblComputersystem
  Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where (tblComputersystem.Domainrole = 2 And tblAssetCustom.State = 1) Or
  (tblComputersystem.Domainrole = 3)
Group By Case tblAssetCustom.Model
    When 'Guest' Then 'Virtual'
    When 'Virtual Machine' Then 'Virtual'
    Else 'Physical'
  End

Chart: Hyper-V hosts and guests

Select Top 1000000 Coalesce(tblAssets.AssetName, 'Unknown') As Host,
  Count(tblAssets.AssetID) As [# of Guests]
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
Group By tblAssets.AssetName
Order By tblAssets.AssetName

Dashboard: Hyper-V hosts and guests