There were thousands of machines in the majority of the large companies I consulted and they wanted to follow these machines with SCCM. Reports that are available in the SCCM sometimes may not provide enough information. In such cases the solution comes with Microsoft SQL Management Studio. If you are a system administrator, you will find yourself writing SQL 🙂
I’m not sure if I can help you write SQL, but I have a lot of ready-made SQL that my customers like a lot. I plan to share these with you one by one. Most of my customers wanted a report like the one below. I have changed the report according to the wishes of the years and I share the final status with you.
Features;
Collection limited,
IP v4 addresses,
IP v6 addresses,
Operating Sistems and Service Pack Levels,
Brand,
Model,
CPU Model,
CPU Core,
CPU Logical Core,
Memory.
–Report start from here
;WITH Table1
AS
(
SELECT distinct
v_R_System.Name0 AS ‘Computer Name’,
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS ‘Manufacturer’,
v_GS_COMPUTER_SYSTEM.Model0 AS ‘Machine Model’,
v_GS_PROCESSOR.Name0 AS ‘CPU Model’,
v_GS_PROCESSOR.NumberOfCores0 AS ‘CPU Core’,
v_GS_PROCESSOR.NumberOfLogicalProcessors0 AS ‘CPU Logical Processor’,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS ‘Memory’,
isnull(v_GS_OPERATING_SYSTEM.Caption0, ”) + ‘ ‘ + isnull(v_GS_OPERATING_SYSTEM.CSDVersion0, ”) AS ‘Operating System and Service Pack’,
v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 AS IP
FROM v_R_System
INNER JOIN v_FullCollectionMembership ON v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
INNER JOIN v_GS_NETWORK_ADAPTER_CONFIGUR v_GS_NETWORK_ADAPTER_CONFIGUR ON v_R_System.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID
INNER JOIN v_GS_X86_PC_MEMORY ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID
LEFT OUTER JOIN V_GS_PROCESSOR
INNER JOIN v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID =V_GS_PROCESSOR.ResourceID
ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
WHERE CollectionID = @COLLID AND v_GS_NETWORK_ADAPTER_CONFIGUR.IPEnabled0 = 1
–AND v_GS_PROCESSOR.GroupID = 1
AND v_GS_PROCESSOR.NumberOfCores0 IS NOT NULL
)
SELECT
“Computer Name”,Manufacturer,”Machine Model”,”CPU Model”,”CPU Core”,”CPU Logical Processor”,Memory,”Operating System and Service Pack”,
STUFF(
(SELECT ‘,’ + IP
FROM Table1
WHERE “Computer Name” = a.”Computer Name” –AND FIRST_NAME = a.FIRST_NAME AND LAST_NAME = a.LAST_NAME
FOR XML PATH (”))
, 1, 1, ”) AS IP
FROM Table1 AS a
group by “Computer Name”,Manufacturer,”Machine Model”,”CPU Model”,”CPU Core”,”CPU Logical Processor”,Memory,”Operating System and Service Pack”
–Report finished here
Enjoy!
Please do not hesitate to ask your questions on this topic or similar topics…
to be continued…