How to join in a WMI Query (WQL)

Question

I want to get the serial number of the boot-harddisk via a WQL query.

The boot-partition can be retrieved using the following query:

SELECT * FROM Win32_DiskPartition where BootPartition=True

The serial number is in Win32_DiskDrive:

SELECT DeviceID, SerialNumber FROM Win32_DiskDrive

Win32_DiskDriveToDiskPartition has the mapping of Win32_DiskDrive to Win32_DiskPartition. They are mapped Win32_DiskDrive.DeviceID to Win32_DiskPartition.DeviceID in Win32_DiskDriveToDiskPartition

How can I build a WQL query that inner joins Win32_DiskPartition and Win32_DiskDrive? Do I have to use Associators or does it work with INNER JOIN?

Answer 1

WQL doesn't support the JOIN clause. You need to use the ASSOCIATORS OF statement as you guessed. Here's an example in VBScript:

strComputer = "." 
Set oWMI = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2") 

Set colPartitions = oWMI.ExecQuery( _
    "SELECT * FROM Win32_DiskPartition WHERE BootPartition=True") 

For Each oPartition in colPartitions 

    Set colDrives = oWMI.ExecQuery( _
        "ASSOCIATORS OF {Win32_DiskPartition.DeviceID='" _
        & oPartition.DeviceID & "'} WHERE ResultClass=Win32_DiskDrive")

    For Each oDrive in colDrives
        WScript.Echo oDrive.SerialNumber
    Next

Next

Note, however, that the Win32_DiskDrive.SerialNumber property isn't available prior to Windows Vista. So, if you want your code to work on earlier Windows versions as well (e.g. Windows XP or Windows 2000) you should consider using APIs other than WMI.

---

Edit: (reply to comment) Yes, you can add a nested ASSOCIATORS OF query to get the Win32_PhysicalMedia instances corresponding to the Win32_DiskDrive instances; something like this:

...
For Each oDrive in colDrives
    Set colMedia = oWMI.ExecQuery( _
        "ASSOCIATORS OF {Win32_DiskDrive.DeviceID='" _
        & oDrive.DeviceID & "'} WHERE ResultClass=Win32_PhysicalMedia")

    For Each oMedia in colMedia
        WScript.Echo oMedia.SerialNumber
    Next
Next

You haven't said what language you're using - I guess in PowerShell or C# the whole thing can be done more elegantly, but VBScript is pretty verbose.

Answer 2

Here is the C++ code that does the same thing as the VBScript code posted by Helen.

// Obtain the initial locator to WMI
// ...
// Connect to WMI through the IWbemLocator::ConnectServer method
// ...
// Set security levels on the proxy
// ...

wchar_t wmihddsn[256];
    *wmihddsn=0;

hres = pSvc->ExecQuery(
    bstr_t("WQL"),
    bstr_t("SELECT * FROM Win32_DiskPartition WHERE BootPartition=True"),
    WBEM_FLAG_FORWARD_ONLY | WBEM_FLAG_RETURN_IMMEDIATELY, 
    NULL,
    &pEnumerator);

if(SUCCEEDED(hres) && pEnumerator) 
{
    // get the first Win32_DiskPartition
    HRESULT hr = pEnumerator->Next(WBEM_INFINITE, 1, &pclsObj, &uReturn);

    if(SUCCEEDED(hr) && 0 != uReturn)
    {
        VARIANT vtProp;
        wchar_t tmp[1024];
        char query[1024];

        // Get the value of the partition's DeviceID property
        hr = pclsObj->Get(L"DeviceID", 0, &vtProp, 0, 0);
        if(SUCCEEDED(hr))
        {
            if(vtProp.vt == VT_BSTR) {
                // wcout << " SerialNumber : " << vtProp.bstrVal << endl;
                wcscpy(tmp, vtProp.bstrVal);
            }
            VariantClear(&vtProp);


            // "join" Win32_DiskPartition to Win32_DiskDrive
            sprintf(query, 
                "ASSOCIATORS OF {Win32_DiskPartition.DeviceID='%s'} WHERE ResultClass=Win32_DiskDrive",
                NarrowWcharString(tmp));

            hres = pSvc->ExecQuery(
                bstr_t("WQL"),
                bstr_t(query),
                WBEM_FLAG_FORWARD_ONLY | WBEM_FLAG_RETURN_IMMEDIATELY, 
                NULL,
                &pEnumerator1);

            if(SUCCEEDED(hres) && pEnumerator1)
            {
                // get the first Win32_DiskDrive
                hr = pEnumerator1->Next(WBEM_INFINITE, 1, &pclsObj1, &uReturn);

                if(SUCCEEDED(hr) && 0 != uReturn)
                {
                    // Get the value of the disk-drive's DeviceID
                    hr = pclsObj1->Get(L"DeviceID", 0, &vtProp, 0, 0);
                    if(SUCCEEDED(hr))
                    {
                        if(vtProp.vt == VT_BSTR)
                        {
                            wcscpy(tmp, vtProp.bstrVal);
                        }
                        VariantClear(&vtProp);


                        // "join" Win32_DiskDrive to Win32_PhysicalMedia
                        sprintf(query,
                            "ASSOCIATORS OF {Win32_DiskDrive.DeviceID='%s'} WHERE ResultClass=Win32_PhysicalMedia",
                            NarrowWcharString(tmp));

                        hres = pSvc->ExecQuery(
                            bstr_t("WQL"),
                            bstr_t(query),
                            WBEM_FLAG_FORWARD_ONLY | WBEM_FLAG_RETURN_IMMEDIATELY, 
                            NULL,
                            &pEnumerator2);

                        if(SUCCEEDED(hres) && pEnumerator2)
                        {
                            // get the first Win32_PhysicalMedia
                            hr = pEnumerator2->Next(WBEM_INFINITE, 1, &pclsObj2, &uReturn);

                            if(SUCCEEDED(hr) && 0 != uReturn)
                            {
                                // get the PhysicalMedia's SerialNumber
                                hr = pclsObj2->Get(L"SerialNumber", 0, &vtProp, 0, 0);
                                if(SUCCEEDED(hr))
                                {
                                    if(vtProp.vt == VT_BSTR) 
                                    {
                                        // wcout << " SerialNumber : " << vtProp.bstrVal << endl;
                                        wcscpy(wmihddsn,vtProp.bstrVal);
                                    }
                                    VariantClear(&vtProp);
                                }

                            }

                            if(pclsObj2) pclsObj2->Release();
                        }
                        if(pEnumerator2) pEnumerator2->Release();

                    } // get disk-drive's DeviceID
                }

                if(pclsObj1) pclsObj1->Release();
            }
            if(pEnumerator1) pEnumerator1->Release();

        } // get partition's DeviceID
    }

    if(pclsObj) pclsObj->Release();
} // if succeeded first query
if(pEnumerator) pEnumerator->Release();

// ...
// cleanup