Get more from your queries


Every SCCM admin has figured out how to create a collection with a query rule to dynamically update its membership (you don't just do direct rules I hope).  Most admins have also learned that the columns able to be returned by a collection are limited but if you create a query in the Query node of the UI instead of under a collection you will have more control over what columns get returned.  Because of this it can be handy to run a query to bring back the info you are after, like a machine name and MAC.  You can't get AC under a collection, but you can as part of a query.

A piece of frustration kicks in at this point for most folks, and that is the fact that your right click options on a query can be.. missing a few things:

While I can't promise the solution to get every option to appear (I have been trying to figure out how to get a delete option to show up) I can share that the proper columns returned will provide more right click options:

The UI can only offer options if it has the necessary data returned to make those options work.  Here is the query syntax to get more options to show up in your Configuration Manager console.  You can build on this to get the specifics you are after.

select distinct SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Client, SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.IPAddresses, SMS_R_System.SMSAssignedSites, SMS_R_System.SMSInstalledSites, SMS_R_System.Active, SMS_R_System.SMSResidentSites, SMS_R_System.MACAddresses, SMS_R_System.ClientVersion, SMS_R_System.NetbiosName, SMS_R_System.SystemRoles from  SMS_R_System

2/2-Minor edits for better readability

Comments (2)

  1. Me – I found that returning ResourceID opens up most options, but under Start you will still not have some things like remote control and such. I kept adding things until I got as much functionality as I could. If there is a way to add more I would love
    to hear it.

  2. me says:

    I had this long time ago and I think only thing that's needed(and identifies a resource exclusively is this:
    SMS_R_System.ResourceId
    if u have this it will work

Skip to main content