Do subqueries work in Win32_Process?

[snowsnowsnow]

I use Win32_Process - with the usual voodoo incantations (including the GetObject() call with the weird strings with impersonation and other nonsense in it) - in a lot of my programs - to get information about processes running on the system.

I have recently written a script that gets the pids of the sibling processes of the current process - i.e., all the processes whose parent is the same as the script's parent.  I have a version that works, by doing two queries - the first to get our parent process id and the second to get all the Pids with that parent process ID. 

Then I got the brilliant idea to try to combine it all into a single query.  Based on what I know of SQL (which isn't much), the following should work, but doesn't:

;;; Assume that mypid has been assigned the correct value.
Query = "Select ProcessId,CommandLine from Win32_Process Where ParentProcessId in (Select ParentProcessId from Win32_Process Where ProcessId = %mypid%) and Name = 'winbatch.exe'"

Should this work or have I botched the syntax somewhere?

P.S.  The failure happens a few lines later when I try to use the value returned by the ExecQuery() call - an unexpected error message about StrCat() having a Function Syntax Error.

As I say, it all works fine in the version without the subquery.

[td]

I confess to not being all that proficient in SQL anymore.  Mostly due to disuse.   Also, WMI's WQL only supports a limited (not sure how limited) subset of SQL syntax.

To get to the point, after setting up a simple script to test your query string, I got a COM exception and this error in the More Error Info box:

SWbemObjectSet
Invalid query

when I attempted to access the Count property of the query's returned object.

I suspect that WQL does not support your syntax.  Here's a link to MSFT's documentation for WQL:

https://docs.microsoft.com/en-us/windows/win32/wmisdk/wql-sql-for-wmi

[stanl]

Select In normally involves a different alias if selecting from the same source. So assume

Code Select Expand


Query = "Select P.ProcessId,P.CommandLine from Win32_Process AS P
Where P.ParentProcessId in (Select P1.ParentProcessId from Win32_Process AS P1 Where P.ProcessId = P1.%mypid%) and Name = 'winbatch.exe'"



But as Tony wrote, WQL probably does not support the In Operator.  Consider reusing the WHERE clause with 'AND'

[snowsnowsnow]

OK - so the gist is kind of as I expected - that the subset of SQL that works is, as you say, kind of limited.

I can't swear to this, but I'm pretty sure I've used the "in" operator before and it has worked.  So, I'm more concerned about the subquery aspect.

I.e., if I am right, I'd expect:

ProcessID in (1,2,3)

to work, but not:

ProcessID in (select ... from ... where ...)

I can't remember now (since my SQL is pretty spotty) whether this is supposed to work or not:

ProcessID = (select ... from ... where ...)

I haven't tried it - 'casue I guess I just hoped that the "in" version would work.

[stanl]

Hadn't written a WMI script since 2013; dug out an old template and below is a test of the in() clause. It finds the instances of winbatch.exe but I get an OLE Object failure when I try to re-query with in().

Code (WINBATCH) Select Expand



IntControl(73,1,0,0,0)
strComputer = "."
oWMI = GetObject( "winmgmts:\\" : strComputer : "\root\cimv2")
;WQL = "SELECT * FROM Win32_Process"
WQL = "SELECT * FROM Win32_Process WHERE Name = 'winbatch.exe'"
oP= oWMI.ExecQuery(WQL)
cProcess = ""
IDs="in ("
found=0
n=1
ForEach P In oP
   cName = P.Caption
   cM = P.Commandline
   cP = P.ProcessId
   cPP = P.ParentProcessId
   If cP>0
      IDs=IDs:cP:","
      found=1
   Endif


   cProcess = cProcess:cName:" ":n:@LF:cM:@LF:cP:@LF:cPP:@LF
   n=n+1
Next
Message("Current Logical Processors",cProcess)
If found
   IDs=StrSub(IDs,1,StrLen(IDs)-1):")"
   WQL = "SELECT * FROM Win32_Process WHERE ProcessId %IDs%"
   Message("Can I use the In Clause",WQL)
   oP= oWMI.ExecQuery(WQL)
   ForEach P In oP
      cName = P.Caption
      cM = P.Commandline
      cPP = P.ParentProcessId
      cProcess = cProcess:cName:" ":n:@LF:cM:@LF:cPP:@LF
      n=n+1
    Next
    Message("Current Logical Processors",cProcess)
Endif
oWMI=0
Exit


:WBERRORHANDLER
oWMI=0
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Message("Error",errmsg)
Exit
;/////////////////////////////////////////////////////




[stanl]

As kind of a postscript, the WQL SQL does recognize ASSOCIATORS OF in the syntax. Never used it, but might help absence of IN() function.