Ben's Experiments: SCCM: Physical Memory and Memory Slots Hardware Inventory extension

This will be my first assignment on reporting in SCCM. Find out the details as below:

The total amount of DIMM-Slots
The free amount of DIMM-Slots

After reading Sherry Kissinger’s blog. I managed to pull the RAM information with SCCM.

What you need to do is to modify the SMS_DEF.MOF which is located in the following location: <smsinstalldir>\inboxes\clifiles.src\hinv

Just add the following code at the bottom of the MOF-File and save it.

//Physical Memory

[SMS_Report (TRUE),
SMS_Group_Name ("Physical Memory"),
SMS_Class_ID   ("Microsoft|Physical_Memory|1.0")]
class Win32_PhysicalMemory : SMS_Class_Template
{
                [SMS_Report (TRUE)] string BankLabel;
                [SMS_Report (TRUE), SMS_Units("Megabytes")] uint64 Capacity;
                [SMS_Report (TRUE)] string Caption;
                [SMS_Report (TRUE)] string DeviceLocator[];
                [SMS_Report (TRUE)] uint16 FormFactor;
                [SMS_Report (TRUE)] string Manufacturer;
                [SMS_Report (TRUE)] uint16 MemoryType;
                [SMS_Report (TRUE)] uint32 PositionInRow;
                [SMS_Report (TRUE)] uint32 Speed;
                [SMS_Report (TRUE),Key] string    Tag;
                [SMS_Report (TRUE),Key] string    CreationClassName;
};

// Physical Memory Array

[SMS_Report (TRUE),
SMS_Group_Name ("Physical Memory Array"),
SMS_Class_ID   ("Microsoft|Physical_Memory_Array|1.0")]
class Win32_PhysicalMemoryArray : SMS_Class_Template
{
                [SMS_Report (FALSE)] string Caption;
                [SMS_Report (FALSE)] string CreationClassName;
                [SMS_Report (FALSE)] string Description;
                [SMS_Report (FALSE)] uint16 Location;
                [SMS_Report (FALSE)] string Manufacturer;
                [SMS_Report (TRUE), SMS_Units("Megabytes")] uint32 MaxCapacity;
                [SMS_Report (TRUE)] uint16 MemoryDevices;
                [SMS_Report (FALSE)] uint16 MemoryErrorCorrection;
                [SMS_Report (FALSE)] string Model;
                [SMS_Report (FALSE)] string Name;
                [SMS_Report (FALSE)] string OtherIdentifyingInfo;
                [SMS_Report (FALSE)] string PartNumber;
                [SMS_Report (FALSE)] boolean PoweredOn;
                [SMS_Report (FALSE)] boolean Removable;
                [SMS_Report (FALSE)] boolean Replaceable;
                [SMS_Report (FALSE)] string SerialNumber;
                [SMS_Report (FALSE)] string SKU;
                [SMS_Report (FALSE)] string Status;
                [SMS_Report (TRUE), Key] string Tag;
                [SMS_Report (FALSE)] uint16 Use;
                [SMS_Report (FALSE)] string Version;
};

SQL statement for Report

select sys.netbios_name0, mem.banklabel0 [Bank Label], mem.capacity0 [Capacity in MB], mem.FormFactor0 [Form Factor],
MEM.memorytype0 [Memory Type], mem.tag0 [TAG] from v_gs_physical_memory as MEM
inner join v_r_system as SYS on SYS.resourceid=MEM.resourceid
where
sys.netbios_name0 = @compname
order by MEM.tag0

select MEMA.MemoryDevices0 [Total Number of Memory Slots] from v_gs_physical_memory_array as MEMA
inner join v_r_system as SYS on SYS.resourceid=MEMA.resourceid
where
sys.netbios_name0 = @compname

select mema.memoryDevices0 - Count(mem.tag0) [Number of Free Slots available] from v_gs_physical_memory as MEM
inner join v_r_system as SYS on SYS.resourceid=MEM.resourceid
inner join v_gs_physical_memory_array as MEMA on sys.resourceid=mema.resourceid
where
sys.netbios_name0 = @compname
group by mema.memorydevices0

With a prompt for compname, provide and sql statement of:

begin
if (@__filterwildcard = '')
SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS ORDER By SYS.Netbios_Name0
else
SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS
WHERE SYS.Netbios_Name0 like @__filterwildcard
ORDER By SYS.Netbios_Name0
end

This will be the result of the Physical Memory Inventory Report.