locked
Where are Queries Stored??? RRS feed

  • Question

  • Hi All,

    I am working on a more automated way for our Help Desk to be able to remove PC's from collections when a PC is re-built or repurposed. In either case the PC should not automatically receive a bunch of software after being rebuilt becuase their is still a reference to the computer name in a series of queries which are then linked to collections.

    What I would like to do is build a HTA that:

    a) lists the collections a PC is a member of

    b) Develop a process to programmatically remove the PC computer name from each collection prior to it being rebuilt

    The collections listing is easy. What I cannot find is where SCCM stores the SELECT statement that is associated with each of our queries. I am making use of the "Creating Custom Reports By Using Configuration Manager 2007 SQL Views" documentation to try and link the appropriate tables together.

    Not technically a reporting question but I though it was more to do with queries and reporting than any other topic.

    Thanks in advance,


    NSutton
    Thursday, October 7, 2010 2:51 AM

Answers

  • To specifically answer the original question, the collection query rules can be found in v_CollectionRuleQuery. If you're using static collections then v_CollectionRuleDirect is the right view. Of course if you're going to change the collection queries then be sure to do that via the SMS Provider, and thus use the equivalent classes. But if you're doing lookups then almost always SQL queries against the views are best.

    But I agree with the general gist of this thread that you should think carefully about whether you need the additional process you're suggesting. You may, and a little more clarification might help us to understand why, but there may be an easier way.

    p.s. If you want to look at the collection rule SQL, as opposed to WQL, you should check out this table: Collection_Rules_SQL. This is one time that you have to use a table because there's no equivalent view.

    • Proposed as answer by Sherry Kissinger Thursday, October 14, 2010 12:49 PM
    • Marked as answer by Nathan Sutton Saturday, November 6, 2010 11:34 PM
    Tuesday, October 12, 2010 7:33 PM

All replies

  • Hi,

    Do you use Direct Membership or query based membership? If you use query-based you would have to achieve this either by re-writing the query or by adding the computer to a "Reinstall-collection" which you exclude from all queries. Otherwise it will be hard to achieve..

    Regards,

    Jörgen


    ---------------------------------------------------- visit my System center blog at www.ccmexec.com
    Thursday, October 7, 2010 5:25 AM
  • Thursday, October 7, 2010 6:49 AM
  • When the machine is reimaged it will get a new record in SCCM. Assuming we are talking about direct memberships here the computer shouldn't run the advertisements that had been previously assigned to it.

    Another way to handle this is to use query based collections and AD group memberships. That way the help desk needs only access to ADUC.

     


    John Marcum | http://myitforum.com/cs2/blogs/jmarcum |
    Thursday, October 7, 2010 1:38 PM
  • To specifically answer the original question, the collection query rules can be found in v_CollectionRuleQuery. If you're using static collections then v_CollectionRuleDirect is the right view. Of course if you're going to change the collection queries then be sure to do that via the SMS Provider, and thus use the equivalent classes. But if you're doing lookups then almost always SQL queries against the views are best.

    But I agree with the general gist of this thread that you should think carefully about whether you need the additional process you're suggesting. You may, and a little more clarification might help us to understand why, but there may be an easier way.

    p.s. If you want to look at the collection rule SQL, as opposed to WQL, you should check out this table: Collection_Rules_SQL. This is one time that you have to use a table because there's no equivalent view.

    • Proposed as answer by Sherry Kissinger Thursday, October 14, 2010 12:49 PM
    • Marked as answer by Nathan Sutton Saturday, November 6, 2010 11:34 PM
    Tuesday, October 12, 2010 7:33 PM
  • Hi Guys,

    Sorry for the late response.

    As Paul mentioned above I did eventually stumble across the v_CollectionRuleQuery and v_CollectionRuleDirect views within the SCCM database. The SCCM database schema documents lead me to these which was helpful. These views relate to a database table which seems to be the central point where all collection membership is stored. Direct membership seems pretty easy, a single record for each PC and associated collection ID. Query based records are complex. They have the query as defined via the GUI in one column and the next column has the query that SCCM has generated and obivuosly uses to find the results. The SCCM query is a superset of the collection query. This would be hard to create programmatically unless, as Paul mentions, there is a method exposed by the SMS Provider.

    Thanks John and Eswar, the AD group membership option is definitely somethiong I can look at. As for the new record option mentioned by John, I have set SCCM to create a conflict record rather than a duplicate. This is becuase if a PC is rebuilt becuase of stability issues we actually want all of the software that was sent to it previsouly to be resent again. Once the conflicting record is acknowlegde all of the previous advertisements start to flow again.

    What I will do is have a look a direct membership for all queries and try to use the SMS SDK to access and manipulate the SCCM database rather than trying to edit the table directly.

    Thanks guys for all of the responses. Once again sorry for the late reply.

     


    NSutton
    Saturday, November 6, 2010 11:34 PM