You can use the NQE group function to collapse rows that have multiple matching columns.

In the following example, we make a list of all devices in the network (and store in a list called data), and group all the devices in data together that have matching values for Vendor, Model, OS, and OS Version. The matching values are grouped by attributes.

Note that list stored in data just has device names. We don’t actually list all the device names within the select statement. The device is the identifier for each unique element that is hidden when you use the group function. We access the list of devices in each group (within the list data) with a foreach loop in order to count the unique items and provide a total in the last column.

/**
 * @intent List devices grouped by matching Vendor, Model, OS, and Version and the total number of devices in each group.
 * @description List devices grouped by matching Vendor, Model, OS, and Version and the total number of devices in each group.
 */

foreach device in network.devices
let platform = device.platform
group { device: device.name } as data
  by { vendor: platform.vendor,
       model: platform.model,
       os: platform.os,
       version: platform.osVersion
    }
    as attributes
select {
  vendor: attributes.vendor,
  model: attributes.model,
  os: attributes.os,
  version: attributes.version,
  total: length(foreach x in data
                select x.device)
}

The output of the query will be a table with columns: vendor, model, os, version, and total.

The total in the last column will the the total number of devices which share the same vendor, model, os, and version.

For example, the output for the query may be:

vendor        model                              os                      version           total

CISCO         WS-C3850-48F-E         IOS_XE             16.06.05        55

ARISTA       CCS-720XP-48ZC2      ARISTA_EOS    4.32.5M         21

and so on.