How to join in a WMI Query (WQL)
Solution 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.
Solution 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
j00hi
Software developer and software architect since the beginning of this century. Used to do a lot web, database and windows applications development but turned my focus to real-time graphics and game development in the past few years. In terms of programming language preferences, I like everything which is strongly typed and allows me to put more than pointers and integers on the stack. While I enjoy the beauty and light-heartedness of C#, the greatest programming language to me is (modern!) C++. It took me a while to get into it, but now I really appreciate its features and also its paradigms. My true passion is computer graphics and game development. I've done quite a lot of OpenGL development and gathered a lot of experience with Unity3D in the context of working on Augmented Reality applications and indie games. In the recent few years, I took a deep dive into Vulkan. I am working on a convenience and productivity layer atop Vulkan-Hpp and on a framework. Those two projects join two of the greatest technologies: Modern C++ and Vulkan.
Updated on July 10, 2022Comments
-
j00hi almost 2 years
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 ofWin32_DiskDrive
toWin32_DiskPartition
. They are mappedWin32_DiskDrive.DeviceID
toWin32_DiskPartition.DeviceID
inWin32_DiskDriveToDiskPartition
How can I build a WQL query that inner joins
Win32_DiskPartition
andWin32_DiskDrive
? Do I have to use Associators or does it work with INNER JOIN? -
j00hi almost 14 yearsThank you very much! Win32_PhysicalMedia is available prior to Windows Vista, as far as I know. Do I have to "join" these three WMI-objects via associators to achieve compatibility with Windows XP?
-
j00hi almost 14 yearsYou helped me really a lot! Thanks! I had to write it in C++, but the proceeding is the same. I'm going to post my C++ code, maybe it could be useful for someone.
-
j00hi almost 14 yearsThe cool thing is that this code is compatible down to at least Windows XP. I successfully tested this code on Win7 64bit, Win7 32bit and WinXP.