Your company's ad could live here and reach over 50,000 people a month!

Share This Post

Hyper-V Host/Guest Reports for ConfigMgr using Normalize CM

This is something you might have missed if you’re not a member of the BDNA Normalize CM Community Group.

Julien Moreau, myITforum member and BDNA person, just posted SQL and WQL queries for gathering information to identify the relation between a Hyper-V host and its VM.

Of course, to get this information, or to make the ConfigMgr data usable, you’ll need Normalize CM deployed.  Check it out:

SQL Query:

      hstR.ResourceID as ‘Host ID’,
      hstR.Netbios_Name0 as ‘Host Name’,
      hstOS.OS_Name0 as ‘Host OS Name’,
      hstOS.OS_MajorVersion0 as ‘Host OS Version’,
      hstOS.OS_Edition0 as ‘Host OS Edition’,
      gstR.ResourceID as ‘Guest ID’,
      gstR.Netbios_Name0 as ‘Guest Name’,
      gstOS.OS_Name0 as ‘Host OS Name’,
      gstOS.OS_MajorVersion0 as ‘Host OS Version’,
      gstOS.OS_Edition0 as ‘Host OS Edition’
INNER JOIN v_R_System gstR ON gstR.ResourceID=gst.ResourceID
LEFT JOIN v_GS_BDNA_Hardware_Data0 gstOS ON gstOS.SMSUniqueIdentifier0=gstR.SMS_Unique_Identifier0
LEFT JOIN v_R_System hstR ON hstR.Netbios_Name0=gst.PhysicalHostName0
LEFT JOIN v_GS_BDNA_Hardware_Data0 hstOS ON hstOS.SMSUniqueIdentifier0=hstR.SMS_Unique_Identifier0
WHERE gst.PhysicalHostName0 IS NOT NULL

WQL Query:

select SMS_G_System_VIRTUAL_MACHINE.PhysicalHostName, gstR.ResourceId, gstR.NetbiosName, gstOS.OS_Name, gstOS.OS_MajorVersion, gstOS.OS_Edition, hstR.Active from SMS_G_System_VIRTUAL_MACHINE inner join SMS_R_System as gstR on gstR.ResourceId = SMS_G_System_VIRTUAL_MACHINE.ResourceID left join SMS_G_System_BDNA_BDNAHARDWAREDATA_1_1 as gstOS on gstOS.SMSUniqueIdentifier = gstR.SMSUniqueIdentifier left join SMS_R_System as hstR on hstR.NetbiosName = SMS_G_System_VIRTUAL_MACHINE.PhysicalHostName left join SMS_G_System_BDNA_BDNAHARDWAREDATA_1_1 as hstOS on hstOS.SMSUniqueIdentifier = hstR.SMSUniqueIdentifier where SMS_G_System_VIRTUAL_MACHINE.PhysicalHostName is not null

Original post in the BDNA Normalize CM forum:

Share This Post

A community professional, keynoter, and evangelist who has driven social media and marketing strategies, editorial successes, delivered customer successes and built some of the largest and longest-running online communities. Rod has created, managed and grown small, medium, and mega-sized conferences; run entire editorial teams to deliver record traffic and market leadership; as product manager, directed the success of hundreds of product releases; supported sales and marketing to ensure customer success; developed, run and sold businesses; written thousands of technical articles, white papers, case studies, and technical documentation; hosted and delivered hundreds of attendance shattering webinars and virtual tradeshows; and delivered keynote speeches and sessions at a wide variety of events including conferences, webinars, events, and user groups.

Leave a Reply