none
Add IP Address to SCCM 2007 Report RRS feed

  • Question

  • Greetings:

    I would like to add MAC Address and IP Address to display for each computer on this report.  Can someone lend me a hand with the SQL?

    Thanks!

    ---select * from v_GS_SYSTEM_ENCLOSURE

    SELECT  distinct
    CS.name0 as 'Computer Name',
    CS.domain0 as 'Domain',
    CS.UserName0 as 'User',
    USR.department0 as 'Department',
    BIOS.SerialNumber0 as 'Bios serial',
    SE.SerialNumber0 as 'System Enclosure serial',
    CS.Manufacturer0 as 'Manufacturer',
    CS.Model0 as 'model',
    OS.Caption0 as 'OS',
    RAA.SMS_Assigned_Sites0 as 'Site',
    RAM.TotalPhysicalMemory0 as 'Total Memory',
    sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
    sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
    CPU.MaxClockSpeed0 as 'Max CPU Speed',
    CPU.Name0 as 'CPU Model',
    CPU.Is64Bit0 as '64 Bit Compatible'
    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
    left join v_R_User USR on USR.Unique_User_Name0 = CS.UserName0
    right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID 
    right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
    right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
    right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
    right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   
    right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
    where
    LDisk.DriveType0 =3
    group by
    CS.Name0,
    CS.domain0,
    CS.Username0,
    USR.department0,
    BIOS.SerialNumber0,
    SE.SerialNumber0,
    CS.Manufacturer0,
    CS.Model0,
    OS.Caption0,
    RAA.SMS_Assigned_Sites0,
    RAM.TotalPhysicalMemory0,
    CPU.MaxClockSpeed0,
    CPU.Name0,
    CPU.Is64Bit0
    ORDER BY CS.name0

    Friday, June 22, 2012 11:42 PM

Answers

  • Hi Sawbone01

    I have made a few changes to the query to include MAC and IP address. I have used the DNSHostName from the v_GS_NETWORK_ADAPTER_CONFIGUR as the device name, so if this is not configured correctly on the network adapter you may get some unusal results.

    I've highlighted the changes to help you understand what I have done.

    SELECT  distinct
    NA.DNSHostName0 as 'Computer Name',
     CS.domain0 as 'Domain',
    CS.UserName0 as 'User',
     BIOS.SerialNumber0 as 'Bios serial',
    SE.SerialNumber0 as 'System Enclosure serial',
    CS.Manufacturer0 as 'Manufacturer',
    CS.Model0 as 'model',
    OS.Caption0 as 'OS',
    RAA.SMS_Assigned_Sites0 as 'Site',
    RAM.TotalPhysicalMemory0 as 'Total Memory',
    sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
    sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
    CPU.MaxClockSpeed0 as 'Max CPU Speed',
    CPU.Name0 as 'CPU Model',
    CPU.Is64Bit0 as '64 Bit Compatible' ,
    NA.MACAddress0 as 'MAC Address',
    NA.IPAddress0 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
     left join v_R_User USR on USR.Unique_User_Name0 = CS.UserName0
     right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID 
    right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
    right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
    right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
    right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   
    full join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
    left join v_GS_NETWORK_ADAPTER_CONFIGUR NA on NA.ResourceID = CS.ResourceID
     where
    LDisk.DriveType0 =3 and NA.DNSHostName0  <> 'Null'
    group by
    NA.DNSHostName0,
    CS.domain0,
    CS.Username0,
    BIOS.SerialNumber0,
    SE.SerialNumber0,
    CS.Manufacturer0,
    CS.Model0,
    OS.Caption0,
    RAA.SMS_Assigned_Sites0,
    RAM.TotalPhysicalMemory0,
    CPU.MaxClockSpeed0,
    CPU.Name0,
    CPU.Is64Bit0,
    NA.MACAddress0,
    NA.IPAddress0
    ORDER BY NA.DNSHostName0

    I hope this is useful and helps you.

    Kind regards

    Adrian

    Tuesday, June 26, 2012 6:19 PM

All replies

  • I would never recommend add either MAC or IP address to a report, this will just add duplicated PCs to your report.


    http://www.enhansoft.com/

    Sunday, June 24, 2012 2:27 PM
    Moderator
  • try this if it helps adding IP Address ? http://eskonr.com/2012/01/sccm-report-computers-ip-address-last-logged-user-name

    Please click on "vote as Helpful" if you feel this post helpful to you.

    Eswar Koneti | My Tech blog: www.eskonr.com | Linkedin: Eswar Koneti

    Monday, June 25, 2012 10:14 AM
  • Hi Eswar,

    Thank you for your reply to my post.  I was wondering if you could help me integrate the SQL query for IP Address/MAC into the existing report in this thread?  I apologize, I am a complete SQL noob.

    Tuesday, June 26, 2012 4:18 PM
  • Hi Sawbone01

    I have made a few changes to the query to include MAC and IP address. I have used the DNSHostName from the v_GS_NETWORK_ADAPTER_CONFIGUR as the device name, so if this is not configured correctly on the network adapter you may get some unusal results.

    I've highlighted the changes to help you understand what I have done.

    SELECT  distinct
    NA.DNSHostName0 as 'Computer Name',
     CS.domain0 as 'Domain',
    CS.UserName0 as 'User',
     BIOS.SerialNumber0 as 'Bios serial',
    SE.SerialNumber0 as 'System Enclosure serial',
    CS.Manufacturer0 as 'Manufacturer',
    CS.Model0 as 'model',
    OS.Caption0 as 'OS',
    RAA.SMS_Assigned_Sites0 as 'Site',
    RAM.TotalPhysicalMemory0 as 'Total Memory',
    sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
    sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
    CPU.MaxClockSpeed0 as 'Max CPU Speed',
    CPU.Name0 as 'CPU Model',
    CPU.Is64Bit0 as '64 Bit Compatible' ,
    NA.MACAddress0 as 'MAC Address',
    NA.IPAddress0 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
     left join v_R_User USR on USR.Unique_User_Name0 = CS.UserName0
     right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID 
    right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
    right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
    right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
    right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   
    full join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
    left join v_GS_NETWORK_ADAPTER_CONFIGUR NA on NA.ResourceID = CS.ResourceID
     where
    LDisk.DriveType0 =3 and NA.DNSHostName0  <> 'Null'
    group by
    NA.DNSHostName0,
    CS.domain0,
    CS.Username0,
    BIOS.SerialNumber0,
    SE.SerialNumber0,
    CS.Manufacturer0,
    CS.Model0,
    OS.Caption0,
    RAA.SMS_Assigned_Sites0,
    RAM.TotalPhysicalMemory0,
    CPU.MaxClockSpeed0,
    CPU.Name0,
    CPU.Is64Bit0,
    NA.MACAddress0,
    NA.IPAddress0
    ORDER BY NA.DNSHostName0

    I hope this is useful and helps you.

    Kind regards

    Adrian

    Tuesday, June 26, 2012 6:19 PM
  • If you want you can also use something like this,..  this way you wont get duplicated PC to your report. Also I add a count for the number of IP addresses;

    Thanks to John Nelson

    http://myitforum.com/cs2/blogs/jnelson/default.aspx

    SELECT  distinct

     CS.name0 as 'Computer Name',
     CS.domain0 as 'Domain',
     CS.UserName0 as 'User',
     USR.department0 as 'Department',
     BIOS.SerialNumber0 as 'Bios serial',
     SE.SerialNumber0 as 'System Enclosure serial',
     CS.Manufacturer0 as 'Manufacturer',
     CS.Model0 as 'model',
     OS.Caption0 as 'OS',
     RAA.SMS_Assigned_Sites0 as 'Site',
     RAM.TotalPhysicalMemory0 as 'Total Memory',
     sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
     sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
     CPU.MaxClockSpeed0 as 'Max CPU Speed',
     CPU.Name0 as 'CPU Model',
     CPU.Is64Bit0 as '64 Bit Compatible',
     STUFF
     (( SELECT
      ', ' + ip.IP_Addresses0
      
      FROM
       v_RA_System_IPAddresses ip

      WHERE

       ip.ResourceID = sysv.ResourceID
       AND ip.IP_Addresses0 LIKE '%.%'
       AND ip.IP_Addresses0 <> '0.0.0.0' --remove 0.0.0.0

      FOR XML PATH('')),1,1,'') AS 'IPv4 Addresses',
     (SELECT
      COUNT (*)
      
     FROM 
      v_RA_System_IPAddresses ip
     
     WHERE
      ip.ResourceID = sysv.ResourceID
      AND ip.IP_Addresses0 LIKE '%.%'
      AND ip.IP_Addresses0 <> '0.0.0.0') AS 'Number of Addresses'

    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
     left join v_R_User USR on USR.Unique_User_Name0 = CS.UserName0
     right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID 
     right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
     right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
     right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
     right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   
     right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
     right join v_R_System_Valid sysv on CS.ResourceID = sysv.ResourceID

    WHERE
     
    LDisk.DriveType0 =3

    GROUP BY
     
     CS.Name0,
     CS.domain0,
     CS.Username0,
     USR.department0,
     BIOS.SerialNumber0,
     SE.SerialNumber0,
     CS.Manufacturer0,
     CS.Model0,
     OS.Caption0,
     RAA.SMS_Assigned_Sites0,
     RAM.TotalPhysicalMemory0,
     CPU.MaxClockSpeed0,
     CPU.Name0,
     CPU.Is64Bit0,
     SYSV.ResourceID
     
    ORDER BY

     CS.name0

    Tuesday, June 26, 2012 7:03 PM
  • nice one! I had to comment the usr.department, how do you get this info? thanks!
    Friday, July 6, 2012 1:30 PM
  • nice one! I had to comment the usr.department, how do you get this info? thanks!

    You need to enable the attribute to be collected by AD user discovery.


    http://www.enhansoft.com/

    Friday, July 6, 2012 6:45 PM
    Moderator
  • Hi,

    What about one  PC with more IP Addresses? Can we merge and write side by side?

    And How we can add Monitor and VGA Card Information to this list. 

    Thanks

    Friday, August 2, 2019 4:49 AM
  • did you look at the queries above? what is wrong with them?

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    Friday, August 2, 2019 11:01 AM
    Moderator