The KQL Mysteries: The Holiday 2023 Episode Part 2
Cracking Codes and Saving Holidays
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 were shocked to discover that the hacker who breached the database server knew Jon's name and left a message for him on the database, saying that he had a gift for him. They realized that the hacker was not just a random cybercriminal, but someone who had a personal connection to Jon.
They decided to use KQL to analyze the hacker's network traffic and database activity, hoping to find some clues about his true identity and location. They knew they had to act fast, before the hacker disconnected and vanished.
Jon started by looking at the network traffic logs again, focusing on the data transferred from the database server to the hacker's IP address. He wondered if he could decrypt or decompress the data and see what the hacker had stolen.
He typed the following query:
let ip = "203.0.113.42";
NetworkTrafficLogs
| where TimeGenerated > ago(1d)
| where DestinationAddress == "Database Server" and SourceAddress == ip
| project TimeGenerated, SourceAddress, DestinationAddress, Protocol, Port, BytesSent, BytesReceived, Data
| order by TimeGenerated desc He ran the query and saw the results in a table. He noticed that the data column contained a lot of hexadecimal values, which indicated that the data was encrypted or compressed. He tried to use some KQL functions, such as base64_decode, gzip_decode, or unzip, to decode the data, but none of them worked. He realized that the hacker had used a custom encryption or compression algorithm, which made it impossible to decode the data with KQL.
He said to Sarah, "I can't decode the data. The hacker used a custom algorithm. We need to find another way to trace him."
Sarah said, "Maybe we can look at the database logs again, and see if he left any other messages or clues on the database. Maybe he made some mistakes or left some traces that we can exploit."
Jon agreed and switched to the database logs. He looked at the SQL commands that the hacker had executed on the database, and noticed that he had used a lot of subqueries, joins, and unions, to access, modify, and delete the credit card information. He also noticed that he had used some SQL injection techniques, to bypass the security measures of the database.
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 left another message on the database, using a SQL comment. He said to Sarah, "Look at this. The hacker left another message on the database. It says:
'Jon, I hope you like puzzles. Here's one for you. What do these numbers mean? 12, 25, 20, 23. Good luck. Your Secret Santa.' "
Sarah looked at the results and said, "What? Another message? And a puzzle? What does he mean by that? What do these numbers mean?"
Jon looked at the results and said, "I don't know. They look like random numbers. Maybe they are some kind of code or cipher. Maybe they are related to the encryption or compression algorithm that he used. Maybe they are a clue to his identity or location."
Sarah said, "Maybe. But how do we solve this puzzle? How do we crack this code? Do we have any tools or resources that can help us?"
Jon said, "I don't know. Maybe we can use some KQL functions or operators, to manipulate or analyze these numbers. Maybe we can find some patterns or relationships, that can reveal their meaning."
Sarah said, "Maybe. But we don't have much time. He could disconnect at any moment and disappear forever. We need to act fast."
Jon said, "Then let's do it. Let's use our KQL skills and solve this puzzle. Let's catch this hacker."
Sarah said, "OK. Let's do it. Let's use KQL and save Christmas."
Stay tuned for The Holiday 2023 Episode Part 3
[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]