Check out the
headlines
headlines
PQL first() function
first() — Selects the first leaf in a tree, such as
the first interface from /network/device.
Syntax
The first() function returns a
string and takes one parameter:
string first(tree input)
Return value
The first() function returns a string.
Description
The first() function selects the
first leaf in a tree, such as the first interface in a list of interfaces from
/network/device. The first() function must include 1
parameter.
This function is useful in limited circumstances. For example, if you want to display the results in a field that has limited space, such as an application with a small UI, you can run the query and show only its first result.
Note: Although the first leaf of a given tree will probably not change much, there is no guarantee that it will always be the same leaf. Over time, the order of leaves can change as new data is added and existing data is reindexed.
Examples
- Example 1: Find the first IP address
- Example 2: Find the first MAC address
- Example 3: Find open ports on the first device
- Example 4: Find open ports on all devices
- Example 5: Find open ports on one device
Example 1: Find the first IP address
Query: What is the first leaf of the first interface of each device on the network?
SELECT first(interface) FROM /network/device
Results: Depending on the database, this query returns results similar to this:
row first(interface) vlan1 row first(interface) 5 row first(interface) 00:11:43:55:16:28 row first(interface) naf0 row first(interface) 00:11:11:45:6A:20 row first(interface) 00:13:20:4A:A3:78 . . .
Example 2: Find the first MAC address
Query: What is the first MAC address of each interface of each device on the network?
SELECT first(mac_address) FROM /network/device/interface
Results: Depending on the database, this query returns results similar to this:
row first(mac_address) 00:10:DB:4C:BA:68 row first(mac_address) 00:10:DB:49:98:00 row first(mac_address) 00:10:DB:6F:34:E0 row first(mac_address) 00:10:DB:3A:F7:70 . . .
Example 3: Find open ports on the first device
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 a device on the network.
The query uses the first() function to select the first device
from the list of network devices, and display all open TCP and UDP ports on
that device.
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 4: 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 5: Find open ports on one device
Query: What ports are open on a specific device?
The query from Example 4 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.
