[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.  

[WMI] permissions for Win32_PerfFormattedData_*

Note: if you're trying to use WMI to query any of the performance counters, you don't just need the normal WMI permissions; you also need the account running the query to be added to "Performance Monitor Users" (or, if using a GPO, "BUILTIN\Performance Monitor Users").  In my case, when I ran the query I'd get nothing.  No errors, no data, nothing.  Adding that group (in addition to all the others like WMI perms) fixed it.

in this case, found it (and more) at: http://vniklas.djungeln.se/2012/08/22/set-up-non-admin-account-to-access-wmi-and-performance-data-remotely-with-powershell/

[Powershell] Credentials/Credential/Credentialing

Had to dig through email to find this, so obviously it needs to go into my aux brain - this blog.

I had a special account set up with WMI permissions.  But that's not the same as the SQL Server account running the script..

• To get credentialing working the first time:
• Log onto the box as the service account for SQL Server
• Run this in Powershell: read-host -assecurestring | convertfrom-securestring |out-file c:\scripts\securestring_theADaccountName.txt # it will ask for the password – provide it.
• (that will read the password then save it, encrypted and readable only by that logged-in user, to the named file.  Name it so that you know which account you have to log in as in order for it to work.)
• To use from then on:

$username = "mydomain\myWMIacct"

$password = cat c:\scripts\securestring_theADaccountName.txt | convertto-securestring

$cred = new-object -typename System.Management.Automation.PSCredential `

         -argumentlist $username, $password

Get-WmiObject -ComputerName myservername -Class Win32_operatingsystem -Namespace "root\cimv2" -Credential $Cred

 Now, when you run the job, it'll run the query as that account.

And if you're feeling like trouble, here's a HORRIBLE alternative:

$credential = New-Object System.Management.Automation.PsCredential("yourdomain\youruser", (ConvertTo-SecureString "yourpassword" -AsPlainText -Force))

Powershell - kill processes remotely

Simple problem: we had to replace the config files for an app that hits the database, pointing it at a CNAME. 

However, the file could be in use because the app was running.  And the app only loads the file on startup.  So we came up with this, which worked quite well.  Note that we use WMI (see my other posts on it!) to grab the application info then a WMI method to terminate the app.

[Presenting] FWSSUG - my Slide Deck on EventLog_Tracking - using WMI to collect event logs (as well as other things)

Here's my latest presentation.  Pretty good reception overall.


Here's the basic premise: there's a lot that goes on our SQL boxes that we normally don't get involved in.  But SQL Server isn't an island - it's affected by the ebbs and flows of other processes.  I had a problem where I needed to collect the Windows Event Logs from a SQL server, and wound up going through a rabbit hole of interesting stuff you can do with WMI.  Here are the lessons I've learned.  All code already available (search for eventlog_tracking on this blog), and it's currently running on 60+ servers, collecting data every 15 minutes. It runs in under 2, and I think I could get it down under 1 if I had the time.

This is a predominantly slide presentation - the only demos I did were to run some of the command in the slide deck.