 Hello everyone, welcome back to DefCon 28, Blue Team Village. We're going to start our next talk. This is a recorded video from the Reconnaissance Team discussing OS Query, continuing on the theme of the introduction of the Open Sock CTF tools. In this case, Eric is going to be doing the narration, and I'm going to post in the Text Workshop's Track 1 the link to the YouTube page where they actually have a lot of these recordings already stored up if you want to go watch them and take a look. On that note, we're going to get this recorded started, and everybody have a good view. Right, this is Eric with Recon Infosec, giving a high-level overview of our new OS Query interface tool. So as you may have known from previous iterations of our network defense range, we relied on a tool called Collide for interacting with OS Query. And while Collide is a fantastic interface, the one issue that we had was it doesn't scale well under heavy load when we have a lot of participants in the environment. And also, it doesn't give much to folks that are new to writing queries in OS Query. It sort of expects that you're already pretty familiar with SQL syntax. And so what we wanted to do was build an interface that would guide you through building your query without having to know the actual syntax behind the scenes. So I'm going to walk you through crafting a query, a really simple one, to kind of give you an idea of how to use this tool. So you'll notice the very first option we have here on the Query Builder is which operating system am I wanting to interrogate. And the reason we select this is because it will then populate the appropriate systems in the environment that fall into that operating system. So I've selected Windows, and what it's done is it's returned all the systems in the environment that are running the Windows operating system, which is quite a few. Now I can choose to query any one or multiple systems in this list, by simply selecting one or holding Control and maybe selecting, I'm sorry, I'm on a Mac, so my control doesn't work the way it should. Holding Control on a Windows system or just holding Shift and selecting multiple systems, I can query, say, ACC01 through 04, just like this here. Now the next option is which table I want to query. So tables are another topic. There's great resources out there to help understand all these different tables and what's contained within them. If you go to osquery.io, there's a schema that will help you understand what all these tables are. But for simplicity, I'll just pick a very simple and very straightforward table here called File. So you notice as soon as I selected File here, over here on the right hand side, some examples popped up. These are sample queries that are commonly used when the File table is being interrogated. So you can see here where it's showing an example where I'm selecting all columns from the File table where the path is Etsy password, right? Or where the path is simply Etsy or where it's Etsy wildcard because a percent sign is a wildcard in SQL. So anyway, I can use that as inspiration as I continue to build my query here. So the next thing that I'm going to have to figure out is which columns from the File table do I want returned by my query? And as you can see here, we're giving you some advice that a safe bet is usually just to say, let's bring back all columns until I know what's in those columns. Then I can get more specific later on with my query. So just bring back all columns is usually a safe bet. Now the next part is where I'm going to filter down the results that are returned to me because maybe there's a good chance. I don't want all the files on the file system. Well, I hope not because that's way too large of a query. So I'm going to have to be more specific than that. So what I'll say is I only want to see files where the path is like see users percent sign. So I can hit every user in the user's directory downloads. So if you notice, I'm simply just taking inspiration from up here, right? Select star all columns from file where path like and then you can see a path with a wildcard here. So I've just basically kind of copied that example and made it my own where path like see users wildcard slash downloads. So when I click run query, scroll up here and you can see this little indicator showing me that that query has been issued out to ACC one through four. And you can also see that it's giving me a preview of the back end syntax of what that query I just wrote looks like. All right. So we've got our responses back from ACC one through four. And you notice there are no results, which is just simply telling me that none of those systems had anything in their downloads directories. And that's fine. So maybe what I'll do now is I'll tweak the query in a way that I know will bring back some results. I'll just simply say just show me everything and see users, right? I've got my wildcard here on the end, run query. So technically, this is going to enumerate all the users on each of these systems based on the presence of a user profile directory in that location. All right. So starting with the first system that returned ACC or you can kind of scroll through the results here and see that it's showing me the directory that was returned see users all users and award default public obviously the root directory and then also this help desk user as well. So that's kind of, you know, a unique approach to maybe identifying all the users that have interactively logged on to this system, but really just to show you a very simple query syntax here that you can use. Now, let me caution you with something that you need to you need to avoid when when writing queries with those query. Another popular table is, for instance, the hash table hashes very much like the file table, except instead of just simply listing the files in a location, it will provide the shot 256 shot one MD five hashes of all those files as well, which is a very useful capability, especially when you're performing a DFIR. But the thing is is calculating hashes is a very expensive operation. And so always query is not going to simply allow it's not going to allow you to request hashes of every file in the file system. So you always need to be as specific as possible. So especially when using say the hash table, I'll still return all columns. That's fine. But I'm going to have to be very specific in my, my query here where path like, you know, I'm going to want to dive dive down deeper and know it kind of a little closer to, you know, the goods of the files that I actually have. I actually want to to hash. Now what you do not want to do. In many of these tables, you do not want to use leading wildcard. Right, so for instance, if I just wanted to hash any file on the file system with the word malware in it. That might seem like a clever approach, but OS query is not going to let me get away with this. And I'll show you, for example, if I run this query, what's going to happen is it's likely just simply going to fail to return at all. And this can often lead you to believe that maybe OS query isn't working properly or you know. And so what I'm trying to show you here is, you know, if you get zero results, it could mean that maybe there simply is nothing that matches your query, or maybe you need to refactor your query to be more specific. Because the OS query agent is not going to allow you to make really, really greedy queries that would cause a performance issue on the system down below. All right, so that's a brief introduction.