Software Metering

Leon TaljaardSystem Center Specialist - Architecture
CERTIFIED EXPERT
Published:
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.

Now we all know that there is some piece of software on our machines that we possibly needed at some time or thought it might be a nice to have and have since never used it again, now you might say “No ways, I always use everything I have installed” but believe me, there are many users with licensed applications in an enterprise environment that do not use the application anymore.

These applications and licenses can rather be transferred to another user in your organization that really needs it or it could just be kept as a spare.  Now with Microsoft System Center Configuration Manager you not only can deploy or uninstall these applications, but you can also check how often they have been used.

This is called “Software Metering” and with this you can basically report on any application and its usage, how often it has been executed and when last it was used.

Based on this you can get a very nice report that would list for example users that have not run Visio Professional 2010 for the last 6 months, then with that information you would be able to add these computers to a “Uninstall” collection and SCCM would then trigger an uninstall of the application.

There are one or two other products on the market as far as Software Metering is concerned, these would include SofTrack and Desktop Central. Now because we already had SCCM and Orchestrator deployed I had to try find a way not only to get the reporting on what our users were using and how they were using it but also to try find a way of automating this whole process of removing un used software with what I had at my disposal. This is entirely based on my own experiences and requirements within my company.

This article is based on Neil Peterson’s great work which you can go have a look at by clicking this link.

I created 2 collections per product that we metered, I will run through 2 of my collections that are in use as this will give you a quick view of how the initial setup within SCCM was done.

The first collection called "Metering - Visio run last 12 months" collects all computers that have run Visio in the last 12 months. Below is the collection query and in this query you will see that I have also included the metering ruleID's for our specific metering rules.

Visio_runlast12monthsQuery.PNGThe second collection is the one I actually use within the runbook and this collection will collect the computers that have not run the specific application in the specified time period. This collection membership rule will have the collection "Exclude" rule as well as a query rule in it, the exclude collection rule will exclude all computers collected in the first collection and the query rule will collect all computers that have the product installed. The below shows what this would look like.

visio_installed2.PNG
The collections I created was what worked for me and how needed it done and it gives you a quick view on how you could also setup yours, but let's focus  and focus on the actual automation of the runbook within Orchestrator.

Now for my company our main concern at the time was to get a view of our Project and Visio installs and making sure that our numbers and usage was correct, so here is a quick view of my collections within SCCM for collecting the devices I required.
 
SCCM-Collections.PNG 
So let’s get to the fun part

below is my first “Main” runbook for Visio. Now this runbook will collect the members of the “Metering - Visio not run last 12 months” collection and pass each member onto the next “Run .Net Script” activity.
 
Visio_Main.PNG 
Run-.Net-Script.PNGThe script takes each member from the "Get Collections" activity and checks if the Visio that is installed is a 32bit or 64bit version. The script then publishes either “32Bit” or “64Bit”, this in turn then follows the respective link which have filters on each of them based on the published data from the “Run .Net Script” activity.

Here is the actual Script I used
#------------- Database Connection and Computer to check ------------#
                      
                      $dataSource = 'SQL Server SCCM Database is on'
                      $ComputerName = 'Collection Member Name'
                      $database = ‘SCCM Database’
                      $connectionString = “Server=$dataSource;Database=$database;Integrated Security=True;”
                      
                      $connection = New-Object System.Data.SqlClient.SqlConnection
                      $connection.ConnectionString = $connectionString
                      
                      $connection.Open()
                      
                      #------------- 32Bit Check ------------#
                      
                      $query1 = "SELECT DISTINCT v_R_System.Netbios_Name0, A.DisplayName0 AS 'DisplayName'
                      FROM         v_R_System INNER JOIN
                                            v_GS_ADD_REMOVE_PROGRAMS AS A ON v_R_System.ResourceID = A.ResourceID
                      WHERE     (A.DisplayName0 IN ('Microsoft Visio Premium 2010', 'Microsoft Visio Professional 2010', 
                                            'Microsoft Visio Standard 2010', 'Microsoft Visio Standard 2013','Microsoft Visio Professional 2013'))  and 
                                            v_R_System.Netbios_Name0 = '$ComputerName'     "
                      
                      $command = $connection.CreateCommand()
                      $command.CommandText = $query1
                      
                      $result1 = $command.ExecuteReader()
                      
                      $table1 = new-object “System.Data.DataTable”
                      $table1.Load($result1)
                      
                      
                      
                      #------------- 64Bit Check ------------#
                      
                      $query2 = "SELECT DISTINCT v_R_System.Netbios_Name0, v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 AS 'DisplayName'
                      FROM         v_R_System INNER JOIN
                                   v_GS_ADD_REMOVE_PROGRAMS_64  ON v_R_System.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID
                      WHERE     (v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0 IN ('Microsoft Visio Premium 2010', 'Microsoft Visio Professional 2010', 
                                            'Microsoft Visio Standard 2010', 'Microsoft Visio Standard 2013','Microsoft Visio Professional 2013'))
                                            and 
                                            v_R_System.Netbios_Name0 = '$ComputerName' "
                      
                      $command = $connection.CreateCommand()
                      $command.CommandText = $query2
                      
                      $result2 = $command.ExecuteReader()
                      
                      $table2 = new-object “System.Data.DataTable”
                      $table2.Load($result2)
                      
                      
                      if ($table1 -ne $null) {
                      
                      $Result = '32Bit'
                      
                      }
                      
                      ElseIf ($table2 -ne $null) {
                      
                      $Result = '64Bit'
                      
                      }
                      
                      Else { 
                      
                      $Result = 'Nothing Found'
                      
                      }
                      
                      
                      $Result
                      
                      $connection.Close()

Open in new window


Check-Product-Name.PNG
Now regardless of which link was chosen based on the previous activity filter the “Check Product Name” activity will query my SCCM database for the Visio edition installed (Standard/Professional/Premium). I have a filer within the script to only look for 2010 versions currently but adding in 2013 would be an option as well.

Here is the "Check Product Name" script I used
SELECT DISTINCT v_R_System.Netbios_Name0, A.DisplayName0 AS '32Bit Version'
                      FROM         v_R_System INNER JOIN
                                            v_GS_ADD_REMOVE_PROGRAMS AS A ON v_R_System.ResourceID = A.ResourceID
                      WHERE     (A.DisplayName0 IN ('Microsoft Visio Premium 2010', 'Microsoft Visio Professional 2010', 
                                            'Microsoft Visio Standard 2010', 'Microsoft Visio Standard 2013','Microsoft Visio Professional 2013'))  and 
                                            v_R_System.Netbios_Name0 = 'Member Name from Get Collection Members'

Open in new window


Trigger-Uninstall.PNGLastly the Invoke Runbook activity “Trigger Uninstall” will then be taking the required info and actually passing it onto the second runbook where all the real work happens. The data consumed by this activity is shown below.

Trigger-Uninstall_Data.PNG 
 
The Second runbook does all the work of placing the identified members into the respective uninstall collections that I have created, these collections have an “Uninstall” deployment targeting them. These collections were not created specifically for this automation as they were used previously for normal uninstalls which our desktop staff use.
 
Now enough rambling, let’s get to it.
Here is the whole flow

Visio_Uninstall.PNG 
The Initialize Data activity gets the info from the first runbook and is configured as follows
Initialize-Data.PNGThe “Computer Name”, “Architecture” and “Visio Version” are received from the first runbook so here again you will see 2 links, one either to add members to the 32Bit uninstall collection or the 64Bit uninstall collection.
 

The Map Published data activity takes the provided “Visio Version” and maps this data to my respective collection name that I mentioned previously. So the “Map To” is the correct collection name which has the uninstall deployment targeted.
Map-Published-Data.PNG 
The below activity will actually add the member to the correct collection within SCCM and the published data for the collection name is passed from the output of the “Map Published Data” activity.
Add-To-32Bit-Collection.PNG  
 
The next 2 activities are really there to just speed things along, you can leave these out if you are not worried about triggering uninstall right away, obviously with me having these in comes the risk of machines not be online. What then you ask?  Well we can always add a “Ping” check or Computer Status check of some sorts and based on the result either run “Client Actions” or not, but it’s all up to you.
 
The “Trigger Machine Policy” does just that, it tells the SCCM client to check for new policy from the SCCM server.
Trigger-Machine-Policy.PNGLastly the “Trigger App Deployment Evaluation” will tell the client to check for any new applications that are required.
Trigger-App-Deployment.PNG 
And that’s it, simple isn’t it?  Yes there are perhaps a few other ways you could do things but this worked for us, you can still add other error checking based on other filters and perhaps even have it right to an Excel spreadsheet and report on which ones have been uninstalled and then send management a detailed report.

There are really many ways Orchestrator can integrate into your environment, it is really just limited to your imagination.

I hope this article was informative and if there are any questions please feel free to ask away.
 
2
4,987 Views
Leon TaljaardSystem Center Specialist - Architecture
CERTIFIED EXPERT

Comments (2)

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Nicely illustrated and reads very well.   Voted Yes.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
You're welcome.  Nice work.

Speaking of .. my recent article just went Featured Article ... Top 10 Ways to Write Rock Star Articles

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.