Lately some customers of mine asked me to count all their Microsoft licenses in use. Since I’ve seen requests for this “feature” on a couple of forums I thought I might share this with you.
I really don’t like the idea of sitting and counting endless of computers with Microsoft Office Suite on them, so I created a couple of reports that does this for me and the hooked them up to a Dashboard that can be sent off to all those big bosses (Just remember to give them rights to view the reports!).
In the pictures below you can see how it turned out.
What you need to do is simple. Start off by creating a report. I chose to start with the Microsoft Office Suite since it felt natural to start with the biggest product. Nothing special about this report really except that it can not include any prompts. Also, I chose to create a new category for all my dashboard reports.
The SQL query should look like this:
Select ARP.Publisher0, ARP.DisplayName0, COUNT(SYS.Netbios_Name0) AS 'Count',
ARP.ProdID0 AS 'Product ID'
FROM v_R_System SYS
JOIN
v_Add_Remove_Programs ARP on SYS.ResourceID = ARP.ResourceID
WHERE
ARP.ProdID0 = '{90120000-0014-0000-0000-0000000FF1CE}'
OR
ARP.ProdID0 = '{90140000-0011-0000-1000-0000000FF1CE}'
OR
ARP.ProdID0 = '{90120000-0030-0000-0000-0000000FF1CE}'
GROUP BY ARP.DisplayName0, ARP.Publisher0, ARP.ProdID0
You do need to change the Product GUIDs in the “Where” part of the query if you need other versions of the Microsoft Office Suite. I called the report “Count All Computers with Microsoft Office Suite”.
Next step is to create a report to list computers with the product in question (Picture #002). I used the following SQL query for that:
Select DISTINCT SYS.Netbios_Name0, SYS.User_Name0, ARP.DisplayName0,
ARP.Publisher0
FROM v_R_System SYS
JOIN
v_Add_Remove_Programs ARP on SYS.ResourceID = ARP.ResourceID
WHERE
ARP.ProdID0 = @ARPProdID
Order by SYS.Netbios_Name0
Then, as you can see in Picture #004 I linked the first report to this report. When that was done I could make copies on the “Count All Computers with Microsoft Office Suite”-report for all other products.
When all reports are done its time to create a Dashboard. It’s pretty straight forward, just decide how many rows/columns you need and the add the different reports you like to view.
Hope it helps!
