Go for it, looking for input. From: [email protected] [mailto:[email protected]] On Behalf Of Ed Aldrich Sent: Friday, November 20, 2015 1:30 PM To: [email protected] Subject: RE: [mssms] Software Metering and software removal
Oooohhhhh… Can I SAY it??? ☺ Ed Aldrich [email protected]<mailto:[email protected]> | www.1e.com<http://www.1e.com/> [Description: Description: cid:[email protected]] Ent Cli Mgmt (2003-2015) Sent from my Microsoft Surface From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Augustine, Greg Sent: Friday, November 20, 2015 11:55 AM To: '[email protected]' <[email protected]<mailto:[email protected]>> Subject: [mssms] Software Metering and software removal 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]<mailto:[email protected]> ________________________________ Legal Notice: This email is intended only for the person(s) to whom it is addressed. If you are not an intended recipient and have received this message in error, please notify the sender immediately by replying to this email or calling +44(0) 2083269015 (UK) or +1 866 592 4214 (USA). This email and any attachments may be privileged and/or confidential. The unauthorized use, disclosure, copying or printing of any information it contains is strictly prohibited. The opinions expressed in this email are those of the author and do not necessarily represent the views of 1E Ltd. Nothing in this email will operate to bind 1E to any order or other contract.
