I created this guide to help explain the group by statement in Network Query Engine (NQE), using a real example where I needed to extract CDP neighbor information from Cisco devices. If you're trying to clean up duplicate entries or shift your query’s focus from devices to neighbors, this walkthrough should make group by much more approachable.
How I think about group by
I think about group by as a way to shift the focus of data from one primary association to another. For me, the key is figuring out what information is unique per device and what stays consistent across devices or throughout the network. Using group by, I can take repeated entries (like neighbors showing up on multiple devices) and consolidate them so each shared element appears once, with its associated device-specific details grouped alongside it.
One crucial nuance I’ve learned: after the group by statement, only the variables I explicitly include in the grouping are accessible. So I always make sure to gather everything I’ll need later before performing the grouping.
Here’s the mental model that helped me wrap my head around how group by works in NQE:
- group different as diffVariable by same as sameVariable
- diffVariable will become a list of the associated data type (string, record, device, etc.)
- foreach item in diffVariable …
- ... select (item, item.name, item.interfaces)
- sameVariable will be a single item of associated data type (string, record, device, etc.)
- ... select (sameVariable, sameVariable.name, sameVariable.interfaces)
My CDP Neighbor Example
Here’s the NQE script I put together to parse CDP neighbor information and apply group by. The pattern matches the neighbor name, IP, platform, and interface:
pattern =
"""
Device ID: {device:string}
Entry address(es):
IP address: {ip:ipv4Address}
Platform: {platform:(string*)}
Interface: {interface:string}
""";
foreach device in network.devices
foreach command in device.outputs.commands
where command.commandType == CommandType.CDP
let response = replaceRegexMatches(command.response, re`Entry`, " Entry")
let response = replaceRegexMatches(response, re`Platform:`, " Platform:")
let response = replaceRegexMatches(response, re`Capabilities:`, "\n Capabilities:")
let response = replaceRegexMatches(response, re`Interface:`, " Interface:")
let response = parseConfigBlocks(device.platform.os, response)
foreach neighbor in blockMatches(response, pattern)
let name = neighbor.data.device
let ip = neighbor.data.ip
let platform = replace(join(" ", neighbor.data.platform), ",", "")
let interface = replace(neighbor.data.interface, ",", "")
group { name: device.name, location: device.locationName, interface } as devices
by { name, ip, platform } as neighbor
select {
"Local Device Name": distinct(foreach device in devices select device.name),
Location: distinct(foreach device in devices select device.location),
"CDP Neighbor Name": neighbor.name,
"CDP Neighbor IP": neighbor.ip,
"CDP Neighbor platform": neighbor.platform,
"CDP Neighbor Interface": (foreach device in devices select device.interface)
}
How I Broke It Down
This script does a few key things:
- Uses regex to reformat CDP output into a nesting pattern, so NQE can recognize each neighbor with block matching.
- Extracts neighbor details (name, IP, platform).
- Applies group by to consolidate neighbor entries across devices.
The result is a much cleaner view. Instead of seeing the same neighbor five times, I now see it once, along with all the interfaces and devices it’s linked to.
Other Ways I’ve Used It
After figuring this out for CDP neighbors, I started using group by in other places:
- To validate ACL structure (ensuring deny any is the last line)
- To reduce output size when listing unused firewall rules across many devices
- To combine standard and extended ACL entries into a unified report
One cool example I built even grouped ACL lines from multiple formats (with or without line numbers) into a single structure.
Tips for Trying It Yourself
If you're getting noisy results or struggling to make sense of big query outputs, I recommend starting with these questions:
- What’s repeated?
- What’s unique?
- What should be the focus of the output?
Once you’ve got that figured out, group by can help you make your output cleaner, simpler, and easier to analyze.
Got a Tip of Your Own?
If you've created a smart use of group by, I’d like to see it! Share your example in the
NQE Discussions space so others can learn from it too.