WEBVTT 00:15.280 --> 00:16.080 Hello, everyone. 00:16.080 --> 00:18.940 Welcome back to DEF CON 28, Blue Team Village. 00:18.940 --> 00:21.100 We're going to start our next talk. 00:21.100 --> 00:33.260 This is a recorded video from the Recall EMPASEC team discussing OS Query, continuing on the theme of the introduction of the OpenSoC CTF tools. 00:33.480 --> 00:48.880 In this case, Eric is going to be doing the narration, and I'm going to post in the Text Workshops 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. 00:49.020 --> 00:55.020 On that note, we're going to get this recorded started, and everybody have a good view. 00:59.900 --> 01:07.860 All right, this is Eric with Recall EMPASEC giving a high-level overview of our new OS Query interface tool. 01:08.280 --> 01:16.260 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. 01:16.260 --> 01:25.880 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. 01:25.880 --> 01:32.000 And also, it doesn't give much to folks that are new to writing queries in OS Query. 01:32.020 --> 01:36.280 It sort of expects that you're already pretty familiar with SQL syntax. 01:36.560 --> 01:46.480 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. 01:46.720 --> 01:52.960 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. 01:53.040 --> 02:00.760 So you'll notice the very first option we have here on the Query Builder is, which operating system am I wanting to interrogate? 02:00.940 --> 02:09.040 And the reason we select this is because it will then populate the appropriate systems in the environment that fall into that operating system. 02:09.040 --> 02:17.000 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. 02:17.440 --> 02:40.020 Now I can choose to query any one or multiple systems in this list, you know, 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, but holding Control on a Windows system or just holding Shift and selecting multiple systems, 02:40.020 --> 02:46.440 I can query, say, ACC01 through 04 just like this here. 02:46.600 --> 02:51.120 Now the next option is which table I want to query. 02:51.140 --> 02:53.480 So tables are another topic. 02:53.480 --> 02:58.680 There's great resources out there to help understand all these different tables and what's contained within them. 02:58.680 --> 03:04.360 If you go to osquery.io, there's a schema that will help you understand what all these tables are. 03:04.360 --> 03:10.480 But for simplicity, I'll just pick a very simple and very straightforward table here called File. 03:10.860 --> 03:16.020 So you notice as soon as I selected File here, over here on the right-hand side, some examples popped up. 03:16.020 --> 03:22.120 These are sample queries that are commonly used when the File table is being interrogated. 03:22.120 --> 03:30.920 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? 03:30.920 --> 03:38.020 Or where the path is simply Etsy or where it's Etsy wildcard because a percent sign is a wildcard in SQL. 03:38.320 --> 03:42.620 So anyway, I can use that as inspiration as I continue to build my query here. 03:42.640 --> 03:50.520 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? 03:50.860 --> 03:57.040 And as you can see here, we're giving you some advice that a safe bet is usually just to say, just bring back all columns. 03:57.560 --> 04:03.160 Until I know what's in those columns, then I can get more specific later on with my query. 04:03.160 --> 04:05.760 So just bringing back all columns is usually a safe bet. 04:06.440 --> 04:14.320 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. 04:14.320 --> 04:18.320 Well, I hope not because that's way too large of a query. 04:18.320 --> 04:20.600 So I'm going to have to be more specific than that. 04:20.600 --> 04:39.460 So what I'll say is I only want to see files where the path is like C user's percent sign, so I can hit every user in the user's directory, downloads. 04:40.380 --> 04:44.960 So if you notice, I'm simply just taking inspiration from up here, right? 04:44.960 --> 04:53.100 Select star, all columns, from file where path like, and then you can see a path with a wildcard here. 04:53.100 --> 05:02.560 So I've just basically kind of copied that example and made it my own, where path like C user's wildcard slash downloads. 05:02.600 --> 05:11.240 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 1 through 4. 05:11.260 --> 05:17.880 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. 05:18.240 --> 05:21.280 All right, so we've got our responses back from ACC 1 through 4. 05:21.280 --> 05:28.460 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. 05:28.560 --> 05:33.460 So maybe what I'll do now is I'll tweak the query in a way that I know will bring back some results. 05:33.460 --> 05:37.380 I'll just simply say, just show me everything in C users, right? 05:37.380 --> 05:40.180 I've got my wildcard here on the end, run query. 05:40.460 --> 05:49.420 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. 05:52.890 --> 06:08.630 All right, so starting with the first system that returned, ACC 04, you can kind of scroll through the results here and see that it's showing me the directory that was returned, C users, all users, an award, default, public, obviously the root directory, 06:08.630 --> 06:10.650 and then also this help desk user as well. 06:10.690 --> 06:18.590 So that's kind of, you know, a unique approach to maybe identifying all the users that have interactively logged on to this system. 06:18.590 --> 06:25.010 But really just to show you a very simple query syntax here that you can use. 06:25.190 --> 06:33.110 Now, let me caution you with something that you need to avoid when writing queries with osquery. 06:33.390 --> 06:37.370 Another popular table is, for instance, the hash table. 06:37.370 --> 06:48.570 Hash is very much like the file table, except instead of just simply listing the files in a location, it will provide the SHA-256, SHA-1, MD5 hashes of all those files as well. 06:48.570 --> 06:54.110 Which is a very useful capability, especially when you're performing a DFIR. 06:54.370 --> 06:59.190 But the thing is, is calculating hashes is a very expensive operation. 06:59.230 --> 07:06.250 And so osquery is not going to simply allow, it's not going to allow you to request hashes of every file in the file system. 07:06.250 --> 07:09.470 So you always need to be as specific as possible. 07:09.470 --> 07:12.270 So especially when using, say, the hash table. 07:12.270 --> 07:18.970 I'll still return all columns, that's fine, but I'm going to have to be very specific in my query here. 07:18.970 --> 07:30.730 Where path, like, I'm going to want to dive down deeper and know a little closer to the goods of the files that I actually want to hash. 07:30.810 --> 07:38.790 Now what you do not want to do, in many of these tables, you do not want to use leading wildcards. 07:39.170 --> 07:50.070 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 osquery is not going to let me get away with this. 07:50.070 --> 07:57.130 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. 07:57.510 --> 08:15.530 And this can often lead you to believe that maybe osquery 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, 08:15.530 --> 08:24.110 because the osquery agent is not going to allow you to make really, really greedy queries that would cause a performance issue on the system down below. 08:25.150 --> 08:27.570 Alright, so that's a brief introduction.