CCNA Data Analysis With XQL Questions

This expression uses the contains operator to check for each substring, combines them with 'or' inside a negation, and correctly excludes any command line that contains either 'Invoke-WebRequest' or 'DownloadString'. The 'not' applies to the entire parenthesized expression, so an event is kept only if it does not contain either string. This is the precise way to apply both exclusions in XQL.

Normalising disparate log sources into a standardised schema directly satisfies the requirement for consistent querying across heterogeneous telemetry. The Cortex Data Model maps vendor-specific fields to common attributes, so analysts write one query that works regardless of source, rather than translating field names per integration.

This query uses the filter command to select authentication events within the last 24 hours using the timestamp comparison with current_time() minus 24 hours. The fields command then projects only the username, src_ip, and timestamp columns. This is the correct syntax for time-based filtering and field selection in XQL.

The lookup stage is the correct XQL mechanism for enriching events with data from a lookup table. It matches the destination_ip field from the events against the ip_address field in the ip_geolocation lookup table and returns the country field. This is the standard, efficient way to add enrichment fields to XQL query results without the overhead of a full join operation.

The alter stage is used to create new fields based on expressions, and the substr() function extracts a portion of a string. Using alter with substr(actor_process_command_line, 0, 100) creates the short_cmd field containing the first 100 characters, making the output more readable for triage. This is the correct approach for deriving a truncated field without modifying the original data.

The dedup command collapses rows sharing the grouping key, emitting only the key fields plus any explicitly retained ones. Unlisted fields are discarded because no single value can represent the merged rows, which explains the missing fields the analyst observes.

After computing the distinct endpoint count per domain with comp, a filter stage is needed to apply the threshold condition. Filtering post-aggregation on the computed count field allows the hunter to isolate domains queried by more than 50 unique endpoints. This is the standard XQL pattern for threshold-based detection after aggregation.

Aggregation groups many events into one output row per distinct group value, so the result set size is bounded by the number of unique group combinations rather than raw event volume. This is exactly how an analyst preserves counts for reporting while dramatically reducing what is returned to the console.

The rename command changes a field's name in the output dataset, letting analysts relabel columns without altering their values. It is the dedicated XQL operator for field renaming, satisfying the requirement to present results under a new field name.

The `stats` command performs aggregation, such as counting events. In this scenario, `stats count() as login_attempts by source_ip, _time` (after binning) would produce the number of failed logins per source IP per 10-minute window. This is essential to compute the counts needed for the threshold filter.

This sequence correctly buckets events into 5-minute windows using `bin`, then uses `stats` with `dc(dst_ip)` to count distinct destination IPs per source IP per time bucket, and finally filters for counts greater than 20. It directly addresses the requirement to detect a single source IP connecting to many distinct destinations within a short window.

The `bin` command groups timestamps into fixed intervals such as 5 minutes, creating a time bucket field. Combining it with `stats count()` grouped by source IP and the bucket yields counts per IP per 5-minute window, which is exactly the time-bucketed aggregation needed for brute-force detection.

Syntax highlighting is a genuine authoring aid in the XQL search interface, colour-coding commands, functions and operators so analysts can read and debug queries at a glance. It satisfies the stem's requirement for features assisting during query authoring, rather than execution or detection.

The `in` operator is designed to test whether a field value matches any element in a list, and it is natively supported in XQL within a `filter` stage. This expression efficiently narrows results to only those events whose `action_file_sha256` equals one of the three supplied hashes. It is the idiomatic and most concise way to perform a set-membership check on a single field.

The ~= operator performs a case-insensitive regular expression match in XQL, and the pattern targets long base64-style runs with optional padding. This correctly flags encoded command lines regardless of letter casing, which is essential because base64 payloads mix upper and lower case characters.

The `stats` command with `count()` grouped by the severity field aggregates events and returns one row per severity value with its occurrence count. This directly produces the distribution the analyst wants, making it the correct and efficient choice for summarizing categorical data.

The IN operator compares a field against a supplied list, returning true when the value matches any member. This directly satisfies the requirement to test membership across multiple values, replacing long chains of OR conditions within a single XQL filter statement.

Naming specific datasets restricts the scan to relevant data rather than searching every dataset matched by a wildcard group. This reduces the volume of data scanned, directly addressing the slow-running query constraint by cutting processing time.

The 'is not null' operator is the correct way to test for the presence of a value in a field. It returns true only when the field has a non-null value, effectively filtering out rows where 'event_id' is missing. This is the standard and reliable method in XQL for null checks.

Normalizing both join keys to lowercase with the `lowercase` function ensures that hostnames differing only in case are treated as equal. This directly resolves the missing matches caused by case sensitivity, allowing the join to pair records that logically refer to the same host.

This query correctly filters for internal source IPs using the ipv4_private() function, then aggregates the sum of outbound bytes grouped by destination IP. The subsequent filter on the aggregated total_bytes ensures only destinations that received more than 100 MB in total are returned. It does not prematurely filter individual events, so it captures all traffic and accurately computes the total per destination, matching the requirement.

Filtering for failed login attempts is the first essential step because the brute-force detection specifically targets failures. Without this filter, successful logins would be included, diluting the count and potentially missing the attack pattern. The filter stage reduces the dataset to only failure events, which is necessary before any time-based grouping or counting. This is a fundamental prerequisite for the analysis.

The dataset command defines which source table the query reads, forming the mandatory first stage of every XQL query. Without it, the query has no data to process, so it satisfies the stem's requirement for a fundamental structural component alongside the stage that follows.

This is correct because bin groups raw events into one-hour buckets, and comp aggregates with count_distinct to count unique destination IPs per source host per bucket. The trailing filter then keeps only buckets exceeding 15 distinct destinations, yielding one row per source host per hour as required.

The `alter` stage creates or replaces fields in the pipeline, and nesting conditional expressions lets the analyst assign tiered values in order. The first matching condition wins, so checking the smallest threshold first then the next threshold correctly buckets low, medium, and high while leaving all original fields intact.

The `bin` function rounds or truncates a timestamp into fixed-width buckets, so applying it to the time field within the grouping produces one row per source-destination pair per hour. This is the standard way to build time-series aggregations in XQL and directly supports beaconing analysis.

The dataset command names the source table or dataset that seeds the query pipeline, so every subsequent stage filters and transforms that data. Without it, XQL has no starting data source, satisfying the requirement to define where the query begins.

This query first counts events per process name and event type. The second 'stats' command sums the counts conditionally: 'exec_count' for process events and 'net_count' for network events, grouped by process name. It then filters for processes with at least 5 executions. Finally, it calculates the ratio of network connections to executions. This precisely meets the requirement.