Helpful ReplyHow to add IP address and Harddisk to this below created query

Author
loganathanv@live.com
New Member
  • Total Posts : 6
  • Scores: 0
  • Reward points: 1290
  • Joined: 2013/11/18 13:31:30
  • Status: offline
2013/11/18 13:42:08 (permalink)
0

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

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
Expert Member
  • Total Posts : 2816
  • Scores: 146
  • Reward points: 247630
  • Joined: 2001/06/05 12:32:00
  • Location: Ottawa, Ontario, Canada
  • Status: offline
Re:How to add IP address and Harddisk to this below created query 2013/11/18 19:31:48 (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
New Member
  • Total Posts : 6
  • Scores: 0
  • Reward points: 1290
  • Joined: 2013/11/18 13:31:30
  • Status: offline
Re:How to add IP address and Harddisk to this below created query 2013/11/19 04:01:03 (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
Expert Member
  • Total Posts : 153
  • Scores: 9
  • Reward points: 64490
  • Joined: 2011/05/05 16:23:35
  • Status: offline
Re:How to add IP address and Harddisk to this below created query 2013/11/19 08:13:39 (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
New Member
  • Total Posts : 6
  • Scores: 0
  • Reward points: 1290
  • Joined: 2013/11/18 13:31:30
  • Status: offline
Re:How to add IP address and Harddisk to this below created query 2013/11/19 10:35:14 (permalink)
0
Thanks baseball. you pointed me the correct report. is it possible to get the report for collection wise.?
#5
jkabaseball
Expert Member
  • Total Posts : 153
  • Scores: 9
  • Reward points: 64490
  • Joined: 2011/05/05 16:23:35
  • Status: offline
Re:How to add IP address and Harddisk to this below created query 2013/11/21 13:56:11 (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
New Member
  • Total Posts : 6
  • Scores: 0
  • Reward points: 1290
  • Joined: 2013/11/18 13:31:30
  • Status: offline
Re:How to add IP address and Harddisk to this below created query 2013/12/10 12:33:07 (permalink) ☄ Helpful
0
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]
post edited by loganathanv@live.com - 2013/12/10 12:38:29
#7
Jump to:
© 2018 APG vNext Commercial Version 5.5