How to add MAC Address on the sccm 2012 report

[sweety_devil]

Dear all,

 

I try to create a custom report.

I wanted to use Hardware 01A report to include the MAC Adresses but I don't know how to do it.

 

Thank you.

 

select distinct

v_R_System_Valid.ResourceID,

v_R_System_Valid.Netbios_Name0 AS [Computer Name],

v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],

v_Site.SiteName as [sMS Site Name],

[Top Console User] = CASE

when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')

then @UnknownLoc

Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0

End,

v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],

v_GS_OPERATING_SYSTEM.CSDVersion0 AS [service Pack Level],

v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [serial Number],

v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],

v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],

v_GS_COMPUTER_SYSTEM.Model0 AS [Model],

v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],

v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],

(Select sum(Size0)

from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )

where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and

v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)],

(Select sum(v_GS_LOGICAL_DISK.FreeSpace0)

from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )

where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)]

from v_R_System_Valid

inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)

left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)

LEFT join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)

LEFT join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)

LEFT join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)

inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)

left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)

LEFT join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)

left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)

Where v_FullCollectionMembership.CollectionID = @CollectionID

Order by v_R_System_Valid.Netbios_Name0

[GarthMJ]

I would recommend against adding MAC address (or IP address) to any report, as this will duplicate lines within the report and add (IMO) no value to the report.

 

Why do you need the MAC address?

[sweety_devil]

Just to have mac address on my report.

I already created a query with this functionality.