SCCM SQL Query : Find SCCM Client Health

select sys.Name0 as ‘Computer Name’, sys.User_Name0 as ‘User Name’, summ.ClientStateDescription, case when summ.ClientActiveStatus = 0 then ‘Inactive’ when summ.ClientActiveStatus = 1 then ‘Active’ end as ‘ClientActiveStatus’, summ.LastActiveTime, case when summ.IsActiveDDR = 0 then ‘Inactive’ when summ.IsActiveDDR = 1 then ‘Active’… Read More ›

SCCM SQL Query : Find Lync Versions

select  distinct v_R_System.User_Name0,v_r_system.Name0,v_R_System.Active0,v_Add_Remove_Programs.DisplayName0,v_Add_Remove_Programs.TimeStamp,v_Add_Remove_Programs.InstallDate0,v_Add_Remove_Programs.Version0 from v_Add_Remove_Programs inner join v_R_System on v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID where v_Add_Remove_Programs.DisplayName0 like ‘Microsoft Lync 2010’

SCCM SQL Query : Computers with Pending Restart or other Update Enforcement States

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_UpdateComplianceStatus ON SMS_UpdateComplianceStatus.machineid=sms_r_system.resourceid WHERE SMS_UpdateComplianceStatus.LastEnforcementMessageID = 9 There are several other enforcement states you can use instead by changing the number after ‘LastEnforcementMessage ID =’ at the end of… Read More ›

SCCM SQL Query : Query to find package pending replication

select pkg.Name as C062, pkg.PackageID, SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) – CHARINDEX(‘\\’, dp.ServerNALPath) – 3 ) AS C070, dp.SiteCode, dp.LastRefreshTime, stat.SourceVersion, stat.LastCopied, stat.SummaryDate, stat.InstallStatus, case when dp.IsPeerDP=1 then ‘*’ else ” end as BranchDP from v_Package pkg join… Read More ›