PQL nvl() and coalesce() functions

nvl(), coalesce() — Return the first argument in a list that is not null.

Syntax

Both nvl() and coalesce() functions return a variant, and take 1 parameter.

nvl():

variant nvl(... input)
coalesce():

variant coalesce(... input)

Return value

Both nvl() and coalesce() functions return an output that is the same type as the input.

Description

Both nvl() and coalesce() return the first argument from a list that is not NULL. The nvl() function is provided for the benefit of Oracle users, and is equivalent to coalesce(). Both functions take any number of parameters, and return the same type.

Examples

You can try the following examples against your own data in the GoToAssist search field, or against fictitious data in the Query Sandbox:

• Example 1: Limit results to a subnet
• Example 2: Find devices and device data
• Example 3: Find open ports on all devices
• Example 4: Find open ports on one device

Example 1: Limit results to a subnet

Query: What devices are in the given subnet?

SELECT nvl(
  system/name, 
  system/dns_name
) 
as "Name", 
  nvl(
    system/model, 
    system/class, 
    wmi/win32_computersystem/domainrole
  ) 
  as "Type",
    interface[
      inet/ip_address like '10.10.10.'
    ]/inet/ip_address as "IP Address",
      last_seen 
FROM /network/device 
WHERE system is not null 
AND interface/inet/ip_address like '10.10.10.'

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

SELECT coalesce(
  system/name, 
  system/dns_name
) 
as "Name", 
  coalesce(
    system/model, 
    system/class, 
    wmi/win32_computersystem/domainrole
  ) 
  as "Type",
    interface[
      inet/ip_address like '10.10.10.'
    ]/inet/ip_address as "IP Address",
      last_seen 
FROM /network/device 
WHERE system is not null 
AND interface/inet/ip_address like '10.10.10.'

Both queries limit the results to display all devices in a specific subnet, as well as some useful information about each device, such as type, IP address, and when it was last seen.

Note: To try this query on your own data, replace '10.10.10.' with the IP address scheme that you use.

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

row
  Name SW2000
  Type 5
  IP Address 10.10.10.10
  last_seen 2008-01-24T16:13:03.0000000Z
row
  Name laser
  Type 1 Member Workstation
  IP Address 10.10.10.20
  last_seen 2008-01-24T16:19:55.0000000Z
row
  Name fw-prem
  Type Ethernet Switch
  IP Address 10.10.10.30
  last_seen 2008-01-24T16:05:40.0000000Z 
  . . .

Example 2: List devices and device data

Query: What devices are on the network?

SELECT nvl(
  system/name, 
  system/dns_name
) as "Name",
  nvl(
    system/model, 
    system/class, 
    wmi/win32_computersystem/domainrole
  ) as "Type",
      interface/inet/ip_address as "IP Address",
      last_seen 
FROM /network/device 
WHERE system is not null

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

SELECT coalesce(
  system/name, 
  system/dns_name
) as "Name",
  coalesce(
    system/model, 
    system/class, 
    wmi/win32_computersystem/domainrole
  ) as "Type",
      interface/inet/ip_address as "IP Address",
      last_seen 
FROM /network/device 
WHERE system is not null

Both queries display the name of every device on the network, as well as data about the type of device, IP address, and when it was last seen.

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

row
  Name rome
  Type 1
  IP Address 10.10.10.10
  last_seen 2008-01-24T16:13:03.0000000Z
row
  Name paris
  Type 5 Primary Domain Controller
  IP Address 10.10.10.20
  last_seen 2008-01-24T16:10:43.0000000Z
row
  Name john-smith
  Type 1 Member Workstation
  IP Address 10.10.10.30
  last_seen 2008-01-24T16:09:17.0000000Z 
  . . .  

Example 3: 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 4: Find open ports on one device

Query: What ports are open on a specific device?

The query from Example 3 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.

Related topics

• Functions
• Operators
• Expressions
• Read about PQL

| Views: 1389 | Last Updated: Thu, May 09 2013 2:28 PM

Is this article helpful?