0123-ibm-630x420

Identify all the devices associated with a specified IP gateway…

SELECT DISTINCT 
  DNSHostName0 AS HostName, 
  ResourceID, 
  IPSubnet0 AS Mask, 
  MACAddress0 AS MAC, 
  IPAddress0 AS IPAddress, 
  DHCPEnabled0 AS DHCPEnabled, 
  DHCPServer0 AS DHCPServer, 
  DNSDomain0 AS DHCPDomain, 
  DefaultIPGateway0 AS Gateway
FROM 
  dbo.v_Network_DATA_Serialized
WHERE (IPSubnet0 IS NOT NULL) 
  AND (IPAddress0 NOT LIKE 'f%')
  AND (DefaultIPGateway0 = '192.168.29.1')
ORDER BY
  DNSHostName0

You can also join this to v_R_System to confirm the AD Site Name, as well as join to v_GS_Computer_System to pull manufacturer and model, or v_GS_System_Enclosure, to get the chassis type and BIOS serial number.

Note: the “(IPAddress0 NOT LIKE ‘f%’)” clause is used to filter out IPv6 rows, which cause duplicates and since the gateway wouldn’t match it avoids it anyway.  I could be incorrect on this however, so always, always, always test this yourself and adjust as you see fit.

SELECT DISTINCT 
  dbo.v_Network_DATA_Serialized.DNSHostName0 AS HostName, 
  dbo.v_Network_DATA_Serialized.ResourceID, 
  dbo.v_Network_DATA_Serialized.IPSubnet0 AS Mask, 
  dbo.v_Network_DATA_Serialized.MACAddress0 AS MAC, 
  dbo.v_Network_DATA_Serialized.IPAddress0 AS IPAddress, 
  dbo.v_Network_DATA_Serialized.DHCPEnabled0 AS DHCPEnabled, 
  dbo.v_Network_DATA_Serialized.DHCPServer0 AS DHCPServer, 
  dbo.v_Network_DATA_Serialized.DNSDomain0 AS DHCPDomain, 
  dbo.v_Network_DATA_Serialized.DefaultIPGateway0 AS Gateway, 
  dbo.v_R_System.AD_Site_Name0, 
  dbo.v_R_System.Client_Version0, 
  dbo.v_R_System.Is_Virtual_Machine0, 
  dbo.v_GS_COMPUTER_SYSTEM.Model0, 
  dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0, 
  dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0
FROM 
  dbo.v_Network_DATA_Serialized 
  FULL OUTER JOIN
  dbo.v_GS_SYSTEM_ENCLOSURE ON 
    dbo.v_Network_DATA_Serialized.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID 
  FULL OUTER JOIN
  dbo.v_GS_COMPUTER_SYSTEM ON 
    dbo.v_Network_DATA_Serialized.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
  FULL OUTER JOIN
  dbo.v_R_System ON 
    dbo.v_Network_DATA_Serialized.ResourceID = dbo.v_R_System.ResourceID
WHERE 
  (dbo.v_Network_DATA_Serialized.IPSubnet0 IS NOT NULL) AND 
  (dbo.v_Network_DATA_Serialized.IPAddress0 NOT LIKE 'f%') AND 
  (dbo.v_Network_DATA_Serialized.DefaultIPGateway0 = '192.168.29.1')
ORDER BY HostName

Enjoy!

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s