Access useful information from the SCCM’s DB #1

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 🙂

Microsoft SQL Server Management Studio 17

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.

Microsoft SQL Server Management Studio 17 about

–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…

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: