Close Menu
    Facebook X (Twitter) Instagram
    Monday, May 12
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»SCCM»SCCM report to list All advertisements with specific status

    SCCM report to list All advertisements with specific status

    Eswar KonetiBy Eswar KonetiJune 12, 4:51 pm2 Mins Read SCCM 968 Views
    Share
    Facebook Twitter LinkedIn Reddit

    This report gives you all percentage count with specific status for all advertisement in your environment. In order to get the list of machines where it is advertised with specific status,this report has to be linked to default report i.e 106.It works with SCCM , if you want to run the report in SMS 2003 ,see below after this code.

    SELECT
    AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS ‘Number of clients with this Status’,
    ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS ‘Percent with this Status’,
    SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END) AS ‘Total # Clients with Accepted Status’

    FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
    (SELECT a.AdvertisementID,a.LastStateName, count(*) as ‘number’
    FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
    WHERE a.LastStatusMessageID = b.MessageID
    AND b.MessageStateName = @status
    GROUP BY a.AdvertisementID, a.LastStateName
    ) AS AdvState

    WHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
    AND AdvState.AdvertisementID = AdvName.AdvertisementID

    GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
    ORDER BY AdvName.AdvertisementName

    Prompt for status:

    SELECT DISTINCT
    a.MessageStateName

    FROM
    v_AdvertisementStatusInformation a,
    v_ClientAdvertisementStatus b

    WHERE a.MessageID = b.LastStatusMessageID

    ORDER BY MessageStateName

    You can apply the above report to SMS 2003 environment also :

    This is similar to the above report but few things gets changed.

    SELECT
    AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS ‘Number of clients with this Status’,
    ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS ‘Percent with this Status’,
    SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END) AS ‘Total # Clients with Accepted Status’

    FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
    (SELECT a.AdvertisementID,a.LastStateName, count(*) as ‘number’
    FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
    WHERE a.LastStatusMessageID = b.MessageID
    AND b.MessageStateName = @status
    GROUP BY a.AdvertisementID, a.LastStateName
    ) AS AdvState

    WHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
    AND AdvState.AdvertisementID = AdvName.AdvertisementID

    GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
    ORDER BY AdvName.AdvertisementName

    The prompt condition for status will be same as above ,use above query and once you do this ,go the report properties and link it to the default report ID i.e 110 in order to get the list of systems where the status is failed.

    advertisements with specific status Configmgr 2007 configmgr report SCCM 2007 SCCM Reports SMS 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

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

    October 26, 10:45 pm

    Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

    October 11, 8:48 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.