Find SQL Server Installed Version 1

Find SCCM SQL Database Size with Database File Path 1

Find Overall SCCM Site Hierarchy Information 1

Find SCCM Site Hierarchy Detailed Information 1

Find Overall Windows Workstations Client Machines OS category with counts 2

Find Overall Windows Servers Client Machines OS category with counts 3

Find Overall WSUS Server Configurations and Ports 3

Find SQL Server Installed Version

SELECT @@VERSION as ‘SQL Server Installed Version’

Find SCCM SQL Database Size with Database File Path

select

Sys.FILEID as ‘FileID’,

left(Sys.NAME,15) as ‘DBName’,

left(Sys.FILENAME,60) as ‘DBFilePath’,

convert(decimal(12,2),round(Sys.size/128.000,2)) as ‘Filesize (MB)’,

convert(decimal(12,2),round(fileproperty(Sys.name,’SpaceUsed’)/128.000,2)) as ‘UsedSpace (MB)’,

convert(decimal(12,2),round((Sys.size-fileproperty(Sys.name,’SpaceUsed’))/128.000,2)) as ‘FreeSpace (MB)’,

convert(decimal(12,2),round(Sys.growth/128.000,2)) as ‘GrowthSpace (MB)’

from dbo.sysfiles Sys

Find Overall SCCM Site Hierarchy Information

select distinct

(select COUNT(*) from v_SystemResourceList where RoleName = ‘SMS Site System’) as ‘SCCM SVR Counts’,

(select COUNT(*) from v_SystemResourceList where RoleName = ‘SMS Site Server’) as ‘SCCM Site Counts’, (

select COUNT(*) from v_SystemResourceList Vrl

Inner Join V_site Vs on Vs.ServerName = Vrl.ServerName

Where Vrl.RoleName = ‘SMS Site Server’ and Vs.Type = 3 and Vs.ReportingSiteCode is not null

) as ‘CAS Site Counts’, (

select COUNT(*) from v_SystemResourceList Vrl

Inner Join V_site Vs on Vs.ServerName = Vrl.ServerName

Where Vrl.RoleName = ‘SMS Site Server’ and Vs.Type = 2 and Vs.ReportingSiteCode is not null

) as ‘PRI Site Counts’, (

select COUNT(*) from v_SystemResourceList Vrl

Inner Join V_site Vs on Vs.ServerName = Vrl.ServerName

Where Vrl.RoleName = ‘SMS Site Server’ and Vs.Type = 1 and Vs.ReportingSiteCode is not null

) as ‘SEC Site Counts’,

(select COUNT(*) from v_SystemResourceList where RoleName = ‘SMS Management Point’) as ‘MP SVR Counts’,

(select COUNT(*) from v_SystemResourceList where RoleName = ‘SMS Distribution Point’) as ‘DP SVR Counts’,

(select COUNT(*) from v_SystemResourceList where RoleName = ‘SMS Software Update Point’) as ‘SUP SVR Counts’,

(select COUNT(*) from v_SystemResourceList where RoleName = ‘SMS SRS Reporting Point’) as ‘SSRS SVR Counts’,

(select COUNT(*) from v_SystemResourceList where RoleName = ‘SMS Provider’) as ‘SMSPro SVR Counts’

from v_SystemResourceList

Find SCCM Site Hierarchy Detailed Information

Select

V_Site.SiteCode as ‘SiteCode’,

V_Site.ReportingSiteCode as ‘ReportTo’,

V_Site.ServerName as ‘ServerName’,

V_Site.SiteName as ‘SiteName’,

CASE

WHEN V_Site.Type = 3 and V_Site.ReportingSiteCode is not null THEN ‘CAS Site Server’

WHEN V_Site.Type = 2 and V_Site.ReportingSiteCode = » THEN ‘Standalone Primary Site Server’

WHEN V_Site.Type = 2 and V_Site.ReportingSiteCode is not null THEN ‘Primary Site Server’

WHEN V_Site.Type = 1 and V_Site.ReportingSiteCode is not null THEN ‘Secondary Site Server’

ELSE ‘Others’ END as ‘Site Server Detail’,

V_Site.InstallDir as ‘Installed Directory’,

CASE

WHEN V_Site.BuildNumber = ‘7711’ THEN ‘2012 RTM’

WHEN V_Site.BuildNumber = ‘7804’ THEN ‘2012 SP1’

WHEN V_Site.BuildNumber = ‘7958’ THEN ‘2012 R2’

WHEN V_Site.BuildNumber = ‘8239’ THEN ‘2012 R2 SP1’

WHEN V_Site.BuildNumber = ‘8325’ THEN ‘1511’

WHEN V_Site.BuildNumber = ‘8355’ THEN ‘1602’

WHEN V_Site.BuildNumber = ‘8412’ THEN ‘1606’

WHEN V_Site.BuildNumber = ‘8458’ THEN ‘1610’

ELSE ‘Others’ END as ‘SCCM Version’,

V_Site.Version as ‘Build Version’

from V_Site

Find Overall Windows Workstations Client Machines OS category with counts

Declare @CollectionID as Varchar(8)

Set @CollectionID = ‘SMS00001’ — specify scope collection ID

Select

Os.Caption0 as ‘Operating System’,

COUNT(*) AS ‘Total’

from v_FullCollectionMembership Vf

inner join v_GS_OPERATING_SYSTEM Os on Os.ResourceID = Vf.ResourceID

where Vf.CollectionID = @CollectionID and Vf.ResourceID in (

select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Workstation%’ )

GROUP BY Os.Caption0

ORDER BY Os.Caption0 Desc

Find Overall Windows Servers Client Machines OS category with counts

Declare @CollectionID as Varchar(8)

Set @CollectionID = ‘SMS00001’ — specify scope collection ID

Select

Os.Caption0 as ‘Operating System’,

COUNT(*) AS ‘Total’

from v_FullCollectionMembership Vf

inner join v_GS_OPERATING_SYSTEM Os on Os.ResourceID = Vf.ResourceID

where Vf.CollectionID = @CollectionID and Vf.ResourceID in (

select ResourceID from v_R_System where Operating_System_Name_and0 like ‘%Server%’ )

GROUP BY Os.Caption0

ORDER BY Os.Caption0 Desc

Find Overall WSUS Server Configurations and Ports

select * from wsusserverlocations