CQURE Hacks #78: 3 Advanced KQL Queries for Faster Security Analysis

Traditional SOC workflows are slow, relying on manual log reviews and reactive alerting that often leaves you one step behind. When malware hides in encrypted payloads or fileless scripts, standard signatures simply aren’t enough.

In this episode, we move beyond basic searches to implement three powerful queries designed to speed up your analysis:

1. Behavioral Anomaly Detection: Using Time-Series Analysis to establish personal baselines for every IP and protocol. By using statistical Z-scores, we find the “unknown unknowns” that traditional thresholds miss.

2. Predictive Alerting: Implementing machine learning features to calculate multi-dimensional risk scores. This allows you to prioritize the riskiest hosts instead of just the noisier ones.

3. Automated Attack Chain Reconstruction: Using the “serialize” and “next” operators to connect fragmented events into a single, chronological story.

These techniques turn a timeline of raw events into a clear attack campaign. Instead of investigating individual alerts, you see the full progression of the threat, reducing investigation time from hours to minutes.

Master the Full Malware Workflow

Malware rarely arrives as an obvious executable. It hides in macros, encrypted payloads, and fileless scripts that live entirely in memory—leaving no trace on disk for traditional scanners.

Investigating these threats requires two complementary approaches: Static Analysis to examine a file’s structure without executing it, and Dynamic Analysis to monitor its behavior in a controlled environment.

In Module 5 of the Cybersecurity Master Annual Program, “Malware Investigation & YARA Rules,” led by Amr Thabet (Cybersecurity Expert & Malware Researcher, and Author of the book “Mastering Malware Analysis”), you will learn to build the full workflow. From identifying indicators to writing custom YARA rules tuned for your specific environment, this is how you bridge the gap between detection and deep investigation. Join the 1-day course this Thursday, April 16:

Check out our hands-on training: https://cqureacademy.com/cyber-security-training/cmap-2026-module-5-malware-investigation-yara-rules/

TRANSCRIPT

Hey everyone, welcome back. If you work in cybersecurity, you know the feeling. Drowning in Lux, chasing alerts, and feeling like you’re always one step behind the attackers. Traditional sock workflows can be painfully slow, relying on manual luxury reviews and reactive alerting. But what if I told you there’s a better way?

Today, we are diving into free advanced KQL queries that can help you analyse security data faster than most SoC teams. We are going beyond the basic searches to show you how to use behavioral anomaly detection using time series analysis, predictive alerting with machine learning, and something most of you were waiting for: automated attack chain reconstruction.

I will be using real network logs showing you exactly how to implement these techniques. Whether you are a security analyst, engineer, or just interested in cybersecurity analytics, just stick around. This could change how you approach threat hunting forever.

Query 1: Behavioral Anomaly Detection

Let’s start the behavioral anomaly detection using time service analysis. This query establishes a normal baseline for each source, IP, port, and protocol combination during June 1st to June 3rd and then it checks June 4th to June 6th data against that baseline using statistical Z scores to find anomalies. Why? It’s faster than traditional methods. No signatures are required. Traditional SOCs rely on known attack patterns or signatures.

This query finds unknown attacks by looking for statistical outliers. Personal baselines. Each IP, port, or protocol gets its own baseline. For example, a web server sending to mex. This is normal, but a DNS server doing it may be highly suspicious. Traditional thresholds. Alerts thread all systems the same maths precision instead of arbitrary thresholds like alert if payload bigger than, for example, 10 megs. This uses standard deviation, so a 3.5 Z score means the event is in the top of 0.02% of expected behavior. Proactive detection.

This one catches data acceleration command and control traffic or malware downloads before they complete, based on statistical anomalies in payload size. In traditional SOC, they get alerts for large file transfers but ignore them because thresholds are set too low, for example, false positives or simply means real exfiltration, because thresholds are too high in our method, a mathematical adaptive baseline that reduces both false positives and false negatives.

Query 2: Predictive Alerting

Let’s move out to predictive alerting with machine learning features. This query calculates a risk score for each source IP based on multiple behavioral features, then prioritizes investigation based on those scores. Why? It’s faster than traditional methods.

First of all, multi-dimensional scoring in traditional SOC alerts may be on too many file logins, port scanning, and so on. But this query combines 8 different features into one risk score. Weighted importance. Notice the weights of unique ports times 0.2 or unique destinations for the same. Scanning many ports and many destinations is worse than just one. Traditional binary alerts miss these nuances. Tool detection.

The query automatically identifies scanning tools like Nmap attack tools, curl, wejet and many different user agents, something which many SOCKS may overlook. What about focus optimization? Top 30 by risk or description? And Azure’s analysts investigate the riskiest host, not the 30 noises or the most recent explainable AI. Unlike black box ML models, you can see exactly how the risk score was calculated. This IP scored 6.8 because it scanned 15 ports plus targeted 8 destinations, plus use nmap and so on. In traditional SOC, it may be inefficient.

Analysts start their day with 200 hours or even more. Investigate randomly or chronically, often wasting time on minor issues with major threats. Wait in our method tells the analyst exactly where to start for maximum impact.

Query 3: Automated Attack Chain Reconstruction

Now let’s have a look at attack chain reconstruction. This is one of the most powerful queries because it doesn’t just find individual attacks; it sequences them into campaigns. Traditional socks say individual alerts. This query shows you the complete attack story. Let’s break out.

First, we filter our data. Look for actual attacks by checking scan type, which gives you both attack and port scan events. We select only the fields we need, and order by attacker IP and timestamp. This chronological ordering is crucial for seeing attack progression. Here is where the magic happens. Surrealize operator preserves raw order.

Then we use the next to look at the next row in sequence, and we filter the quip on the rows where the same attacker appears in consecutive events. Calculate time gaps between attacks. Why does this beat manual analysis? Traditional socks might see this as separate alerts. Our support for this query connects them as one continuous campaign. Now we summarize into an attack campaign. Make a list to create a chronological list of each attack. We calculate totals, time span, and unique targets, and produce all grouped by attackers and attack type. We filter for campaigns with at least two attacks showing sustained effort. Rename columns for clarity and order by total attacks to show the most active attackers first. Why is this revolutionary? Traditional SoC workflow. Another bot attack from IPX, two hours later, alert 2, but an attack from the same IPX. Analysts probably investigate them separately, never connecting them as one campaign. It’s just a waste of time. If a peace meal investigation in our KQL method, one query shows IPX conducted 15, but attack attempts over 4 hours. Again, 8 targets. Complete story in seconds. Analysts understand the scope immediately.

Let’s check the real-world impact check. For example, IP 3.222.94.132. It conducted multiple bot attack attempts across different ports and targets. A traditional SIM would show this as six separate alerts. Our query shows it’s one coordinated campaign. The attack campaign column literally writes the incident report for you, for example, SSH to 114.115.29.125 on port 8080 or SNMP to 11411529.125 on the same 8080 port. You can see the attacker trying different services on the same target. There are a lot of key advantages over traditional methods. Context preservation, so not just that an attack happened, but how it progressed. Time intelligence so calculates the exact time span between attack steps.

Target analysis shows how many different systems were targeted. Port patrol recognition can reveal if attackers are focusing on specific services. The campaign identification group relates attacks that might be ours apart. This isn’t just about finding attacks faster; it’s simply about understanding them better. When you see an attack company, instead of individual alerts, you can prioritize better, respond smarter, investigate deeper, and report accurately. Simply provide management with Campaign 11 intelligence.

The Serialize and Next functions are secret weapons here. It’s quite easy, but they turn a timeline of events into a story of an attack, and stories are what humans understand the best, not rough data points. Now that we can sequence attacks, how do we know which ones to investigate first? That’s where the new query comes in.

That’s it. Three powerful KQL queries that can dramatically speed up your security analysis. Remember, it’s not just about finding threats faster; it’s about finding the right threats first.

The key takeaways from today:

1. Use behavioral baselines to find anomalies before they become incidents.
2. Reconstruct attack sequences to understand the full picture.
3. Prioritize with risk scoring to focus on what matters most.

These techniques can reduce investigation time from hours to minutes, but the real power comes from combining them into a cohesive strategy. What next? Try implementing one of these queries in your environment this week. If you want more content like this, make sure to hit that subscribe button and ring the bell so you don’t miss our next deep dive. Bye.

Want to know more?