Query Details
Device IP History
Query
let QueryDevice = "devicename";
DeviceNetworkEvents
| where DeviceName startswith QueryDevice
| where LocalIP !in ("", "::", "::1", "127.0.0.1", "0.0.0.0")
| where LocalIP !startswith "::ffff:"
| where LocalIP != RemoteIP
| where ActionType !in ("ListeningConnectionCreated", "ConnectionFailed") // only successful peer connections are evidence
| where ActionType !endswith "Inspected" // bad data -- Zeek flips RemoteIP and LocalIP sometimes
| extend AccurateDirection = tostring(parse_json(AdditionalFields).direction)
| extend EstimatedDirection = iff(LocalPort > RemotePort, "Out", "In") // rarely wrong; needed when sensor isn't logging directionality
| extend Direction = iff(AccurateDirection != "", AccurateDirection, EstimatedDirection)
| summarize
Start=min(Timestamp),
End=max(Timestamp),
ConnectionCount=count(),
InboundConnections=countif(Direction=="In"),
OutboundConnections=countif(Direction!="In"),
Peers=make_set(RemoteIP)
by LocalIP
| where OutboundConnections > 0 // wrong directionality captured by EstimatedDirection
| sort by Start descExplanation
This query is designed to analyze network events related to a specific device, identified by the name "devicename". Here's a simplified breakdown of what the query does:
-
Filter Events by Device Name: It starts by selecting network events from the
DeviceNetworkEventstable where the device name begins with "devicename". -
Filter Out Unwanted IPs: It excludes events where the local IP address is empty, unspecified, or a loopback address (like "127.0.0.1").
-
Exclude Certain Events: It filters out events where the local IP is the same as the remote IP, and it excludes events with specific action types that indicate unsuccessful or irrelevant connections.
-
Determine Connection Direction: It attempts to determine the direction of the connection (inbound or outbound) using additional fields and port numbers.
-
Summarize Connection Data: It summarizes the data by local IP, calculating the start and end times of the connections, the total number of connections, and the number of inbound and outbound connections. It also compiles a list of unique remote IPs (peers) connected to the local IP.
-
Filter for Outbound Connections: It only includes records where there is at least one outbound connection.
-
Sort Results: Finally, it sorts the results by the start time of the connections in descending order, showing the most recent connections first.
Overall, this query is used to identify and analyze successful network connections for a specific device, focusing on connections that are not local or loopback and ensuring that there is at least one outbound connection.
Details
C.J. May
Released: February 10, 2026
Tables
Keywords
Operators