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

6

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

[table caption=”SCCM Server Data Table Details” class=”table table-bordered” ai=”1″ width=”400″ colwidth=”150|50|300″ colalign=”left”]

ID,Name,SiteCode,ServerRole,SQLInstance,SQLPort,ConfigMgrDatabase,SiteStatus,RecoveryCount

[/table]

Select * from Sites

Select-Sites-SCCM2012-SQL

[table caption=”SCCM Sites Table Details Part 1” class=”table table-bordered” ai=”1″ width=”400″ colwidth=”150|50|300″ colalign=”left”]

SiteKey, SiteCode, SiteName, ReportToSite, Status, DetailedStatus, TimeStamp, SomeId, SiteType

[/table]

[table caption=”SCCM Sites Table Details Part 2” class=”table table-bordered” ai=”1″ width=”400″ colwidth=”150|50|300″ colalign=”left”]

BuildNumber,Version, SiteServer, InstallDir, TimeZoneInfo, Capabilities, DefaultMP, DefaultMPType

[/table]

[table caption=”SCCM Sites Table Details Part 2” class=”table table-bordered” ai=”1″ width=”400″ colwidth=”150|50|300″ colalign=”left”]

DefaultMPSslState, DefaultMPCapabilities, LocaleID, ReplicatesReservedRanges, Settings, rowversion

[/table]

Select * from ServerComponents

Select-Server Components-SCCM2012-SQL

 

[table caption=”SCCM 2012 Server Components” class=”table table-bordered” ai=”1″ width=”400″ colwidth=”150|50|300″ colalign=”left”]

ID,Name
130,SMS_SITE_CONTROL_MANAGER
131,SMS_COLLECTION_EVALUATOR
132,SMS_DISCOVERY_DATA_MANAGER
133,SMS_DISTRIBUTION_MANAGER
134,SMS_MP_CONTROL_MANAGER
135,SMS_MP_DEVICE_MANAGER
136,SMS_MP_FILE_DISPATCH_MANAGER
137,SMS_COMPONENT_MONITOR
138,SMS_AMT_OPERATION_MANAGER
139,SMS_AMT_PROXY_COMPONENT
140,SMS_DELIVERY_SCOPE_EVALUATOR
141,SMS_STATE_MIGRATION_POINT
142,SMS_FALLBACK_STATUS_POINT
143,SMS_PXE_SERVICE_POINT
144,SMS_MULTICAST_SERVICE_POINT
145,AI_UPDATE_SERVICE_POINT
146,SMS_WSUS_CONFIGURATION_MANAGER
147,SMS_WSUS_CONTROL_MANAGER
148,SMS_SRS_REPORTING_POINT
149,SMS_AWEBSVC_CONTROL_MANAGER
150,SMS_PORTALWEB_CONTROL_MANAGER
151,SMS_ENROLL_SERVER
152,SMS_ENROLL_WEB
153,SMS_INBOX_MANAGER
154,SMS_INBOX_MONITOR
155,SMS_OUTBOX_MONITOR
156,SMS_INVENTORY_DATA_LOADER
157,SMS_INVENTORY_PROCESSOR
158,SMS_SOFTWARE_INVENTORY_PROCESSOR
159,SMS_LAN_SENDER
160,SMS_ASYNC_RAS_SENDER
161,SMS_ISDN_RAS_SENDER
162,SMS_X25_RAS_SENDER
163,SMS_SNA_RAS_SENDER
164,SMS_OFFER_MANAGER
165,SMS_OBJECT_REPLICATION_MANAGER
166,SMS_RULE_ENGINE
167,SMS_AI_KB_MANAGER
168,SMS_WSUS_SYNC_MANAGER
169,SMS_OFFLINE_SERVICING_MANAGER
170,SMS_POLICY_PROVIDER
171,SMS_PACKAGE_TRANSFER_MANAGER
172,SMS_SCHEDULER
173,SMS_EXCHANGE_CONNECTOR
174,SMS_COMPONENT_STATUS_SUMMARIZER
175,SMS_SITE_SYSTEM_STATUS_SUMMARIZER
176,SMS_OFFER_STATUS_SUMMARIZER
177,SMS_NETWORK_DISCOVERY
178,SMS_CLIENT_CONFIG_MANAGER
179,SMS_WINNT_SERVER_DISCOVERY_AGENT
180,SMS_AD_SYSTEM_DISCOVERY_AGENT
181,SMS_AD_USER_DISCOVERY_AGENT
182,SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT
183,SMS_DATABASE_NOTIFICATION_MONITOR
184,SMS_CLIENT_CONFIG_MANAGER
185,SMS_PROVIDERS
186,SMS_SOFTWARE_METERING_PROCESSOR
187,SMS_STATE_SYSTEM
188,SMS_CLIENT_HEALTH
189,SMS_SYSTEM_HEALTH_VALIDATOR
190,SMS_WAKEONLAN_COMMUNICATION_MANAGER
191,SMS_CLIENT_CONFIG_MANAGER
192,SMS_WAKEONLAN_MANAGER
193,SMS_REPLICATION_MANAGER
194,SMS_CLOUD_SERVICES_MANAGER

[/table]

6 COMMENTS

  1. Kool ! Nice one.. 🙂 But fetching out desire data from all these tables using SQL Query is something really annoying :S

    • 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. 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

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.