PQL join() function

join() — traverses a tree and concatenates all values with a provided string, or selects data from two or more paths and joins the data to make the result complete.

Syntax

The join() function returns a string and takes 2 parameters, a tree and a string:

string join(tree input, string input)

Return value

The join() function returns a string.

Description

The join() function traverses a tree and concatenates all values with a provided string, or selects data from two or more paths and joins the data to make the result complete. The join() function must include 2 parameters, a tree or path, and a string.

Examples

Example 1: Find multiple interface IPs

Query: What are the IP addresses of all the interfaces?

 SELECT join(interface/inet/ip_address, ', ') as ip 
 FROM /network/device

This query uses the join() function to traverse a tree and concatenate all values with a provided string.

The PQL join() function is useful in situations like this one, where you don't know exactly what a PQL tree holds, and have no clear logic for choosing a sub-tree, such as if you want to print the IP address of a device with multiple interfaces.

Results: Depending on the database, this query returns results similar to this:

   row:
     ip: null
   row:
     ip: 10.10.10.10
   row:
     ip: 10.10.10.20
   row:
     ip: 10.10.10.30
   row:
     ip: 10.10.10.77, 10.10.10.78, 10.10.10.79    
   row:
     ip: 10.10.10.93, 10.10.10.94
   . . .

Note that devices with multiple interfaces are represented by rows with multiple IP addresses.

Example 2: Find open ports

Query: What ports are open on the first network device?

SELECT coalesce(system/dns_name, 
  first(interface/inet/ip_address)) as "Device", 
  join(evidence/tcp_port/param/port, ',') as "TCP Ports", 
  join(evidence/udp_port/param/port, ',') as "UDP Ports" 
FROM /network/device ORDER BY 1

This query shows the open TCP and UDP ports for the first device on the network. The query uses the join() function to traverse the tree and concatenate all values with the provided string.

Results: Depending on the database, this query returns results similar to this:

row 
   Device 10.10.10.102 
   TCP Ports 9100,631,515,514,80,23,21 
   UDP Ports 161 
row 
   Device 10.10.10.111 
   TCP Ports 111,81,80,22 
   UDP Ports null 
row 
   Device 10.10.10.13 
   TCP Ports 280,80,23 
   UDP Ports 161 
row 
   Device 10.10.10.14 
   TCP Ports 23 
   UDP Ports 161
   . . .

Example 3: Find discovered hosts

Query: Which hosts have been discovered on my network?

SELECT nvl(system/dns_name,system/name) as "Name", 
  join(interface/inet/ip_address, ', ') as "IP Address", 
  system/computed_vendor as "Vendor", 
  last_seen 
FROM /network/device 
WHERE system is not null

This query finds all the devices found on your network. The query uses the join() function to select data from multiple paths and combine the data to make the result complete.

Results: Depending on the database, this query returns results similar to this:

row 
  Name firewall.altoworks.local 
  IP Address 1.4.6.26, 1.2.3.66, 192.168.1.2, 192.168.1.1 
  Vendor Juniper Networks 
  last_seen 2008-06-18T17:22:27.0000000Z 
row 
  Name HP ProCurve 
  IP Address 192.168.1.4, 192.168.1.4 
  Vendor Hewlett-Packard 
  last_seen 2008-06-18T17:29:10.0000000Z 
row 
  Name pluto.altoworks.local 
  IP Address 192.168.1.120 
  Vendor Ubuntu 
  last_seen 2008-06-18T18:22:36.0000000Z 
row 
  Name neptune 
  IP Address 192.168.1.119, 127.0.0.1 
  Vendor Lanier 
  last_seen 2008-06-18T17:40:16.0000000Z 
  . . .

Example 4: Find everything

Query: Show me everything on my network, starting from root.

 SELECT join(/, ',') 
 FROM /

This query is impossibly broad and would be very expensive to run. The join function truncates at 500 characters to avoid problems with a query like this one.

Results: Depending on the database, this query returns results similar to this:

row
   join(, ,)44,67,66,2007/02/01 17:07:49,2008/04/04 
     14:57:19,MSSQLSvc/paris.altoworks.local,
     HOST/paris.altoworks.local,HOST/VOLANS,5.1 
     (2600),Service Pack 2,Windows XP Professional,79,
     192,54,211,0,62,177,69,133,162,71,5,16,214,175,75,
     VOLANS,2008/01/31 16:53:14,2008/01/31 16:53:14,
     2008/01/31 16:53:14,1601/01/08 -15:-10:-55,
     paris.altoworks.local,CN=VOLANS,OU=Altoworks,
     DC=altoworks,DC=local,VOLANS$,VOLANS$,1,5,0,0,0,0,
     0,5,21,0,0,0,22,242,224,162,94,55,174,52,101,104,1,
     167,237,4,0,0,top,person,organizationalPerson,user,
     computer . . .

Example 5: Find open ports on all devices

Query: What ports are open on each machine on the network?

SELECT nvl(system/dns_name, 
    first(interface/inet/ip_address)) as "Device", 
  join(evidence/tcp_port/param/port, ',') 
    as "Open TCP Ports", 
  join(evidence/udp_port/param/port, ',') 
    as "Open UDP Ports" 
from /network/device 
order by 1

Since the nvl() and coalesce() functions are interchangeable, they work the same way in the same query:

SELECT coalesce(system/dns_name, 
    first(interface/inet/ip_address)) as "Device", 
  join(evidence/tcp_port/param/port, ',') 
    as "Open TCP Ports", 
  join(evidence/udp_port/param/port, ',') 
    as "Open UDP Ports" 
from /network/device 
order by 1

Both queries display the name of every device on the network, alongside the TCP and UDP ports that are open on that device.

In the first line, either the nvl() or coalesce() function identifies the device by the DNS name or IP address. The coalesce() function uses the first non-NULL value, which displays the IP address for devices that don't have DNS names. The first() function is applied to the IP address so that the query only uses the first IP address of the device. The next 2 lines gather the lists of TCP and UDP ports that are open. The join() function takes the port trees and turns them into comma-separated lists. The data comes from the /network/device level of the tree, and put in order by the first column.

Results: Depending on the database, this query returns results similar to this:

row
   Device 10.10.10.102
   Open TCP Ports 9100,631,515,514,80,23,21
   Open UDP Ports 161
row
   Device 10.10.10.111
   Open TCP Ports 111,81,80,22
   Open UDP Ports null
row
   Device 10.10.10.13
   Open TCP Ports 280,80,23
   Open UDP Ports null 
  . . .

These results are easier to read when converted to a table:

Example 6: Find open ports on one device

Query: What ports are open on a specific device?

The query from Example 5 can be modified so you can look up the open ports on any single device. This is handy to post on a dashboard, and uses a search template so you can choose which device to display by selecting the IP address of that device.

SELECT coalesce(system/dns_name, 
    first(interface/inet/ip_address)) as "Device", 
  join(evidence/tcp_port/param/port, ',') 
    as "Open TCP Ports", 
  join(evidence/udp_port/param/port, ',') 
    as "Open UDP Ports" 
from /network/device 
where interface/inet/ip_address = 
'[[1param|All hosts|select first(interface/inet/ip_address),
  coalesce(system/dns_name || ' - ' || 
    first(interface/inet/ip_address),
  first(interface/inet/ip_address)) 
from /network/device 
where interface/inet/ip_address is not null
order by 2]]'
order by 1

To see other PQL functions, see Functions.

Syntax

Return value

Description

How do I find out more?