A new incident comes in. The CEO’s laptop shows possible Cobalt Strike activity. Your host investigation shows that the attacker likely gained privileged access to her host and the initial activity is from two days ago. You contain the host in your EDR agent. But now you must determine if the attacker moved laterally inside your network.

It would be easiest to search her host name in the SIEM, but there are many types of logs that don’t include a host name (certain Active Directory events, unauthenticated proxy logs, firewall logs, web application logs, etc). The next best option is to determine every IP address this device had in the last two days and when those IP addresses were assigned to the device.  

Unfortunately, the CEO is very busy and moves around constantly. She was initially in her office (connected via ethernet cable), and then moved to multiple conference rooms (Wi-Fi), and is currently on a business trip (multiple VPN sessions). It is important that we know when her device started and stopped specific IPs, because the IPs are likely in a dynamic assignment pool, which means they are automatically reassigned to new devices. For example: If she was only assigned a Wi-Fi IP address for 20 minutes, then those 20 minutes are the only times we care about that IP address.

This is a common investigation requirement, so let’s see if we can automate it.

Visualizing the desired output

Let’s first visualize the outcome we want.

We know the CEO moved around a lot during this time period. It would be great if we had a table showing us a list of IP addresses that the device had throughout the investigation timeframe.

Something like:

Before we spend time trying to do this ourselves, let’s investigate if Defender XDR and Microsoft Defender for Endpoint (MDE) already provide this information.

Data in the Defender XDR Portal

MDE provides information about the assigned IP addresses of a device in a few different places. One place is the Defender XDR portal, under the device’s object page.

On the device’s object page, click on See IP addresses info on the left panel (scroll down):

There is a lot of great info here. However, it is only the last seen information. In order to build a table showing assignments over time, we need historical information from the time the activity started to containment.

DeviceNetworkEvents

This table is part of Defender XDR’s Advanced Hunting capability, which allows analysts to query logs from different Defender products. This feature retains logs for 30 days. This table is populated by devices onboarded to MDE and contains network events from the hosts.

For example:

• outbound connections,
• inbound connections,
• listening sockets,
• some application-level protocol information.

We could try to create a distinct list of source IPs (for outbound connections) and destination IPs (for inbound connections) and generate our table from that. However, MDE does not log every single event that occurs on a host[1], so we could get false negatives. We’ll keep this as a backup.

AssignedIPAddresses()

In Advanced Hunting, there is a built-in function called AssignedIPAddresses. This function returns “the latest IP addresses that have been assigned to the device or the most recent IP addresses from a specified point in time”. The output looks like:

This does not meet our requirement, because it only provides the IP addresses at a specific point in time. We need results from multiple days.

It would be easier if we just had a list of IP addresses assigned to the device…

DeviceNetworkInfo

This is where the DeviceNetworkInfo table is helpful. This table contains information about network adapters, MAC addresses, connected networks, DNS servers, and most importantly, assigned IP Addresses.

Additionally, this is not an event-based table. That means devices report the data with some frequency (if the device is online of course). Therefore, we can use the table to create logs similar to DHCP, which show when a device started using an IP and when it stopped using an IP address.

First, we need to understand the data in the table. The IPAddresses column contains any IP addresses assigned to the adapter. Since adapters can have multiple IPs (IPv4 + IPv6), it is an array. Below, the device reports two IPs. (The IPv6 address is a link-local address, which we will exclude in our final queries.)

The first step is to get every IP address the device reported during our time of investigation. We need each IP on a separate row, in order to do analysis.

We can use the amazing mv-expand operator to do this. mv-expand takes an array column from a row and creates new rows for each entry in the array. It copies the column values from the source row into the new row(s).

DeviceNetworkInfo
| where Timestamp > ago(3d)
| where DeviceName == “hoth.galacticempire.local” //change to your device 
| mv-expand todynamic(IPAddresses)
| extend IP = todynamic(IPAddresses).IPAddress

Note: We also created a new column called IP for the IP address in each row.

Next is the hardest part.

We need to determine the sessions (or windows) for when an IP was reported by a device. The first idea is to create a summarization using max(Timestamp)/min(Timestamp) functions and grouped by IP address. However, this will not work, because a device could rotate between the same IP address multiple times. For example, a device could be assigned:

• Monday – 192.168.1.1
• Tuesday – 10.55.66.44
• Wednesday – 192.168.1.1

If we used min and max functions grouped on the IP address, like so:

| summarize min(Timestamp), max(Timestamp) by IPAddress

The result for 192.168.1.1 would be:

• Min: Monday
• Max: Wednesday

This implies the device was assigned the IP address on Tuesday, which is not correct. Therefore we need a way to create sessions for each assignment period.

Defining a session

A session starts when an adapter reports a new IP address for an IP version.

In the Figure 7 below, we see that the IPv4 address for Adapter1 was 192.168.1.2 from 9am-11am. This is our first session.

Then it changes to 10.55.66.77 and the second session starts. Finally, it changes back to the first IP, 192.168.1.2, and a third session starts.

Note: The adapter’s IPv6 address never changes.

In order to uniquely identify a session when using aggregation functions (like min/max), the session identifier must be unique across all session. This uniqueness allows us to group by the session identifier to determine when it started and ended. For example, the pseudo logic might be:

summarize StartTime=min(Timestamp), EndTime=max(Timestamp) by SessionId, Adapter, Ipver, IP

This outputs something like:

We have our pseudo logic and now need to make it work in KQL.

Implementing sessions in KQL

Our original query provides the IP assignments:

DeviceNetworkInfo
| where DeviceName == “hoth.galacticempire.local” //change to your device
| where NetworkAdapterStatus == "Up"
| extend IPAddresses = todynamic(IPAddresses)
| project Timestamp, DeviceId, DeviceName, IPAddresses, NetworkAdapterName //only use the required columns
| mv-expand IPAddresses
| extend IP = tostring(IPAddresses.IPAddress)
| where IP !startswith “fe80” and IP !startswith “169.254” //link local IPAddresses
| project-away IPAddresses
| extend IPver = iif(IP contains ":","IPv6","IPv4") //classify the IP version

To create the sessions we need to perform multiple steps.

First, using the partition operator, we can create temporary tables to run a sub-query on. This lets us analyse the IP addresses of each combination of: [Device + Adapter + IP version] individually. You could visualize this as:

The partition operator requires a unique partition key. Since the sessions are per [Device + Adapter + IP version], we concatenate them into one string and create a unique hash value.

| extend Hash_Device_Adapter_IPver = hash(strcat(DeviceId,NetworkAdapterName,IPver))
| partition by Hash_Device_Adapter_IPver (<sub query here>)

Now, we need to add the session identifiers. A session starts (and ends) when a new IP address is seen on a device. Using the prev() function, we can look at the previous row in an ordered series and compare it to the current row.

| partition by Hash_Device_Adapter_IPver
    (
        sort by DeviceId, NetworkAdapterName, Timestamp asc
        | extend SessionId = iif( prev(IP) != IP,
                                    tostring(hash(strcat(DeviceId,Timestamp,IP))),
                                    ""
                                )
    )

In the partition query, we sort the rows by Timestamp to put them in chronological order. Then, we create a column called SessionId, which contains the session identifier.

If the previous row’s IP address is not equal to the current row’s IP, then the adapter’s IP address  is new and therefore we create a new SessionId based on the [ DeviceId + Timestamp (aka session start) + IP address ]. The inclusion of the session start Timestamp in the hash calculation ensures that if the same IP address is re-assigned to the adapter, a unique SessionId is created. The output of the above query is:

If we tried to generate the min and max Timestamp for each SessionId, they would be the same Timestamp! That’s because the SessionId only exists on the row where the session started.

Therefore, we need to fill in the rest of the rows with their matching SessionId.

The scan operator can help. This operator is quite complicated, but essentially scan allows you to keep track of state across different steps in a query. We can use it to fill in our SessionId values.

| partition by Hash_Device_Adapter_IPver
    (
        sort by DeviceId, NetworkAdapterName, Timestamp asc
        | extend SessionId = iif( prev(IP) != IP,
                                    tostring(hash(strcat(DeviceId,Timestamp,IP))),
                                    ""
                                )
        | scan declare (SessionIdFilled: string="") with
        (
            step s1: true => SessionIdFilled = iif(isempty(SessionId), s1.SessionIdFilled, SessionId);
        )
        | summarize FirstReported=min(Timestamp), LastReported=max(Timestamp) by DeviceId, DeviceName, NetworkAdapterName, SessionIdFilled, IP
    )

First, we declare a new column for scan to use called SessionIdFilled. We have 1 step in our scan, which populates the SessionIdFilled column, based on a simple if function.

• If the row’s SessionId is empty:
  • Fill it with the value stored in the state’s step

• Else: (meaning it is the start of a session)
  • Fill it with the value of the row’s SessionId column

The output:

Creating the final table

Our final query:

DeviceNetworkInfo
| where Timestamp > ago(30d)
| where DeviceName == "hoth.galacticempire.local" //change to your device
| where NetworkAdapterStatus == "Up"
| extend IPAddresses = todynamic(IPAddresses)
| project Timestamp, DeviceName, DeviceId, IPAddresses, NetworkAdapterName //only use the required columns to save compute
| mv-expand IPAddresses
| extend IP = tostring(IPAddresses.IPAddress)
| project-away IPAddresses
| where IP !startswith "fe80" and IP !startswith "169.254" //link local IPAddresses
| extend IPver = iif(IP contains ":","IPv6","IPv4")
| extend Hash_Device_Adapter_IPver = hash(strcat(DeviceId ,NetworkAdapterName,IPver))
| partition by Hash_Device_Adapter_IPver
    (
        sort by DeviceId, NetworkAdapterName, Timestamp asc
        | extend SessionId = iif( prev(IP) != IP,
                                    tostring(hash(strcat(DeviceId,Timestamp,IP))),
                                    ""
                                )
        | scan declare (SessionIdFilled: string="") with
        (
            step s1: true => SessionIdFilled = iif(isempty(SessionId), s1.SessionIdFilled, SessionId);
        )
        | summarize FirstReported=min(Timestamp), LastReported=max(Timestamp) by DeviceId, DeviceName, NetworkAdapterName, SessionIdFilled, IP
    )

This outputs:

And that’s it! We have a table showing IP assignment session for the device and when it started and stopped.

In summary, we used the DeviceNetworkInfo table in Advanced Hunting to identify the IP addresses assigned to a device. Then we created SessionIds in order to track sessions over time and added the SessionIds to each row. Finally, we created a table that summarizes when these sessions started and stopped along with their associated IP addresses.

Analysts no longer need to manually track IP address assignment periods or reverse engineer them from logs. This table saves analysts large amounts of time when tracking IP addresses for an investigation.

We could also:

• Use this table in a second KQL query (maybe against other logs stored in Sentinel)
• Add it as additional context for an Incident ticket
• Reference it ad-hoc during an investigation

Let me know your ideas in the comments!

About the Author

Ethan Bowen

Ethan is a consultant on the Security Operations Engineering team at NVISO. He focuses on helping clients implement and use Microsoft security tools.

[1] https://medium.com/falconforce/microsoft-defender-for-endpoint-internals-0x03-mde-telemetry-unreliability-and-log-augmentation-ec6e7e5f406f