[Powershell] Tracking down that failing app on another server using WMI's win32_process and Profiler

(Yes, I'm still using profiler in some cases)

As we continue to move off SQL Server 2008, we've found instances where we change all the application configs, yet something on ServerA is still querying ServerB.  And since our internal application doesn't have the connection string set to give an ApplicationName, we have no idea which one is misconfigured - or if one was missed entirely!

1) Set up a profiler trace on one side, making sure to grab ClientProcessID

2) Run this WMI query on ServerA, using Powershell. 

$loopcount = 1

$futuretime = (get-date).addseconds(60)

$processeses = gwmi -Query "select caption, commandline, processid, name, executablepath from win32_process" # run locally on the server making the calls for speed)

do {

$processeses += gwmi -Query "select caption, commandline, processid, name, executablepath from win32_process" -ComputerName "mem-qa-trdb-17"

$loopcount++} while ((get-date) -lt $futuretime)

$processeses | ogv

(using $loopcount will tell you how many times it ran in that minute)

You could run it remotely if you had the proper WMI rights set up (which is probably the case on WS2016+), but my fear is that it'd be slow enough that it wouldn't "catch" the app as it's running, if it's spinning up on its own, failing to connect, then quitting. 

If the app is just running and not starting/stopping, then it'll keep the same PID (process_id) and you can just run the first $processeses and filter it. 

3) When the ogv (out-gridview) runs, it'll pop up an interactive window.  Type the actual number for ClientProcessID you got from the profiler trace, in the "filter" box, and it should filter down to just the Process (and location of the executable) that you need to fix.