:::: MENU ::::

Collection based on Heartbeat “N” number of days

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 ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=14) and AgentName = “Heartbeat Discovery”)


SCCM 2012 Roles

In order to learn the SCCM, We requires to know what are the roles available .

What is Role :– With SCCM we have many features. In-order to use those feature we requires to enable these roles. Most of the roles as similar to SCCM 2007. If something is added I’m pointing at the line

Site Servers:

Using installations with Software Media Wizard server Called as Site Server. Without Site Server You can’t install SCCM in our hierarchy. While installing Site Serve we will mention Site Server Name with 3 digit (alpha-numeric) code.It hosts the configuration manager components and services

Site System:

Once we have the Site Server, site system roles can be hosted on site server Or Installing the roles on different server. A server or server Share that hosts one or more site system roles for Configuration Manager site Manage content distribution

Site Database Server:

A site system role that runs Microsoft SQL server and hosts the configuration Manager Site Database

Management Point:

A site system role that replies to configuration Manager Client’s requests and accepts management data from configuration manager clients

Distribution Point:

A configuration manager role that stages packages for Distribution to clients

Application Catalog Website Point:

A site system role that serves as an application catalog website point

Application Catalog Web service point :

A site system role that serves as application catalog web services point

Asset Intelligence Synchronization Point :

A site system role that connects to system center online to download asset intelligence catalog information and upload uncategorized titles that can be considered for future inclusion in the catalog

Reporting services Point:

A site system role that provides integration with SQL server reporting services to create and manager reports for configuration manager

Software update point:

A site system role that runs Microsoft windows server update services and allows configuration manager to use the WSUS catalog to scan configuration manager clients for software updates

State migration Point:

A site system role that store user state and settings migrated during the Operating system deployment

Fallback Status Point:

A site system role that receives messages from ConfigMgr clients that cannot communicate with their management point

 


Central Administration Site:

A Central Administration Site (CAS) is the top level or Root level Site server in the hierarchy .CAS is dedicatedly for administration purpose. We cannot assign Clients to the CAS or we can’t manage clients with help of CAS. We can’t enable all roles in CAS.

CAS can’t support grandchild primary sites [Vertical] architecture. However, CAS can have multiple child primary sites [Horizontal]. Which they send the client data to CAS, It will stores in the SQL database which is nothing but CAS site database. In CAS we will be installing Administrator console from which we can manage our SCCM infrastructure.

Without CAS also we can install and manage the SCCM with Standalone Primary site .When we have more than one Primary site we required the CAS.

We install standalone primary site server, In future, based on business we need to expand the sites or need to add some more primaries. Then that time if we have the SCCM 2012 R2, we can install a CAS after we installed a primary site. Before SP1(service pack1),We have to install the CAS First.

CAS can support max 25 child primary sites

 

For roles supporting configuration http://technet.microsoft.com/en-in/library/gg682077.aspx#BKMK_SiteAndRoleScale

Primary SITE

Clients can assigned only to a Primary site. At least 1 Primary site requires in SCCM environment.Assigned clients data will be stored in SCCM database .Primary site can have multiple secondary sites.All these secondary will report to Primary site. Primary can be standalone or It may report CAS Site. Primary site have both Primary site client machines information and Secondary site data. Primary site requires the SCCM License

Primary site can support up to 250 secondary sites (It’s completely depends on WAN speed)

In SCCM 2007:

Not much difference but in SCCM 2007 Primary can have multiple primary sites.

 

Secondary Sites

Client won’t assign to Secondary sites .Secondary sites are always child site of the primary sites. So it managed through the console connected to parent primary site. Secondary wont requires SCCM license

Secondary site is a mediator or forwarder, it gathers the information like inventory, system status info, etc. from clients and sends to parent site. Secondary site are more useful where we have remote locations where we need links where we need to control bandwidth

In generally we used to implement on following circumstances :

Remote location has around 500+ clients

If required to use software update point

To control the Bandwidth like upward flowing traffic

In SCCM 2012 : It requires SQL database  atleast SQL server Express edition will fine. Most of the client to server data will be replicated with help of database in SCCM 2012. so they have introduced database to secondary site . Unlike in SCCM 2007 ,we have to install the secondary site from it parent site console wizard in CM 2012, at that time if database not exist , it automatically installs SQL Server Express edition

 


All SCCM Servers with Roles in a Site hierarchy – SQL Query

–Displays ServerType now with roles, not just saying remote.
SET NOCOUNT ON

select distinct sum.SiteCode,
SUBSTRING(SiteSystem, (CHARINDEX(‘\\’,sum.SiteSystem)+2), (CHARINDEX(‘\’,SiteSystem,(CHARINDEX(‘\\’,sum.SiteSystem)+2)) – (CHARINDEX(‘\\’,sum.SiteSystem)+2))) ‘Server’,
CASE Sum.Role
WHEN ‘AI Update Service Point’ THEN ‘X’ ELSE ‘ ‘
End ‘AI Point’,
CASE Sum.Role
WHEN ‘SMS Distribution Point’ THEN ‘X’ ELSE ‘ ‘
End ‘DP’,
CASE Sum.Role
WHEN ‘SMS Fallback Status Point’ THEN ‘X’ ELSE ‘ ‘
End ‘FSP’,
CASE Sum.Role
WHEN ‘SMS Management Point’ THEN ‘X’ ELSE ‘ ‘
End ‘MP’,
CASE Sum.Role
WHEN ‘SMS PXE Service Point’ THEN ‘X’ ELSE ‘ ‘
End ‘PXE’,
CASE Sum.Role
WHEN ‘SMS Server Locator Point’ THEN ‘X’ ELSE ‘ ‘
End ‘SLP’,
CASE Sum.Role
WHEN ‘SMS Site Server’ THEN ‘X’ ELSE ‘ ‘
End ‘Site Server’,
CASE Sum.Role
WHEN ‘SMS Software Update Point’ THEN ‘X’ ELSE ‘ ‘
End ‘SUP’,
CASE Sum.Role
WHEN ‘SMS SQL Server’ THEN ‘X’ ELSE ‘ ‘
End ‘SQL’,
CASE Sum.Role
WHEN ‘SMS SRS Reporting Point’ THEN ‘X’ ELSE ‘ ‘
End ‘SSRS’,
CASE Sum.Role
WHEN ‘SMS Reporting Point’ THEN ‘X’ ELSE ‘ ‘
End ‘RP’,
Sum.Role
into ##temp
from v_SiteSystemSummarizer sum
where not sum.Role = ‘SMS Component Server’

SET NOCOUNT OFF

–select * from ##temp

select distinct t1.SiteCode,t1.[Server],
(select top 1 [AI Point] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [AI Point],
(select top 1 [DP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [DP],
(select top 1 [FSP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [FSP],
(select top 1 [MP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [MP],
(select top 1 [PXE] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [PXE],
(select top 1 [SLP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SLP],
(select top 1 [Site Server] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [Site Server],
(select top 1 [SUP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SUP],
(select top 1 [SQL] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SQL],
(select top 1 [SSRS] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [SSRS],
(select top 1 [RP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc) [RP],
CASE Site1.Type
WHEN 1 THEN ‘Secondary’
WHEN 2 Then ‘Primary’
ELSE
case when (select top 1 [AI Point] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘AIPoint ‘ else ” end+
case when (select top 1 [DP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘DP ‘ else ” end+
case when (select top 1 [FSP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘FSP ‘ else ” end+
case when (select top 1 [MP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘MP ‘ else ” end+
case when (select top 1 [PXE] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘PXE ‘ else ” end+
case when (select top 1 [SLP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SLP ‘ else ” end+
case when (select top 1 [Site Server] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SiteServer ‘ else ” end+
case when (select top 1 [SUP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SUP ‘ else ” end+
case when (select top 1 [SQL] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SQL ‘ else ” end+
case when (select top 1 [SSRS] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘SSRS ‘ else ” end+
case when (select top 1 [RP] from ##temp t11 where t11.SiteCode=t1.SiteCode and t11.Server=t1.Server order by 1 desc)=’X’ then ‘RP ‘ else ” end
End as [ServerType],
–site1.ReportingSiteCode
(select distinct site11.ReportingSiteCode from v_Site site11
where site11.SiteCode = t1.SiteCode and site11.ReportingSiteCode is not null) as [ReportingSiteCode],
–site1.Version
(select distinct site11.Version from v_Site site11
where site11.SiteCode = t1.SiteCode and site11.Version is not null) as [Version]
from ##temp t1
left join v_Site site1 on t1.SiteCode = site1.SiteCode and t1.Server = site1.ServerName

drop table ##temp

 


SCCM report for all expired adverts

SELECT v_Advertisement.AdvertisementID AS [Advertisement ID],
v_Advertisement.AdvertisementName AS [Advertisement Name],
v_Advertisement.PackageID AS [Package ID],
v_Package.Name AS [Package Name],
v_Advertisement.ProgramName AS [Program Name],
v_Collection.CollectionID AS [Collection ID],
v_Collection.Name AS [Collection Name],
v_Advertisement.PresentTime AS [Advertisement Creation Date],
v_Advertisement.ExpirationTime AS [Advertisement Expiration Date]
FROM v_Advertisement INNER JOIN
v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID INNER JOIN
v_Package ON v_Package.PackageID = v_Advertisement.PackageID
WHERE (v_Advertisement.ExpirationTimeEnabled = 2) AND (v_Advertisement.ExpirationTime < GETDATE()) AND (v_Advertisement.PresentTimeIsGMT = 0) OR (v_Advertisement.ExpirationTimeEnabled = 2) AND (v_Advertisement.ExpirationTime < GETUTCDATE()) AND (v_Advertisement.PresentTimeIsGMT = 1)


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