Collection creation based on state messages
Back in my System Center Configuration Manager (SCCM) 2007 days I wrote up a blog post about something I did with a customer to create collections based on status messages. I have referred back to that many times, as have a few of my customers. Recently I had a similar request, but in this case it was to build a collection based on state messages that are more common in the SCCM 2012 product. "State" and "Status" are similar.., but there are differences. To simplify, "state" gets tagged to an object, rather than existing as a separate entity in SCCM. To this end, collection creation is a little different.
To focus this post I'm going to go over deployment state of an application, not a package or baseline or anything else. The concepts should work for those other items but, honestly, I don't have the time to check them all. I do have a day job taking care of customers and unless they ask for it I will have to just leave the rest to all of you out in internet-land. Feel free to post your findings in the comment section to share with everyone else.
The customer scenario is that they have want a collection of all machines which have failed a given deployment. In concept you could then target this "collection of failures" for some other activity that is appropriate to their failing state. In the SCCM UI there are 4 different states for a application deployment and each has its own WQL namespace to call.
As I often recommend, before creating a collection I suggest creating a query first and then going from there. In this case I did some examination of the smsprov.log while clicking around in the UI and I was able to narrow things down some. I also realized that I was going to need something more unique than a deployment name to work with. By adding the column for "Assignment ID" to my application deployments (Monitoring/Deployments) I found my unique item and came up with the following query:
SELECT sys.name, app.AppStatusType, app.StatusType
FROM sms_r_system AS sys
INNER JOIN SMS_AppDeploymentAssetDetails as APP
on App.MachineName = sys.name
WHERE AssignmentID = '21'
This query is nicely setup to be used for a collection, but as a query it is even better. In it you need to change the AssignmentID to match the application deployment you are most interested in The results will be the state of every machine for that deployment. Here is an example result set form my lab:
That information right there can be very handy... and easily dumped to a file if you need it. Many customers want to take it to the next step and actually create a collection so they can deploy some kind of remediation out. If we did a collection based on the above query we would get all the machines. However, if we add the limiters based on the "Application Status Type" and "Status Type" we can narrow the results down to anything we want. For example, to find just the machines that had the error we would change the query to have a few more limiters (notice that the StatusType is numerical unlike the screen shot above and matches the AppStatusType number):
SELECT sys.name, app.AppStatusType, app.StatusType
FROM sms_r_system AS sys
INNER JOIN SMS_AppDeploymentAssetDetails as APP
on App.MachineName = sys.name
WHERE AssignmentID = '21' AND StatusType = 5 AND AppStatusType = 5
This query nicely gives us the machines which failed the original application deployment. Now we can make a collection based on this query and reach the final customer goal.
Comments
- Anonymous
October 09, 2017
Hi Mike,I am trying to create a collection with the machines failed to install Software updates. Here is the query I got from SMS Provider, however it shows syntax error. Can you please help with this?SELECT * FROM SMS_SUMDeploymentAssetDetails WHERE AssignmentID = '1534' AND StatusType = 5 AND StatusEnforcementState = 6 AND StatusErrorCode = -2016410012- Anonymous
September 18, 2018
Hi thereDid you ever manage to create the Collection?I'm having the same issue:---------------------------Configuration Manager---------------------------The query statement that you entered is not valid. Enter a valid query statement.---------------------------OK ---------------------------Best regardsAlfredo
- Anonymous