Friday, August 8, 2008

Retrieve Machines IP Address, Default Gateway And MAC Address

SQL Query To Retrieve Machines IP Address, Default Gateway And MAC Address





To retrieve the IP address; Default Gateway and the Mac address for your SMS discovered resources.
This query has added logic that will enable you to filter out those machines that do not have a specified Default Gateway set as well as those machines that do not have a specified IP address set or have an IP address that is 0.0.0.0 which is typically the case for machines with multiple Network Interface Card's (NIC's) installed where the NIC is not in use.

SQL Query:

Select Distinct

SD.Name0,

IP.IpAddress0,

IP.DefaultIpGateway0,

IP.MacAddress0

From v_Gs_System SD

Join v_Gs_Network_Adapter_Configur IP

On SD.ResourceId = IP.ResourceId

Where IP.DefaultIPGateway0 Is Not NULL

And IP.IPAddress0 Is Not NULL

And IP.IPAddress0 <> '0.0.0.0'

Order By SD.Name0


Enjoy,
Paddy


1 comment:

PaulHut said...

How would I get it to not display the IPv6 address?