List All EC2 Hosts With FirstSeen Data Within 14 Days

List all the EC2 hosts with FirstSeen data within 14 days using the groupBy() function with selectLast()

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3["Expression"] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result
logscale
#repo=sensor_metadata #data_source_name=aidmaster cloud.provider = "AWS_EC2_V2"
| groupBy([aid], function=(selectLast([event_platform, aid, ComputerName, AgentVersion, FirstSeen])), limit=max)
| FirstSeen := formatTime("%FT%T%z", field=FirstSeen)
| TimeDelta := now() - duration("14d")

Introduction

In this example, the groupBy() function is used with selectLast() to retrieve the latest information about AWS EC2 instances running CrowdStrike sensors, showing their platform, hostname, agent version, and when they were first seen, with a 14-day reference point for age comparison.

Example incoming data (raw data in sensor_metadata) might look like this:

@timestampaidcloud.providerevent_platformComputerNameAgentVersionFirstSeen
2025-05-20T10:00:00Z1234abcdAWS_EC2_V2Windowsec2-web-016.45.156782025-01-15T08:30:00Z
2025-05-21T11:00:00Z1234abcdAWS_EC2_V2Windowsec2-web-016.45.156792025-01-15T08:30:00Z
2025-05-22T12:00:00Z5678efghAWS_EC2_V2Linuxec2-app-026.45.156782025-02-01T14:45:00Z
2025-05-23T13:00:00Z5678efghAWS_EC2_V2Linuxec2-app-026.45.156792025-02-01T14:45:00Z
2025-05-24T14:00:00Z90123ijkAWS_EC2_V2Windowsec2-db-036.45.156782025-03-10T09:15:00Z
2025-05-25T15:00:00Z90123ijkAWS_EC2_V2Windowsec2-db-036.45.156792025-03-10T09:15:00Z

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3["Expression"] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    #repo=sensor_metadata #data_source_name=aidmaster cloud.provider = "AWS_EC2_V2"

    Searches in the sensor_metadata repository, and filters for #data_source_name fields containing the value aidmaster, looking for cloud.provider of the type AWS_EC2_V2 only.

  3. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3["Expression"] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | groupBy([aid], function=(selectLast([event_platform, aid, ComputerName, AgentVersion, FirstSeen])), limit=max)

    Groups results by the field aid (Agent ID). Then, for each unique group, selects the most recent values for the fields: event_platform, aid, ComputerName, AgentVersion, FirstSeen.

    Using the selectLast() within the groupBy() is what actually selects the most recent record for each group.

  4. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3["Expression"] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 3 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | FirstSeen := formatTime("%FT%T%z", field=FirstSeen)

    Formats the timestamp in the FirstSeen field into ISO 8601 format. The result is stored back in the FirstSeen field.

  5. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[/Filter/] 2{{Aggregate}} 3["Expression"] 4{{Aggregate}} result{{Result Set}} repo --> 1 1 --> 2 2 --> 3 3 --> 4 4 --> result style 4 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    | TimeDelta := now() - duration("14d")

    Calculates timestamp from 14 days ago, and returns the results into a new field named TimeDelta. The calculation is done by subtracting a 14-day duration from the current time using duration().

    This new TimeDelta field that represents a timestamp from 14 days ago, can be used for filtering or comparing against the FirstSeen timestamps.

  6. Event Result set.

Summary and Results

The query is used to retrieve the latest information about AWS EC2 instances running CrowdStrike sensors, showing their platform, hostname, agent version, and when they were first seen, with a 14-day reference point for age comparison.

The query is useful, for example, for auditing EC2 instance coverage, identifying newly added EC2 instances within the last two weeks, monitoring sensor versions or identifying aging or outdated installations.

Sample output from the incoming example data:

aidevent_platformComputerNameAgentVersionFirstSeenTimeDelta
1234abcdWindowsec2-web-016.45.156792025-01-15T08:30:00+00002025-05-12T13:06:56+0000
5678efghLinuxec2-app-026.45.156792025-02-01T14:45:00+00002025-05-12T13:06:56+0000
90123ijkWindowsec2-db-036.45.156792025-03-10T09:15:00+00002025-05-12T13:06:56+0000

Each aid appears only once with its most recent values. Note that TimeDelta value is based on the current date provided (Mon, 26 May 2025 13:06:56 GMT).