myITforum and Windows IT Pro Forums

How to add IP address and Harddisk to this below created query

Author Message
loganathanv@live.com

  • Total Posts : 6
  • Scores: 0
  • Reward points : 1290
  • Joined: 11/18/2013
  • Status: offline
How to add IP address and Harddisk to this below created query Monday, November 18, 2013 1:42 PM (permalink)
0
[Helpful answer received] / [List Solutions Only]
Hi,
I have found a sccm report which lists all properties which I expected except the IP address and the hard disk details. I have been unable to add and not able to find also. Any help would be appreciated.
select distinct
    R.Netbios_Name0 as 'Name',
    R.User_Name0 as 'User',
    CS.Manufacturer0 as 'Manufacturer',
    CS.Model0 as 'Model',
    BIOS.SerialNumber0 as 'Serial',
    SD.Name0 as 'Processor',
    RAM.TotalPhysicalMemory0 as 'Memory',
    VC.Name0 as 'Video Card',
    Convert(VarChar, VC.AdapterRam0 / 1024) + ' MB' as 'Video Card RAM'
from 
    dbo.v_R_System R
    inner join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = R.ResourceId
    inner join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = R.ResourceID
    inner join dbo.v_GS_PC_BIOS BIOS on BIOS.ResourceID =R.ResourceId
    inner join dbo.v_GS_SYSTEM_DEVICES SD on SD.ResourceID = R.ResourceId
    inner join dbo.v_GS_Video_Controller VC on R.ResourceID = VC.ResourceID
where
    SD.CompatibleIDs0 = 'ACPI\Processor'
    and VC.Name0 != 'ConfigMgr Remote Control Driver'
 
Thanks.!
 
#1
    gjones

    • Total Posts : 2513
    • Scores: 142
    • Reward points : 145740
    • Joined: 6/5/2001
    • Location: Ottawa, Ontario, Canada
    • Status: online
    Re:How to add IP address and Harddisk to this below created query Monday, November 18, 2013 7:31 PM (permalink)
    0
    I never recommend adding IP addresses to a report.
    Adding HD data will cause multiple row per PC. So I again avoid adding this to reports.
     
    #2
      loganathanv@live.com

      • Total Posts : 6
      • Scores: 0
      • Reward points : 1290
      • Joined: 11/18/2013
      • Status: offline
      Re:How to add IP address and Harddisk to this below created query Tuesday, November 19, 2013 4:01 AM (permalink)
      0
      Thanks jones.. i understood the difficulties. can you suggest me any other queries to generate the same like all these properties. because this is one of my business requirement .
       
       
      #3
        jkabaseball

        • Total Posts : 152
        • Scores: 9
        • Reward points : 58360
        • Joined: 5/5/2011
        • Status: offline
        Re:How to add IP address and Harddisk to this below created query Tuesday, November 19, 2013 8:13 AM (permalink)
        0
        Here is the one I use.  It's got IP address in there and works for the most part.  Laptops mess it up a bit.
         
        SELECT SYS.Netbios_Name0, OPSYS.Caption0 as OS, OPSYS.Version0 as Version,
         MEM.TotalPhysicalMemory0 as 'Total Memory', CSYS.Manufacturer0,
         CSYS.Model0 as 'Model', Processor.Name0 as 'CPU', VCARD.NAME0,NW.IPaddress0,NW.MACAddress0, BIOS.SerialNumber0, CDROM.Name0 as "CD-ROM",NA.Name0 as "Network Adapter"
        FROM v_R_System SYS
        JOIN  v_Network_DATA_Serialized NW on SYS.ResourceID = NW.ResourceID
        JOIN  v_GS_Network_Adapter NA on SYS.ResourceID = NA.ResourceID
        JOIN  v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID
        JOIN  v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID
        JOIN  v_GS_PROCESSOR Processor  on Processor.ResourceID = SYS.ResourceID
        JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
        JOIN v_GS_VIDEO_CONTROLLER VCARD on SYS.ResourceID=VCARD.ResourceID
        JOIN v_GS_PC_BIOS BIOS on SYS.ResourceID=BIOS.ResourceID
        JOIN v_GS_CDROM CDROM on SYS.ResourceID=CDROM.ResourceID
        WHERE NW.IPaddress0 NOT LIKE '%fe%' AND NW.IPaddress0 NOT LIKE '0.0.0.0' AND NW.IPaddress0 NOT LIKE '192.168%' AND NA.Name0 NOT LIKE '%ISATAP%' AND NA.Name0 NOT LIKE '%TUNNEL%' AND NA.Name0 NOT LIKE '%RAS%' AND NA.Name0 NOT LIKE '%WAN%' AND SYS.Netbios_Name0 LIKE @Computer_Name
         
        #4
          loganathanv@live.com

          • Total Posts : 6
          • Scores: 0
          • Reward points : 1290
          • Joined: 11/18/2013
          • Status: offline
          Re:How to add IP address and Harddisk to this below created query Tuesday, November 19, 2013 10:35 AM (permalink)
          0
          Thanks baseball. you pointed me the correct report. is it possible to get the report for collection wise.?
           
          #5
            jkabaseball

            • Total Posts : 152
            • Scores: 9
            • Reward points : 58360
            • Joined: 5/5/2011
            • Status: offline
            Re:How to add IP address and Harddisk to this below created query Thursday, November 21, 2013 1:56 PM (permalink)
            0
            Yes, It is possible.  There is a download for SCCM 2007 that lays out the views for SCCM that would be very helpful for what you are doing.  I have it printed out here and use it all the time.
             
            #6
              loganathanv@live.com

              • Total Posts : 6
              • Scores: 0
              • Reward points : 1290
              • Joined: 11/18/2013
              • Status: offline
              Re:How to add IP address and Harddisk to this below created query Tuesday, December 10, 2013 12:33 PM (permalink)
              0
              [This post was marked as helpful]
              Here is the query I have created. instead of IP have added ADsite to identify the client location. Thanks to all
               
               
              SELECT     TOP (100) PERCENT dbo.v_R_System.User_Name0 AS [User Name], dbo.v_R_System.Netbios_Name0 AS [Computer Name],
                                    dbo.v_R_System.Operating_System_Name_and0 AS [Operating System Version], dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
                                    dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [No.of Processor],
                                    dbo.v_GS_DISK.Size0 AS [Hard Disk (MB)], dbo.v_GS_OPERATING_SYSTEM.Caption0 AS OS, dbo.v_GS_VIDEO_CONTROLLER.Name0 AS [Video Controller],
                                    dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 / 1024 AS [RAM (MB)], dbo.v_FullCollectionMembership.CollectionID,
                                    dbo.v_GS_PROCESSOR.Name0 AS ProcessorName, dbo.v_GS_PC_BIOS.SerialNumber0 AS [Serial Number],
                                    dbo.vSMS_CombinedDeviceResources.ADSiteName
              FROM         dbo.v_GS_OPERATING_SYSTEM INNER JOIN
                                    dbo.v_GS_X86_PC_MEMORY INNER JOIN
                                    dbo.v_GS_PC_BIOS INNER JOIN
                                    dbo.v_GS_DISK ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_GS_DISK.ResourceID ON
                                    dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_GS_DISK.ResourceID INNER JOIN
                                    dbo.v_GS_VIDEO_CONTROLLER ON dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_GS_VIDEO_CONTROLLER.ResourceID ON
                                    dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_GS_VIDEO_CONTROLLER.ResourceID INNER JOIN
                                    dbo.v_FullCollectionMembership ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_FullCollectionMembership.ResourceID INNER JOIN
                                    dbo.v_GS_PROCESSOR ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_PROCESSOR.ResourceID INNER JOIN
                                    dbo.vSMS_CombinedDeviceResources ON dbo.v_FullCollectionMembership.Name = dbo.vSMS_CombinedDeviceResources.Name INNER JOIN
                                    dbo.v_R_System INNER JOIN
                                    dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID ON
                                    dbo.v_GS_PC_BIOS.ResourceID = dbo.v_R_System.ResourceID
              WHERE     (dbo.v_FullCollectionMembership.CollectionID = @CollectionID)
              ORDER BY [Computer Name]
              <message edited by loganathanv@live.com on Tuesday, December 10, 2013 12:38 PM>
               
              #7
                Online Bookmarks Sharing: Share/Bookmark

                Jump to:

                Current active users

                There are 0 members and 2 guests.

                Icon Legend and Permission

                • New Messages
                • No New Messages
                • Hot Topic w/ New Messages
                • Hot Topic w/o New Messages
                • Locked w/ New Messages
                • Locked w/o New Messages
                • Read Message
                • Post New Thread
                • Reply to message
                • Post New Poll
                • Submit Vote
                • Post reward post
                • Delete my own posts
                • Delete my own threads
                • Rate post

                2000-2014 ASPPlayground.NET Forum Version 3.9