:::: MENU ::::

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


SCCM Report – Last 30 days Advertisment Status

Note : By default I kept for Last 30 days . You change as per your requirement .It will show up Last 30 days advertisment status

SELECT    DISTINCT v_Package.Name AS ‘Package Name’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID) AS ‘Total Targeted Machines’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘No Status’)) AS ‘No Status machines’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID AND LastState!=0) AS ‘Total Accepted Machines’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Succeeded’, ‘Reboot PendINg’)) AS ‘Total Succeeded Machines’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Failed’)) AS ‘Failed’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Accepted – No Further Status’)) AS ‘Accepted – No Further Status’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Retrying’)) AS ‘Retrying’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Running’)) AS ‘Running’,
(SELECT COUNT(*) FROM v_ClientAdvertisementStatus
WHERE v_ClientAdvertisementStatus.AdvertisementID=v_Advertisement.AdvertisementID
AND v_ClientAdvertisementStatus.LastStateName IN(‘Waiting’)) AS ‘Waiting’,
v_Advertisement.AdvertisementName AS ‘Advertisement Name’,
v_Advertisement.AdvertisementID AS ‘Advertisement ID’
FROM v_Advertisement
INNER JOIN v_Package ON v_Advertisement.PackageID = v_Package.PackageID
INNER JOIN v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID
INNER JOIN v_ClientAdvertisementStatus ON v_Advertisement.AdvertisementID = v_ClientAdvertisementStatus.AdvertisementID
AND v_Advertisement.AdvertisementID in(SELECT advertisementid FROM V_advertisement WHERE DATEDIFF(D, V_advertisement.presenttime,GETDATE())<=30)
ORDER BY v_Advertisement.AdvertisementName


SCCM Report for List of advertisements Advertisment status

This below report will be usefull when your trying to generate the report with mutiple advertisement in single execution

SELECT DISTINCT v_R_System.Name0 AS [System Name], v_R_System.User_Name0 AS [User Name],
vSMS_ClientAdvertisementStatus.AdvertisementID AS [Advertisemnt ID],
v_Advertisement.AdvertisementName,
v_Advertisement.ProgramName,vSMS_ClientAdvertisementStatus.LastStatusMessageIDName AS [Last Status Message],
vSMS_ClientAdvertisementStatus.LastStateName AS [Last Status], vSMS_ClientAdvertisementStatus.Lastexecutionresult
FROM vSMS_ClientAdvertisementStatus INNER JOIN
v_R_System ON vSMS_ClientAdvertisementStatus.ResourceID = v_R_System.ResourceID INNER JOIN
V_Advertisement ON vSMS_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID INNER JOIN
v_RA_System_SMSAssignedSites ON v_R_System.ResourceID = v_RA_System_SMSAssignedSites.ResourceID
WHERE    v_Advertisement.AdvertisementID in(‘ADVERTID1′,’ADVERTID2’)
ORDER BY v_Advertisement.ProgramName


SCCM Collections WQL Query

Below SQL query will show-up the Collections WQL query for specific Collection ID

Day to day operation in order to go and check the collection properties its some what difficult. we can get in side collections WQL query :

 

 

SELECT dbo.v_Collection.Name AS [Collection Name], dbo.Collection_Rules_SQL.WQL AS [WQL Query], dbo.Collection_Rules_SQL.QueryKey AS
Query,dbo.v_CollectionRuleQuery.LimitToCollectionID, dbo.v_Collection.CollectionID FROM  dbo.Collection_Rules_SQL INNER JOIN
dbo.v_Collection ON dbo.Collection_Rules_SQL.CollectionID = dbo.v_Collection.CollID INNER JOIN dbo.v_CollectionRuleQuery ON
dbo.v_Collection.CollectionID = dbo.v_CollectionRuleQuery.CollectionID AND dbo.Collection_Rules_SQL.QueryKey = dbo.v_CollectionRuleQuery.QueryID
WHERE     (dbo.v_Collection.CollectionID = ‘XXXXXXX’)


Windows Operating System Names and version numbers

Windows Operating System Names and version numbers

Operating system Name Release version number
Windows 8.1 6.3
Windows Server 2012 R2 6.3
Windows 8 6.2
Windows Server 2012 6.2
Windows 7 6.1
Windows Server 2008 R2 6.1
Windows Server 2008 6
Windows Vista 6
Windows Server 2003 R2 5.2
Windows Server 2003 5.2
Windows XP 64-Bit Edition 5.2
Windows XP 5.1
Windows 2000 5

Patch installation process in SCCM client side

When we Deploy software updates to SCCM Client what will happens in the client side. Here is the Complete flow with logs:

1. Update Evaluation is triggered either manually , via schedule or due to mandatory patch enforcement.
2. Manual: Users select to begin a software updates or software updates evaluation cycle.    SMScliui is triggered to submit action to updates Deployment : SMScliui.log
3. Updates deployment is called to begin evaluation and application process :Updatesdeployment.log
4. CI agent is called to evaluate Applicalbe CI’s :CIagent.log
5. updates handler is called to handle the scan and patch deployment :Updateshandler.log
6. Scan agent is called to clear the scan history and initiate and scan: scanagent.log
7. Scan agent submit a Location services request to find WSUS server for use in scanning: Locatioservices.log
8. WUAhanlder is called to perform a scan.: WUA handler.log
9. Updatestore called to Adjust setting ion WMI as needed :Updatesstore.log
10. targeted CIs are evaluated and installed where applicable.
11. Updates deployment wakes up to begin CI evaluation and installation: Updatesdeployment.log
12. CI agent start-up to check targeted CIs and download if necessary :CIAgent.log
13. CIA agent calls SDM agent to download packages if necessary.
14. UpdatesDeploymnet calls Updates handler to initiate patch install : updatesdeployment.log
15. updates handler call WUA handler to facilitate patch install : Updateshanlder.log
16. WUAhanlder coordinates with exec mgr during patch install process. :WUAhanlder.log
17. Execmgr monitors software update installation :Execmgr.log