Close Menu
    Facebook X (Twitter) Instagram
    Monday, May 19
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»SCCM 2012 Collection SSRS report with No SEP client or SEP Client is older version

    SCCM 2012 Collection SSRS report with No SEP client or SEP Client is older version

    Eswar KonetiBy Eswar KonetiOctober 10, 7:30 am3 Mins Read CM2012 4,479 Views
    Share
    Facebook Twitter LinkedIn Reddit

    Rolled out new Symantec endpoint Protection (SEP) Version to clients across all the sites. After few days ,report (All application deployment –Basic)  says ,around 100+ computers still running on old version which did not upgraded to deployed version.Out of these 100+ clients,some of them already have deployed version if I check via resource explorer (also check from DB with v_add_remove_programs).So ,it assume that,reports having some problem (Table that it retrieve the info from).

    Since ,the reports are giving inaccurate results,I feel ,It would be good to have these failed clients into one collection to perform certain actions (I know there are tools that you run the specific action or other ways to do it) or let the helpdesk focus only these computers to troubleshoot the issue.with this way,we at least can get the real computers that have SEP failed/unknown or whatever the status.

    To get list of computers without specific application or application did not upgrade to deployed version,we need to go with subs elected query. In tis collection,we look for computers that did not upgrade to deployed version with limiting collection(original deployed collection).

    First create collection that have deployed SEP version installed and then use resourceid not in to get failed computers. In this case, we need to perform this on both 32bit (SMS_G_System_ADD_REMOVE_PROGRAMS) and 64bit(SMS_G_System_ADD_REMOVE_PROGRAMS_64)

    image

    Collection list computers that have SEP version installed for 32bit:

    select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId

    where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Symantec Endpoint Protection" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version = "12.1.5337.5000"

    Collection list computers that have SEP version installed For 64bit:

    select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId

    where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Symantec Endpoint Protection" and SMS_G_System_ADD_REMOVE_PROGRAMS_64.Version = "12.1.5337.5000"

    Now go with original query creation with resourceID not in from both above queries:

    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_ADD_REMOVE_PROGRAMS on
    SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Symantec Endpoint
    Protection" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version = "12.1.5337.5000")
    and SMS_R_System.ResourceId not in
    (select SMS_R_System.ResourceId from  SMS_R_System inner join
    SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId
    where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Symantec Endpoint
    Protection" and SMS_G_System_ADD_REMOVE_PROGRAMS_64.Version = "12.1.5337.5000")

    If you want to create collection for computers that did not have specific application ,you can just replace the display name with desired one and remove the version field.

    SSRS Report /SQL Query:

    select
    R.netbios_name0,
    R.user_name0,
    OS.Caption0 AS 'Operating System',
    ES.SerialNumber0 AS 'Serial Number' ,
    varp.displayname0,varp.version0
    from
    dbo.v_R_System R
    join dbo.v_GS_OPERATING_SYSTEM OS on OS.ResourceID = R.ResourceID
    JOIN dbo.v_GS_System_Enclosure ES on ES.ResourceID = R.ResourceID
    JOIN dbo.v_FullCollectionMembership AS vfcm ON vfcm.ResourceID = R.ResourceID
    join dbo.v_Add_Remove_Programs AS varp ON varp.ResourceID = R.ResourceID
    where
    R.ResourceID not in
    (
    select distinct
    ARP.ResourceId
    From
    dbo.v_ADD_REMOVE_PROGRAMS ARP
    join dbo.v_GS_System S on ARP.ResourceID = S.ResourceId
    where
    ARP.DisplayName0 ='Symantec Endpoint Protection' AND ARP.version0 like '12.1.5337.5000'

    )
    and vfcm.CollectionID='CMS000FF'
    AND varp.DisplayName0='Symantec Endpoint Protection'

     

     

    CM12 Collection collection for appilcation failed Collection for application not installed configmgr SCCM WQL Query
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    Investigating Co-Management Issues with Windows Endpoints in SCCM/Intune

    October 26, 10:45 pm

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.