Power to the people.
That's what this whole series of articles has hopefully been about. Taking information, along with real-world scenarios that we as Altiris administrators face every day, and sloshing them together to hopefully provide, in each part, a little more insights into my favorite canned report, the Count of Distinct Add/Remove Program Applications report.
This is the third and final article in the series. I hope you've enjoyed them.
I saved what I consider the best bang for our last exercise. In parts I and II, we tweaked the CoDARPA report to open results in a new window. We tackled a real-world reporting request from our internal packageers, as I call them. Now, we're going to do something special: Leverage Microsoft Exchange (C) Inventory user data to provide even more power to our CoDARPA report - because computer names are great, but what we really want out of this report is people data. We want to know what organizations are using all of the Adobe licenses. We want contact information to quickly be able to call Bonnie in Sales and ask her if she really needs Project 2003, since, according to Application Metering, she hasn't even used since LAST April!
So let's get started. Here's our scenario: I'm an Altiris Administrator. I'm tired of people coming back to me after I send them computer inventory reports asking for user data. While I've become adroit at performing vLookups in Microsoft Excel©, I'm tired of doing it. I want user, application, and computer data all in one happy little report.
Sound a little difficult? Using the skills you've developed over the earlier parts of our series, you'll find it's not that difficult at all. We'll walk through it like the others. Updated code is in bold, and I'll skip over some of the basic stuff we covered in parts I and II.
1. Clone the right-clickable report we created in part I.
2. Click 'Edit This Report'
We talked about different query levels earlier. Which level do you think we'll want to add our SQL data to for this part of our report? Think about it before you look to the next line.
Have you thought about it?
Okay, it's the second level, which, in this case, is level 1. (Complicated stuff, I know.)
3. Click on the yellow editing pencil next to level one in our queries section.
So the way these queries are written is interesting.
In the first SELECT DISTINCT section, we're kind of cherry picking the different fields we want to display in our report page. There's even some tweaking going on here. Look at car.[Name].
If you follow that down to the FROM section, you'll see that car is really a column from the Cmn_OS_Add_Remove_Programs_Common table.
It's the column that references the Name. What's neat about it is that with the editor, we can add a different, more descriptive name for the columns in our headers as is done in this case. Where "Name" is replaced with "Application Name".
Now that we understand this relationship, we can begin to formulate a plan of attack for grabbing user data, and injecting it into our report.
4. Using a computer in your inventory and Part II of this series, find the table housing the location of the User Inventory Data.
5. Using SQL Management Studio, find the reference GUID in that table. Hint: It has the word GUID in it
6. Join that table to the query at level 1.
This one I'm helping you with - because we haven't done a join in our earlier exercises.
6A. Looking at the FROM section, we can see a lot of joins. The joins are ways for other tables to hang out and associate with our cool report data results. They're like friends of ours. Just like your friends are bonded to you by unique experiences you have shared together (Vegas, anyone?), the tables are bonded to our query by their unique identifiers, or GUIDs. So the join statement must have:
- The table name we're going to hang with
- A shortened nickname for the query (because friends always give friends nicknames)
- A description of the unique bond (or GUID location)
So here's what ours will look like for our people data:
FROM dbo.Item i
JOIN dbo.Inv_AeX_OS_Add_Remove_Programs_spt a1
ON a1.[_ResourceGuid] = i.Guid
JOIN dbo.Cmn_OS_Add_Remove_Programs_Common car
ON car.[_KeyHash] = a1.[_KeyHash]
JOIN dbo.Inv_AeX_AC_Identification d
ON d.[_ResourceGuid] = i.Guid
JOIN dbo.Inv_Aex_EU_Contact_Detail p
ON p.[_ResourceGuid] = i.Guid
JOIN dbo.CollectionMembership cm
ON cm.ResourceGuid = d.[_ResourceGuid]
JOIN dbo.Item it
ON it.Guid = cm.CollectionGuid
WHERE d.[System Type] = 'Win32'
AND i.[Name] LIKE '%ComputerName%'
AND d.Domain LIKE '%Domain%'
AND it.[Guid] = '%Collection%'
As you can see, I gave our new friend the nickname of 'p'. It's catchy, and more importantly, no one else in our query has the nickname of p.
To make sure our query remembers p, we reference it there in the second line. Did you find the right resource guid in the exercise? If so good job!
We're linking to the i.Guid, which is the reference guid for the app on the workstation we're buddying up with.
So now we're friends with the Exchange Contact Detail table. Remember how I mentioned the SELECT DISTINCT section?
Now we can:
7. Go to the SELECT DISTINCT section and add the information we want from our people data table to our report results. Here's what mine, and the example looks like:
SELECT DISTINCT
i.[Name] [Name],
p.[Exchange Display Name],
p.[Title],
p.[Department],
p.[Office Location],
p.[Office Telephone],
car.[Name] [Application Name],
car.[Version],
car.[Publisher],
a1.[_ResourceGuid]
FROM dbo.Item i
Note: Don't forget to bracket the column name, and include a comma at the end of the line!
There's our friend p, adding some really important data to an already powerful report.
8. Click on 'Run' to confirm your SQL is correct.
9. Click 'Finish'
10. Click 'Apply'
Go ahead and run your report - Note that we're not providing user data in the first query level.
When you select an application, and with the power of right-clickability open it into a new window, you'll see computer, user, and application data - all in one, happy place.
I hope this article, and series, was helpful for you.