I would like all of your thoughts on software metering and collections to automatically remove software. We have built collections like the below that give us the information but when we run the removal it does not seem to update the information. I am assuming that it is related to the software inventory update cycle.
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_SoftwareFile 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 = 16777399 AND DateDiff(day, SMS_G_System_SoftwareFile.ModifiedDate, GetDate()) > 60 AND SMS_R_SYSTEM.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()) < 60 AND SMS_MeteredFiles.RuleID = 16777399) We have also look at the following query to see if it work out better, which it does run fast thru SQL. 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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "snagit32.exe" and SMS_G_System_SoftwareFile.FileVersion like "11.%" and 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.RuleID = 16777399) and SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId where Datediff(day, SMS_G_System_LastSoftwareScan.LastScanDate, GetDate()) > 3) What are you all using to do software removal base on software that is not used? Greg Augustine Office of Administration Information Technology Services Division 301 W. High St. Jefferson City, MO 65101 Office No: 573-751-4714 E-mail: [email protected]
