Access/SQL Table Parser

Question

I'm trying to take a list of IPs and hostnames and relating it to a netstat pull. Currently the relevant information from the netstat pull is (Source IP, Source Port, and Destination IP). However netstat doesn't pull Hostnames in the environment I'm working with. So I've created a second table with IP and Hostnames related. There is no DNS in this network so assume this table relation is the only way possible. Currently my query is as follows:

SELECT 
  IP_network.IP_src AS [Source IP]
  IP_lookup.Hostname AS [Source Hostname]
  IP_network.Port_src AS [Source Port]
  IP_network.IP_dst AS [Destination IP]
  IP_lookup.Hostname AS [Destination Hostname]
FROM IP_network
INNER JOIN IP_lookup ON IP_network.IP_src = IP_lookup.IP

One of the main issues I have is getting the Hostname column linked distinctly to the Source IP and a separate Hostname linked to Destination IP. For example,

Source IP     Source Host     Source Port     Destination IP     Destination Hostname
----------    ------------    ------------    ---------------    ---------------------
127.0.0.1                     22              192.168.0.1                   
10.10.10.1     Host1          22              127.0.0.1          Host1
10.50.10.1     Host2          23              10.10.50.1         Host2
10.50.10.1     Host3          23              10.10.50.1         Host3

I would also like to get any NULL Hostname's to read as 'UNK', but I couldn't get an IsNull command to work in Access. Any ideas are helpful, thanks for your time.

score 0 · Accepted Answer · edited May 23 '17 at 12:25

You can join the same table more than once using different aliases. Like this:

SELECT 
  ipne.IP_src AS [Source IP]
  iplo1.Hostname AS [Source Hostname]
  ipne.Port_src AS [Source Port]
  ipne.IP_dst AS [Destination IP]
  iplo2.Hostname AS [Destination Hostname]
FROM IP_network AS ipne
  INNER JOIN IP_lookup AS iplo1 ON iplo1.IP = ipne.IP_src
  INNER JOIN IP_lookup AS iplo2 ON iplo2.IP = ipne.IP_dst

Concerning the nulls, look this question: coalesce alternative in Access SQL

Access/SQL Table Parser

1 Answers1