Examine local NDR data in the Investigation Console

The NDR Investigation Console provides local data from the assigned NDR sensors—not only the data transferred to the Sophos Data Lake. This runbook takes you from a hypothesis in Dashboard > Overview to a narrowly scoped ClickHouse query under Query. Every query described here is read-only.

The Investigation Console is not interchangeable with these query paths:

A result from the Investigation Console is not yet a confirmed incident. Hand off suspicious findings through the applicable SOC, XDR, or MDR process. Response actions are not part of this runbook.

Access, roles and investigation mandate

Use a personal account with the permissions required for the investigation. The local Investigation Console account is separate from the roles in Sophos Fusion. If Query, a required schema, or a button is missing, ask the responsible administrator to check the local account and selected console. If the entry point from Sophos Fusion or a later cloud pivot is missing, check the licence, Fusion role, and product scope of any Custom Role separately. Do not broaden a role speculatively.

Establish the following before the investigation:

• a concrete hypothesis, for example communication with an approved target IP over unexpected protocols;
• affected sensor or network area and expected source or target systems;
• the start, end and time zone of the event;
• a ticket or Case for notes and the person who will take ownership of a suspicious finding;
• permitted handling of IP addresses, hostnames, and exported results.

The console must be accessible and receive data from at least one NDR integration appliance. A successful login alone proves neither that sensor data is current nor that mirror coverage is complete.

1. Limit time period and affected sensors in the dashboard

After logging in, the console opens Dashboard > Overview. The page shows Total Indicators, Network Traffic, Total Indicators By Severity, Total Indicators By Type, Geolocation Map and Recent flow detections. Back leads back to Sophos Fusion. This Appliance shows system details and is not necessary for this investigation.

1. Under Filters, open Time Range first. The standard is Last 1 hour.
2. For a known incident, select Absolute time range and enter start and end with enough context before and after the event. For a first overview, a quick range such as Last 7 days can suffice. Confirm with Apply time range.
3. Note the data boundary: the console provides only the last 30 days. A longer time range does not provide additional local data. A missing older match is therefore not negative confirmation.
4. Under Filters, select an available database column, the appropriate operator, and a value. Sophos gives MasterProtocol, Equals, and HTTP as an example. Numeric columns offer operators such as =, <, or >=.
5. Add only criteria that belong to the hypothesis. After each configured criterion, click Add first to include it in the filter, and then Apply. Check that charts and table now reflect the selected time period and filters.
6. Use Save As only for a stable, understandably named filter. The save icon overwrites an existing filter. With Clear you remove the current filter settings.

Record the filters and time period and time zone. Then compare at least two independent presentations:

• Total Indicators shows IoCs of types DGA, IDS, EPA, and SRA. Clicking a type toggles it in the bar chart. Hovering over a bar shows the time and value.
• Network Traffic shows data rate in Mbit/s, packets per second and flows per second. If you hover the cursor over the graph, you will see the volume of data sent and received in gigabytes.
• Total Indicators By Severity is grouped by Critical, High, Medium, Low and Info. Total Indicators By Type shows the same IoC types as a donut diagram.
• Geolocation Map is based on IP groupings. A region provides a starting point for investigation, but proves neither a host’s actual location nor that it is malicious.
• Recent flow detections shows suspicious network flows. Check the existing flow details and confirm field names and meanings against the current schema instead of just adopting a chart tip.

If the dashboard and table do not match for the same period, reduce the time window and check the active filters. Only then start a free query.

2. Start with a prepared query

Open Query and stay in the Library tab first. For this runbook, use only individual, read-only SELECT queries. For customized or own queries, knowledge of ClickHouse SQL is required. Commands that change data, tables, schemas, users, permissions or server settings are excluded.

Start with a pre-configured query that fits your hypothesis:

1. In Library, open the appropriate category and open the query.
2. Read the full text. Check tables, fields, time condition, grouping, sorting, and an existing result limit based on your hypothesis.
3. Switch to Schema. Unfold the schema name and confirm field names and field types for each table used. Do not take field names from Data Lake or Appliance Manager examples.
4. Based on the current schema, limit the SELECT to the required time period and, if possible, a single indicator, host, source or destination IP or protocol. Change a preconfigured time condition only if field and ClickHouse syntax are unique.
5. Click once on Run. The results appear below the query. Repeated clicking does not speed up the run and makes it difficult to assign to History.
6. Extend the scope only when the first execution is successful and the result is plausible and manageable.

Use variables and examples safely

If a prepared query contains a variable such as @DestIp, an input field appears on the left. Protocols For Destination IP is a documented example. Use this field and do not simultaneously change the variable replacement, table, and filter logic.

For a syntax or workflow check, use only an address approved for that purpose. 192.0.2.10 comes from a network reserved for documentation and is used here only as a formatting example. It will not necessarily return results and is not a production indicator. Real IP addresses, domains, and hostnames must come from the authorised ticket, not arbitrary examples.

When saving, the console can transfer the variable value to the query text. @DestIp then becomes, for example, the used address. Check the text before the next run. Save a customized variant over Save As under a name that describes purpose and scope, and do not overwrite the preconfigured initial query. Because stored queries can contain confidential indicators, local data classification applies.

To create your own category, click on the Plus icon at the top right of Library, enter the name and description and confirm with Create. For a new query, enter the tested SELECT text on the right, test it with Run and select Save As. Select the category, enter a name and confirm with Create. Save only reusable, tested queries.

The console shares resources with local data storage and display. Therefore, filter early by time and a selective field. Limit results using a method already used in Library and tested for ClickHouse; do not remove any existing limit on the first test. Before JOIN, sub-queries, broad groupings or sorts, check the current Schema and test with a small time window. Do not copy a Data Lake SQL query to the console. Read queries from tickets, chats or public examples before they are executed and compare them with Schema.

3. Validate results

A successfully executed query is not automatically correct in content. Check the result in this order:

1. Investigation scope: Do the time window, time zone, affected sensors, and filters match the assignment exactly? Is the event within the 30 days available locally?
2. Schema: Do field types and meaning match Schema? In particular, check whether IP addresses, time values and numbers are interpreted correctly.
3. Control match: Search for a known, expected flow in the same narrow time period. If that is also absent, an empty result is not reliable.
4. Dashboard comparison: Do the order of magnitude and timeline agree with Network Traffic, IoCs, or Recent flow detections? Dashboard aggregates and individual rows need not be identical, but contradictions require an explanation.
5. Cross-check: Remove exactly one narrow filter or shift the time window in a controlled way. A plausible difference shows that the condition is working. Never change several conditions at once.
6. Documentation: Record the query name or text, variables, time period with time zone, execution time, result count, and relevant rows in the ticket. Treat results as potentially sensitive network data.

Then open History. There, the console shows the user type, date and time, the number of results, and successful or failed. Assign the entry of your execution. History proves the execution, but neither completeness nor correctness of the query.

Limit errors and return safely

Dashboard is empty

Check Time Range, active Saved Filters and Filters. Select a short period of expected network traffic and remove the filters with Clear. If Network Traffic also remains empty, the problem is not due to a free query. Check whether the correct console is open and whether the expected sensor or the relevant appliance delivers data. A green appliance status does not prove complete mirror coverage. Transfer time, expected flow, console and affected sensor to the operation or support process, instead of extending the time period over 30 days.

Query returns zero rows

Confirm time period and time zone. In Schema, check if the table, field name and type are current. Then remove the narrowest technical filter and run the query again. Also, test a known expected value in the same time window. If an unchanged preconfigured query does not provide expected data, check the data path and affected sensors. The empty result does not prove that the activity is missing.

Query is failed

Find the appropriate entry in History and document status, query text and execution time in your work notes. Check syntax, field names and field types using Schema. Open the last unchanged, previously functioning query from Library, limit its SELECT to the smallest meaningful time period and set only one validated variable value. Start a new run. If the preconfigured query still shows failed, document the error and escalate it. Do not bypass it with write commands, schema changes or server settings.

Query runs unusually long or loads the console

Do not click Run again. Record start time, user and the name of the query. Do not start another broad query. After completion, check in History whether the run was successful or failed and how many results were obtained. If the interface remains slow, finish the query work and pass the observation to the operation of the console. A restart or shutdown is not part of this runbook.

Return to a functioning query

1. Stop running the same variant. Do not launch rapid retries or another broad control query.
2. If the interface is responsive, copy the query text and document start time, filters, variables, and the associated History entry. Do not save the faulty variant as a new standard query.
3. Open the original preconfigured query again from Library. Check that no inserted variable values or unstored changes have been adopted.
4. Limit the SELECT to a short period of time, a validated value and a small set of results based on the confirmed schema. Check tables and fields again under Schema.
5. Perform a single, already successfully executed read test. Check results and History.
6. If this test also fails or the console remains impaired, end the investigation and escalate with the secured information. Do not use database repair, delete or teardown commands.

Close and hand off

Finally, document the hypothesis, the console used, the affected sensors, the time window with time zone, the dashboard filters, the query executed, variables, History status and number of results. Hand off positive findings through the existing SOC, XDR, or MDR process. A negative result means only that nothing was found within the documented local investigation scope. It does not prove that the activity is absent from the entire network.

Remove sensitive sample values from a query definition that is only temporarily needed or clarify their allowable retention with the person responsible for Library.

Frequently asked questions