System Center Blog

    by Mikael Perhult (SCCMx)

    Browsing Posts tagged SQL Query

    When migrating to SCCM 2012 there can be a good way to keep track of the actual agent status over time using dynamic collections. This since the agent upgrade has an automatic roll-back if the upgrade failes.

    These collections can be used for both workstations or servers by using Limit to Collection in the query for the following:
    All Windows Workstation or Professional Systems
    All Windows Server Systems

    (The settings for date and time must be based on how the current setting is made oh HW inv client agent)

    List Active Computers:

    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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Name like “Microsoft%” and SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan <= DateAdd(dd,-1,GetDate()))

    List Non-Active Computers:

    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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_OPERATING_SYSTEM.Name like “Microsoft%” and SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= DateAdd(dd,-10,GetDate()))

    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