:::: MENU ::::

SCCM Clients status on List of Machines – SQL Query

SELECT Name0 AS [Machine Name], User_Name0 AS [User Name],
CASE Active0 WHEN ‘0’ THEN ‘InActive’ WHEN ‘1’ THEN ‘Active’ ELSE ‘Unknown’ END AS [Active Client],
CASE Client0 WHEN ‘0’ THEN ‘No’ WHEN ‘1’ THEN ‘Yes’ ELSE ‘Unknown’ END AS [Client Status],
CASE obsolete0 WHEN ‘0’ THEN ‘No’ WHEN ‘1’ THEN ‘Yes’ ELSE ‘Unknown’ END AS [Obsolete Client], DATEDIFF(D,V_GS_WORKSTATION_STATUS.LASTHWSCAN,GETDATE()) AS [HW SCAN DIFFERENCE]
FROM v_R_System LEFT JOIN V_GS_WORKSTATION_STATUS ON V_R_SYSTEM.RESOURCEID = V_GS_WORKSTATION_STATUS.RESOURCEID
WHERE (Name0 IN (‘Machine1’, ‘Machine2’))


List of Machines to add in a SCCM Collection

When we have the machines in an Excel or handy we need to create a collection without using Right-click tools or any other third-party tools by using below WQL query we can create the collection

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.name in(‘machine01′,’machine02’)


To Find the Package or Task sequence Folder Hive -SQL Query

WITHfolderHierarchy(ContainerNodeID,Name,ObjectType,ParentContainerNodeID,[Path])

AS(SELECTContainerNodeID,Name,ObjectType,ParentContainerNodeID,CAST(‘/’+[Name]+‘/’ASVARCHAR(MAX))AS[Path]

FROMdbo.FoldersWHEREParentContainerNodeID= 0

UNIONALL

SELECTchild.ContainerNodeID,child.Name,child.ObjectType,child.ParentContainerNodeID,parent.[Path]+child.[Name]+‘/’AS[Path]

FROMdbo.FoldersASchild

INNERJOINfolderHierarchyASparent

ONparent.ContainerNodeID=child.ParentContainerNodeID)

SELECTdistinctfldr.[Path],mbr.InstanceKeyASPackageID,pkg.Name

FROMfolderHierarchyASfldr

INNERJOINdbo.FolderMembersASmbrONfldr.ContainerNodeID=mbr.ContainerNodeID

INNERJOINdbo.v_packageASpkgONpkg.PackageID=mbr.InstanceKey

WHEREpkg.Namein(‘Packagename’)


SCCM / SMS Client Status on List of Specific Machines:

SELECT     Name0 AS [Machine Name], User_Name0 AS [User Name],

CASE Active0 WHEN ‘0’ THEN ‘InActive’ WHEN ‘1’ THEN ‘Active’ ELSE ‘Unknown’ END AS [Active Client],

CASE Client0 WHEN ‘0’ THEN ‘No’ WHEN ‘1’ THEN ‘Yes’ ELSE ‘Unknown’ END AS [Client Status],

CASE obsolete0 WHEN ‘0’ THEN ‘No’ WHEN ‘1’ THEN ‘Yes’ ELSE ‘Unknown’ END AS [Obsolete Client], DATEDIFF(D,V_GS_WORKSTATION_STATUS.LASTHWSCAN,GETDATE()) AS [HW SCAN DIFFERENCE]

FROM         v_R_System  LEFT JOIN V_GS_WORKSTATION_STATUS ON V_R_SYSTEM.RESOURCEID = V_GS_WORKSTATION_STATUS.RESOURCEID

WHERE     (Name0 IN (‘Machine1’))


Please use the below query for creating the collection for Re-run advertisements based on scheduled re-occurrence.

Please use the below query for creating the collection for Re-run advertisements based on scheduled re-occurrence (This query will remove re-run advertisement installation succeeded machines from collection automatically)

 

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_ClientAdvertisementStatus JOIN

SMS_R_System ON SMS_R_System.ResourceID = SMS_ClientAdvertisementStatus.ResourceID

WHERE (SMS_ClientAdvertisementStatus.AdvertisementID =’Master Advertisement ID’)

 

AND

(SMS_ClientAdvertisementStatus.LastStateName=’Failed’

OR

SMS_ClientAdvertisementStatus.LastStateName=’Accepted – No Further Status’

OR

SMS_ClientAdvertisementStatus.LastStateName= ‘Retrying’

OR

SMS_ClientAdvertisementStatus.LastStateName= ‘Waiting’)

AND

SMS_R_System.ResourceID NOT IN(SELECT SMS_ClientAdvertisementStatus.ResourceID FROM SMS_ClientAdvertisementStatus

WHERE SMS_ClientAdvertisementStatus.AdvertisementID =’Re-run Advertisement ID’

AND SMS_ClientAdvertisementStatus.LastStateName =’Succeeded’)


Failed Advert Machine list Based on Site Code Wise

SELECT DISTINCT

v_R_System.AD_Site_Name0 AS [AD Site Code], v_Advertisement.ProgramName,

v_ClientAdvertisementStatus.LastStatusMessageIDName AS [Last Status Message], v_ClientAdvertisementStatus.LastStateName AS [Last Status],

v_ClientAdvertisementStatus.LastExecutionResult, COUNT(v_ClientAdvertisementStatus.LastStatusMessageIDName) AS ‘Error  Count’

FROM         v_ClientAdvertisementStatus INNER JOIN

v_R_System ON v_ClientAdvertisementStatus.ResourceID = v_R_System.ResourceID INNER JOIN

v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID INNER JOIN

v_Package ON v_Package.PackageID = v_Advertisement.PackageID

WHERE     (v_ClientAdvertisementStatus.AdvertisementID IN (‘AdvertID1’,

‘AdvertID2’)) AND (v_ClientAdvertisementStatus.LastStateName = ‘Failed’)

GROUP BY v_ClientAdvertisementStatus.LastExecutionResult, v_Advertisement.ProgramName, v_ClientAdvertisementStatus.LastStatusMessageIDName,

v_ClientAdvertisementStatus.LastStateName, v_ClientAdvertisementStatus.LastExecutionResult, v_R_System.AD_Site_Name0

ORDER BY v_Advertisement.ProgramName


Collection Query Based on Advertisement Last staus

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_ClientAdvertisementStatus join SMS_R_System on SMS_R_System.ResourceID = SMS_ClientAdvertisementStatus.ResourceID where SMS_ClientAdvertisementStatus.AdvertisementID in(‘A0120005’) and SMS_ClientAdvertisementStatus.LastStateName In(‘Succeeded’)


Clean Up of Updates (Patches) which are superseded by New Bulletin IDs

Microsoft releases Patches every month on Second Tuesday, some of this patches will supersede the previous updates which can be deleted from System Center Configuration Manager 2007 Deployment. This will help in keeping the Patch Package size small which in turn will take less time to get replication to the Distribution Points (DPs) and save the Space on all Servers.

Following steps are to be followed for cleaning up this updates

Steps I: Delete the Updates from the Deployment

In the Deployment, check for the grayed out updates and see if they are superseded by any new released  Updates (Patches with Bulletin IDs like MS08-072 is superseding MS08-008)

Note :  Updates superseded by Service Packs Should not be deleted unless if the Services Pack is installed on all the machines present in the Environment.

Navigate to the Deployment Management in CM07 Console.

Ex: 2008 BaseLine Security Updates Part 1

Open System Center Configuration Manager 2007 Console à Computer Management à Software Updates à Deployment Management à

Select the Bulletin IDs to delete as per the criteria described above .

Multiple Bulleting IDs can be deleted at a time by holding CTRL key

Note: Please make a Note of Number of Updates are selected for deleting

01

02

03

 

Steps 2: Delete the above Updates from Deployment Package

Note Down the size of the package before starting the following process

Example: 2008 BaseLine Security Updates Part 1

Navigate to Deployment packages Expand  Package “2008 2008 BaseLine Security Updates Part 1 software Updates à Select the Bulletin IDs to delete in the Right Pane.

Note: No of updates to be deleted should be same as deleted in the Deployment.

Right Click on the Select items à Delete

 

04

 

Click  OK on the Pop Up for Refreshing the Package

05

 

Click OK on the Confirmation PopUP that this updates are also part of Deployment as we have already deleted them from Deployment in the Step 1

06

Note down the Size of the Package which will be less than the previous size.

Monitor the DP Replication and see if the all the DPs are updated with the above changes

 

 

 


To find the AdvertID on which folder structure created in SCCM console

To find the AdvertID on which Folder structure created in SCCM console

 

SELECT     Folders.name AS [Advertisement Created Under(Folder Name)], Foldermembers.instancekey AS AdvertID,

V_advertisement.Advertisementname AS [Advertisement Name]

FROM         dbo.Folders INNER JOIN

dbo.FolderMembers ON Foldermembers.containernodeid = folders.containernodeid INNER JOIN

dbo.v_Advertisement ON V_advertisement.advertisementid = Foldermembers.instancekey

WHERE     (V_advertisement.advertisementid = ‘AdvertID’)


SQL query that will gives list of all “query based collections”

SELECT TOP (100) PERCENT dbo.v_Collection.Name, dbo.v_Collection.CollectionID, dbo.v_CollectionRuleQuery.RuleName, dbo.v_CollectionRuleQuery.QueryID, dbo.v_CollectionRuleQuery.LimitToCollectionID, dbo.v_CollectionRuleQuery.QueryExpression FROM dbo.v_Collection INNER JOIN dbo.v_CollectionRuleQuery ON dbo.v_Collection.CollectionID = dbo.v_CollectionRuleQuery.CollectionID ORDER BY dbo.v_Collection.Name, dbo.v_Collection.CollectionID