Tweaking the Add/Remove Programs Report Part II: Adding Another Level To Your Query

In part one of this article series, I showed you how a small change can make a powerful impact on the day-to-day results of report generation using the Distinct Add/Remove Programs Report. Here, in part II, we're diving a little deeper - making changes to SQL, and understanding a few exploration tools available within the Altiris interface to get us to the data behind the reports. Let's get started:

Modifying Query Level Data

First, let's explain what query level data is. In the CoDARP report, if you've followed the previous exercise, you'll note we have 2 query levels to our data. Put very simply, level one gives us our first page of results (count, application name), and level two gives us the right-clickable drilldown results (Computer Name, AppName & Version).

So, level data is the 'stuff' that makes what we see at each of those points. Make sense? Good.

Note: This is a very simplified explanation. There are many more things that go on here, but for the sake of our exercise, I'm leaving a lot of that stuff out.

Now I want to outline why we would want to modify level data in the first place. One of the great things about reporting in Altiris is the flexibility of the platform, and the dizzying array of reports made available to us right out of the box. Understanding how to manipulate results, and provide different data to your internal clients is really where power over the platform begins.

NOTE: I'm breaking up our level exercise into two separate articles: Level One, where we'll actually add another level to the initial report results, and Level Two, (or Part III) where we'll add some user information to our drilldown results.

Adding a Level to the Count of Distinct Add/Remove Program Applications

The Scenario: Your Packaging Team Wants to Be Able to View the Add/Remove Program Applications Report data with Install or Uninstall Data about the Application, in the initial results screen of install counts for all of the applications on that first count screen.

You don't want to re-invent the wheel, you do want to get this done quickly. If you're in a REAL hurry, you can download the attached example called 'Exercise1'.

I would challenge you though to read through these steps and understand the process.

Here's how it happens:

1. Clone the Original Report. (Or, clone the Right-clickable one we made in our first exercise in Part I).

2. Add the following SQL to level 0, which adds or 'declares' in SQL speak, a third level to our query. You'll note that in the original canned report, when running it, you have two levels to choose from. We're adding a third level, in accordance with our scenario, to allow our techs to see install data.

Updated code is in bold.

   The step by step activities to get here are:
   A. Click on Edit this Report
   B. Click on the Yellow Pencil just to the left of the Level 0 Query, and add the line in bold:

DECLARE @level1 varchar(100) 
DECLARE @level2 varchar(100) 
DECLARE @level3 varchar(100) 

DECLARE @group1 varchar(100) 
DECLARE @group2 varchar(100)

3. Now that the level's declared, we'll need to set the level in our query around the @SQL statement. I've set it here:

IF %Level1% = 'None' 
      BEGIN 
      SET @level1 = '''''' 
      END 
ELSE 
      BEGIN 
      SET @level1 = '%Level1%' 
      SET @group1 = %Level1% + ',' 
      SET @order1 = %Level1% + ',' 
     END 

IF %Level2% = 'None' 
     SET @level2 = '''''' 
ELSE 
     BEGIN 
     SET @level2 = '%Level2%' 
      SET @level3 = '%Level3%' 
     SET @group2 = %Level2% + ',' 
     SET @order2 = %Level2% + ',' 
   END 

4. Our next challenge is to identify where in the database the information our clients is looking for lives, and how to best bring it forward in our report. There are different ways to do this, but I usually like to keep as much of the original structure intact. So back to our scenario we go:

After checking the inventory tab on a workstation in my environment,, I found that the Install and Uninstall Path information was stored under Inventory, Aex_OS_Add_Remove_Programs, as you can see:

5. Now, I wasn't sure which table had the columns, so I hopped over to my database server to check. Ideally, the information I'm looking for will already exist under one of the tables already declared in my query.

Table structure usually follows consistently with solutions, so I'm looking for something that matches my folder tree structure above. Inventory is shortened to Inv in the database, and the rest is pretty self explanatory.

I see the table which has the data I'm looking for: Inv_Aex_OS_Add_Remove_Programs_spt. I confirm the table names, and write them down for my report: Uninstall Path and Install Path.

Here's where I'd like you to investigate something on your own in a Hands-On Exercise.

A. Open your Notification Server Console.
B. Find the Count of Distinct Add/Remove Program Applications and Clone it.
C. Call this: Test Exercise Report.
D. Click 'Edit this Report'.
E. Click on the pencil next to level 0 under Queries.
F. Can you identify if this table, Inv_Aex_OS_Add_Remove_Programs_spt, is used in this query?
   If so, can you tell me what reference is provided for it?
G. Close out of the report.
H. Delete Test Exercise Report.

Now for the answer:

Yes! We're already calling this table in our report - in the from section - which will make our work a lot easier. Since it's already making a reference to the table, we just need to know the reference given. That reference, as you saw in our exercise, is a1 like the steak sauce, (but not really).

This is important though, because as we add references to the two columns we need for Install Path and Uninstall Path, we'll use this as our reference pointer.

6. We're going to add our level 3 data to the SELECT statement as follows, again, in bold:

SET @sql = 
'SELECT COUNT(DISTINCT i.[Name]) [Number of Occurrences], 
' + @level1 + ' [Level 1], 
' + @level2 + ' [Level 2], 
' + @level3 + ' [Level 3] 

7. Now, SQL needs us to add the columns, so we'll do so in the GROUP BY section as detailed below, using our a1 reference:

GROUP BY car.[Name],a1.[Install Path],a1.[Uninstall Path],' + @group1 + @group2 + '' 

8. Click 'Run' in the query window to confirm you at least get the 0 results window.

9. Click Apply twice.

10. Click Edit this Report. We're half way there.

11. Now we're going to add the Drop-Down Menu for our Packaging Engineers to Select The Uninstall or Install Path Data in their reports.

   A. Click on the 'New Parameter' button under Global Parameters.
   B. Give it a name of Level3, and value of None|Uninstall Path;a1.[Uninstall Path]|Install Path;a1.[Install Path]
   C. Here's what the Parameter should look like:

This will create a third dropdown, with options for None, Uninstall Path, and Install Path.

12. Click OK.

13. Click Apply.

You've done it! Go on and run your report, and note the additional column for the Install or Uninstall Path in your report results.

In my next article, I'll detail modifying the second level report, adding user Exchange data to make the results more powerful for you and your enterprise. Stay tuned.