SCCM 2012 – List of SQL queries

I have included a list of sql queries that we frequently use in SCCM 2012.

1.  SCCM 2012 SQL query to get the machines name with required , not required & installed security updates.

select a.Name0,
c.BulletinID,
c.ArticleID,
c.Title,
Case c.Severity
WHEN ’10’ THEN ‘Critical’
WHEN ‘8’ THEN ‘Important’
WHEN ‘6’ THEN ‘Moderate’
WHEN ‘2’ THEN ‘Low’
WHEN ‘0’ THEN ‘None’
ELSE ‘Unknown’
END AS SeverityLevel,
d.CategoryInstanceName,
c.DateCreated,
c.DateLastModified,
C.DateRevised,
e.StateName,
c.IsDeployed,
c.IsSuperseded,
c.IsExpired
FROM v_R_System a INNER JOIN
v_Update_ComplianceStatusAll b ON b.ResourceID=a.ResourceID INNER JOIN
v_UpdateInfo c ON c.CI_ID=b.CI_ID INNER JOIN
(SELECT CI_ID, CategoryInstanceName
FROM v_CICategoryInfo_All
WHERE CategoryTypeName =’UpdateClassification’) as d ON d.CI_ID=c.CI_ID INNER JOIN
v_StateNames e on e.StateID=b.Status INNER JOIN
v_FullCollectionMembership f ON f.ResourceID=a.ResourceID

WHERE d.CategoryInstanceName=’Security Updates’ and e.TopicType = ‘500’ and f.CollectionID=’CollectionID‘

order by a.Name0

User Input -> Collection ID

Result:

 

 

2. SQL Query to find ADD or Remove Programs of a specific machine

select b.Name0,a.DisplayName0,b.User_Name0,
a.Version0 from v_Add_Remove_Programs a join v_R_System b on a.ResourceID=b.ResourceID
where b.Name0 like ‘Machine name’

User input -> Machine name

Result:

 

 

3. SQL Query to get Advertisement status of a specific deployment

Select a.Name0,
b.LastStateName
from v_R_System a join v_ClientAdvertisementStatus b on a.ResourceID=b.ResourceID
where b.AdvertisementID = ‘DeploymentID’

User input -> DeploymentID

Result:

 

4. SQL Query to list the Boundary Group details

Select a.Name as [BoundaryGroup Name],
a.DefaultSiteCode,a.MemberCount as [Boundaries Count],
a.SiteSystemCount,
c.SiteSystemName,
‘Connection’ = case
when Flags = ‘0’ then ‘Fast’
when Flags = ‘1’ then ‘Slow’
else ‘unknown’
End
from
vSMS_BoundaryGroup a
Join vSMS_BoundaryGroupMembers b on a.GroupID=b.GroupID
join v_BoundarySiteSystems c on c.BoundaryID=b.BoundaryID
join vSMS_BoundaryGroupSiteSystems d on a.GroupID=d.GroupID

Result:

 

5. SQL query to list the machines name that Hardware inventory not scanned more than 10 days

select sys.Name0,
ws.LastHWScan
from v_R_System sys join
v_GS_WORKSTATION_STATUS WS on sys.ResourceID=ws.ResourceID inner join
v_FullCollectionMembership FCM on FCM.ResourceID=sys.ResourceID
where ws.LastHWScan < DateAdd(dd,-10,GetDate())

Result:

 

 

 

 

 

 

6.  SQL Query to find collection membership information of a specific machine

select v_FullCollectionMembership.CollectionID As ‘Collection ID’,
v_Collection.Name As ‘Collection Name’,
v_R_System.Name0 As ‘Machine Name’
from v_FullCollectionMembership
JOIN v_R_System on v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
JOIN v_Collection on v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
Where v_R_System.Name0=‘Machine name’

User input-> Machine name

Result:

 

 

 

 

 

 

7. Workstation with SCCM Client installed

select sys.Name0,
sys.Client0,
sys.Client_Version0,
sys.Operating_System_Name_and0
from v_R_System sys where sys.Operating_System_Name_and0 like ‘Microsoft Windows NT Workstation%’ and sys.Client0=1

Result: