Configuration Manager 2012 – Reporting on Visual Studio Licenses

This was posted for Kevin Saye…

Reporting on Visual Studio License, via System Center Configuration Manager 2012

I recently had a request to report on the number of Visual Studio installs in a customer environment. Honestly, the customer did not have a full understanding of how many and where it was installed. Luckily, the customer had System Center Configuration Manager 2012 installed, so this was a snap.

I was asked "Kevin, how can I report on where it is installed?"

Answer: "Simple, write a report that shows only what you want. What do you want?"

Next question: "Umm, machine name, version of Visual Studio, User and OS".

Next Answer: "Great, do these 8 steps…."

 

Step 1.    Goto your Configuration Manager Reports site: https://servername/Reports and click "Report Builder"

Step 2.    Create a Table Report:

Step 3.    Chose the dataset that matches your Configuration Manager install:

Step 4.    Click the "Edit as Text" and type in the following query. (See appendix for a copy and paste of the query)

Step 5.    Set the "ProductName" as the Row Group and all others as Values.

Step 6.    Resize the report and title it as you like.

Step 7.    Save the report (rdl file). I saved mine directly in the "Software – Companies and Products" folder in the System Center directory.

Step 8.    Simply run the newly published report to see the results. You can also export to Excel here to slice and dice as you wish.

Appendix:

select distinct P.DisplayName0 as ProductName,

    P.InstallDate0 as InstallDate,

    S.Netbios_Name0 as ComputerName,

    S.User_Name0 as LastUser,

    S.Operating_System_Name_and0 as OperatingSystem,

    S.Last_Logon_Timestamp0 as LastSeen

    

from [dbo].[v_Add_Remove_Programs] as P,

    [dbo].[v_R_System] as S

                

where

    S.ResourceID = P.ResourceID

    and

    (P.DisplayName0 like 'Microsoft Visual Studio %Premium%'

    or P.DisplayName0 like 'Microsoft Visual Studio %Ultimate%'

    or P.DisplayName0 like 'Microsoft Visual Studio %Professional%'

    or P.DisplayName0 like 'Microsoft Visual Studio %Express%' )

    and P.InstallDate0 is not null

order by S.Netbios_Name0