Client Management Suite

Here's what I'm up against. Every one of our 1500+ branches is allowed to buy their own shrink-wrapped software if they have a need for a product not provided under the "Enterprise" umbrella. Software Licensing is a nightmare during audit, of course. To stay ahead, I'd like to be able to provide an installed software list, by branch, to our license compliance folks so they can track down licenses. I have a very manual way to do one branch at a time currently: Step 1: Get the GUID for the custom filter I've written that the branches computers dynamically fall into. Select Guid from vCollection where [name] like '%Branch xyz123%' Step 2: Get a list of all software from add/remove programs at that branch Select invAR.DisplayName, invAR.[DisplayVersion], invAR.Publisher, Count(*) from Inv_AddRemoveProgram invAR Join vComputer vc on invAR.[_ResourceGuid] = vc.[Guid] Where vc.[Guid] in ( Select [ResourceGuid] from CollectionMembership where CollectionGuid = <guid 1="" from="" step="">) Group by invAR.[DisplayName], invAR.[DisplayVersion], invAR.[Publisher] In this new process, I need a way to loop through every custom filter and run the second step automagically. I have my query to get this GUID list from vCollection: --SQL query Select GUID from vCollection where [ProductGuid] = 'D0E33520-C160-11D2-8612-00104B74A9DF' and [Attributes] = 0 and [Name] like '%branch%' --End SQL Query I need a subroutine or something that checks CollectionMembership and skips that CollectionGUID if that particular collection doesn't have any computers in it, as well as some way to let me know that branch had nothing. The end result would eventually be a custom report that will give me the following information Level 1: Branch # and a count of software in the add/remove table for that branch. Level 2: This drilldown shows the software for the branch that was selected by Displayname, DisplayVersion, and Publisher. Level 3: This drilldown shows the computers where the selected software is installed at that branch</guid>

It might be easier if you skipped the filter membership step.  Can you simply further restrict the computers you're looking at by copying the filter criteria?

For example, you're essentially doing this:
SELECT vc.Guid FROM vComputer vc WHERE vc.Guid IN (<my filter's membership>)

And then separately, you have this filter defined:
SELECT vc.Guid FROM vComputer vc WHERE vc.[OS Name] LIKE '%XP%'

Instead, use the filter's SQL as the basis for your software report, then add in the data you require from Inv_AddRemoveProgram.  Alternatively, you could add a filter parameter to your software report so that you're restricting the report to only the filter you select.

Does this get you anywhere?  I may not be understanding the report request properly.

I'm not filtering by OS.  The OS of the devices isn't a requirement.

The filter membership is the key to the data I need.  I don't want to restrict the data to a filter - that's what I'm doing manually now.  I need to go the other direction entirely - I need all the data for every filter at once.

I need to automate the process so the first step of my process loops automatically, turning my two-step process into a one-step, grabbing an aggregate report for all populated filters, and generating output that looks something like this:

Branch 0001

Branch 0002

(same table as above)

Branch 0003

(same table as above)

(...)

So, basically, an installed software at every branch master report that I can dump to excel for the folks that have requested the data.  The thing that is killing me is that I have a procedural programming background, and working with large "clouds" of data has me confused.

Pseudocode looks something like this:

1.  Get a list of GUIDs for all filters with "Branch" in the name.

2. Loop through this list, and query CollectionMembership with the current GUID to get the computers that are in that filter. 

3.  With the CollectionMembership GUIDs, query Inv_AddRemoveProgram with this group and get back an aggregate list of all software installed at that branch.

4.  Format the results and add to result set

5.  Loop back to step 2 and get the next GUID in the list.  Repeat until finished.

The local DBA here is helping me understand cursors and temporary tables, and that's the route we're looking at currently. 

I'm closing in.  The results of this query are very ugly and painful to look at, but this might help show what I'm after:

declare @RowNum int

declare @BranchID UniqueIdentifier

DECLARE BranchCursor CURSOR

FOR (Select Distinct Guid from vCollection where [name] like '%Branch%')

OPEN BranchCursor

FETCH NEXT FROM BranchCursor

INTO @BranchID

set @RowNum = 0

WHILE @@FETCH_STATUS = 0

BEGIN

Select vColl.Name as 'Branch Name', invAR.DisplayName, invAR.[DisplayVersion], invAR.Publisher, Count(*)

from Inv_AddRemoveProgram invAR

Join vComputer vc on invAR.[_ResourceGuid] = vc.[Guid]

Join vCollection vColl on vColl.[Guid] = @BranchID

Where

vc.[Guid] in

(

Select [ResourceGuid] from CollectionMembership

where

CollectionGuid = @BranchID

)

Group by vColl.[Name], invAR.[DisplayName], invAR.[DisplayVersion], invAR.[Publisher]

Order by DisplayName

FETCH NEXT FROM BranchCursor

INTO @BranchID

END

CLOSE BranchCursor

DEALLOCATE BranchCursor

 A aA

A screenshot of some of the result set is attached.

Right, OS name was just an example of how you could remove the need to check filter membership by making it part of the SQL query.  But that's irrelevant now that I see the result you want.

Try defining Branch as a column.  Then use UNION to bring the results together.  I've done this before when querying multiple databases.  For example:

SELECT
'NS6' as 'Source NS',
vc.[OS Name],
COUNT(vc.Guid) as 'Number'
FROM Altiris6-sql.Altiris.dbo.vComputer vc
GROUP BY [Source NS],vc.[OS Name]

UNION

SELECT
'NS7' as 'Source NS',
vc.[OS Name],
COUNT(vc.Guid) as 'Number'
FROM Altiris7-sql.Symantec_CMDB.dbo.vComputer vc
GROUP BY [Source NS],vc.[OS Name]

When you define the source and then join these, you get a result like this:
NS6...Windows XP...1084
NS6...Windows Server 2003...48
NS6...Windows 7...42
NS7...Windows XP...12
NS7...Windows Server 2003...17
NS7...Windows 7...7484
NS7...Windows Server 2008 R2...24

Does that make sense?  I didn't look exactly at your SQL, but I saw you were able to define what a Branch was in each query, and you had multiple queries you wanted to merge, so this seemed like a good approach.

im supprised that cms 7.1 doesnt offer these kind of software reports right out of the box

There is a parameter you can choose for MSD and task-based delivery, but you have to choose the task or MSD. Reporting in 7.1 is a mess - half the reports give conflicting information for the same data, many of the reports are based on task tables that are purged so often in a large environment that they are useless.  We've resorted to writing our own queries because we can't rely on the built-in reporting.

What I'd like to do in the next dev iteration is take all these separate result sets that I'm getting from each iteration and append them to a single result set.  I also need to remove the branches that have no members in their respective branch filter.