Time to catch some phishermen! I’m sure we have a special dislike for unsolicited phone calls, worse if it’s a potential scammer! Yet another great real-world use case for the power of KQL, I’m sure my Cybersecurity colleagues had a great time with this one.
For this case there are some assumptions that have to be made about certain behavior, I would suggest starting as simply as possible as once you go down certain rabbit holes you can go off down a long path which will not get you to the right answer. Check out those clues!
Challenge: Case 2
We’ve got another case that needs your expertise! The people of our city are being targeted by phishermen, and they need your help to stop them in their tracks.
The complaints are pouring in, and people are fed up with the sudden increase in phishing calls attempting to steal their identity details. We can’t let these scammers get away with it, and we need your help to catch them!
The police have asked for our assistance, and we’ve got a massive data set to work with. We’ve got listings of all the calls that have been made during the week, and we need to find the source of the phishing calls.
It’s not going to be easy, but we know you’re up for the challenge! We need you to analyze the data and use your detective skills to find any patterns or clues that could lead us to the source of these calls.
Once we have that information, the police can take action and put a stop to these scammers once and for all! Are you ready to take on this challenge, detective?
We’ve got your back, and we know you can do this! Let’s catch those phishermen!
Captain Samuel Impson.
Time to find that scammer!
The are a couple of different way to get to the right phone number but the command we’ll use are the same. Check out these KQL commands for some help extend, join and dcount.
Depending on the assumptions you’ve made about the scammer behavior it will also affect how you start writing your query.
Solution – Spoilers below
What were your assumptions detective?
//The key behaviors that come to mind are firstly that the scammer probably called a lot of people, secondly those calls were probably on the short side because we know the citizens are getting fed up.
//Who called the most citizens?
| where EventType == ‘Connect’
| extend Origin = tostring(Properties.Origin)
| extend Dest = tostring(Properties.Destination)
| extend IsHid = tostring(Properties.IsHidden)
| where IsHid == “true”
| join kind=inner
| where EventType == ‘Disconnect’
| extend DiscBy = tostring(Properties.DisconnectedBy)
| where DiscBy == “Destination”)
| summarize Scammer = dcount(Dest) by Origin
| top 1 by Scammer
Another criminal brought to justice, great work detectives! This wasn’t the most exciting case but the application of useful KQL commands more than made up for it, this challenge was a great learning exercise, nicely done Kusto Detective Agency team!