Client Management Suite

Hi,

I am trying to look for some way to create a report that shows the computers on Organization Views and Groups.

Basically we have many OU's based on regions, functions. I am trying to get list of computers on each OU on a excel sheet.

Thanks

Not an easy task. I have seen a lot of requests for this, but never a solution.

This is not exactly what you want, but maybe someone can take it as a start to build the hierachie.

Will require some coding. This is as close as I can get.

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.[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]

1. Once you have the output of that SQL, paste it into Excel.  First three columns are SQL data.
2. Run MAX(C2:C999999) -- this is the number of new columns you will need.  Let's suppose this number is 5, as it would be for the Default View.
3. Title Column D as 5, Column E as 4, and so forth to 0 with Column I
4. In Column J, on the last row of the first computer (for me it was Row 6), enter this formula in J6:
   =IF(A6<>A7,IF(D6="Default",CONCATENATE(D6," > ",E5," > ",F4," > ",G3," > ",H2," > ",I1),IF(E6="Default",CONCATENATE(E6," > ",F5," > ",G4," > ",H3," > ",I2),
   The formula is written to work for two levels that we might care about: a 5-level deep resource (a Virtual Machine) and a 4-level deep resource (a Computer).  If resources could exist at any level of depth, you would continue to write out the IF statements for each depth.
   It does this by checking to see if this is the final entry for our computer name in Column A.  If the following row in Column A is different, we know that this is the "most deep" entry for this computer, since Column C (Depth) is sorted in ascending numeric value per computer name.  So if the computer name is about to change, then this is the final row, and it should check to see how deep this entry goes.  Because the top level of our OV, 'Default', is not always in the same place, we check to see -- is this a 5-level deep (Column D's cell = "Default") or a 4-level deep (Column E's cell = "Default") resource?  And depending on the result it just concatenates the data.
   You'll receive a blank cell if there's nothing going on (not the deepest level for a resource), or output like this if it is: Default > All Resources > Asset > Network Resource > Computer > Virtual Machine
5. This assumes you have a top-level OG called 'Default' within the OV.

It's not perfect, but maybe it gets you somewhere from an Excel perspective.  And yeah, I can't believe there isn't a report for this.

Will it be easier to have C sorted descending? Then you have "most deep" entry ontop. Excel is not my cup of tea ^^.

ORDER BY sm.[ResourceGuid], fbf.[FolderGuid], fbf.[Depth] desc

Thanks a lot Mistral. Actually this query saved my time.

Also I renamed OU inorder to get the right values.

Also Thanks Mike for the excel tips.