Report

[Ariel]

I'm new to SCCM 2007 r2 and SQL express in the same box and I'm trying to figure out how to create a custom report that will display VLAN,Netbios Name, MAC Address, and Serial Tag for all systems in my domain. Can anyone help me out?

[sreeni]

I'm new to SCCM 2007 r2 and SQL express in the same box and I'm trying to figure out how to create a custom report that will display VLAN,Netbios Name, MAC Address, and Serial Tag for all systems in my domain. Can anyone help me out?

 

Hi

 

Hope this will help You!!!! This gives all the information except VLAN info. I am trying to find out more about the same. Will keep you posted..

 

-----

 

SELECT distinct

CS.name0 as 'Computer Name',

CS.resourceid as 'Resource ID',

BIOS.SerialNumber0 as 'Bios serial',

SE.SerialNumber0 as 'System Enclosure serial',

RAA.SMS_Assigned_Sites0 as 'Site',

mac.mac_addresses0 as 'MAC Address',

ip.ip_addresses0 as 'IP Address'

from

v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID

right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID

right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID

right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID

right join v_RA_System_MACAddresses MAC on MAC.resourceid = cs.resourceid

left join v_RA_System_IPAddresses ip on ip.resourceid = cs.resourceid

left join v_GS_NETWORK_ADAPTER nw on nw.resourceid = cs.resourceid

 

group by

CS.Name0,

cs.resourceid,

BIOS.SerialNumber0,

SE.SerialNumber0,

RAA.SMS_Assigned_Sites0,

MAC.mac_addresses0,

ip.ip_addresses0

 

order by

cs.resourceid

 

Regards,

Sreeni