June 1 2011

SCCM report – count physical vs virtual servers

This report displays the number of servers in the environment and gives a breakdown and percentage view of physical vs virtual:


Declare @Total as int
Declare @Physical as int
Declare @Virtual as int
set @Virtual =
(Select distinct count (*)
From
v_R_System full Join
v_GS_COMPUTER_SYSTEM On v_GS_COMPUTER_SYSTEM.ResourceID =
v_R_System.ResourceID
where v_R_System.Operating_System_Name_and0 LIKE '%server%'
and v_R_System.Client0 = 1
and (v_GS_COMPUTER_SYSTEM.Manufacturer0 = 'Microsoft Corporation' or v_GS_COMPUTER_SYSTEM.Manufacturer0 = 'VMware, Inc.'))
set @Total =
(Select distinct count (*)
From
v_R_System full Join
v_GS_COMPUTER_SYSTEM On v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
where v_R_System.Operating_System_Name_and0 LIKE '%server%'
and v_R_System.Client0 = 1 )
set @Physical = (select @Total - @Virtual)
Select @Physical as 'Physical servers',
@Virtual as 'Virtual servers',
@Total as 'Total servers',
(select (@Virtual * 100) / @Total) as 'Percentage (%) that are virtual'

 
 



----------------------------------------------------------------------------
I use a maximum of one Google Ad per post to help offset some of my blog hosting costs.

----------------------------------------------------------------------------


Tags: , , , , ,

Posted June 1, 2011 by danovich in category "SMS / SCCM

2 COMMENTS :

  1. By DoSerMaN on

    This looks like it would be a great report. Though, I can not get it to run. I get:

    An error occurred when the report was run. The details are as follows:
    Divide by zero error encountered.
    Error Number: -2147217900
    Source: Microsoft OLE DB Provider for SQL Server
    Native Error: 8134

    Any Ideas?

    Reply

Leave a Reply