Wednesday, May 1, 2013

SCCM report for Domain controller



Recently I have queried for a SCCM report for Server Configuration for domain controller ,
User wants to find the all details in one report like, Server Name, Bios Name, Serial Number, Model, Manufacturer, Ram Size, and Drive later and How many drives server have,

here is SQL query to find out the complete report


SELECT
sys.Name0 AS 'System Name',
os.Caption0 AS 'Operating System',
os.CSDVersion0 AS 'Service Pack',
PM.TotalPhysicalMemory0 /1024 as 'RAM in MB',
OS.InstallDate0 AS 'Original OS Install Date',
os.LastBootUpTime0 AS 'Last Boot Time',
NAC.IPAddress0 AS 'IP Address',
NAC.IPSubnet0 AS 'Subnet Mask'
FROM
dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC
INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON NAC.ResourceID = OS.ResourceID
INNER JOIN dbo.v_GS_PC_BIOS BIOS ON NAC.ResourceID = BIOS.ResourceID
INNER JOIN dbo.v_GS_SYSTEM sys ON NAC.ResourceID = sys.ResourceID
INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS on NAC.ResourceID = CS.ResourceID
INNER JOIN dbo.v_GS_X86_PC_MEMORY PM on NAC.ResourceID = PM.ResourceID
Where
NAC.IPaddress0 is Not Null
and CS.Roles0 like '%Domain_Controller%'


SCCM Report for Server Configuration

Recently I have queried for a SCCM report for Server Configuration,
User wants to find the all details in one report like, Server Name, Bios Name, Serial Number, Model, Manufacturer, Ram Size, and Drive later and How many drives server have,

here is SQL query to find out the complete report,

Select distinct V_R_System.Name0 as 'Server Name', V_R_System.Operating_System_name_and0 as 'OS', V_GS_Computer_System.Model0 as 'Model',
V_GS_Computer_System.NumberOfProcessors0 as 'Processors', v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as 'Serial-Number' , V_GS_X86_PC_Memory.TotalPhysicalMemory0 as 'RAM'
,V_GS_Logical_Disk.DeviceID0 as 'Disk_Drive', V_GS_Logical_Disk.FreeSpace0,
V_GS_Logical_Disk.Size0 as 'Total'

from V_r_System inner join


V_GS_Logical_Disk on V_r_System.ResourceID=V_GS_Logical_Disk.resourceId Inner Join
V_GS_Disk on V_r_System.ResourceID=V_GS_Disk.ResourceID Inner Join
V_GS_Computer_System on V_r_System.ResourceID=V_GS_Computer_System.ResourceID inner join
v_GS_COMPUTER_SYSTEM_PRODUCT on V_r_System.ResourceID=v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceId inner join
V_GS_X86_PC_Memory on V_r_System.ResourceID=V_GS_X86_PC_Memory.ResourceID

where V_R_System.Operating_System_name_and0 like '%Microsoft%Server%' and DriveType0='3'