[00:15.280 --> 00:18.940]  Hello, everyone. 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:26.080]  This is a recorded video from the Recall EMPASEC team
[00:26.820 --> 00:29.220]  discussing OS Query,
[00:29.220 --> 00:30.700]  continuing on the theme of the introduction
[00:30.700 --> 00:33.260]  of the OpenSoC CTF tools.
[00:33.480 --> 00:36.700]  In this case, Eric is going to be doing the narration,
[00:37.040 --> 00:40.900]  and I'm going to post in the Text Workshops Track 1
[00:41.480 --> 00:43.160]  the link to the YouTube page
[00:43.160 --> 00:45.040]  where they actually have a lot of these recordings
[00:45.660 --> 00:48.880]  already stored up if you want to go watch them and take a look.
[00:49.020 --> 00:51.840]  On that note, we're going to get this recorded started,
[00:51.840 --> 00:55.020]  and everybody have a good view.
[00:59.900 --> 01:02.600]  All right, this is Eric with Recall EMPASEC
[01:02.600 --> 01:07.860]  giving a high-level overview of our new OS Query interface tool.
[01:08.280 --> 01:10.740]  So as you may have known from previous iterations
[01:10.740 --> 01:12.280]  of our network defense range,
[01:12.280 --> 01:14.380]  we relied on a tool called Collide
[01:14.380 --> 01:16.260]  for interacting with OS Query.
[01:16.260 --> 01:19.040]  And while Collide is a fantastic interface,
[01:19.040 --> 01:22.220]  the one issue that we had was it doesn't scale well
[01:22.220 --> 01:24.700]  under heavy load when we have a lot of participants
[01:24.700 --> 01:25.880]  in the environment.
[01:25.880 --> 01:28.740]  And also, it doesn't give much to folks
[01:28.740 --> 01:32.000]  that are new to writing queries in OS Query.
[01:32.020 --> 01:34.840]  It sort of expects that you're already pretty familiar
[01:34.840 --> 01:36.280]  with SQL syntax.
[01:36.560 --> 01:38.740]  And so what we wanted to do was build an interface
[01:39.400 --> 01:42.080]  that would guide you through building your query
[01:42.540 --> 01:46.480]  without having to know the actual syntax behind the scenes.
[01:46.720 --> 01:49.240]  So I'm going to walk you through crafting a query,
[01:49.520 --> 01:51.320]  a really simple one, to kind of give you an idea
[01:51.320 --> 01:52.960]  of how to use this tool.
[01:53.040 --> 01:55.520]  So you'll notice the very first option we have here
[01:55.520 --> 01:57.860]  on the Query Builder is,
[01:57.860 --> 02:00.760]  which operating system am I wanting to interrogate?
[02:00.940 --> 02:02.200]  And the reason we select this is
[02:02.200 --> 02:06.000]  because it will then populate the appropriate systems
[02:06.000 --> 02:09.040]  in the environment that fall into that operating system.
[02:09.040 --> 02:10.360]  So I've selected Windows,
[02:10.360 --> 02:11.840]  and what it's done is it's returned
[02:11.840 --> 02:13.340]  all the systems in the environment
[02:13.340 --> 02:15.800]  that are running the Windows operating system,
[02:15.800 --> 02:17.000]  which is quite a few.
[02:17.440 --> 02:20.000]  Now I can choose to query any one
[02:20.000 --> 02:23.160]  or multiple systems in this list,
[02:23.160 --> 02:25.400]  you know, by simply selecting one
[02:25.400 --> 02:29.300]  or holding Control and maybe selecting,
[02:29.300 --> 02:30.560]  I'm sorry, I'm on a Mac,
[02:30.560 --> 02:32.600]  so my Control doesn't work the way it should,
[02:32.600 --> 02:35.740]  but holding Control on a Windows system
[02:35.740 --> 02:40.020]  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:54.960]  There's great resources out there
[02:54.960 --> 02:56.960]  to help understand all these different tables
[02:56.960 --> 02:58.680]  and what's contained within them.
[02:58.680 --> 03:00.820]  If you go to osquery.io,
[03:00.820 --> 03:02.940]  there's a schema that will help you understand
[03:02.940 --> 03:04.360]  what all these tables are.
[03:04.360 --> 03:07.300]  But for simplicity, I'll just pick a very simple
[03:07.300 --> 03:10.480]  and very straightforward table here called File.
[03:10.860 --> 03:13.520]  So you notice as soon as I selected File here,
[03:13.520 --> 03:16.020]  over here on the right-hand side, some examples popped up.
[03:16.020 --> 03:18.780]  These are sample queries that are commonly used
[03:18.780 --> 03:22.120]  when the File table is being interrogated.
[03:22.120 --> 03:24.140]  So you can see here where it's showing an example
[03:24.140 --> 03:28.020]  where I'm selecting all columns from the File table
[03:28.020 --> 03:30.920]  where the path is Etsy password, right?
[03:30.920 --> 03:32.980]  Or where the path is simply Etsy
[03:32.980 --> 03:34.880]  or where it's Etsy wildcard
[03:34.880 --> 03:38.020]  because a percent sign is a wildcard in SQL.
[03:38.320 --> 03:40.240]  So anyway, I can use that as inspiration
[03:40.240 --> 03:42.620]  as I continue to build my query here.
[03:42.640 --> 03:44.780]  So the next thing that I'm going to have to figure out
[03:44.780 --> 03:47.540]  is which columns from the File table
[03:47.540 --> 03:50.520]  do I want returned by my query?
[03:50.860 --> 03:53.080]  And as you can see here, we're giving you some advice
[03:53.080 --> 03:55.240]  that a safe bet is usually just to say,
[03:55.240 --> 03:57.040]  just bring back all columns.
[03:57.560 --> 03:59.900]  Until I know what's in those columns,
[03:59.900 --> 04:03.160]  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:08.720]  Now, the next part is where I'm going to filter down
[04:08.720 --> 04:10.160]  the results that are returned to me
[04:10.160 --> 04:11.480]  because maybe there's a good chance
[04:11.480 --> 04:14.320]  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:23.480]  So what I'll say is I only want to see files
[04:24.180 --> 04:34.720]  where the path is like C user's percent sign,
[04:34.720 --> 04:39.460]  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:50.960]  Select star, all columns, from file where path like,
[04:50.960 --> 04:53.100]  and then you can see a path with a wildcard here.
[04:53.100 --> 04:55.340]  So I've just basically kind of copied that example
[04:55.340 --> 05:02.560]  and made it my own, where path like C user's wildcard slash downloads.
[05:02.600 --> 05:05.940]  So when I click run query, scroll up here,
[05:05.940 --> 05:07.860]  and you can see this little indicator showing me
[05:07.860 --> 05:11.240]  that that query has been issued out to ACC 1 through 4.
[05:11.260 --> 05:13.560]  And you can also see that it's giving me a preview
[05:13.560 --> 05:17.880]  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:24.360]  You notice there are no results, which is just simply telling me
[05:24.360 --> 05:27.520]  that none of those systems had anything in their downloads directories,
[05:27.520 --> 05:28.460]  and that's fine.
[05:28.560 --> 05:30.480]  So maybe what I'll do now is I'll tweak the query
[05:30.480 --> 05:33.460]  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:44.080]  So technically, this is going to enumerate all the users on each of these systems
[05:44.080 --> 05:49.420]  based on the presence of a user profile directory in that location.
[05:52.890 --> 05:57.170]  All right, so starting with the first system that returned, ACC 04,
[05:57.170 --> 05:58.930]  you can kind of scroll through the results here
[06:00.630 --> 06:03.850]  and see that it's showing me the directory that was returned,
[06:03.850 --> 06:08.630]  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:15.610]  So that's kind of, you know, a unique approach to maybe identifying
[06:15.610 --> 06:18.590]  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:29.930]  Now, let me caution you with something that you need to avoid
[06:29.930 --> 06:33.110]  when writing queries with osquery.
[06:33.390 --> 06:37.370]  Another popular table is, for instance, the hash table.
[06:37.370 --> 06:39.810]  Hash is very much like the file table,
[06:39.810 --> 06:43.490]  except instead of just simply listing the files in a location,
[06:43.490 --> 06:48.570]  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:02.070]  And so osquery is not going to simply allow,
[07:02.070 --> 07:06.250]  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:14.590]  I'll still return all columns, that's fine,
[07:14.590 --> 07:18.970]  but I'm going to have to be very specific in my query here.
[07:18.970 --> 07:23.550]  Where path, like, I'm going to want to dive down deeper
[07:23.550 --> 07:30.730]  and know a little closer to the goods of the files that I actually want to hash.
[07:30.810 --> 07:33.110]  Now what you do not want to do,
[07:33.110 --> 07:38.790]  in many of these tables, you do not want to use leading wildcards.
[07:39.170 --> 07:43.090]  So for instance, if I just wanted to hash any file on the file system
[07:43.090 --> 07:47.070]  with the word malware in it, that might seem like a clever approach,
[07:47.070 --> 07:50.070]  but osquery is not going to let me get away with this.
[07:50.070 --> 07:52.950]  And I'll show you, for example, if I run this query,
[07:52.950 --> 07:57.130]  what's going to happen is it's likely just simply going to fail to return at all.
[07:57.510 --> 08:01.770]  And this can often lead you to believe that maybe osquery isn't working properly,
[08:01.770 --> 08:06.290]  or, you know, and so what I'm trying to show you here is, you know,
[08:06.290 --> 08:08.850]  if you get zero results, it could mean that
[08:08.850 --> 08:11.790]  maybe there simply is nothing that matches your query,
[08:11.790 --> 08:15.530]  or maybe you need to refactor your query to be more specific,
[08:15.530 --> 08:18.750]  because the osquery agent is not going to allow you to make
[08:18.750 --> 08:22.090]  really, really greedy queries that would cause a performance issue
[08:22.090 --> 08:24.110]  on the system down below.
[08:25.150 --> 08:27.570]  Alright, so that's a brief introduction.