I would like to create a query to base collection membership of software metering results. I would do this to automatically uninstall unused software. Here is the scenario: 1. We meter the use of app XYZ 2. I create a collection called App XYZ removal 3. The membership would be base on machines that havent run App XYZ for 6 months or since a specified date 4. I would create and advertise a program to this collection that uninstalls App XYZ I am unsure of step 3. Has anybody done this? Is it possible? Id like to avoid looking at the metering reports and creating direct memberships if possible. Thanks.

Possible the only way to do this would be to have an external script that can look at the report and place machines in a collection or remove them as the data changes. Run the vbscript as a Scheduled Task each day to coincide with Adv push down.

Attribute class would sms_g_system_softwareusagedata, displayed as "software usage" in the query builder. This was already possible in sms 2003, and was one of the big changes in software metering for sms 2003, the ability to query for it. The Collection query below with give you all machines that DID run notepad.exe 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_G_System_SoftwareUsageData on SMS_G_System_SoftwareUsageData.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareUsageData.FileName = "notepad.exe" and this would be the machines that have NOT run notepad.exe: 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_G_System_SoftwareUsageData on SMS_G_System_SoftwareUsageData.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareUsageData.FileName = "notepad.exe") To be really usefull you would probably have to modify the query to include a certain dateframe in which notepad.exe was not run. Oh, and you might want to use something else than notepad.exe, as uninstalling it would not really be feasible.

This is very helpful. Thank you. Would it be possible to specify # of days rather than a specific date? If we could do that, we could basically set this and forget it. Even if we can't do that, these examples are very helpful.

So, how do we do this as a COLLECTION based on a QUERY, instead of running a report, writing down the names and then pasting them in on a monthly basis? Surely the same tables we can see in SQL are somehow exposed to the WQL based queries we do for collections. Seriously, it has to be there, or Software Metering's usefulness is marginal at worst, and manual at best. What WQL 'table' or 'object' names should we be using to query this data?In Version 2003, there was SMS_G_System_SoftwareUsageData and some others, a la this article: http://www.myitforum.com/articles/8/view.asp?id=10912What are we looking at here?I know that I can create a dummy WQL query and inject the SQL into the collection row in the backend storage , but if someone happens to accidentally try and view the WQL, then it's all shot. So I guess what I'm asking is, what is this same feature now called in SCCM 2007, that was formerly SMS_G_System_SoftwareUsageData in SMS 2003. I can't allow myself to believe that it's been removed.I don't mean to be flip about this, but he clearly asked about a collection (WQL), and was clearly responded to as if he said Report (SQL).Edit: I noticed this from kim's reply up above"Attribute class would sms_g_system_softwareusagedata, displayed as "software usage" in the query builder."Where is there a comprehensive listing of all of these attribute class to query builder names so that we can avoid having to ask this every time?

I have tested the Ian Dearing way, but this is for SMS2003 and it doesn't work an SCCM. Has anybody had any luck in creating a collection based on the metering of software? I'm looking for example to automat the deinstallation of Visio if it is not used for more than 90 days. But I have a hardtime creating a collection that will work. Any help would be appriciated.Thanks in advance,GiovanniGio

Could you give us a little more detail, what exactly isn't working with Ian's method?"Everyone is an expert at something" Kim Oppalfens Configmgr expert for lack of any other expertise. http://www.scug.be/blogs/sccm

It seems that in SMS 2003 you where able to query the database and create a collection. It seems that is no longer works that way.I am able to query the computers using SQL query analyzer.. here is the codeSELECT DISTINCT * SMS_R_System FROM SMS_G_System_SoftwareFile INNER JOIN v_R_System ON SMS_G_System_SoftwareFile.clientID = SMS_R_System.ResourceID INNER JOIN SMS_MeteredFiles ON SMS_G_System_SoftwareFile.FileID = SMS_MeteredFiles.MeteredFileID INNER JOIN SMS_MeteredProductRule ON SMS_MeteredProductRule.RuleID = SMS_MeteredFiles.RuleID WHERE SMS_MeteredFiles.RuleID = 103 AND DateDiff(day, SMS_G_System_SoftwareFile.ModifiedDate, GetDate()) > 30 AND DateDiff(day,SMS_MeteredProductRule.LastUpdateTime, GetDate()) > 30 AND SMS_R_System.Operating_System_Name_And0 LIKE '%Workstation%' AND SMS_G_System_SoftwareFile.clientID NOT IN (SELECT DISTINCT SMS_MonthlyUsageSummary.ResourceID FROM SMS_MonthlyUsageSummary INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFiles.MeteredFileID WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 30 AND SMS_MeteredFiles.RuleID =103)As soon as I add this query in the console to build the collection it gives an error that the query is invalid.Is there a way to have sccm query tables and views on the database directly?

Has anyone got this to work? I used the query: SELECT DISTINCT * FROM SMS_G_System_SoftwareFile INNER JOIN sms_R_System ON SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceID INNER JOIN SMS_MeteredFiles ON SMS_G_System_SoftwareFile.FileID = SMS_MeteredFiles.MeteredFileID INNER JOIN SMS_MeteredProductRule ON SMS_MeteredProductRule.RuleID = SMS_MeteredFiles.RuleID WHERE SMS_MeteredFiles.RuleID = 103 AND DateDiff(day, SMS_G_System_SoftwareFile.ModifiedDate, GetDate()) > 30 AND DateDiff(day,SMS_MeteredProductRule.LastUpdateTime, GetDate()) > 30 AND SMS_R_System.OperatingSystemNameAndVersion LIKE '%Workstation%' AND SMS_G_System_SoftwareFile.ResourceID NOT IN (SELECT DISTINCT SMS_MonthlyUsageSummary.ResourceID FROM SMS_MonthlyUsageSummary INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFiles.MeteredFileID WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 30 AND SMS_MeteredFiles.RuleID =103 And it does show any computers in the collection.

correction is does not show any computers in the collection for SCCM 2007 R2