ceviewer -- I look at that all the time to see what was the longest running collection (the last time). I also have a couple of reports which can tell me which collections took > xx seconds to run, and via SRS then I link to another report about "everything I know about that collection that I could think of"; including the WQL and SQL queries inside to see if those specific ones could be improved in some way.
But yeah... sadly, like you, we keep telling people over and over and OVER again--delete collections ASAP when no longer needed. "they don't have time for cleanup". Lemme grab the sql behind the reports (not sure if they'll run in your environment... I have linked servers in sql at the CAS level so that I can query from SSRS directly to the Primary Databases, for things like this) and forward them to you Henry... On Tue, Apr 4, 2017 at 7:33 AM, <henry.wil...@sanofi.com> wrote: > Yes > > > > > > *Best regards / Cordialement * > > > > *Henry E. WILSON* > > Technical Expert Engineer > > Information Technology & Solutions > Infrastructure Management > > Automation Services > 55 Corporate Drive > > Bridgewater, NJ 08807 > > TEL.: +1 908 981 3288 <(908)%20981-3288> > CELL.: +1 610 724 1169 <(610)%20724-1169> > > FAX: +1 908 635 5858 <(908)%20635-5858> > > System Center Technical Blog > <http://wssamer.sanofi.com/ws/AutomationCenterProvTools/SCTechBlog/Lists/Posts/AllPosts.aspx> > (for > internal use only) > > SCCM 2012 Dashboard > <http://xspw10w201w/Reports/Pages/Report.aspx?ItemPath=%2fUnRestricted+Reports%2fInfrastructure+Health+Status%2fConfigMgr+2012+Dashboard> > (for > internal use only) > > Provisioning Tools & Lifecycle Sharepoint Site > <http://wssamer.sanofi.com/ws/AutomationCenterProvTools/Documents/Forms/AllItems.aspx> > (for > internal use only) > > > > *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists. > myitforum.com] *On Behalf Of *Garth Jones > *Sent:* Tuesday, April 04, 2017 7:32 AM > *To:* mssms@lists.myitforum.com > *Subject:* [mssms] RE: SQL Query question > > > > Hi Henry, what exactly is the goal? Is it to find all computers without > .net 4.5.2? > > > > *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists. > myitforum.com <listsad...@lists.myitforum.com>] *On Behalf Of * > henry.wil...@sanofi.com > *Sent:* April 4, 2017 6:16 AM > *To:* mssms@lists.myitforum.com > *Subject:* [mssms] SQL Query question > > > > Hello SQL Guru’s > > > > We’re having a discussion with some Admin’s in Europe and I would like an > opinion on 2 Collection queries looking for the same result: > > > > Which one is more efficient? > > > > *Collection Query #1:* > > 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_INSTALLED_SOFTWARE on > SMS_G_System_INSTALLED_SOFTWARE.ResourceID > = SMS_R_System.ResourceId > > where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName in > > ("Microsoft .NET Framework 4.5.2","Microsoft .NET Framework > 4.6.1","Microsoft .NET Framework 4.6","Microsoft .NET Framework 4.6.2") > > ) > > ================================================================= > > *Collection Query #2:* > > 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_installed_software ON > sms_g_system_installed_software.resourceid > = sms_r_system.resourceid > > WHERE sms_g_system_installed_software.arpdisplayname = "microsoft .net > framework 4.5.2" > > or sms_g_system_installed_software.arpdisplayname = "microsoft .net > framework 4.6.1" > > or sms_g_system_installed_software.arpdisplayname = "microsoft .net > framework 4.6" > > or sms_g_system_installed_software.arpdisplayname = "microsoft .net > framework 4.6.2" > > ====================================================================== > > > > A DCM has also been mentioned to achieve the same result. > > > > Comments or recommendations? > > > > Thanks > > > > *Best regards / Cordialement * > > > > *Henry E. WILSON* > > Technical Expert Engineer > > Information Technology & Solutions > Infrastructure Management > > Automation Services > 55 Corporate Drive > > Bridgewater, NJ 08807 > > TEL.: +1 908 981 3288 <(908)%20981-3288> > CELL.: +1 610 724 1169 <(610)%20724-1169> > > FAX: +1 908 635 5858 <(908)%20635-5858> > > System Center Technical Blog > <http://wssamer.sanofi.com/ws/AutomationCenterProvTools/SCTechBlog/Lists/Posts/AllPosts.aspx> > (for > internal use only) > > SCCM 2012 Dashboard > <https://urldefense.proofpoint.com/v2/url?u=http-3A__xspw10w201w_Reports_Pages_Report.aspx-3FItemPath-3D-252fUnRestricted-26-2343-3BReports-252fInfrastructure-26-2343-3BHealth-26-2343-3BStatus-252fConfigMgr-26-2343-3B2012-26-2343-3BDashboard&d=DQMFAg&c=Dbf9zoswcQ-CRvvI7VX5j3HvibIuT3ZiarcKl5qtMPo&r=rYU1OkDK6KGNCD3HQmVUxzwASyVld-wfgoG00ci6xjA&m=MMFkG-9PwzSKzcY5ZyvS_IAhrJY9YN2NUUSFFLHMgAc&s=YEfQW7Q-RoYf6tErPTwoZiC7byWKjKLfFhVaDQdT2zY&e=> > (for > internal use only) > > Provisioning Tools & Lifecycle Sharepoint Site > <http://wssamer.sanofi.com/ws/AutomationCenterProvTools/Documents/Forms/AllItems.aspx> > (for > internal use only) > > > > > > > > -- Thank you, Sherry Kissinger My Parameters: Standardize. Simplify. Automate Blogs: http://www.mofmaster.com, http://mnscug.org/blogs/sherry-kissinger, http://www.smguru.org
