The KQL Mysteries: The Holiday 2023 Episode Part 1

Christmas Carol

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 Block was looking forward to spending Christmas Eve with his family, but his plans were interrupted by an urgent call from his boss, Sarah. She told him that there was a major security breach at one of their clients, and they needed his help to investigate it using KQL.

Jon reluctantly agreed to join the incident response team, and drove to the office, leaving behind his wife and kids. He hoped he could finish the job quickly and return home before midnight.

When he arrived at the office, he met with Sarah and the other analysts. They explained that the client was a large online retailer, and someone had hacked into their database and stolen the credit card information of thousands of customers. They had to find out who was behind the attack, and how they did it.

Jon logged into the Microsoft Sentinel portal and opened the KQL query editor. He started by looking at the sign-in logs of the database server, to see if there was any suspicious activity.

He typed the following query:

SigninLogs 
| where TimeGenerated > ago(1d) 
| where ResourceDisplayName == "Database Server" 
| project TimeGenerated, UserPrincipalName, ResultType, ResultDescription, IPAddress 
| order by TimeGenerated desc  

He ran the query and saw the results in a table. He noticed that there were several failed sign-in attempts from an unknown IP address, followed by a successful one. He copied the IP address and used another query to find its location.

He typed the following query:

let ip = "203.0.113.42";
IPAddresses 
| where Address == ip 
| project Country, Region, City  

He ran the query and saw the results in a table. He was surprised to see that the IP address belonged to a city in North Korea.

He said to Sarah, "I think I found the hacker. He used a North Korean IP address to log into the database server. He must have guessed the password somehow."

Sarah looked at the results and frowned. She said, "That's too easy. It doesn't make sense. Why would a hacker use such an obvious IP address? And how could he guess the password? It was a complex one, with letters, numbers, and symbols. There must be more to this."

Jon agreed that it was strange and decided to dig deeper. He wondered if the hacker had used any other tools or techniques to compromise the database server. He decided to look at the network traffic logs, to see if there was any unusual activity.

He typed the following query:

NetworkTrafficLogs 
| where TimeGenerated > ago(1d) 
| where DestinationAddress == "Database Server" 
| project TimeGenerated, SourceAddress, DestinationAddress, Protocol, Port, BytesSent, BytesReceived 
| order by TimeGenerated desc  

He ran the query and saw the results in a table. He noticed that there was a lot of traffic from the same North Korean IP address, using different protocols and ports. He also noticed that there was a large amount of data transferred from the database server to the hacker's IP address, which indicated that the hacker had downloaded the credit card information.

He said to Sarah, "Look at this. The hacker used various network tools to scan, probe, and exploit the database server. He also transferred a lot of data from the server to his IP address. He must have used some kind of encryption or compression to hide the content."

Sarah looked at the results and nodded. She said, "Good catch. This confirms that the hacker was skilled and sophisticated. He knew what he was doing. But I still don't understand why he used a North Korean IP address. It's too obvious. It's like he wanted us to find him."

Jon agreed that it was puzzling, and decided to look at the database logs, to see if there was any clue about the hacker's motive or identity.

He typed the following query:

DatabaseLogs 
| where TimeGenerated > ago(1d) 
| where ServerName == "Database Server" 
| project TimeGenerated, UserName, Action, ObjectName, Details 
| order by TimeGenerated desc  

He ran the query and saw the results in a table. He noticed that the hacker had executed several SQL commands on the database, to access, modify, and delete the credit card information. He also noticed that the hacker had left a message on the database, using a SQL comment. He said to Sarah, "Look at this. The hacker left a message on the database. It says:

'Merry Christmas, Jon Block. I hope you enjoy your gift. Signed, Your Secret Santa.' "

Sarah looked at the results, and gasped. She said, "What? How does he know your name? And what gift is he talking about?"

Jon looked at the results and felt a chill down his spine. He said, "I don't know. This is creepy. And look at the time stamp. He did this just a few minutes ago. He's still online. He's watching us."

Sarah said, "This is not good. We need to alert the authorities. And we need to find out who this hacker is, and why he's targeting you."

Jon said, "I agree. But how do we do that? He's using a North Korean IP address, which is probably a proxy or a VPN. He's hiding his real location and identity. And he's using encryption and compression to mask his data. He's a ghost."

Sarah said, "There has to be a way to trace him. Maybe we can use some advanced KQL functions or operators, to analyze his network traffic or database activity. Maybe we can find some patterns or anomalies, that can reveal his true IP address or identity."

Jon said, "Maybe. But we don't have much time. He could disconnect at any moment and disappear forever. We need to act fast."

Sarah said, "Then let's do it. Let's use our KQL skills and catch this hacker. Let's solve this mystery."

Jon said, "OK. Let's do it. Let's use KQL and save Christmas.

Stay tuned for The Holiday 2023 Episode Part 2

[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]