The Case
The Final Case. El Puente has some intel; a heist is going down and it’s up to us to find out where and when.
The crims are using an unencrypted, public chat server and we have the metadata. We have to find their IPs, then use El Puente’s slightly sketchy “sneakinto” backdoor app to snoop for more clues.
Part One - Find the IPs
El Puente’s given us the chat logs - 3,803,816 records:
Looking at the format, we can break this down into the user performing an action and some additional contextual data. User names are always the first quoted string, the ‘action’ follows with the additional data quoted at the end of the message.
Let’s extract these:
ChatLogs
| extend args = extract_all(@"'(.+?)'", Message)
| extend uid = tostring(args[0]), arg = tostring(args[1])
| extend action = extract(@"(joined|left|user|channel|logged in|logged out)", 1, Message)
| project Timestamp,uid, action, arg
Now, if I was a slightly sus criminal, I’d not want to be using a channel with other users present. There again, I wouldn’t use an unencrypted public chat server, but hey. So, testing this hypothesis, lets look for channels where the max number of simultaneous users was 4.
We could use some of Kusto’s User and Sequence Analytics plugins like session_count but let’s try to solve the puzzle using basic KQL.
First, let’s find all the join and left events, and turn these into something we can aggregate:
let actions = materialize(ChatLogs
| extend args = extract_all(@"'(.+?)'", Message)
| extend uid = tostring(args[0]), arg = tostring(args[1])
| extend action = extract(@"(joined|left|user|channel|logged in|logged out)", 1, Message))
;
actions
| where action in ("joined", "left")
| extend a = iff(action == "joined", 1, -1)
...
We now have a column ‘a’ we can use to calculate a rolling sum of the number of users in a channel. We can filter this to where the max number of users is 4:
...
| sort by arg, Timestamp asc
| serialize usercount=row_cumsum(a, arg != prev(arg))
| summarize max(usercount) by arg
| where max_usercount == 4
...
Now, let’s join back onto our actions and find the channel where the count is only ever 4 distinct users
...
| join actions on arg
| project arg, uid
| distinct arg,uid
| summarize Count = count() by arg
| where Count == 4
Let’s plug this into a new query:
let actions = materialize(ChatLogs
| extend args = extract_all(@"'(.+?)'", Message)
| extend uid = tostring(args[0]), arg = tostring(args[1])
| extend action = extract(@"(joined|left|user|channel|logged in|logged out)", 1, Message));
actions
| where action == "channel" and arg == "cf053de3c7b"
| distinct uid
| join actions on uid
| where action == "logged in"
| distinct arg
| project strcat("https://sneakinto.z13.web.core.windows.net/",arg)
It’s not the most elegant solution, but we got there.
Part Two - Date and Location
This is where it got tricky. Accessing each of the URLs gave us more clues including a rather worrying video lesson on stealing big data:
Finding the location was simple enough - the clues are easy to find in the various files available on the gang’s servers. But then came this:
and another KQL script with a message from El Puente:
Hi there! How are you?
I’d be better if you’d make these clues easier 😛
ReadMessage(
````
Hi there! How are you?
PS:
This is a nice utility that reveals what hidden messages the text may have.
We may read the message and think: is there anything beyond words?
Can we find it without the utility, or it will become too much of a headache?
```,
h@'dhkl4fva!that:2,9,15,22,31'
)
So, find the historical nonsense event and that’ll give us the date. This is where the overthinking kicked in.
First I tried wikipedia looking for dates in history of the file creation date of the email. No, it wasn’t Groucho Marx’s birthday. Then I tried some other dates hidden in the EXIF of other images on the gangs servers. Nope.
How about something hidden in the PDF metadata? More hidden EXIF fields? HTTP headers returned by the gangs servers?
What about the phrase El Puente’s message decode script generated? I hadn’t actually ran that yet…
what We think: we become
The first bing result lead to: https://www.elephantjournal.com/2015/08/what-we-think-we-become-the-magic-free-meaning/
Elephants? Check. Nonsense? Could be?
But no, August 21, 2015 was not the date.
I looked back at Case 4 for clues (that had been another one where the code was easy but the puzzle harder)
Impressive, you got it right! Something BIG is going to happen… Keep the next hint close to you, it will help you. We will be in touch soon. El Puente.
wytaPUJM!PS:2,7,17,29,42,49,58,59,63
That key looks familiar. But for which message?
After hunting around for the right message to decrypt, I eventually ended up with some text that revealed a a bing search that returned a single result of a rather uncomfortable elephant. Unfortunately, the image is copyright so I can’t include here but the date of the painting lead straight to the date of the planned heist. I think.
Update
Looks like I got it right. What a great way to round out the game with a nice certificate of gratitude from the Mayor:
