Hello, everyone. Welcome back to DEF CON 28, Blue Team Village.
We're going to start our next talk.
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.
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.
On that note, we're going to get this recorded started,
and everybody have a good view.
All right, this is Eric with Recall EMPASEC
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,
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,
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,
just 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 bringing 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 C user's 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 C user's 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 1 through 4.
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 1 through 4.
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 in C 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 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,
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 avoid
when writing queries with osquery.
Another popular table is, for instance, the hash table.
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.
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 osquery 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 query here.
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.
Now what you do not want to do,
in many of these tables, you do not want to use leading wildcards.
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.
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 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,
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.
Alright, so that's a brief introduction.