In some cases, it may be difficult to find the computer behind a given IP address. SCCM does not natively have a function to find a computer from an IP. We will see that the name can easily be found using SQL Server.

Problem
In some incidents, or when we are checking declared boundaries, the only information we have is an IP address. Of course, in most cases, an nslookup can find the name of the machine. But there may be security rules that prevent this tool from returning the correct result. Fortunately, it is easy to find this information thanks to SQL Server.

Finding a computer from an IP: solution

The following query allows you to find a machine from an IP:

select distinct
    sys.Name0 as [Name],
    sys.Model0 as [Model],
    sys.UserName0 as [User],
    os.Name0 as [OS],
    sys.SystemType0 as [OS Type],
    LEFT(IPAddress0, LEN('%.%.%.%.%.%.%') - patIndex(',', IPAddress0)) AS [IP Address]
from
    v_GS_COMPUTER_SYSTEM sys
    inner join v_GS_NETWORK_ADAPTER_CONFIGUR net on sys.ResourceID = net.ResourceID
    inner join v_GS_OPERATING_SYSTEM os on sys.ResourceID = os.ResourceID
where
    IPAddress0 IS NOT NULL
    AND IPAddress0 LIKE '10.17.%'
    AND NOT (IPAddress0 LIKE '192%')
    AND NOT (IPAddress0 LIKE '0%')
order by sys.Name0

The easily modifiable options are at the IP level:

• IPAddress0 LIKE: this criterion allows you to define an exact IP address. You can also target an IP range to find a list of machines.
• AND NOT (IPAddress0 LIKE ‘192%’): This criterion defines the IP ranges to be excluded. For a machine with multiple IPs, this allows the exclusion of home IPs, for example when the customer is at home.

This query can be run directly from a SQL Server query, or from the SCCM console.

Conclusion

Thanks to this query, we can easily find the computers of a given IP range or of a specific IP. It can be of great help during maintenance tasks, especially when reviewing boundaries.