Locating machines in your SMS database with a static assigned IP address is actually quite easily accomplished. The key is to find machines that are not using DHCP to retrieve their IP address. Once this has been specified you need to further specify that you want machines where the IP address is not empty or blank. If the Where DhcpEnabled0 = 0 and the And IpAddress0 Is Not NULL were not specified then you could potentially get results such as the one shown below:
Name0 = Machine1970 IPAddress0 = NULL
Name0 = Machine1970 IPAddress0 = 0.0.0.0
Name0 = Machine1970 IPAddress0 = 192.168.1.111
Basic SQL Query:
Select Distinct
Sys.Name0,
Nic.IPAddress0
From v_R_System Sys
Join v_Gs_Network_Adapter_Configur Nic
On Sys.ResourceID = Nic.ResourceId
Where DhcpEnabled0 = 0
And IpAddress0 Is Not NULL
Order By Name0
Server SQL Query:
Nic.IPAddress0,
Nos.Caption0
Join v_Gs_Operating_System Nos
On Sys.ResourceID = Nos.ResourceId
And Caption0 Like '%Server%'
Note: To find machines that are not servers yet have static IP addresses change the line that reads: And Caption0 Like '%Server%' To: And Caption0 Not Like '%Server%'
No Comments