Chicago Endpoint Management User Group

 View Only
  • 1.  SQL Query to show what tasks are assigned to each collection

    Posted Dec 22, 2009 11:34 AM
    I need to find a way to show what tasks are assigned to each collection...anyone have a query to do this? 


  • 2.  RE: SQL Query to show what tasks are assigned to each collection

    Broadcom Employee
    Posted Dec 22, 2009 03:47 PM
    I suspect the KB I wrote a while back would apply here [https://kb.altiris.com/article.asp?article=31124&p=1]:

    Question
     
      When I try to delete an object, the Altiris Console returns a reference dependency report showing the references on the item. How can I create a report so I can see the references on a collection to help decide whether to reuse the collection for another Software Ddelivery task or policy?

     


    Answer
     
      Use the following SQL statement into SQL Query Analyzer or create a report based on this SQL query:
    select it.name as [Collection Name], count(ir.childitemguid) as [Collection in use #]
      from ItemReference ir
      join Item it
        on ir.childitemguid = it.guid
     where hint = 'policyappliestocollection'
     group by ir.childitemguid, it.name
    having COUNT(ir.childitemguid) > 1


  • 3.  RE: SQL Query to show what tasks are assigned to each collection

    Posted Dec 22, 2009 03:56 PM

    Thanks....Mike Sellers actually gave me one too:

    Select DISTINCT Item.[Name], [Policy Name], Item.[Description] [Policy Description], vCollection.[Name] [Collection Assigned To], vCollection.[Description] [Collection Description], vPolicyAppliesToResource.[Enabled] [Is Enabled]

    FROM Item
    LEFT OUTER JOIN vPolicyAppliesToResource ON vPolicyAppliesto Resource.[PolicyGuid] = Item.[Guid]
    Join vCollection ON vCollection.[Guid] = vPolicyAppliesToResource.[CollectionGuid]

    Order by vCollection.[Name]



  • 4.  RE: SQL Query to show what tasks are assigned to each collection

    Broadcom Employee
    Posted Dec 22, 2009 04:13 PM
    As the interfaces are most likely to remain in NS 7.0, whilst I tend to use native tables (where the data lives, instead of views where it is aggregated) which generaly gives better prefromance (and I am against too many levels of abstractions as they tend to blur the picture, making things simpler at a hidden cost).