Live Interrogation With Osquery

all right thank you are you all doing today doing good let's hear it you guys doing alright alright thank you to all of the volunteers and the staff and the team that have put together besides Augusta this is definitely one of the events that I looked forward to every year so thanks again to all of you have put work into this my name is Josh Brower I've been when IT since my teens been involved in InfoSec the last ten years and endpoint and network detection as a focus of that as well today we're going to be talking about an app called OS query is anybody heard of OHS query before this talker before yesterday at

the Security Allen conference okay a couple maybe half the room is anybody using OS query on a regular basis especially in your professional work we have a couple great yes the guy with OS pretty shirt thank you all right good so OS query we're gonna be talking in this very short talk we're gonna talk a little bit about what Oscar is in induction to its those of you at the Esso talk we'll just have a little bit of redundancy there but we're gonna talk about how to use O's query the interactive shell to interrogate a Windows 10 system and it will look at how to do that with a demo that hopefully should work today all right

let's get started a lot of interrogation with OS query we can do this we can we have the technology there we go and over here so s query is an open source application it was released by the Facebook team back in 2014 it allows you once you install it on your system it allows you to query your system as if it's a relational database ok for instance if you want to look at all of the users the local users on your system once you install it you can say select star from users semicolon and it gives you all of the users on the system as well as metadata about it ok so it's open source it allows you to use sequel

syntax with it and finally is cross-platform I has a wide range of support for most of the Linux is out there as vsts mac OS and windows lots and lots of support across all the different platforms and let's see how we can use this practically in a recent issue i think it happened about six weeks ago i just come over here and manually tap it there we go anybody familiar with this the mega chrome extension that got compromised about six weeks ago okay the mega chrome extension had about has about two million users and what happened is the latest version was sending credentials for Amazon I think it was github Google and Microsoft okay now if you're in this situation and you

want to know if any of your users on your network on your systems has this compromised extension installed here's what we can do with OS query we can we can look at the different tables that OS query has if you go to the oath query web site query dot IO flash schema there are over two hundred and twenty tables that are available each of these tables allow you to look at a certain part of the system I already mentioned the users table there's also there's a couple of quite a few up there that you can see you can look at under Mac OS X protect you can see the logs related X protect cron jobs lots and lots of different

tables fortunately for us there is a Chrome extensions table it is Chrome others to underscore extensions and you can see the different columns up here these are and there's quite a few others that aren't actually listed although in the upper right hand corner we also have what are the different platforms that are supported for this table and in this case it's all of the major platforms so the build is simple query we're gonna say select the user ID column the name column and the identifier column from the Chrome extensions table now each Chrome extension has a unique identifier if you go to the Chrome extension store look up the Chrome extension the identifier will be within the URL so

you'll be able to easily identify a chrome extension by this identifier okay so when we run this we will get things like this may not be very readable we have to use their ID five on the left hand side the name of the extension version number and the identifier that I just mentioned this is great we got all of our extensions right but if we have lots of users and hundreds of extensions to go through that's not very practical for us to try to manually extract out is a mega extension in there so next we're going to edit our query we're gonna add a filter cloths we're gonna say select the user ID name identifier from the Chrome

extensions table we're gonna say where the identifier is the mega extension identifier and the version number is the compromised version number version three 39.4 okay now I also have that last filter crossed out because when you're in this kind of a scenario you'll also want to run this query from the perspective of okay is there anybody that currently has the compromised version installed but is there anybody that has the extension installed period in case it updates to the compromised version we want to know if anybody has it installed period right so there's two different options there so what we'll do the results we get back would you see a user that has this Chrome extension

installed okay this is good we know somebody has it but we don't know the user name we just have a user ID which is not very helpful for us humans we need to be able to identify this user somehow and this query only gets the Chrome extensions for the current user so we want to get if we're let's say looking at a Windows Terminal Server we want to look at all the different users that are on the box and to do that we're gonna join the Chrome extensions table with the users table this is our final query here we're gonna say select the users that username Chrome extensions named Chrome extensions that version from the users table we're gonna cross

join that with the Chrome extensions table using the common column user ID all right now to those of you who don't have a background in sequel this probably looks like gobbly and doesn't mean much to you and that's okay this actually one of the major benefits of OS query if you want to use those query you don't need to learn an esoteric proprietary query language you can go out and find free low-cost quality training for sequel and you can take what you learned and implement it right there with OS query now those of you who are familiar with with sequel you may be wondering why we're using a cross join in this particular instance it just works more consistently with

this table this is why we're using the cross join alright so this gives us the results of we have Dresden and Everett so that converted the user IDs to the user names and then we do see that we have two mega extensions installed and they're both the compromised version okay is that making sense so far a little bit I'm seeing some I'm seeing quite a few okay good this is good all right so here just before we're gonna go ahead and look at our um our demo let's talk about OS query I know Square D there are two components to OS query the interactive shell and the daemon which do you think is which the

only answer come on yes you got it which one is that the interactive shell yes good job and we need to give this guy prize do we have any prizes okay does he just get to pick okay come on up and once you grab yourself a prize we have three different prizes all right thank you for being the one that answered the question I appreciate it you can just come on right on back and grab yourself something all right so there's two components query I know Square D Oh Square D the Dayman version you run in the background on your endpoints and we'll talk about a little bit later if we have time we can

schedule queries against it and send those logs elsewhere os query the interactive shell we'll see in just a minute we drop it on a system we can interactively use os query to query the system I just need to make a note there because when we talk about os query people talk about one or the other and so I'm always clear what we're doing so right now we're gonna drop into OS query I what do you end up getting that looks pretty good a wireless staff there okay I think I got that one a couple years ago it's good I'm sure it's a new one right a new version all right so let's jump to the

demo here's a scenario you are a tech for a local company and you've been you got a call from a user who says that they go to office 365 in their browser and they're getting a weird error okay so you go to the Windows 10 system you can see you have some apps installed you bring up their browser you can see that if we go to portal office comm which is the entry point to office 365 we get this odd for for website not found error okay so from here we can open up a elevated powershell session we can run query i and this is the interactive shell now before we start just from like

the ten seconds of context i gave you about office portal a portal office comm any ideas on what it could be just want to take some ideas any ideas on you're going to portal office comm we're getting this weird like for for error it's working on other people's machines any ideas on what could be wrong with the system just from that little information yes okay so DNS so dns resolution okay so it's definitely an interesting one to look yes back here I'm sorry okay host file great another one so we have DNS host file yes browser jens okay good good so three any others back there no network access yeah well okay let's pretend that we have network access but

good call okay alright so those are some good ideas right so what we could do we could just go ahead and start looking at those specific areas or the way that I typically like to start is look at what a running process what processes are running on the system so let's start looking at what processes are running and then we'll go from there now if I'm in the interactive shell I can say dot version I can type right I'm always horrible typing I'm going to take an extra five minutes just because of that all right that version so you can see we're running OS query version 3.3 so we're going to query the processes table and if you use a query and you can

remember what columns are available in a particular table you can use this handy little command it says dot schema and the process is table and that will show you the schema for the processes table you can get the column as well as the column type all right so in this case there's a process ID there's a name the path the command line we're going to start with a couple of those we'll say select the process ID and the path from processes typically I would start with process ID path and command line but because we have a lack of space we'll just start with those two and go from there so we run that we can see that

there are quite a few process that process excuse me that are running and it's not really even formatted that great and it's kind of hard to look through right so we should go ahead and filter this down a little bit so we can look at the processes make a little bit easier so we'll say select the process ID path from processes where the path is not like and say anything outside of C windows so this is pretty arbitrary but what I like to do is split when I'm when I'm looking at processes split it in an arbitrary ways so that you can make it easier to filter out and look at the different processes so right now we're

filtering out all the processes that are inside C windows and then we can flip that around and filter for everything that's inside C windows so when we run that we'll see that there are a lot less two chrome processes you know there's a lot less I will say that in my in my preview excuse me in the when I was working on this demo previously I didn't have quite the issue with the screen so it's a little bit harder to read right now because I have it so big but let's just deal with it is there anything right now that we should look at a little closer just from that screen right there okay who said that go ahead and give

this man a prize all right that was something running so we have two books it looks like they we have something running out of C downloads right here okay and of course it's Dresden right so it's our user Dresden and it's running out of C downloads and it's LastPass - installed a fixie now that's not necessarily abnormal it's just maybe an empire here okay it's running out the Downloads you don't see the installer typically when someone installs something there's something that you can see right but you don't see anything on screen it's something it's not nicely suspicious but let's take a look at it so we'll say let's select all the columns select star from the processes

where the process ID equals 5908 5908 and that's going to give us a whole bunch of data that's hard to read so we can switch output modes host query I has a couple different output modes we'll say dot mode line and rerun that query it makes it a bit easier to read we have one result we can read that a little bit easier now this doesn't give us a whole ton of extra information we can see that the path the command line the command line doesn't give us anything really current working directory we do have the user ID which I assume is Dresden because it's running out of The Dresden Downloads folder but we also do have the

have to scroll a little bit do you have the parent there it is right there we have the parent process ID so let's go ahead and walk the stack up a little bit excuse me walk the walk it up just one more well say let's look at the process ID where was that parent is a 3088 okay so when we look at this started that LastPass installer okay and that's explorers so that means more than likely the user opened up Windows Explorer went to downloads and double-click the file and that does tell us something that tells us that it wasn't running from a services or it wasn't being started from a scheduled task or something like that

so that's valuable information but it doesn't really give us at this point anything else if we go back and let's wreck weary our LastPass let's see if LastPass stashed install started anything so we check to see what the parent process is let's see if it started any processes we can do that by going to select star from processes where the parent process is the LastPass have we run that we see that it started a PowerShell session ok and it's running so PowerShell execution policies bypass and it's running this PowerShell script which is little odd alright so let's copy this let's take a look at what this is well quit out of us query little cat

and paste that guy

okay if you guys can see that what do you think that's looking pretty suspicious isn't it anybody raise your hand and tell me what's going on there yes sir okay so it's writing to the SC host file what's it doing what's this doing over here so there's a loop it's checking to see get content it's checking to see if this portal office comm is in the 8c host file if it isn't it adds it or appends it and then it sleeps for 300 seconds okay definitely malicious right so we found something malicious in summary we found this installer that apparently spawned powershell session and is sitting in the background looking to see if portal dot

alphas comm is in Etsy host now we can actually confirm let's go back let's go back and see is there Etsy host table in OS query so we can confirm this now if you're not sure what tables you currently have access to you can say dot tables and you could do a search on Etsy and that shows you that we have a few different tables with Etsy in it and there is an Etsy host so if we say select star from Etsy host again if I can spell run that and we can confirm that there is an entry and Etsy host okay so that is what's going on when when this user is going to portal office

com um this is overriding this is overriding the regular DNS entry and it's directing them to something else all right now keep in mind I think that one of the big powers of a query is that all the commands I just typed are consistent across all of the platforms that you're running on so all if I just typed select star from Etsy host all the processes stuff it's the same whether you're not you're on a Mac OS Linux or Windows which is great because we can't always you can never remember right if I'm on this platform what do I use to access this and so that I think is one of the one of the very

powerful functions of OS query now I think we're just about done how many more minutes do I have five five minutes thank you perfect flip back real quick we have a cheat sheet that I put together it's available up here you notice that there are two hundred plus tables inside os query right and so it can be very overwhelming to unearth to think through what's what should I be looking for how should I use it so we have a cheat sheet on the on the part on the this page right here it's process interrogation we have different techniques as well as queries to run to look at the different processes on your system on the flip side we have

uncovering persistence with Oh squaring and again it is different tables that you can use to uncover common persistence techniques and it's mapped to the attack framework and the different techniques that are there okay so those are available up here they're also available at the applied network defense booth I just recently last week released a query class under Chris Andrews applied network defense at learn Oh square calm you can swing by and get more information as well as pick up the cheat sheet right here okay I think that is it any questions comments or snide remarks unusual yes what's the daemon use for yes great question so what's the daemon used for so you just saw I did interactive stuff

right now imagine doing those same exact type queries across your entire infrastructure so the daemon is used for you deploy to all of your hosts it connects back to a management server there's both commercial and open source management servers you schedule queries to run every so often you can do ad-hoc or you could do scheduled queries you could say select star from users and you can run that every 60 seconds once that initial data is sent all users on the system every time a new user is added to the system it sends a differential log so you only get what was added a new user was added to the system so though that's what the daemon is used for is to

be able to schedule queries and get the logs sent back to your manager and that's what I presented on yesterday at the security unconference is integrating that data into the network data that we get from security onion yes yes log format by default is JSON and it's very very easy to work through yep

yeah that's a great question I haven't seen anything I've deployed it in production a long side are we have a couple different AV solutions and I haven't seen anything it certainly could happen at some point but I haven't seen anything yet we'll go right here yes

yeah so you have to be careful the question was do you get differentials life for unique kids every time a process that started or something like that that's where you have to be cautious on how you're writing your query so that you don't get overwhelmed if you want that kind of data then you then you can select those columns and get so for instance there's a listening ports table okay so I go ahead and select from listening ports every five minutes on these servers if there's a new process that starts I'm certainly going to see that if there's a new process depending on how I write the query I could get a bunch of data or it

could be a little more limited in scope just depends on what you how you want to look at it yes it is when I'm deploying it so it is encrypted through TLS all of the communication is done through TLS and I believe it's mutually authenticated remember correctly with how you can set it up how am i doing on time oh I have yes thank you one minute let me do let me do a I already gave away my my answer to one of my questions um okay who said who said the Etsy host file there you go your pick come on down pick one of these and then next question we'll get the other one I'm sorry that's

horrible yes

it is yeah the question is what kind of impact from a performance perspective does it doesn't have an on the end points I didn't mention this at the very beginning but Oh square was built from the ground up to be high-performance it's built to be able to run on your critical production systems it's also built to be read-only it does not make state changes to your endpoints in core you can have extensions that changes the systems but not in core and so my understanding is Facebook runs this on millions of hosts from them is what they say they run this on large amounts of hosts so we run it again on our critical systems it is very dependent on the

queries you implement and how much data is turning back how you're doing joins obviously can imagine if you're doing some pretty complex sub queries or joins you're going to get performance issues and so it's very dependent on that OS query does have a very very active slap community and so you can certainly ask those types of questions within there all right I think you're right the time thank you all so much appreciate it