Easy collection of system information from SQL Server in XML

Fairly easy, extensible and readable way to get system information from SQL Server for reporting without having to create a complex system.  The code in the Common Table Expressions (WITH…) are from the SQL Server DMV Diagnostics Queries by Glenn Berry.

 DROP VIEW server_config  
 GO  
 CREATE VIEW server_config (server_config) AS  
      WITH os_windows_info as  
      (  
           SELECT windows_release,   
                windows_service_pack_level,   
                windows_sku,   
                os_language_version  
           FROM sys.dm_os_windows_info   
      ),  
      os_system_info as   
      (  
           SELECT cpu_count AS [Logical_CPU_Count], scheduler_count, hyperthread_ratio ,  
                cpu_count/hyperthread_ratio AS [Physical_CPU_Count],   
                physical_memory_kb/1024 AS [Physical_Memory_MB], committed_kb/1024 AS [Committed_Memory_MB],  
                committed_target_kb/1024 AS [Committed_Target_Memory_MB],  
                max_workers_count, affinity_type_desc AS [Affinity_Type],   
                sqlserver_start_time, virtual_machine_type_desc  
           FROM sys.dm_os_sys_info WITH (NOLOCK)  
      )  
      SELECT  
           @@servername as instance_name,  
           getdate() as collection_date,  
           (SELECT * FROM os_system_info FOR XML path(''),TYPE) as 'hardware_info',  
           (SELECT * FROM os_windows_info FOR XML path(''),TYPE) as 'os_information'  
      FOR XML path(''),TYPE, ROOT('server_config')  
 GO  
 SELECT * FROM server_config   

The resulting XML in the will be like the one below, and can easily be parsed by Powershell or stored in a reporting database.

 <server_config>  
  <instance_name>*******\SQL2012DE</instance_name>  
  <collection_date>2015-01-09T14:26:23.510</collection_date>  
  <hardware_info>  
   <Logical_CPU_Count>4</Logical_CPU_Count>  
   <scheduler_count>4</scheduler_count>  
   <hyperthread_ratio>4</hyperthread_ratio>  
   <Physical_CPU_Count>1</Physical_CPU_Count>  
   <Physical_Memory_MB>8066</Physical_Memory_MB>  
   <Committed_Memory_MB>211</Committed_Memory_MB>  
   <Committed_Target_Memory_MB>2048</Committed_Target_Memory_MB>  
   <max_workers_count>512</max_workers_count>  
   <Affinity_Type>AUTO</Affinity_Type>  
   <sqlserver_start_time>2015-01-09T09:18:13.100</sqlserver_start_time>  
   <virtual_machine_type_desc>NONE</virtual_machine_type_desc>  
  </hardware_info>  
  <os_information>  
   <windows_release>6.1</windows_release>  
   <windows_service_pack_level>Service Pack 1</windows_service_pack_level>  
   <windows_sku>4</windows_sku>  
   <os_language_version>1033</os_language_version>  
  </os_information>  
 </server_config>  

Have fun!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s