Skip to main content

Software Update Management- SCCM 2012

The below post explains the end to end process of software updates management in SCCM 2012 Pre-requisites: 1.  WSUS Server (WSUS 3.0 SP2 ) should be installed on the SCCM site server or if you want to install WSUS on another server, it is important that you install WSUS administration console as the SCCM Server uses API. This  link explains the step by step procedure to install WSUS 3.0 SP2 2. Install SUP-Software Update Point on the SCCM Server . 3. Reporting Services Point: Install reporting services point role as described in this link . 4. Client agent: The software update client agent should be enabled(will be enabled by default) and the settings have to specified as per the requirement. On SCCM console, go to Administration>Site Configuration>Client settings>Right click on Default client settings> Click on Properties. If required, we can create custom client settings and then enable client settings for that settings. Unde
4 comments

SQL Queries for SCCM Sites

The below queries are useful for assessing the site and hierarchy information from SCCM Site servers.


1.Site Roles

select SiteCode, RoleName, ServerName from SysResList

2.Hierarchy information including site version

select * from sites


3.Total Secondary sites

select CAST(Version as varchar) 'Category', count(*) 'SiteCount' from v_Site (NOLOCK) where Type<>1 group by Version UNION select 'Total Primary Sites' 'Category', count(*) 'SiteCount' from v_Site (NOLOCK) where Type<>1 ORDER BY Category DESC

4.Client count by site

select sms_assigned_sites0, count(distinct name0) from v_RA_System_SMSAssignedSites ass join v_R_System sys on ass.resourceID=sys.resourceID
where client0=1 and obsolete0=0 and active0=1
group by sms_assigned_sites0 order by count(distinct name0) desc

5.MP Communication Status in last 7 days

declare @olddate datetime, @NullVal datetime
set @olddate=DATEADD(day,-7, getdate())
set @NullVal = CONVERT(datetime,'1/1/1980')
select distinct v_site.sitecode as Site,
(select count(*) from v_ClientHealthState
where healthtype ='1000' and healthstate ='1'and lasthealthreportdate >@olddate and assignedsitecode =v_site.sitecode) as "Client successfully communicating with MP",
(select count(*) from v_ClientHealthState
where healthtype ='1000' and healthstate ='2'and lasthealthreportdate >@olddate and assignedsitecode =v_site.sitecode ) as "Client failing to communicate with MP",
(select count(*) from v_ClientHealthState
where healthtype ='1001' and healthstate ='1'and lasthealthreportdate >@olddate and assignedsitecode =v_site.sitecode ) as "Client successfully retrieving certificate",
(select count(*) from v_ClientHealthState
where healthtype ='1001' and healthstate ='2'and lasthealthreportdate >@olddate and assignedsitecode =v_site.sitecode ) as "Client failing to retrieve certificate"
from v_site join v_ClientHealthState on v_site.sitecode =v_ClientHealthState.assignedsitecode
and v_site.type=2
order by v_site.sitecode

6.WSUS Scan status in last 10 hours
 
select count(*)as clients,statename,upp.lastscanpackagelocation,sit.sms_assigned_sites0
from v_updateScanStatus upp
  join v_statenames stat on stat.stateid = upp.lastscanstate
 join v_RA_System_SMSAssignedSites sit on sit.resourceid = upp.resourceid
and stat.topictype ='501' and upp.lastscanpackagelocation like'http%'
and lastscantime >DATEADD(hour,-10, getutcdate())and lastscantime < getutcdate()
group by upp.lastscanstate,stat.statename,upp.lastscanpackagelocation,sit.sms_assigned_sites0
order by sit.sms_assigned_sites0,clients desc

7.SCCM Clients installed per each AD-Site

select
    AD_Site_Name0 as 'AD Site Name',count(DISTINCT name0) as 'Clients Installed'
FROM v_R_System where Active0 = 1 and Client0 =1 and Obsolete0 != 1 and ad_site_name0 != 'null' group by AD_Site_Name0








Labels:

Comments

Post a Comment

Popular posts from this blog

Registering SPN for SQL Server for SCCM

If using a domain account to install SQL server 2008 R2 for SCCM, you have to register a SPN (Service Principal Name) in Active Directory for that domain account. Two SPNs for the account should be registered,     1. For NETBIOS name of the SQL Server     2. For the FQDN of SQL server. The procedure to do that is as follows 1. Log on to a domain controller; open a command prompt with administrative privileges. 2. Type the below commands replacing SQL server name. setspn –A MSSQLSvc/< SQL Server NETBIOS name >:1433<Domain\Account> setspn –A MSSQLSvc/< SQL Server FQDN >:1433 <Domain\Account> 3. As shown in the below screenshot, the server name here is  CM2012 for NETBIOS name and CM2012.CONTOSO.COM 4.  Verify the registration of SPN by typing the below command Setspn –L  <domain\account>
1 comment
Read more

Consistency validation for SQL Server registry keys failed error -SQL for SCCM

During installation of SQL server, "Consistency validation for SQL Server registry keys failed” error pops up in the below scenarios. 1.   Previous installation of SQL exists. 2.   Inappropriate permissions on the registry keys of Microsoft SQL server. Solution that worked me is explained below. A. Identify the issue: 1. Go to %Program Files%\Microsoft SQL Server\100\SetupBootstrap\Log\”date  and time of installation” 2. Search (in Detail_GlobalRules.txt) for lines containing the following string "Could not fix registry key" 3. Run “regedit”, s et full control permissions for the appropriate registry keys mentioned in "Detail_GlobalRules.txt" file. Re-run the installation. B. Modifying the registry: 1. Locate HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server in registry. (To open registry, run “regedit”) 2. Right click and go to Permission 3. Click on Advance tab and c heck the below options.     i. Include i
6 comments
Read more

Collection query to find Machines--based on GUID Criteria

You can use the below SQL query to create a new collection based on GUID. 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.SMBIOSGUID = " 18764D56-C91F-83A5-51FB-4AD4B6699D04 " Replace the GUID with the one that you intend to delete. This is particularly useful when performing OSD related tasks. To know how to create a collection , go through this link .
3 comments
Read more