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  
 CREATE VIEW server_config (server_config) AS  
      WITH os_windows_info as  
           SELECT windows_release,   
           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)  
           @@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')  
 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.

   <windows_service_pack_level>Service Pack 1</windows_service_pack_level>  

Have fun!


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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s