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:
I see you don’t monetize your blog, don’t waste your traffic, you can earn extra cash every month
because you’ve got hi quality content. If you want
to know how to make extra money, search for: Mrdalekjd methods for $$$
LikeLike
Hi Karthick, do we have any sql query for package distribution to particular dp via sql query
LikeLike
Hi Thyagarajan, Can u verify in this link to see if it helps you.
https://karthickjokirathinam.com/2017/10/15/sql-queries-for-packages-distribution-status-on-distribution-points/
LikeLike
If some one wants to be updated with most recent technologies therefore he
must be go to see this web site and be up to date every day.
LikeLike