August 18 2011

SCCM report – count Windows Server versions on physical hardware only



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

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

I had a requirement to count the different versions of Windows Server but only on physical hardware. This SCCM report does this by excluding VMWare and Hyper-V machines:

SELECT OPSYS.Caption0 as C054, COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM OPSYS
full Join v_GS_COMPUTER_SYSTEM On v_GS_COMPUTER_SYSTEM.ResourceID = OPSYS.ResourceID
inner join v_R_System sys on OPSYS.ResourceID=sys.ResourceID
where OPSYS.Caption0 LIKE '%server%'
and (v_GS_COMPUTER_SYSTEM.Manufacturer0 NOT LIKE '%Microsoft Corporation%')
and (v_GS_COMPUTER_SYSTEM.Manufacturer0 NOT LIKE '%VMware, Inc.%')
GROUP BY OPSYS.Caption0
ORDER BY OPSYS.Caption0

 
 



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

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

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'

 
 

February 2 2011

SCCM report to count enabled users in every domain

I needed a SCCM report to count all enabled users in every domain. This is different to the builtin ‘Count users by domain’ as that one only lists domain users that have logged onto a system (that one is from v_R_System).

Query =

SELECT Windows_NT_Domain0, count(*)
FROM
v_R_User
WHERE User_Account_Control0 = '512'
GROUP BY Windows_NT_Domain0
Order by Windows_NT_Domain0

While I am here, I may as well list 2 other reports that were useful to count all enabled computers in every domain, one report for workstations and one for servers:

SELECT Resource_Domain_OR_Workgr0, count(*)
FROM
v_R_System
WHERE Operating_System_Name_and0 LIKE '%workstation%' AND Resource_Domain_OR_Workgr0 NOT LIKE ''
GROUP BY Resource_Domain_OR_Workgr0
Order by Resource_Domain_OR_Workgr0

SELECT Resource_Domain_OR_Workgr0, count(*)
FROM
v_R_System
WHERE Operating_System_Name_and0 LIKE '%server%' AND Resource_Domain_OR_Workgr0 NOT LIKE ''
GROUP BY Resource_Domain_OR_Workgr0
Order by Resource_Domain_OR_Workgr0

 
 

December 16 2010

SCCM report to count hardware models

I needed an SCCM report to count how many of each type of hardware model we had in the environment. I created a new report with the following query:

select
GSCS.Manufacturer0 as [Manufacturer],
GSCS.Model0 as [Model],
count(*) as [Count]
from v_R_System RS
left join v_GS_COMPUTER_SYSTEM GSCS on RS.ResourceID = GSCS.ResourceID
Group by GSCS.Model0, GSCS.Manufacturer0
Order by GSCS.Model0