Following the previous post on Adobe products reporting, Here are a few tips to give a better look to the report. See the original report below
And the final report below
The first thing that I did not like is that the Product is subdivided by Version (8, 9, X) but also by service packs (8.0.1, etc). This subdivision makes reporting on number of licenses more difficult, so I choose to use v_GS_Installed_Software_Categorized.ProductID0 to check that it’s a full version (This works for Adobe products, where ‘none’ or Null would mean that it’s an update – I did not test that it works also with other products so be prudent).
Another thing is that Asset Intelligence does not correct wrong package Display names like is that case for ‘Adobe Acrobat 8 Standard’ – there is an additional space before the 8. I used the v_GS_Mapped_Add_Remove_Programs table to get the correct name. Note that this table lists the changes that you make to the Software Inventory Client Agent Properties.
Next step, give the full user name instead of the samAccountName which is more criptic for the non-IT guy. For this, link the v_R_User table via the User_Name0 field
Finally, and that is a point that I find interesting, you can limit the products that you want in your report based on a Label being present. I choose that Report for Label3 (Tag3ID) to filter which adobe products i would present in the report (setting Label3 to Report for another Adobe Product would directly make it available in the report)
Same point for Tag1ID, which in my case will track which software is standard and which is not. (It is not being used YET in this report, but i will post the changes when I am happy with the result).
Here is the query I used:
SELECT DISTINCT v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ARPDisplayName0 AS ‘Product’, v_R_System_Valid.Netbios_Name0,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.VersionMajor0, v_R_System_Valid.Resource_Domain_OR_Workgr0, DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS Last_Scan, v_R_System_Valid.User_Name0, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID, v_LU_SoftwareList_Local.SoftwareID, v_LU_SoftwareList_Local.Tag3ID, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductCode0, v_GS_Mapped_Add_Remove_Programs.DisplayName0, v_GS_Mapped_Add_Remove_Programs.ProdID0, v_LU_SoftwareList_Local.Tag1ID, v_R_User.Department0, v_R_User.Full_User_Name0, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductID0
FROM v_R_System_Valid INNER JOIN v_GS_INSTALLED_SOFTWARE_CATEGORIZED ON v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID = v_R_System_Valid.ResourceID INNER JOIN v_GS_WORKSTATION_STATUS ON v_R_System_Valid.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN v_LU_SoftwareList_Local ON v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID = v_LU_SoftwareList_Local.SoftwareID LEFT OUTER JOIN v_GS_Mapped_Add_Remove_Programs ON v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductCode0 = v_GS_Mapped_Add_Remove_Programs.ProdID0 INNER JOIN v_R_User ON v_R_System_Valid.User_Name0 = v_R_User.User_Name0
WHERE (v_LU_SoftwareList_Local.Tag3ID = ‘100013’) AND (v_R_System_Valid.Resource_Domain_OR_Workgr0 = ‘Mydomain’) AND (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ARPDisplayName0 LIKE ‘Adobe%’) AND (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductID0 Like ‘none’ OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductID0 IS NOT NULL)
ORDER BY ‘Product’, v_R_System_Valid.Netbios_Name0
And Here are the main areas of change in the report
One last thing was to change the colour of the font when the last scan is more than 20 days. (see below the code linked to the Last_scan text box Color property).
As you can see, the reports are much better looking and customizable than was the case with Sms and pre-Sccm SP2 reports. I look forward to see how the final Sccm 2012 reports will look like but based on the Beta I have the feeling that they will be quite basic…