Client Management Suite

 View Only

  • 1.  Help with report of all resources under an organizational view

    Posted Mar 05, 2012 09:00 AM

    So I need to make a report of all resources under a particular org group.  The problem is that this org group is several levels higher than any resources, so all it contains is other org groups.  I've got a query for the 'base' level org groups that I've cobbled together based on other posts.  What I'm asking may be obvious, but if not, here is a graphical view:

    - Patching

     -- Saturday

       --- 2am

        ---- Resource A

        ---- Resource B

       --- 4am

        ---- Resource C

     

    How do I get a report of everything for 'Saturday'?  If it helps, here's the query I've got to get everything in the '2am' group:

     

    SELECT
    vc.Guid, vc.Name
    FROM
    ScopeMembership sm
    JOIN 
    Item i ON sm.ScopeCollectionGuid = i.SecurityGuid
    INNER JOIN
    vComputer vc ON sm.ResourceGuid = vc.Guid
    WHERE
    i.Name = '2am'


  • 2.  RE: Help with report of all resources under an organizational view

    Posted Mar 05, 2012 09:55 AM

    There appears to be a similar thread asking this on the first page of the forum today.  *smacks head*

    https://www-secure.symantec.com/connect/forums/reports-organizational-views

     

    SELECT c.[Name], nri.[Name], fbf.[Depth]
    FROM FolderBaseFolder fbf
    INNER JOIN vNonResourceItem nri
    ON fbf.[ParentFolderGuid] = nri.[Guid]
    INNER JOIN ScopeMembership sm
    ON fbf.[FolderGuid] = sm.[ScopeCollectionGuid]
    INNER JOIN vComputer c
    ON sm.[ResourceGuid] = c.[Guid]
    WHERE c.[IsManaged] = 1
    AND nri.[Name] = 'Saturday'
    AND ( nri.[ClassGuid] IN ( SELECT cbc.[ClassGuid] FROM ClassBaseClass cbc WHERE cbc.[BaseClassGuid] IN ( '2A20DBEB-F827-4b02-BDCB-815B1ED6A706', 'A1BBB63E-7194-4282-AB92-64E27FA2C18D' ) ) )
    ORDER BY sm.[ResourceGuid], fbf.[FolderGuid], fbf.[Depth]
     

    The query listed there gets me close.  However the top level group shows 180 members, yet this query only pulls back 175.  Suggestions?



  • 3.  RE: Help with report of all resources under an organizational view

    Posted Mar 05, 2012 10:41 AM

    Maybe you have unmanaged computers inside?



  • 4.  RE: Help with report of all resources under an organizational view

    Posted Mar 05, 2012 10:53 AM

    You nailed it.

    As an addition to your query, is there an easy way to display the hierarchy path of all resources in the query?  I'm trying to figure that one out, but I fear it is beyond my limited SQL abilities.



  • 5.  RE: Help with report of all resources under an organizational view

    Posted Mar 05, 2012 10:58 AM

    Like i said in this other thread, this will require some programming.

    Thats probably the reason why there is no report for this in the system.



  • 6.  RE: Help with report of all resources under an organizational view

    Posted Mar 05, 2012 05:12 PM

    Is security involved in your org groups?  If not, I'm not sure why these are organizational groups instead of filters with inclusions, e.g. WHERE vc.Name IN ('Resource A','Resource B')

    Organizational views and groups really shine for security.  They aren't my first choice for targeting, where I'd rather use targets and filters.



  • 7.  RE: Help with report of all resources under an organizational view

    Posted Mar 07, 2012 03:23 PM

    I started with filters, but really liked OG's better for a couple of reasons:

    1. Each computer can only exist once.  I was finding that we were having servers in multiple patching filters.  I know that's more of a tracking issue, but its one easily resolved with OGs.

    2. Heirarchy.  It's extremely easy for me to click on Week 2 and see everything listed beneath it.  While that can also be setup with filters, its completely manual.  With OGs, as soon as I add another group with more servers under Week 2, it is instantly reflected in the Week 2 count.

    That said, I was able to work out a query that shows me the two lowest groups that a computer belongs to, which works perfectly for the way we have it setup.  Well, technically I wasn't, because a coworker and I were somewhat racing to see who could do it first.  He beat me.  sad  Here is the query we used:

     

     

    SELECT( nri.[Name] +'\'+(
    SELECT 
    nri2.[Name]
    FROM 
    FolderBaseFolder fbf2
    INNER JOIN 
    vNonResourceItem nri2 ON fbf2.[ParentFolderGuid] = nri2.[Guid]
    WHERE 
    fbf2.depth = 0
    AND fbf.FolderGuid=fbf2.ParentFolderGuid
    )) As BaseLevel,c.[Name]
     
    FROM 
     
    FolderBaseFolder fbf
     
    INNER JOIN 
     
    vNonResourceItem nri ON fbf.[ParentFolderGuid] = nri.[Guid]
     
    INNER JOIN 
     
    ScopeMembership sm ON fbf.[FolderGuid] = sm.[ScopeCollectionGuid]
     
    INNER JOIN 
     
    vComputer c ON sm.[ResourceGuid] = c.[Guid]
     
    WHERE 
     
    nri.Name LIKE 'W1%'
    AND c.[IsManaged] = 1
    AND nri.Attributes = 512
    AND fbf.FolderGuid <> '25134AE7-5259-4139-8686-F7E76F9EA1D3'
    AND fbf.FolderGuid <> '328CD0C5-50CC-4F5C-B65B-F640E30ED7BB'
    AND fbf.FolderGuid <> '523A368F-9487-4CBE-9281-11AC638D9E5A'
    AND fbf.FolderGuid <> 'DC0A2FC0-8BCB-4BC0-8D7B-DBF4CF81D4EF'
    AND fbf.depth = 1
    AND (nri.[ClassGuid] IN (
    SELECT 
    cbc.[ClassGuid] 
    FROM 
    ClassBaseClass cbc 
    WHERE 
    cbc.[BaseClassGuid] ='A1BBB63E-7194-4282-AB92-64E27FA2C18D'
    ) )
    ORDER BY 
     
    BaseLevel, c.[Name]

    And I have attached screenshots of how our org groups are setup (company info excluded to protect the innocent).  Hopefully this helps someone.