Kusto Detective Agency — Big heist (Part 5 of 5)

While browsing twitter, I came across Kusto Detective Agency — a gamified way of learning Kusto Query Language (KQL).

There are a set of five challenges that participants are required to solve using their KQL skills and earn badges. Challenges are in the form of cases that need to be investigated by a detective agency (the storyline is awesome :P)

To start, participants are required to create a free Kusto cluster and separate data ingestion scripts are provided on a case to case basis.

Challenges

• Challenge 1: The rarest book is missing! (Here)
• Challenge 2: Election fraud? (Here)
• Challenge 3: Bank robbery (Here)
• Challenge 4: Ready to play? (Here)
• Challenge 5: Big heist (Current Article)

Big Heist

— — — — —

The challenge starts with the news of a heist that is supposed to happen soon. We are required to find out the exact place (latitude and longitude) and time (DD-MM-YYYY) of the heist. We are also provided with some key information about the gang members.

• There are 4 members in the gang
• The gang has a dedicated channel on the public chat server through which all of its members communicate.
• We are given a small utility to sneak into the gang members machine’s and find more hints:
  https://sneakinto.z13.web.core.windows.net/<ip>

Let’s run the provided ingestion script…

Once the data has been ingested let’s check the row count.

ChatLogs
| count

We have a table with two columns — TimeStamp and Message. Let’s do some data exploration…

After reviewing the message column entries we can come to the conclusion that all Messages belong to one of these types:

· User X logged in from IP

· User X logged out from the server

· User X joined the channel Y

· User X left the channel Y

· User X sent message to the user Y

· User X sent message to the channel Z

We will use the extract function along with appropriate regex patterns to carve out only relevant fields from the entire Message. For starters, let’s try to extract the username and IP address from “<user> logged in from <IP> message”

ChatLogs
| where Message matches regex ".*logged in from.*"
| extend IP = extract("logged in from '([0-9.]+)'",1,Message)
| extend User = extract("User '([a-z0-9]+)' ",1,Message)
| distinct User,IP

Next let’s set up the logic for the subsequent queries…

• Each gang member will login into the dedicated channel only i.e. if a user is a gang member then distinct count of logged in channels should be 1
• The gang channel should have distinct count of users joined equal to 4 since no other regular user other than gang members would be logging into the channel

So, we will filter for only “<user> joined the <channel >” messages and then group by User to see the count of distinct channels the user has joined. Next we will filter for only those entries where this count of distinct channel is 1 and display a list of the corresponding channels.

ChatLogs
| where Message matches regex ".*joined the channel.*"
| extend User = extract("User '([a-z0-9]+)' ",1,Message)
| extend channel = extract("channel '([a-z0-9]+)'",1,Message)
| summarize count_channel = array_length(make_set(channel)), make_set(channel) by User
| where count_channel == 1
| project channel = tostring(set_channel[0])
| distinct channel

Next, we will again filter for the same “<user> joined the <channel >” messages and this time group by Channel to find the distinct count of users joining the channel. We are looking for only those channels which fall in the first list AND where no of users joined is a multiple of 4 AND distinct no of users joined are exactly 4.

We have our gang channel — cf053de3c7b. Let’s use a scatter chart to check the distribution of user joining time.

ChatLogs
| where Message matches regex ".*joined the channel.*"
| extend User = extract("User '([a-z0-9]+)' ",1,Message)
| extend channel = extract("channel '([a-z0-9]+)'",1,Message)
| where channel == "cf053de3c7b"
| summarize count() by User,Timestamp
| render scatterchart

From the chart it is evident almost all 4 users logged into the channel at the same time. Next we will extract the individual ip addresses of the users.

We can use the hack utility now to snoop into the gang member’s respective machines.

We have a few interesting files…

Message about Project X and mention of day of action

Image inside pdf marked in red

Utility function to decrypt message using key

Additionally we find four images in a particular gang member’s machine out of which one of them (image3.jpg) has origin metadata like Date Taken

Next, about the historical non-sense event that was mentioned in the Project X memo we can use the utility function to decrypt the Message from the question using the key from the last case (Ready to Play)

Encrypted message

Decrypted text from message

Opening the link gives us the required year — 1891

Following the formula as mentioned in the Project X memo we have the date of the heist now!

09–07–2020 + (1891 % 1000) days
= 09-07-2020 + 891 days
= 17-12-2022 

Finally to find the location, we will use the tool mentioned in utility.txt — https://tool.geoimgr.com/ on the image (image3.jpg) obtained from one of the gang member’s system using the hack utility.

So now we have the exact coordinates as well — (58.9688665166667,-3.38010413333333)

PS: I started learning KQL recently, feel free to reach out to me and correct me, incase you spot some inconsistencies I would highly appreciate that. And finally thank you very much for taking your time to read this post.