Advertisement

How to Find SCCM 2012 Server Components Data and Site Status Details from SQL DB

In SCCM 2012, ServerComponents, ServerData and SiteStatus tables are very important. We can get loads of information from these 3 tables. I was troubleshooting on SCCM 2012 SQL based (not SQL replication) replication (thanks to Umair Khan for more details on this) related issues and found that these tables would be really useful to understand the status of the replication and other SCCM related troubleshoting.

I’m not going to explain SQL troubleshooting part in this post. Most of the information are available in Umair’s posts as mentioned above. In this post, I just wanted to share the details of┬áServerData and SiteStatus. This would be useful at the time of SQL troubleshooting on SCCM 2012 replication issues.

select * from ServerData

Site Status 1

SCCM Server Data Table Details
No.ID Name SiteCode ServerRole SQLInstance SQLPort ConfigMgrDatabase SiteStatus RecoveryCount

Select * from Sites

Select-Sites-SCCM2012-SQL

SCCM Sites Table Details Part 1
No.SiteKey SiteCode SiteName ReportToSite Status DetailedStatus TimeStamp SomeId SiteType
SCCM Sites Table Details Part 2
No.BuildNumber Version SiteServer InstallDir TimeZoneInfo Capabilities DefaultMP DefaultMPType
SCCM Sites Table Details Part 2
No.DefaultMPSslState DefaultMPCapabilities LocaleID ReplicatesReservedRanges Settings rowversion

Select * from ServerComponents

Select-Server Components-SCCM2012-SQL

 

SCCM 2012 Server Components
No.ID Name
1130 SMS_SITE_CONTROL_MANAGER
2131 SMS_COLLECTION_EVALUATOR
3132 SMS_DISCOVERY_DATA_MANAGER
4133 SMS_DISTRIBUTION_MANAGER
5134 SMS_MP_CONTROL_MANAGER
6135 SMS_MP_DEVICE_MANAGER
7136 SMS_MP_FILE_DISPATCH_MANAGER
8137 SMS_COMPONENT_MONITOR
9138 SMS_AMT_OPERATION_MANAGER
10139 SMS_AMT_PROXY_COMPONENT
11140 SMS_DELIVERY_SCOPE_EVALUATOR
12141 SMS_STATE_MIGRATION_POINT
13142 SMS_FALLBACK_STATUS_POINT
14143 SMS_PXE_SERVICE_POINT
15144 SMS_MULTICAST_SERVICE_POINT
16145 AI_UPDATE_SERVICE_POINT
17146 SMS_WSUS_CONFIGURATION_MANAGER
18147 SMS_WSUS_CONTROL_MANAGER
19148 SMS_SRS_REPORTING_POINT
20149 SMS_AWEBSVC_CONTROL_MANAGER
21150 SMS_PORTALWEB_CONTROL_MANAGER
22151 SMS_ENROLL_SERVER
23152 SMS_ENROLL_WEB
24153 SMS_INBOX_MANAGER
25154 SMS_INBOX_MONITOR
26155 SMS_OUTBOX_MONITOR
27156 SMS_INVENTORY_DATA_LOADER
28157 SMS_INVENTORY_PROCESSOR
29158 SMS_SOFTWARE_INVENTORY_PROCESSOR
30159 SMS_LAN_SENDER
31160 SMS_ASYNC_RAS_SENDER
32161 SMS_ISDN_RAS_SENDER
33162 SMS_X25_RAS_SENDER
34163 SMS_SNA_RAS_SENDER
35164 SMS_OFFER_MANAGER
36165 SMS_OBJECT_REPLICATION_MANAGER
37166 SMS_RULE_ENGINE
38167 SMS_AI_KB_MANAGER
39168 SMS_WSUS_SYNC_MANAGER
40169 SMS_OFFLINE_SERVICING_MANAGER
41170 SMS_POLICY_PROVIDER
42171 SMS_PACKAGE_TRANSFER_MANAGER
43172 SMS_SCHEDULER
44173 SMS_EXCHANGE_CONNECTOR
45174 SMS_COMPONENT_STATUS_SUMMARIZER
46175 SMS_SITE_SYSTEM_STATUS_SUMMARIZER
47176 SMS_OFFER_STATUS_SUMMARIZER
48177 SMS_NETWORK_DISCOVERY
49178 SMS_CLIENT_CONFIG_MANAGER
50179 SMS_WINNT_SERVER_DISCOVERY_AGENT
51180 SMS_AD_SYSTEM_DISCOVERY_AGENT
52181 SMS_AD_USER_DISCOVERY_AGENT
53182 SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT
54183 SMS_DATABASE_NOTIFICATION_MONITOR
55184 SMS_CLIENT_CONFIG_MANAGER
56185 SMS_PROVIDERS
57186 SMS_SOFTWARE_METERING_PROCESSOR
58187 SMS_STATE_SYSTEM
59188 SMS_CLIENT_HEALTH
60189 SMS_SYSTEM_HEALTH_VALIDATOR
61190 SMS_WAKEONLAN_COMMUNICATION_MANAGER
62191 SMS_CLIENT_CONFIG_MANAGER
63192 SMS_WAKEONLAN_MANAGER
64193 SMS_REPLICATION_MANAGER
65194 SMS_CLOUD_SERVICES_MANAGER

About Author 

Anoop is Microsoft MVP and Veeam Vanguard ! He is a Solution Architect on enterprise client management with more than 13 years of experience (calculation done on the year 2014) in IT. He is Blogger, Speaker and Local User Group Community leader. His main focus is on Device Management technologies like SCCM 2012,Current Branch, Intune. He writes about the technologies like SCCM, SCOM, Windows 10, Azure AD, Microsoft Intune, RMS, Hyper-V etc...

    Find more about me on:
  • googleplus
  • twitter
  • facebook
  • linkedin
  • youtube
Posted in: ConfigMgr (SCCM)

7 Comments

  1. Raman Katoch says:

    Kool ! Nice one.. ­čÖé But fetching out desire data from all these tables using SQL Query is something really annoying :S

    • Anoop says:

      I know, Unfortunately, if you’ve hierarchy in SCCM 2012 with SCCM CAS and Primary then we need to deal with it. It’s very new for SCCM admins. My heading spinning now because of some SQL based replication issues.
      Regards
      Anoop

  2. bharat says:

    Its not that difficult but yes little annoying.

  3. kapil says:

    Hi sir,
    my environment has SCCM 2012 R2… with 1 CAS, 4 primary, and 32 secondary….

    i am handling a secondary site related to first primary site…

    now the task is I have to reinstall (refresh) the windows 7 profession version OS in my secondary site, already we have windows 7 professional version but with 3 drives in it, so now i have to push OS with partisioning only one drive and push OS into all systems and no data should be lost,…

    please specify me what are the requirements and steps involved in it

    Regards
    Kapil

    • Anoop says:

      Hi Kapil ! – You can’t install SCCM 2012 secondary server on Windows 7 prof ! It’s not supported scenario…

      Regards
      Anoop

Leave a Comment and Contact Anoop