Prepare for a SOC or security analyst interview

Most SOC and security analyst interviews include a technical part where you find something in logs, live or as a take-home test. Here are the tasks that come up most, solved in KQL, Elastic and Sophos SQL, with what the interviewer is checking.

The tasks

Pick your language on each one. The table and field names are the real ones.

Task 1
Find failed sign-ins
“We think someone is guessing passwords. Which accounts had the most failed sign-ins in the last 24 hours, and where did they come from?”
What they’re checking
That you set a time range, group by account and source, and sort so the worst cases come first.
The mistake that costs marks
Counting by account only. One address failing against many accounts is password spraying, and a count by account alone hides it.
DEFENDER · KQL
```
DeviceLogonEvents
| where Timestamp > ago(24h)
| where ActionType == "LogonFailed"
| summarize Failures = count() by AccountName, RemoteIP
| top 10 by Failures
```
ELASTIC · ES|QL
```
FROM logs-system.security*
| WHERE @timestamp > NOW() - 24 hours
| WHERE event.code == "4625"
| STATS failures = COUNT(*) BY winlog.event_data.TargetUserName, source.ip
| SORT failures DESC
| LIMIT 10
```
Windows event 4625 is a failed sign-in.
Sophos Live Discover and the Data Lake aren’t built for sign-in history. Say you’d check the SIEM or the Windows Security log for event 4625 instead. Knowing which tool to use is part of the answer.

Task 2

Find Office starting a shell

“A user opened a suspicious attachment. Did Word, Excel or Outlook start PowerShell or a command prompt on any device this week?”

What they’re checking: That you ignore case on file names and show the command line, because that’s where the evidence is.
The mistake that costs marks: Matching file names exactly. OUTLOOK.EXE and outlook.exe are different to a case-sensitive match.

DEFENDER · KQL

DeviceProcessEvents
| where Timestamp > ago(7d)
| where InitiatingProcessFileName in~ ("winword.exe", "excel.exe", "outlook.exe")
| where FileName in~ ("powershell.exe", "pwsh.exe", "cmd.exe")
| project Timestamp, DeviceName, AccountName, InitiatingProcessFileName, ProcessCommandLine

ELASTIC · ES|QL

FROM logs-*
| WHERE @timestamp > NOW() - 7 days
| WHERE TO_LOWER(process.parent.name) IN ("winword.exe", "excel.exe", "outlook.exe")
| WHERE TO_LOWER(process.name) IN ("powershell.exe", "pwsh.exe", "cmd.exe")
| KEEP @timestamp, host.name, user.name, process.parent.name, process.command_line

SOPHOS · SQL

SELECT meta_hostname, username, parent_name, cmdline
FROM xdr_data
WHERE query_name = 'running_processes_windows_sophos'
  AND LOWER(parent_name) IN ('winword.exe', 'excel.exe', 'outlook.exe')
  AND LOWER(name) IN ('powershell.exe', 'pwsh.exe', 'cmd.exe')

A Data Lake query. Set the time range in the console.

Task 3

Find encoded PowerShell

“Attackers often hide PowerShell commands in Base64. Find any encoded PowerShell from the last week.”

What they’re checking: That you know why attackers encode commands, and that you’d decode what you find before calling it.
The mistake that costs marks: Forgetting the short forms. PowerShell also accepts -e and -ec, so mention them even if your first query only looks for -enc.

DEFENDER · KQL

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine contains "-enc"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine

ELASTIC · ES|QL

FROM logs-*
| WHERE @timestamp > NOW() - 7 days
| WHERE TO_LOWER(process.name) IN ("powershell.exe", "pwsh.exe")
| WHERE TO_LOWER(process.command_line) LIKE "*-enc*"
| KEEP @timestamp, host.name, user.name, process.command_line

SOPHOS · SQL

SELECT meta_hostname, username, cmdline
FROM xdr_data
WHERE query_name = 'running_processes_windows_sophos'
  AND LOWER(name) IN ('powershell.exe', 'pwsh.exe')
  AND LOWER(cmdline) LIKE '%-enc%'

Task 4

Find who talked to an IP

“Threat intel flagged 203.0.113.10. Which devices connected to it, which program made the connection, and when?”

What they’re checking: That you give first and last seen and the program behind it, not just a list of devices.
The mistake that costs marks: Checking one direction only. Look for the address as the source too, in case it connected in.

DEFENDER · KQL

DeviceNetworkEvents
| where Timestamp > ago(7d)
| where RemoteIP == "203.0.113.10"
| summarize Connections = count(), FirstSeen = min(Timestamp), LastSeen = max(Timestamp)
    by DeviceName, InitiatingProcessFileName

ELASTIC · ES|QL

FROM logs-*
| WHERE @timestamp > NOW() - 7 days
| WHERE destination.ip == TO_IP("203.0.113.10")
| STATS connections = COUNT(*), first_seen = MIN(@timestamp), last_seen = MAX(@timestamp)
    BY host.name, process.name

SOPHOS · SQL

SELECT p.name, p.path, s.remote_address, s.remote_port
FROM process_open_sockets s
JOIN processes p ON s.pid = p.pid
WHERE s.remote_address = '203.0.113.10';

A Live Discover query. It only sees connections that are open right now.

Task 5

Find rare programs

“Which programs ran on only one or two devices this week? Those are worth a closer look.”

What they’re checking: That you count devices, not runs. A program that ran 500 times on one machine is still rare.
The mistake that costs marks: Grouping by name only. Malware often copies a normal name, so group by the file hash too when you have it.

DEFENDER · KQL

DeviceProcessEvents
| where Timestamp > ago(7d)
| summarize Devices = dcount(DeviceName), Runs = count() by FileName, SHA1
| where Devices <= 2
| sort by Devices asc

ELASTIC · ES|QL

FROM logs-endpoint.events.process-*
| WHERE @timestamp > NOW() - 7 days
| STATS devices = COUNT_DISTINCT(host.name), runs = COUNT(*) BY process.name
| WHERE devices <= 2
| SORT devices ASC

Uses Elastic Defend process events.

SOPHOS · SQL

SELECT name, COUNT(DISTINCT meta_hostname) AS devices
FROM xdr_data
WHERE query_name = 'running_processes_windows_sophos'
GROUP BY name
HAVING COUNT(DISTINCT meta_hostname) <= 2
ORDER BY devices

Task 6

Find possible lateral movement

“Which accounts signed in remotely to the most devices in the last day?”

What they’re checking: That you know logon type 3 is a network sign-in, like file shares or PsExec, and type 10 is Remote Desktop. One account reaching many devices is the warning sign.
The mistake that costs marks: Leaving computer accounts in. Names ending in $ are machines, and they add a lot of noise.

DEFENDER · KQL

DeviceLogonEvents
| where Timestamp > ago(24h)
| where ActionType == "LogonSuccess"
| where LogonType in ("Network", "RemoteInteractive")
| where AccountName !endswith "$"
| summarize Logons = count(), Devices = dcount(DeviceName) by AccountName, RemoteIP
| sort by Devices desc

ELASTIC · ES|QL

FROM logs-system.security*
| WHERE @timestamp > NOW() - 24 hours
| WHERE event.code == "4624"
| WHERE winlog.logon.type IN ("Network", "RemoteInteractive")
| WHERE NOT winlog.event_data.TargetUserName LIKE "*$"
| STATS logons = COUNT(*), devices = COUNT_DISTINCT(host.name)
    BY winlog.event_data.TargetUserName, source.ip
| SORT devices DESC

Windows event 4624 is a successful sign-in.

SOPHOS · SQL

SELECT user, type, host, time FROM logged_in_users;

A Live Discover query. It shows who is signed in right now, not past sign-ins.

Task 7
Find new scheduled tasks
“We think the attacker set something up to survive a restart. Were any scheduled tasks created this week?”
What they’re checking
That you know scheduled tasks are a common way to stay on a machine, and that you filter out the system’s own.
The mistake that costs marks
Keeping the SYSTEM account in. S-1-5-18 creates plenty of normal tasks and buries the odd one.
DEFENDER · KQL
```
DeviceEvents
| where Timestamp > ago(7d)
| where ActionType == "ScheduledTaskCreated"
| where InitiatingProcessAccountSid != "S-1-5-18"
| project Timestamp, DeviceName, InitiatingProcessAccountName, AdditionalFields
```
ELASTIC · ES|QL
```
FROM logs-system.security*
| WHERE @timestamp > NOW() - 7 days
| WHERE event.code == "4698"
| KEEP @timestamp, host.name, winlog.event_data.SubjectUserName, winlog.event_data.TaskName
```
Windows event 4698 is a scheduled task being created.
SOPHOS · SQL
```
SELECT name, action, enabled, last_run_time FROM scheduled_tasks WHERE enabled = 1;
```
A Live Discover query. It lists the tasks on the device now, so look for odd names and programs in user folders.

Task 8

Build a timeline for one device

“An alert fired on ws01 at 9am. Show me everything that happened on it between 8 and 10.”

What they’re checking: That you sort oldest first and pull processes, network and files together, so you can tell the story in order.
The mistake that costs marks: Sorting newest first by accident. In KQL, sort is descending unless you write asc.

DEFENDER · KQL

let Start = datetime(2026-10-01 08:00);
let End = datetime(2026-10-01 10:00);
union DeviceProcessEvents, DeviceNetworkEvents, DeviceFileEvents
| where Timestamp between (Start .. End)
| where DeviceName startswith "ws01"
| project Timestamp, ActionType, FileName, ProcessCommandLine, RemoteIP, FolderPath
| sort by Timestamp asc

DeviceName holds the full domain name, so startswith catches ws01.corp.local too.

ELASTIC · ES|QL

FROM logs-*
| WHERE host.name == "ws01"
| WHERE @timestamp >= TO_DATETIME("2026-10-01T08:00:00Z")
    AND @timestamp < TO_DATETIME("2026-10-01T10:00:00Z")
| KEEP @timestamp, event.action, process.name, process.command_line, destination.ip, file.path
| SORT @timestamp ASC

SOPHOS · SQL

SELECT calendar_time, name, parent_name, cmdline
FROM xdr_data
WHERE query_name = 'running_processes_windows_sophos'
  AND meta_hostname = 'ws01'
ORDER BY time

A Data Lake query for processes only. Set the time range in the console.

How to answer well

Say the time range and the table first
Before you type, say where the data lives and how far back you’ll look. It shows you think before you query.
Start broad, then narrow
Run a simple version, check how many rows come back, then add filters. Interviewers like to see you check your work.
Mention case and empty fields
Say when a match ignores case and when a field might be empty. Those are the two things that most often make a query miss rows.
Explain what you’d do next
A result is only half the answer. Say what you’d check next, who you’d tell, and what would make you escalate.
It’s fine not to know the tool
If they use a SIEM you haven’t used, say how you’d do it in one you know, then how you’d find the equivalent. That’s what the job is like.

If the job uses a tool you don’t know

Most of these tasks work the same way everywhere. Only the names change.

Practise these before the interview

SOC Fluent gives you exercises like these to solve on realistic lab data, and shows you what your query missed. Interview next week? A track pass is a one-off payment for one language.

Join the waitlist See pricing

Free to join. Students get 50% off at launch.

Prepare for a SOC or security analyst interview

The tasks

Find failed sign-ins

Find Office starting a shell

Find encoded PowerShell

Find who talked to an IP

Find rare programs

Find possible lateral movement

Find new scheduled tasks

Build a timeline for one device

How to answer well

Say the time range and the table first

Start broad, then narrow

Mention case and empty fields

Explain what you’d do next

It’s fine not to know the tool

If the job uses a tool you don’t know

Practise these before the interview