Software Metering Deep Dive and Automation Part 2: Use It Or Lose It - The Collections

  • Articolo

In the first portion of this multi-blog look at software metering, I discussed how to create a software metering rule, observe on the client both the storage and processing of said rule, and finally discussed the server side summarization and how to manually initiate this summarization. With the metering data collected and summarized it is now ready for use. Natively, without introducing new objects into the Configuration Manager environment, this data can be reported on using the Configuration Manager reports. While it is pretty rad to be able to determine who is using a product, or potentially more interesting who is not using a particular product, it is in my opinion even more rad to be able to automate actions based on this data. In this, the second post in this series, I will look at some of the native reporting, and then create a series of collections based on the metering data. These collections can ultimately be used for automation purposes, such as software removal.

While the ability to automate the removal of this unused and potentially expensive software is very helpful and can contribute to cost savings and potential licensing saturation issues, ultimately my goal with this series of posts is to expand on simple software removal, looping in some cool workflow such as email notification, and an opportunity for the user to opt out of the software removal. I will soon be posting part 3 to this blog series in which I will dig into some sample Orchestrator Runbooks that provide this advanced software removal workflow.

In case you've landed on this page without seeing the other posts, I am including links to both part 1 and part 3 of this series.

The Setup:

Lets set the stage a bit for the remainder of this blog series. Let's assume that I am an application deployment engineer responsible for not only the deployment of applications, but also the tracking of software licenses. The organization that I support has a line of business application that should only be installed on computers on which it is actively used. If any machine is detected with this software, and it has also been detected that the software has not been used in 30 days, the software should be removed from the system. For examples sake lets call this software Runbook Commander 2.0. In Part 1 of this series I demonstrated the creation of the software metering rule for Runbook Commander (RBCommander.exe).

Reporting:

Before digging into any collection creation, I am going to check out some of the metering data for Runbook Commander 2.0 using the default 2012 Configuration Manager reports. I will first be looking at "Install base for all metered software programs" in order to understand how many computers in my organization have the software installed. As we can see in the screen shot, there are four machines in my lab environment with the Runbook Commander software.

Next I want to understand which machines have the software, but have not used it in the most recent summarized month. For this I will execute the 'Computers that have a metered program installed but have not run the program since a specified date' report. From this report we see that my lab contains two computers that have not recently executed Runbook Commander 2.0.

From this data, I can expect that if I were to create a collection of all system with Runbook Commander 2.0 that have not run the software in 30 days, that this collection would contain these two computers. I can then target a Runbook Commander uninstallation package or application at this collection. As additional machines are detected as having the software however having not used it in 30 days, they will also land in this collection, and the software will be removed - absolutely fabulous.

The Collections:

It goes without saying that with just about any activity in Configuration Manager, there are multiple methods that can be used to achieve the same goal. In this case there are definitely multiple ways to skin a cat, err a collection. As I have been testing different collection rules based off of metering data, I have found many iterations that have worked, some better than others. This collection rule criteria has often time included many sub select statements, and in general been kind of messy. I did however find the following simple combination of two collections gives me predictable and accurate results. To summarize, the two collections in this solution are as follows

  • Collection 1 - All Systems that have run a metered piece of software in x days (Software Run in x Days).
  • Collection 2 - All Systems with the metered software installed, excluding those in Collection 1 (Software not Run in x Days).

Screen Shot of Collection Structure (click image for a better view).

In effect, Collection 2 will contain all computers that have the metered software but have not run it in x days. This is the collection that we will take action against or in this case target with the uninstallation application / package.

Creating Collection 1: Software Run within x days.

Notice here that we are referencing the Software Metering Rule ID. Refer to Post 1 in this series for information on determining this Rule ID.

All System That have executed RBCommander.exe in 30 days

  1. select SMS_R_SYSTEM.ResourceID,
  2. SMS_R_SYSTEM.ResourceType,
  3. SMS_R_SYSTEM.Name,
  4. SMS_R_SYSTEM.SMSUniqueIdentifier,
  5. SMS_R_SYSTEM.ResourceDomainORWorkgroup,
  6. SMS_R_SYSTEM.Client
  7.  
  8. from SMS_R_SYSTEM inner join SMS_MonthlyUsageSummary on SMS_R_SYSTEM.ResourceID = SMS_MonthlyUsageSummary.ResourceID  
  9. INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFile.MeteredFileID  
  10. WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 30  AND SMS_MeteredFiles.RuleID = 16777217

Creating Collection 2: Software not run in x days.

This is a simple query of all machines with the software, notice that I am referencing the software executable. This query makes up only half of the collection criteria.

All Systems with RBCommander.exe

  1. select SMS_R_SYSTEM.ResourceID,
  2. SMS_R_SYSTEM.ResourceType,
  3. SMS_R_SYSTEM.Name,
  4. SMS_R_SYSTEM.SMSUniqueIdentifier,
  5. SMS_R_SYSTEM.ResourceDomainORWorkgroup,
  6. SMS_R_SYSTEM.Client
  7.  
  8. from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceId = SMS_R_System.ResourceId
  9. WHERE SMS_G_System_SoftwareFile.FileName = "RBCommander.exe"

In addition to the query collection membership rule, we will add to collection 2 an exclude rule selecting collection 1 (All systems that have executed RBCommander.exe in 30 days) as the collection to exclude. 

Finally if we look at the members of Collection 2: Software not run in x days, we will see the expected computers as indicated in the report from above.

Automated Removal:

At this point two things can happen. I could launch RBCommander.exe on each of these machines, trigger a 'Software Metering Usage Report Cycle, Summarize the data server side, update each of these collections, and the machines would drop out of Collection 2. This would obviously happen 'organically' if the end user were to use the application, and as the Configuration Manager application and client process data. However in many cases the desired outcome would be to simply remove the software from the systems. In this case a package or Application could be deployed to Collection 2. As the machine process policy and executes the application, the metered software will be removed and the computer will drop from these collections.

I will not go into software removal, however for completeness sake here are a few screen shots of the removal setup.

Sample uninstallation package (click image for better view).

 

Sample package deployment, notice tht the target collection is collection 2 from this discussion (click image for a better view).

Conclusion:

There you have it, automated software removal based on metering data. As seen in this post once software metering rules have been defined, and the environment has had opportunity to process these rules and collect / summarize the data, we can begin to use the data both in reporting activities and also as collection rule criteria. With a series of simple collections, we can identify machines that have not executed a particular application in specified amount of time and then take action against these machines (remove the software). Thus far this blog series has focused on fairly straight forward Configuration Manager activities. In the next post of this series, I will explore how to use System Center 2012 Orchestrator integration to provide email notifications to the end user before software removal, and also provide the end user some warning time during which they can opt out of the software removal. Stay tuned for that post and please provide any feedback / subscribe to my twitter feed for notification on updates to this blog.

Comments

  • Anonymous
    January 01, 2003
    Excellent!

  • Anonymous
    January 01, 2003
    thanks

  • Anonymous
    March 18, 2013
    Excellent article, Thank you.

  • Anonymous
    April 24, 2013
    hi, i have a dought ?? where to find this "Install base for all metered software programs"

  • Anonymous
    July 10, 2013
    Hi, Thank you for great post - I do really like your idea of automatic uninstall software that are not used after a certain period. However, with application deployment if the application is deployed as required wouldn't it just go in to a loop? i.e. uninstall removes it and then the deployment detect that it has been removed and puts in back on. isn’t there a need for a process to remove the Machine/User from the collection that has the software deployment as "required"?

  • Anonymous
    September 05, 2014
    Creating Collection 1 query does not work.

  • Anonymous
    September 15, 2014
    Does a computer that has just installed the application go straight into the 'not run in 30 days' collection since there is no metering data for it?

  • Anonymous
    October 02, 2014
    There is a typo in the first collection query. Correction is below:

    select SMS_R_SYSTEM.ResourceID,
    SMS_R_SYSTEM.ResourceType,
    SMS_R_SYSTEM.Name,
    SMS_R_SYSTEM.SMSUniqueIdentifier,
    SMS_R_SYSTEM.ResourceDomainORWorkgroup,
    SMS_R_SYSTEM.Client

    from SMS_R_SYSTEM inner join SMS_MonthlyUsageSummary on SMS_R_SYSTEM.ResourceID = SMS_MonthlyUsageSummary.ResourceID
    INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFiles.MeteredFileID
    WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 30 AND SMS_MeteredFiles.RuleID = 16777217

  • Anonymous
    November 06, 2014
    The comment has been removed

  • Anonymous
    February 10, 2015
    Wonderful !

  • Anonymous
    March 04, 2015
    A note that some of you may find helpful - you need to Specify the 'Internal Rule' for the SMS_MeteredFiles.RuleID - The Software Metering section in the console shows the Rule ID and so does the MTRMGR.LOG file, which is not the correct one to use. In software metering section of the console add the extra column 'Internal Rule ID' and use that ID.

  • Anonymous
    March 09, 2015
    Did not make sense Adrian?? What exactly you're trying to say here?

  • Anonymous
    March 10, 2015
    He is saying to click on the software metering rule and in the summary section you will se the Internal Rule ID. That is the number you need to use in the query.

  • Anonymous
    March 12, 2015
    Not getting, please tell us where exactly this is?????

  • Anonymous
    March 25, 2015
    Raman - Assets and Compliance --> Software Metering --> then click once on your rule so its highlighted --> look at the bottom of the screen you will see the info there.

  • Anonymous
    April 03, 2015
    I'm not sure this will work as you describe. Say I install the application on a computer today and don't run it. Tomorrow, won't it simply get added to the collection that it hasn't run in the past 30 days and then get uninstalled?

  • Anonymous
    July 07, 2015
    Good job, thak you..

  • Anonymous
    October 05, 2015
    Great article!

    One small thing. I seldom have a use for a collection that tells me if a computer has run some software.

    I prefer to use the function "NOT IN" instead of creating two collections if I really just want one for "not run in x days". That way I don't have to confuse myself with include and exclude rules, also there are fewer things to change when I copy the collection.

    This WQL query will create one collection with any PC who has not run visio.exe in the last 90 days.

    SELECT
    SMS_R_SYSTEM.ResourceID,
    SMS_R_SYSTEM.ResourceType,
    SMS_R_SYSTEM.Name,
    SMS_R_SYSTEM.SMSUniqueIdentifier,
    SMS_R_SYSTEM.ResourceDomainORWorkgroup,
    SMS_R_SYSTEM.Client

    FROM SMS_R_SYSTEM

    WHERE SMS_R_SYSTEM.ResourceID NOT IN (SELECT SMS_R_SYSTEM.ResourceID FROM SMS_R_SYSTEM
    INNER JOIN SMS_MonthlyUsageSummary ON SMS_R_SYSTEM.ResourceID = SMS_MonthlyUsageSummary.ResourceID
    INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFile.MeteredFileID
    WHERE DATEDIFF(DAY, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 90 AND SMS_MeteredFiles.FileName = "visio.exe")

  • Anonymous
    January 28, 2016
    Kenneth Sundby - your wql query is flawed

  • Anonymous
    January 28, 2016
    Kenneth Sundby - your wql query is flawed