locked
SQL Query to find program exit code on client for software distribution RRS feed

  • Question

  • Hi,

    Can you point me to right SQL Query that can result all my clients failing with software distribution with its error codes as well. I see this is exaclty available in Status Mesage viwer in Console but coulnt find equivalent sql table or view. I could get close to below, but then it still doesnt offer me, with what error code (1639 or sorts) client failed to excute program. Appreciate help

    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
    from v_ClientAdvertisementStatus sts
    join v_AdvertisementStatusInformation adt on adt.MessageID=sts.LastStatusMessageID
    join v_Advertisement adv on adv.AdvertisementID=sts.AdvertisementID and  adv.AdvertisementName like '%sw_in_ind%'
    join v_R_System sys on sys.ResourceID=sts.ResourceID where ((sts.LastAcceptanceStatusTime  > @olddate) or (sts.LastStatusTime  > @olddate)) order by sys.Name0

    --Thanks,


    Vasu

    • Changed type Garth JonesMVP Saturday, January 5, 2013 8:04 PM It is a question.
    Saturday, January 5, 2013 4:58 PM

Answers

  • you can try this :

    DECLARE  @olddate datetime 
            ,@NullVal datetime 
    SET @olddate = DATEADD(day,-2, 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%' and sts.LastExecutionResult like '1603'
     order by sys.Name0

    change the Number of days how you want.


    Please click on "vote as Helpful" if you feel this post helpful to you.

    Eswar Koneti | My Tech blog: www.eskonr.com | Linkedin: Eswar Koneti


    Monday, January 7, 2013 2:46 AM