System Center Blog

    by Mikael Perhult (SCCMx)

    Browsing Posts tagged Deployment

    Here is a query that lists all AD groups the computer is member of together with deployments assigned to the computer.
    Software Update deployments are excluded.

    SELECT     v_R_System.Name0, v_RA_System_System_Group_Name.System_Group_Name0, v_CIAssignment.AssignmentName, v_CIAssignment.Description
    FROM         v_R_System INNER JOIN
                          v_RA_System_System_Group_Name ON v_R_System.ResourceID = v_RA_System_System_Group_Name.ResourceID RIGHT OUTER JOIN
                          v_CIAssignmentTargetedMachines ON v_R_System.ResourceID = v_CIAssignmentTargetedMachines.ResourceID RIGHT OUTER JOIN
                          v_CIAssignment ON v_CIAssignmentTargetedMachines.AssignmentID = v_CIAssignment.AssignmentID
    WHERE     (v_R_System.Name0 LIKE 'PC001') and (AssignmentName not like '%Software Update%' and AssignmentName not like '%Endpoint Protection%')

    This query lists all failed deployments for package/programs on Servers.

    declare @__timezoneoffset int select @__timezoneoffset = DateDiff(ss,getutcdate(),getdate());
    select sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0,site.SMS_Installed_Sites0, Client_Type0, ainfo.AdvertisementName, 
    LastStatusMessageID, LastStatusMessageIDName, 
    DATEADD(ss,@__timezoneoffset,LastStatusTime) as LastStatusTime, 
    stat.AdvertisementID, LastExecutionResult, LastExecutionContext, sys.Operating_System_Name_and0
    from v_ClientAdvertisementStatus stat
    join v_AdvertisementInfo ainfo on stat.AdvertisementID=ainfo.AdvertisementID
    join v_R_System sys on stat.ResourceID=sys.ResourceID
    left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID
    where stat.LastState='11' /* 11 = failed */
    and sys.Operating_System_Name_and0 like '%server%'
    order by sys.Netbios_Name0