Close Menu
    Facebook X (Twitter) Instagram
    Friday, May 16
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»SCCM Configmgr report Advertisement Status Filter LastAcceptanceStatusTime

    SCCM Configmgr report Advertisement Status Filter LastAcceptanceStatusTime

    Eswar KonetiBy Eswar KonetiJanuary 07, 10:51 am1 Min Read CM2012 528 Views
    Share
    Facebook Twitter LinkedIn Reddit

    Status of Specific Advertisement can get from Default available report but what if you want to find the status of specific advertisement with its LastAcceptanceStatusTime greater or less than X days.

    Full Details :http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/30e4dbda-982b-43dc-8665-8dd142925d4f

    DECLARE  @olddate datetime
            ,@NullVal datetime
    SET @olddate = DATEADD(day,-5, GETUTCDATE())

    select adv.AdvertisementName 'Application', sys.Name0 'System', LastAcceptanceStateName 'Acceptance',
    sts.LastAcceptanceStatusTime 'AcceptTime_GMT', sts.LastStatusMessageIDName 'ExecutionStatus',
    sts.LastStateName 'ExecutionState',sts.LastStatusTime 'ExecutionTime_GMT',
    adt.MessageName,sts.LastExecutionResult
    from v_ClientAdvertisementStatus sts
    join v_AdvertisementStatusInformation adt on adt.MessageID=sts.LastStatusMessageID
    join v_Advertisement adv on adv.AdvertisementID=sts.AdvertisementID
    join v_R_System sys on sys.ResourceID=sts.ResourceID
    where ((sts.LastAcceptanceStatusTime  < @olddate) or (sts.LastStatusTime  < @olddate))
    and  adv.AdvertisementName like '%Adobe_reader_Eng%' and sts.LastExecutionResult like '1603'
    order by sys.Name0

    Change the number of days and Advertisement Name.

    Note: Please change the Quotes ,blog converts them as fancy here.

    advertisement status cm configmgr configmgr report errocode LastAcceptanceStatusTime LastStatusTime sccm report SQL
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

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

    February 11, 9:50 pm

    Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

    October 11, 8:48 pm

    Troubleshooting Windows KMS Activation Issues with SCCM scripts feature

    September 09, 11:07 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.