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.
# 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)