The KQL Mysteries: The Holiday 2023 Episode Part 3
A Festive Chase to Doom
This episode, we take a slight detour into the holiday season for a seasonal themed one-shot episode. After the holidays, we’ll get back to the story at hand.
Catch up on this series by going to: https://aka.ms/KQLMysteries
New chapters release weekly.
Jon and Sarah had been racking their brains over the mysterious numbers left by the hacker: 12, 25, 20, 23. The numbers seemed random, but they knew there had to be a pattern. As they delved deeper into the KQL logs, a realization struck Jon.
“Sarah, these numbers, they’re not random at all. They represent dates!” Jon exclaimed. “Think about it, 12 for December 25 for Christmas Day, and the year 2023. But what about 20?”
Sarah’s eyes widened as she made the connection. “The 20th of December! That’s today! The hacker is planning something for today!”
With a new sense of urgency, Jon and Sarah used KQL to monitor real-time network traffic, looking for any anomalies that could lead them to the hacker’s location. As they filtered through the data, a pattern emerged—a series of failed login attempts to the database server from an unrecognized IP address.
let suspiciousIP = "198.51.100.7";
NetworkTrafficLogs
| where TimeGenerated >= now(-1h)
| where SourceAddress == suspiciousIP
| summarize Count=count() by SourceAddress, Port
| where Count > 5
| order by Count descRunning the query, they discovered the IP address was bouncing through multiple proxies, but one thing stood out—a consistent port number being used in the attempts. It was a clue that could only mean one thing: the hacker was using a backdoor they had previously installed.
“Let’s cross-reference this port with our security patch logs,” Sarah suggested. “Maybe we missed an update that could have closed this vulnerability.”
Jon quickly typed up another KQL query, correlating the suspicious port activity with their patch history.
let vulnerablePort = 3389;
SecurityPatchLogs
| where TimeGenerated > ago(30d)
| where Port == vulnerablePort and PatchStatus == "Pending"
| project TimeGenerated, ComputerName, PatchName, PatchStatus
| order by TimeGenerated ascThe results were alarming. A critical security patch had been pending for weeks, leaving the system exposed. The hacker had been exploiting this all along.
“We need to apply this patch immediately and alert the cybersecurity team,” Jon said, his voice tense.
As they initiated the patch, the hacker’s activity ceased abruptly. It was as if they knew their window of opportunity had closed.
“Jon, look at this,” Sarah pointed to the screen. “The hacker’s activity stopped, but not before they scheduled a task on the server. It’s set to execute tonight at midnight.”
Jon’s heart raced as he read the scheduled task’s name:
Krampus_attack
Determined to catch the hacker, they decided to set a trap. They would monitor the network for any activity from the suspicious IP and intercept the hacker’s connection.
As they implemented their plan, the festive lights around them flickered, casting a warm glow over their intense focus. The holiday season was in full swing, but for Jon and Sarah, the real celebration would come with the capture of the elusive hacker.
Stay tuned for The Holiday 2023 Episode Part 4
[Want to discuss this further? Hit me up on Twitter or LinkedIn]
[Subscribe to the RSS feed for this blog]
[Subscribe to the Weekly Microsoft Sentinel Newsletter]
[Subscribe to the Weekly Microsoft Defender Newsletter]
[Subscribe to the Weekly Azure OpenAI Newsletter]
[Learn KQL with the Must Learn KQL series and book]
[Learn AI Security with the Must Learn AI Security series and book]