:::: MENU ::::

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


SCCM 2012 New Features

In two ways we can compare the new features

1. Site Level or Administration or Hierarchy level

2. New added features in support side.

 

Site Level  Changes :

  1. SCCM 2012 Introduced Top level hierarchy called CAS (Central Administration Site ). When we installing the SCCM 2012, We have a separate wizard called CAS exist. Which we don’t have  a wizard in SCCM 2007. We used to assume that First installation site will be Central Site. we cont assign clients to CAS, we cont enable all rolls in CAS
  2. SCCM 2012 supports only side by side Migration
  3. SCCM 2012 supports only horizontal hierarchy level. It wont support unlike SCCM 2007 supports primary can contains child primary sites.
  4. In SCCM 2012 to speed up the replication process in secondary site we need to install SQL server or else it automatically installs SQL Server Express
  5. Few rolls are introduces newly and some or merged with existing roles Like SLP role merged with MP and PXE role merged with DP.we will go in detail after few posts

 


SYSTEM CENTER CONFIGURATION MANAGER

 

System Center Configuration Manager its also known as configmgr2012 and configmgr2007 and simply configmgr. Earlier it also known as Systems Management Server. It is a system management software Product by Microsoft for managing large group of computers like Windows, mac OS, Unix,Linux and mobile operating systems also like windows phone, android and IOS.

 

configmgr_history

 

Below are the top features listed using SCCM

  1. Windows 10 Management
  2. Application Delivery
  3. Device Management
  4. Virtual Desktop Management
  5. Endpoint Protection
  6. Compliance and Setting Management
  7. Software Update Management
  8. Power Management
  9. Operating System Deployment
  10. Client health and monitoring
  11. Asset Intelligence
  12. Hardware Inventory and Software Inventory report
  13. Reporting
  14. In Console updates

Pages:12345678