System Center Blog

    by Mikael Perhult (SCCMx)

    Browsing Posts tagged SCCM

    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

     

     

     

    By using HINV and changes to SMS_DEF.MOF can inventory be made of maintenance windows in SCCM.
    Edit SMS_DEF.MOF (make sure to have a backup and check dataldr.log that the changes was applied successfully).

    SMS_DEF.MOF:

    //  *Maintenance Window - COMPANY
    
        #pragma deleteclass("COMPANY_MAINT_WINDOW",NOFAIL)
    
        #pragma namespace ("\\\\.\\root\\cimv2\\sms")
    
        [SMS_Report     (TRUE),
        SMS_Group_Name ("Company Maint Window"),
        Namespace      ("root\\\\ccm\\\\policy\\\\machine\\\\actualconfig"),
        SMS_Class_ID   ("CUSTOM|COMPANY_MAINT_WINDOW|1.0") ]
    
        class CCM_ServiceWindow : SMS_Class_Template
    
        {
         [SMS_Report(TRUE), key] String ServiceWindowID;
         [SMS_Report(TRUE)] String Schedules;
         [SMS_Report(TRUE)] uint32 ServiceWindowType;
        };

     

    This inventory can then be added as rules to Collections.

    Assigned Servers with Maintenance Windows (WMI):

    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 inner join SMS_G_System_CUSTOM_COMPANY_MAINT_WINDOW_1_0 on SMS_G_System_CUSTOM_COMPANY_MAINT_WINDOW_1_0.ResourceID = SMS_R_System.ResourceId where SMS_G_System_CUSTOM_COMPANY_MAINT_WINDOW_1_0.ServiceWindowType = 1

    Servers with no assigned Maintenance Windows (WMI):

    (Change to CollectionID above).

    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.Active = 1 and SMS_R_System.ResourceId not in (select ResourceId from SMS_CM_RES_COLL_XXX000YY) and OperatingSystemNameAndVersion like ‘Microsoft Windows NT%Server%’