CMPivot to check Services and start them (with a little help)

-

Have you ever needed to get a really fast real-time look at if a service is running on a set of servers or workstations? Open CMPivot against a collection, type in your query, and send it. Seconds later you get real-time answers to your query for any online device. Queries for CMPivot run on 42 devices at once, until all devices you're querying have responded. 

The last time my organization did server updates, I (and a trusty super awesome coworker) had to verify if a couple of SQL services were running on a small collection of servers. As we were manually checking these one by one, I came up with the idea that it would be incredibly helpful to use CMPivot. 

The below query in CMPivot will return all devices where a SERVICE with the name containing SQL is NOT RUNNING and the service's start type is AUTO. In other words, if a service with SQL in the same is supposed to be automatically running and it's not, this query tells us.

Service | where Name contains 'sql' | where State != 'Running' | where StartMode == 'Auto'

What if we wanted to see all devices with a SQL service regardless of the state or start type? Take out the last two pieces of the query and you get it all unfiltered. 

Service | where Name contains 'sql'

WHY THE #$%& DID I GET "Failed to parse query" ON THIS?!?!
All Operators and Functions are CASE SENSITIVE. These are your where or contains type things. Go back to the capital letter you typed in and make it small just like the examples above.

What if you wanted to start a service that is showing as Stopped in your CMPivot query results? Use the Run Scripts feature of SCCM. Create the following script in Software Library > Scripts and then you can use it from your results in CMPivot. For more on creating and approving Scripts in the Software Library, see my previous posts.

# Prompt for parameter (service name)
Param(
[Parameter(Mandatory=$True)]
[string]$ServiceName
)
# Start the service
Start-Service -Name $ServiceName

From your CMPivot query results, right click on a Device (or use shift / control clicking to highlight multiple devices you want to start the same service on) and click on Run Script. Select your Start-Service script from the list, input the Service Name as the parameter in the wizard, and click Next until your only option is to close it. 

Now comes the fine print. CMPivot reduces the amount of actual queries ran against devices by caching your query results and using them to instantly display query results when you only make minor changes like modifying a query operator. This means if you ran the query from the above screenshot and then you just hit "Run Query" again, it is showing you cached results. In order to get new results, hold down the CTRL key and click Run Query. This essentially clears the cached results and does a fresh query. 

Note to self... put in a User Voice to get a "Refresh Query" button added. (CTRL works but it would still be nice to have a GUI option or a tip that pops up about CTRL)

Comments

Post a Comment

Popular posts from this blog

Run Scripts with Parameters in MEMCM (R.I.P. SCCM)

-
Image
How do you use Parameters in MEMCM's (R.I.P. SCCM) Run Scripts? How do you use Parameters on the Run Scripts  feature? You get three guesses, but the first two don't count. That's right, you create a script with standard PowerShell Parameters and you build it out in MEMCM. In your MEMCM console, navigate to Software Library > Overview > Scripts. If you're not familiar with Run Scripts  in MEMCM, check out my previous Blog articles on it. Click on Create Script . In the Create Script Wizard  that pops up input a Script Name , pick between PowerShell or PowerShell in your Script Language , import or paste in your script with Parameters, and click on Next . My demo script for this article ( scroll alllll the way down, it's at the bottom ) is about an 8 out of 10 on the cool factor, and about a 3 out of 10 on the usefulness factor. Long story short, the script will make a computer talk. There are two Parameters that you can input, one for "what do y...
Read more

SCCM CMPivot and Run Scripts

-
Image
What is CMPivot? What are Scripts? Why do I care about either of them? I recently attended MMS 2019 and took a great session from Jason Sandys and Tom Degreef on CMPivot and Run Scripts. They dove deep into the technical aspects of both of these somewhat new "fast channel" features of SCCM. Full disclaimer: I do not know everything and I may occasionally not know as much as you. Test everything you see here on a LAB environment and make sure you fully understand it. If you have any suggestions for improving this post, please feel free to drop a comment and I'll give you some kudos for your edit. Over simplified, CMPivot is a near real-time (not milliseconds, but usually seconds, and definitely no more than a few minutes) way to query information about your systems. You can find out literally anything about any system within your reach. Scripts pairs nicely with CMPivot because it is a real-time way to run scripts against your query results, or just another old col...
Read more