The Inside Scoop on Using KQL for Cloud Data Security

Sherrod DeGrippo: Welcome to "The Microsoft Threat Intelligence Podcast." I'm Sherrod DeGrippo. Ever wanted to step into the shadowy realm of digital espionage, cybercrime, social engineering, fraud? Well, each week, dive deep with us into the underground. Come hear from Microsoft's elite threat intelligence researchers. Join us as we decode mysteries, expose hidden adversaries, and shape the future of cybersecurity. It might get a little weird. But don't worry, I'm your guide to the back alleys of the threat landscape. [ Music ] I am Sherrod DeGrippo, and this is "The Microsoft Threat Intelligence Podcast." I'm here with the authors of the new book "The Definitive Guide to KQL: Using Kusto Query Language for Operations, Defending, and Threat Hunting." With me is Rod Trent, senior program manager Microsoft; Matt Zorich, principal security research manager; and Mark Morowczynski, principal product manager for customer experience engineering at Microsoft. Thanks for joining me, everyone. >> Doing great. >> Awesome. How are you? I'm good. I'm excited because you guys have released another book. I know that Matt has done some books before, which is mind blowing to me because it seems like a lot of work. But back in May, "The Definitive Guide to KQL: Using Kusto Query Language for Operations, Defending, and Threat Hunting" was released from Microsoft Press, and I want to hear all about it. First, what is KQL? >> Matt, would you like to start?

Matt Zorich: Yeah, so KQL is Kusto query language, and that's a query language we initially used just at Microsoft as kind of a big data hunting query, and then it made its way into our public-facing products as well. So you see it in Microsoft Defender. You see it in Microsoft Sentinel. In our jobs, Rod and I, we use it every day. I'm probably writing thousands of queries every day. And we use it in the ghost team to look at the bad guys and find compromise and try to help our customers.

Sherrod DeGrippo: Yeah, Rod, tell me your kind of journey with KQL.

Rod Trent: My journey with KQL. So, first, anecdotally, I think one of the things Matt didn't mention I think would be of interest to others, these three letters, KQL, or Kusto query language, I think it's of interest to know that the name Kusto, where it comes from, it actually comes from the old undersea world explorer from the 1970s, Jacques Cousteau. Which really relates to what this query language is intended to do because of, with the data that we ingest into the cloud daily, I mean, it's massive amounts of data, there was not a clear-cut really good efficient query language available when the cloud started to become this big thing, to be able to efficiently go through and expose the data within these log files and things like this. This is where KQL actually was birthed from, the idea that we should be able to use the power of the cloud to actually query and bring back the results very quickly and very efficiently, particularly when we talk about our approach to this query language in this book. Because the approach is for security purposes when you consider yourself as a security analyst or security engineer, whatever your title is, when you're looking through this data, looking for those specific occurrences of potential threats within your environment, you're literally on the clock, and those results coming back quickly is super, super important. So we needed a query language that could enable those security operationalists to be able to do that. Understanding that the name Cousteau is actually related to our Kusto clusters within Azure. So it uses the power of those clusters for its compute resources to ensure that those results come back quickly. I think my journey for KQL is actually sort of super unique. When I first started at Microsoft about a little over five years ago, I was delivering probably two or three Microsoft Sentinel -- one of our security products -- workshops every single week. I think in one year, I delivered like a little over 85 workshops on Microsoft Sentinel. It was during the middle of that workshop, there was a little module about this crazy simple query language called KQL that I could see when I was delivering this part of the workshop, customers, their faces would light up. They're like, oh, this is so easy, this is so simple; and if I can just understand this, I can understand Microsoft Sentinel. So as a result of those Microsoft Sentinel workshops, I sort of identified that there wasn't a lot of documentation; there wasn't really a high priority on putting documentation and learning together for KQL. So in the very beginning, I created what turned into a workshop, a blog series, what-have-you, called Must Learn KQL. And I understood very quickly over that period of time that this was sort of a necessity for people to learn, whether you're doing security or not. Anything data related in the cloud, these people needed to be able to learn how to write very simple KQL queries. Because even a simple KQL query can produce some really cool actionable data. So along that way, earlier on in my career, before I joined Microsoft, I used to write books. But I realized early on that it's a lot of work, and I just literally stopped doing it. But this -- Mark actually reached out to me. And this topic was the thing that really kind of brought me back into it, because I knew this needed to have kind of an official stamp on it with Microsoft Press.

Sherrod DeGrippo: Mark, can you kind of tell us like what you're using KQL for primarily, like where you see it manifest most in your day-to-day work?

Mark Morowczynski: In my current role, I work a lot with customers on their deployments of Entra ID, so a lot in the workbook space to look at different operational information as well as like monitoring -- so things like patterns are changing or maybe we've seen alerts come through, things of that nature.

Sherrod DeGrippo: Got it. And Matt, I know that you use this quite a bit on incidents. So are there any examples that make sense for practitioners to know around how this book and how KQL can help them if they're in an incident response scenario?

Matt Zorich: Yeah, definitely. I think that's a key part to this book is we wanted, as authors, to position this book as really real-world focused and kind of focused on what our customers are seeing, what threats they're facing. And obviously between Rod, Mark, and myself, we all have kind of a different perspective of Microsoft customers. And myself as an incident responder and threat hunter, I guess have the perspective of what it's like to deal with ransomware of financial crime and things like that. So the book, in kind of the backend of the book, has a few like fictitious scenarios. So there's phishing attacks. There's compromised users. There's a fake ransomware kill chain. There's even things like going through a fake audit. And there's queries and process and methodology to how we would do it at Microsoft. So we really wanted to position it for not just this group of queries that can you look at at GitHub and find them really anyway; we wanted to give it that real-world flavor. So we're really proud of how it's come up in that way.

Sherrod DeGrippo: Got it. So I want to know, too, like what would you say to someone who has never used KQL before? Maybe they're moving to an environment where they have Sentinel. What would you say to convince them that KQL is the right place for them to be and the right thing for them to learn?

Rod Trent: This is Rod. I can answer that. And I'm glad that you mentioned Microsoft Sentinel. Because, as I was talking about with those workshops, KQL is kind of essential. So when you stand up something like Microsoft Sentinel, right, there are some things that come enabled out of the box. We have, in Microsoft Sentinel, what are called "number one analytics rules." And those are all based on those KQL queries. They run automatically to expose alerts and expose incidents that analysts can actually partake and identify and try to remediate potentially within their environment. But those are based on, the very basis of those analytics rules is this query language. Same query language that goes across all of our products within Azure. Additionally, our workbooks. These visual components within Microsoft Sentinel. The backend, the code for it, that's also KQL. So you literally can't get away from it. It's interesting that, I talked about the Microsoft Sentinel component, but even today, where my focus is at Microsoft today is Copilot for Security -- one of our Microsoft Copilots. We have within Copilot for Security the ability to enable and create custom plug-ins. These plug-ins, one of the types of plug-ins that we can utilize is a KQL based plug-in. So again, if you want to create something custom for your environment based on this security Copilot that we have, you can create this as long as you know and understand KQL. So it literally goes through and continues to be a very valuable component of not just security but anything that's data centric within Azure.

Sherrod DeGrippo: I've actually heard something about Copilot for Security as well as some of the other LLM interfaces that are out there commercially. I've heard that it's the capability for many of these type of applications to write and review KQL is actually super, super good. So there's a lot of resources there. Are you finding that people are using like the Copilots or other LLMs to help them write and view KQL too? >> They are. I would be sort of tentative to say, yeah, thumbs up, go ahead, go all out, just use a Copilot or a ChatGPT to create KQL queries. I will tell you, though, again, you still kind of need to have a really good simple understanding. Because the queries that get developed, number one, they have to know about your environment, right. So if you use a Copilot, it has to know what data you have. It may not know that. You have to ask it the right questions. And if you don't ask the right questions, you're not going to get the right response. Which means that you have to still kind of pore over and use some human eyeballs to validate that KQL query. So you still kind of need a sense of what that query is intended to do, and that it's going to be optimized to be able to run effectively. I think probably one of the biggest value adds for this book is that every query that exist within this book exists on the GitHub repository for this book, Microsoft Press, GitHub repository. And so you go through this book, it's not just a static, okay, let's just read this. Because there is a demo environment for anyone reading this book that they can access, take these queries from our GitHub repository, and actually run them and use them and edit them themselves. >> I think the other thing, too, is that it's in a way that if you're an expert, there's stuff you can pull out of it, as well as if you've never looked at KQL and you've kind of avoided it, it gives you like a path to get more familiar with it, and to Rod's point, to start running it in your environment. Awesome. >> I think Mark's perspective when he wrote the book is really unique is that he would self-admit that he wasn't anywhere near a KQL expert, so he was essentially learning as he was writing the book. Which I think gives the introductory chapters like a really good context is he was coming at it fresh as well. So like I've read those first couple of chapters, and as an introduction, they're amazing, like take it from someone who is learning his writing, I think you should hit up Mark and get him to answer that. Yeah, so, Mark, you had to learn quite a bit to be able to write the book on it. I want to hear a little bit about like your journey, if you feel like you sort of were back in school a little bit?

Mark Morowczynski: Sure. So my knowledge of KQL before writing the book I would say was kind of basic. I could usually make sense of what a query was if I found it somewhere or if I was editing a workbook. But I really didn't have a good understanding of why you do certain things or how some of the stuff works. So when I wrote some of the first two chapters of the book, I really -- it forced me to really go deeper and try to understand how do you do things a certain way, why do you do those things a certain way. And because I don't have as much experience as Rod or Matt or Chris, who was our technical reviewer, I really approached this as a way if you have never touched KQL before that you could follow along, run the queries that are in the book against your environment, and start pulling out like real critical useful information from the start.

Sherrod DeGrippo: Have any of you played the KC7 game? >> Yes. >> Yes. That's pretty fantastic. Well, and I just did that -- recently, somebody sent me the link and I went through it, I'm like, this is fun. It's entertaining but it's also informative. I thought it was really cool. The KC7 game, for those who don't know, is sort of like a threat intelligence and security first-person shooter, though you're not shooting anything. It's a first-person game where you're playing as an analyst and there's multiple scenarios. But there is an opportunity in the game quite a few times in KC7 to write KQL. And I'm just thinking for a lot of our listeners they do play KC7. It's very popular in the threat intelligence space. If you need to brush up on your KQL so that you can get better at the game, maybe you should check out the book and you'd up your score. >> Right. Well, and also another thing to highlight is that if you go through some of our SC series of exams, highlight SC 200 specifically, you have to know KQL to pass or go through that learning module but also pass that exam. So brush up, everyone. Get your KQL skills going with the book. Well, Mark, Rod, Matt, it was so great to speak with you about the new book that is available now. And, in fact, I heard that you could go to the Microsoft Press Store and use discount code kusto and get about 30% off; is that right? >> That's correct. It comes out of Matt's pay. Personal paycheck. >> Yeah, we're all good with that. So if you want to get a discount and also take money out of Matt's discretionary fund, please go buy the book, "The Definitive Guide to KQL: Using Kusto Query Language for Operations, Defending, and Threat Hunting. Thank you so much for joining me, and I hope to talk to all of you again soon. >> Thank you. >> Thanks for having us. >> Thank you. [ Music ] I am joined today with Lekshmi, senior threat hunter. Welcome to the show, Lekshmi.

Lekshmi Vijayan: Thank you, Sherrod; thank you for inviting me to the show.

Sherrod DeGrippo: So something that we wanted to talk to you about which is really interesting is a big thing that people love, which is PowerShell. They love it for good and they love it for not so good sometimes. We know that threat actors love to use PowerShells. So tell me about this technique that we're seeing with copy/pasting malicious PowerShell.

Lekshmi Vijayan: So this technique mainly focuses on the theory that humans are the weakest link in the security, where an attacker tricks a user to copy malicious partial code and then execute it in the PowerShell console. They mention that they will get error pop up or a pop up message from an already compromised website. Or through an email, they will get an HTML file, from which notification that shows that you have some specific error that is happening. So in order to fix that, you can copy the specific command lines and execute it in PowerShell. So, you know, we all follow that steps most of the time. Because even when you want to fix some things, you are trying to troubleshoot yourself. That is a common thing for anybody who uses computer. So they trick users in copying this command line, which when executed, it is a malicious command line that is resulting in multiple, you know, different techniques. For example, there is one specific campaign where the PowerShell will connect to a malicious domain and download NetSupport, right. Or in other scenarios we have observed, it will connect to a malicious domain and download Lumma Stealer or target malware or malware sample. So these are the main behaviors that we see in the specific strain of technique. And we also noticed this one very interesting piece, that when you copy a component -- like anything in your Windows machine -- it gets to your clipboard. So attacker is also very smart to make sure that the copied content is later emptied so that, you know, when they paste it in another page, it will not be reflecting in there. You know, let's say they are trying to paste it in a notepad, so in scenario when this clipboard is not emptied, basically they will be copy/pasting the malicious command. So attacker is also very smart to make sure that the clipboard is emptied back. So this has been going on from almost -- we started noticing it in the month of July, but it was happening from the month of June, where, you know, multiple different categories of malware where they worked through this specific technique.

Sherrod DeGrippo: And so what are volumes looking like on this? Is it widespread? Very targeted? What are we seeing there?

Lekshmi Vijayan: So we will not say that it is a targeted attack. It's more like an opportunist attack, which were really beginning during the time from June to July. And we see that the volume has dropped consistently from August.

Sherrod DeGrippo: And what's the primary delivery mode? Are these coming through email or malvertising?

Lekshmi Vijayan: We noticed two methods. One is email and then malvertising. So through emails, a user will get an email with an HTML attachment, where your user then opens it to display sometimes a Word file that has a small message, and then you have some issue in your machine and you can fix it by executing these steps. And in case of malvertising, you're on a compromised website or something. When user visits, they are presented with specific, you have an issue in your machine; in order to fix that, please execute this, copy/paste this particular command to fix the error. So those are the two methods.

Sherrod DeGrippo: It gives you actual code to paste into the command line into PowerShell?

Lekshmi Vijayan: Copy button, so you click on that to fix the error. But then you click on that and then it gets copied to your clipboard, basically.

Sherrod DeGrippo: That sounds quite technically in depth for some of the attacks we've seen. It's not exactly turnkey. Do you have any insight on sort of the degree of technical difficulty that's expected from the targeted party here?

Lekshmi Vijayan: Yeah. I think the main thing here is that we have seen attackers leveraging, you know, clipboard before as well, where they were doing clipboard hijacking to hijack etcetera. But this is a little bit change, right, where they want to -- it does basically a simple thing. You can actually trick users that will increase the possibility of a successful attack mode. So I think that is one of the reasons this is chosen by the attackers.

Sherrod DeGrippo: Got it. Let's talk a little bit about information stealers. I saw that this one does Amadey, and you said that there was another one that it does too, DarkGate?

Lekshmi Vijayan: Yes, yes.

Sherrod DeGrippo: Tell us a little bit from the audience perspective so they can understand what do infostealers really do?

Lekshmi Vijayan: So infostealers, I mean, there are different types. So, for example, some focuses on your favorites and your browsers. And then there are some focusing on your valid keys, etcetera. But nowadays, we can see that basically they gather all the sensitive information that you have in your browsers, your wallets. There's also some extracting specific files from your system. So there is a very huge spike in information stealers from, you know, last couple years. And we see that it has been very active. And we are seeing a lot of successful progression as well. And I think every time they keep on changing the initial access, how they access the user machine, so that they can, you know, focus on that. And of course, we have seen where the stealers have exfiltrated data. And later we see that they are using these credentials to log into other user, other sensitive websites, etcetera.

Sherrod DeGrippo: And so in terms of final objective, what are we seeing with a lot of these groups? Why are they doing this technique? Because it doesn't sound like there's any sort of final payoff with the PowerShell piece and the info stealer. What's the next step in the chain?

Lekshmi Vijayan: They can actually use it for extracting users' logins. And then, another thing is, where they are trying to install NetSupport Manager. So we also observed that in some campaigns, they were installing NetSupport, which is an admin tool, which can later help attacker to have access to the user machine. So those are the main end goals for these kind of attacks.

Sherrod DeGrippo: RMM is something that we've been seeing a lot in terms of living off land attacks, remote management, and monitoring software. A lot of this is legitimate but it also acts basically as a RAT (a remote access trojan). Again, going back to that theme, right, of you can use this tool for legitimate purposes, or you can use this tool for malicious purposes. And I feel like this particular attack we're seeing lately is a lot of those aspects, right?

Lekshmi Vijayan: Yes. So it is very difficult for us to, you know, categorize it as a legit use and malicious use, because it is widely used everywhere. And in every organization, there will be at least three or four tools that they are using. So I think that makes it very difficult to categorize them as malicious and non-malicious. And it is really on spike, whether it is screen connect or any desk. All of those things are, you know, widely spiking that we are seeing the abuse a lot.

Sherrod DeGrippo: And something else I think that's important to understand with these attacks is that once the threat actor has remote access to a machine, obviously that threat actor can go through and do whatever they want. But in the economy, they can also package these up and sell these as initial access to other threat actor groups.

Lekshmi Vijayan: Yes, yes, that is true.

Sherrod DeGrippo: And then once those threat actor groups sort of take control of these lots of machines that have remote access into them, the initial access broker, the group that got the initial access, doesn't even know what happens to it next.

Lekshmi Vijayan: Exactly.

Sherrod DeGrippo: They just pass it off and sell it. And from there, it could be anything.

Lekshmi Vijayan: Anything, yeah. That is, you know, one serious concern. So we have seen that this tool's being delivered through emails or through phone calls, etcetera. So once the initial part is done, then this access will be sold to other actors who can actually misuse it on a very big level.

Sherrod DeGrippo: I think that's one of the things that it's really important to understand, especially if you look at kind of the graph of crimeware, or even we talk about ransomware. It really is this big ecosystem. It's not just one threat actor group or five ransomware threat actor groups. It's these broad systems of different types of interconnected groups that do certain things. Because with this particular PowerShell, you know, threat, it's malicious PowerShell code into the terminal. That really and of itself isn't a payday. The payday comes when they leverage that access and maybe derive value from the particular machine one at a time or package them all up and sell them.

Lekshmi Vijayan: And that is true. So like I'm part of the defender hunting survey. So we basically have very unique methods of hunting for these admin tools, and we are closely monitoring. Because we know that it could end up in very malicious activities once it is passed over to the other actors, where it could lead to ransomware and other malicious activities. So very critical impacts. So we focus a lot on hunting on the specific TPPs where an admin is installed into a system. So we track it to understand how it is progressing very well, so that, you know, we will not see a big impact on our customers.

Sherrod DeGrippo: Lekshmi Vijayan, senior threat hunter at Microsoft, thank you so much for joining me and helping us understand malicious PowerShell code being seen in the threat landscape today. It was really great to talk with you.

Lekshmi Vijayan: Thanks a lot, Sherrod.

Sherrod DeGrippo: Thanks for listening to "The Microsoft Threat Intelligence Podcast." We'd love to hear from you. Email us with your ideas at tipodcast@microsoft.com. Every episode, we'll decode the threat landscape and arm you with the intelligence you need to take on threat actors. Check us out, msthreatintelpodcast.com for more, and subscribe on your favorite podcast app.