Wednesday, July 19, 2023

Lookup IP address host using SQL

Added as part of the recent Technology Refreshes, IBM i 7.5 TR2 and 7.4 TR8, is a new scalar function that returns the hostname for an IP address.

The syntax for this scalar function, DNS_LOOKUP_IP, is simple, and can be used in one of two ways:

01  VALUES DNS_LOOKUP_IP(IP_ADDRESS => '123.456.789.12') ;

02  VALUES DNS_LOOKUP_IP('123.456.789.12') ;

Line 1: The scalar function can be used with the parameter name.

Line 2: Or not.

It is a question of personal preference which example to use, as there is just one parameter passed to the scalar function.

Let me try this to lookup the host for the following IP address:

01  VALUES DNS_LOOKUP_IP(IP_ADDRESS => '172.217.1.142')

The IP address I used is the one that is returned when I "ping" google.com from my PC.

The result is:

00001
-------------------------
dfw28s23-in-f14.1e100.net

If no result is returned the IP address is not valid.

What if I wanted to look up my own host?

Rather than having to look up the IP of this computer I can use system global variable: CLIENT_IPADDR

01  VALUES DNS_LOOKUP_IP(IP_ADDRESS => SYSIBM.CLIENT_IPADDR)

Notice that I had to give the library name as well as the global variable's name. If I did not I get the following message:

SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable CLIENT_IPADDR not found.

You may not. If you do qualify CLIENT_IPADDR with its library, SYSIBM.

I am not going to show my result as mine will differ from yours, as you are not attached to my host.

A better way to illustrate how this works is to use the DNS_LOOKUP Table function to provide IP addresses that the DNS_LOOKUP_IP scalar function can then look up.

The following statement will look up the IP addresses for google.com and ibm.com, and then look up the hostnames:

01  SELECT 'google.com' AS "Domain",
02         ADDRESS_SPACE_TYPE AS "Type",
03         IP_ADDRESS,
04         DNS_LOOKUP_IP(IP_ADDRESS) AS "DNS lookup"
05  FROM TABLE(QSYS2.DNS_LOOKUP('google.com'))
06  UNION
07  SELECT 'ibm.com',ADDRESS_SPACE_TYPE,IP_ADDRESS,
08         DNS_LOOKUP_IP(IP_ADDRESS)
09  FROM TABLE(QSYS2.DNS_LOOKUP('ibm.com'))

Line 1: The first column of the results will be the domain I looked up.

Line 2: Returns the IP address type, IPV4 or IPV6.

Line 3: The returned IP address.

Line 4: Here I use the DNS_LOOKUP_IP to look up the hostname of the IP address that was returned by DNS_LOOKUP.

Line 5: I am looking up the DNS for "google.com".

Line 6: Here I am using an UNION to combine the results from the two DNS lookups.

Lines 7 and 8: Same columns as I defined on lines 1 – 4.

Line 9: DNS look up for "ibm.com".

Domain      Type  IP_ADDRESS                 DNS lookup
----------  ----  -------------------------  -------------------------------
google.com  IPV4  142.251.40.78              dfw28s34-in-f14.1e100.net
google.com  IPV6  2607:f8b0:4000:803::200e   dfw28s38-in-x0e.1e100.net
ibm.com     IPV4  23.64.138.216              a23-64-138-216.deploy.static...
ibm.com     IPV6  2600:1404:ec00:1289::3831  g2600-1404-ec00-1289-0000-00...
ibm.com     IPV6  2600:1404:ec00:128f::3831  g2600-1404-ec00-128f-0000-00...

This is a useful addition to be able to validate that an IP resolves to the correct host.

 

You can learn more about the DNS_LOOKUP_IP scalar function from the IBM website here.

 

This article was written for IBM i 7.5 TR2 and 7.4 TR8.

3 comments:

  1. if you are interested please vote for this IBM request to enhance DNS_LOOKUP adding a parameter for the DNS record type (MX etc.).

    https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3361

    ReplyDelete
  2. VALUES SYSIBM.CLIENT_IPADDR produces a value of 10.3.1.3
    VALUES DNS_LOOKUP_IP(IP_ADDRESS => SYSIBM.CLIENT_IPADDR) produces a null value.
    We're on 7.4 with the latest PTFs.

    ReplyDelete
  3. I think that is so because there is not a DNS entry for that IP address.
    I checked with various Reverse DNS look up websites and not one of them said they found and entry for that IP.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.