TryHackMe! Finding Computer Artifacts with osquery

there are a whole lot of ways to interrogate data and information from an endpoint a device from a machine from a computer and one of the coolest ways to do that is to treat the whole operating system like a database and in this video we're going to play with that I want to showcase OS query within a try hack me room or exercise or lab or environment that we can play with it and it's already all spun up and installed for us I'll include a link in the video description but if you are online on tryhackme.com and you move into the learn section you can take a look at some of the different learning paths whether or not you're getting into offensive security or defensive security red teaming or blue teaming all the things you could learn to get into cyber security in this video we're gonna go explore a component of the sock level 1 learning path and it teaches you all about becoming a security analyst whether or not you're going to be junior intermediate senior Advanced all of this is a component of the career now I do want to Speed it run and move on ahead because a lot of the Cyber defense framework stuff and some of the fundamentals I'm sure a lot of you are already familiar with some of the tools that you might dig into for cyber threat intelligence Yara open CTI misp Etc the network security and traffic analysis one is also phenomenal I do love a whole lot of networking but truthfully I'm a big fan of the endpoint I love host forensics I like digging into the machine itself so hey let me take a look at OS query but I do want to show you hey you could also zoom in to seam whether it's Splunk whether it's elk you could get into some other dfir digital forensics and in response whether or not it's new artifacts other tools memory forensics yada yada yada anyway I'm sorry I'm rambling let me go dig into OS query now try hack me streamlines all of this for us and gives us a whole lot of info gives us a little bit to help with our learning and our education here check it out OS query is an open source agent that was created by Facebook in 2014. it converts the operating system into like a database that allows you to basically ask questions like you would interact with SQL queries or that structured query language but that way you could list process Services user accounts even files all incredible things that could really really help out instant responders security analysts threat Hunters detection Engineers whatever it might be over on the detection engineering side so much stuff all the different things you could get into within blue team or defensive cyber security and Os query is one tool in our toolkit so hey uh say we have read that we can mark that as complete and now let's go ahead and move on to spin up our machine now this is awesome because again try hack me will already have this installed and ready for us we can just kind of hit go click on that nice green start machine and it spins it all up even side by side in the web interface in the environment so it already just gives us access to this box it's going to take a little bit for this to spin up but it's still just super convenient hey we already have it ready for us and we can keep reading right on the other side okay our machine is coming together here we got some life in here they do give us credentials over on the left hand side but it looks like it's already set to Auto log in and we are just ready to roll okay okay take a look now we have a full-blown Windows desktop all within our browser that is so slick and uh take a look it looks like we will get started with the Powershell terminal pinned on the taskbar that is that blue icon down below and we'll enter the command OS query I to enter the interactive mode of os query let me make this text size a little bit bigger okay now we are within our Powershell prompt and I will enter OS query I to get into interactive mode just as it suggests and we should be ready to rock takes just a moment for it to spin up and it says we're using a virtual database if you need help type dot help we can mark that as completed and then we can move on to the next task oh okay take a look this is how we will work with OS query I or the interactive rendition of os query again that dot help looks like the syntax to be able to list out all the things that it might be able to do and a lot of this is word wrapped and forgive me for the giant text but we can see things like dot tables to list tables in the database or we could get different types or different modes or all the things that we might want to be able to do with OS query and of course this is all explained in the definitions on the left hand side and note the documentation states that there are meta commands and they are prefixed with a DOT or a period now note just as we mentioned dot tables will allow us to look through any of the tables in the database and if I scroll down here note hey let me get back to that we could just dot tables something like processes or just process on its own and to list all of the tables that include that as like a substring in their name so processes is an option process open sockets or process memory map all the different things we could drill down into and explore just by kind of querying okay what tables exist within OS query they do this for user and if you wanted to actually drill down house too how is that table made What fields or data or information do you actually include in that table well we could use dot schema now let's do that on the processes table that we just learned about and that will tell me all of the things are included and it's a little bit of a mess here I'll note that hey you've got all the things like the path to the process maybe the current working directory maybe the user time maybe disk bytes read or protection type all the different types included with that whether or not it's a string or an integer etc etc now this is very SQL like right it's using the same sort of commands that you might use if you're working with a natural database like MySQL MySQL sqlite mssql transact SQL postgres whatever you want select is probably all that you'll end up using because we are just going to be querying information about the database you probably wouldn't insert anything you're just trying to learn about the file system and the operating system right but you could select any of the given columns that you want from a table from processes from users Etc one of the important things here though is that they do get into how you might display data now again in sort of a cramped vertical setup that we're in right now uh maybe it's not the best to try and display it out like a table with ASCII art and all that we could actually check out different modes if we wanted to view oh maybe a mode in list format that way it'll be a little bit easier to read and look through what we're actually exploring and seeing in the database here couple questions hey how many tables are returned when we query table process in the interactive mode of os query we ran that just a moment ago that answer is three from what I remember looking at the schema of the processes table which column displays the process ID for the particular process well we did that a moment ago uh what is it PID that's probably what we're going to be up against yeah oh I see it right here just underneath us we're running the command PID for process ID let's enter that examine the help command how many output display modes are available for the dot mode command well we saw that just a moment ago it was a little bit messy and hard to read but looks like okay we have CSV we have column line list and pretty so that's what five yep let's try that cool now we can dig into the schema documentation let me scroll up here and it actually links us and I really really appreciate that it tells us hey go check out the real documentation actually go see all the manual instructions for how you might be able to learn more about OS query now at the time of writing that lab and that environment actually had the OS query version as Dot 5.5.1 and you might be able to see it I realize my face in the way but actually in the top right you can toggle that to the current which is 5.82 at the time recording but for the focus of this lab exercise environment let's remain on 5.5.1 they actually said hey go ahead and change this to uh tables that are compatible with different architectures different operating systems excuse me right now it'll probably just show all platforms or at least okay everything that might be an option across maybe Mac maybe Linux maybe Windows whatever you're up against but if we just set it to Windows as it is what we're looking at inside of our virtual machine that gives us about 106 tables all the things that we might be able to actually learn about inside the documentation not just that dot help command but that way we can get a description for every single column for every single kind of table and really see everything that's available to us now try hackme's explanation Nation also goes through this and just as I toggled it to Windows they explained that just as well and if we're actually taking a look here in that version 5.5.1 how many tables are returned when we select both Linux and windows well let's go take a look I will scroll back up to the top and let's select Linux alongside windows and that gives me 56 tables I can see in the top left here so let's enter that looking good how about for Mac okay toggle that let's get to Mac that's 180 I can see in the top left let's enter that in the Windows operating system which table is used to display the installed programs oh okay so hey uh having fun with this it gives me a little bit of chance to explore maybe uh reinforce what I'm learning here maybe uh get Hands-On some education application based practical way let me go ahead and control F hit Ctrl F on my keyboard so that I can search and find in this page I do want to look for installed programs no uh don't see any hits for that how about just programs uh oh okay represents products as they are installed by Windows installer is that what I'm looking for let's try that programs a good way to sanity check good good okay what column that contains the registry value within the registry table well let's toggle back to our documentation here let me control F4 registry and now we can see again in the description these are all the different things that the columns might contain their type and of course the description looks like the data is what contains the contents of a registry value so the answer there I'm going to assume matching those four stars is data perfect perfect now let's go ahead and create some SQL queries again uh everything that we were just kind of alluding to is it will use the select statement we want to end up querying things from the operating system in the file system everything on this device and endpoint but that's really all it takes so we could try to I don't know select all like a star from programs and I realize my face is in the way so let me bring this line down a little bit let's go and select star from programs and they use limit one here so only one result will be returned note that you do have to include a semicolon here if you don't it'll kind of give you a secondary prompt like it's it's waiting and it's asking for more input from you you will always have to include a semicolon to end the command or the line that you're trying to run so that's all that it takes and my mode did not seem to stick did I uh get that wrong mode lines how about that let's try and run that one more time I'm using the up arrow on my keyboard to be able to see that and I'll include the semicolon here there we go now we have a little bit more of a better and easily red display okay so hey we just got the very first with the limit one only return one result for programs that we wanted to query now it's different from their example they expressed that it very well could be but rather than using the star we could actually go ahead and get any specific column or field that we want based off of their name they showcase an example here with okay version install location install date all these other columns that we saw in the schema of the table looking good now we could go ahead and select the count a little bit of a function that we might wrap around with all of the results from our programs table again I'll use that semicolon looks like I have 19 present on this device here and we can actually drill down and actually set filters we could use a where Clause to say look I only want to return results I only want to get entries and records that match where a username or a column whatever field we want is actually equal to any value any criteria that we suggest here they're looking for users where their username is James setting the equals there and they have a bunch of these conditional operators that are listed out and you might even be able to do like substring searching if you use like one of the special keywords that might allow us to say hey is something like this does it contain the word uh user or anything we kind of really want to match they use this with a wild card that is no longer an asterisk when we're working inside of a string it's actually a percent sign so that's something we can keep in mind and they showcase some examples for that and of course I'm sorry if I'm just speed running maybe a little bit of Crash Course through this of course I really really encourage you to go fire this up and try it yourself that's the fun of try hack me is that you can do this at your own pace and really get Hands-On and explore so look at it all the stuff that they're drilling down into and stuff that we might already be familiar with if you're really always working in databases to begin with so hey it was asking how many programs are installed on this host we saw 19 from doing that just a moment ago oh submit that what is the description for the user James ooh okay so here we can use that where Clause let's go ahead and select description I'm going to assume is a column name from the users table where the username is equal to James and note that I'm using single quotes to denote a string there I'll go ahead and hit enter and it tells me it is a creative artist okay so let's go ahead and enter that as our answer perfect now here they actually give us the queries we can select path key name from registry where key is equal to hkey users we're trying to find the full Sid or the security identifier descriptor thing of the user with our ID 1009. uh okay so let's try to select there I've typed out uh what they suggested there and I'm not seeing anything come through did I have a typo or is it just sensitive on the strings let me use these single quotes there how about that oh my goodness I forgot the other the one that I actually need to have a k for I just can't read or write apparently okay so here are a couple that have including the whole Sid uh and it just ends in that 1009 which should be our rid or our rid so now we can copy and paste that and select that as our Sid I'll enter that there looking good all right let's see if I can actually uh type and actually include the keys that we need and the next question let's look for our IE extensions oh man I'm trying really hard my brain is working for this what is the Internet Explorer browser extension installed on this machine really really cool that it actually has this as a a database or table within OS query we can see the path here and I'm going to assume that that is what it is looking for as the answer to this because it does note a colon after the asterisk so this is probably IE frame dll we can go and submit that and that looks good let's try another one where we are going to select the name and install location from programs where name is like note that operator there and we're going to be using our string so if we use our wild cards that has to be a percent sign and we want Wireshark as what we are trying to match as sort of a substring including that so there we go we have a name of Wireshark version 3.6.8 64-bit and that t is falling off but I'll copy that line and spit that back in to submit it looking good we have completed that task let's move on to the very very last one and now we need to know which Table stores the evidence of process execution in the Windows operating system oh I want to go take a look at the documentation here let me control F for like execution application compatibility cache contains artifacts of execution is that right shim cash yeah let's try that how about shim cash any luck nope I got it wrong what is this user assist registry key that tracks in a user executes an application from Windows Explorer uh is it user assist user assist yay okay cool that's fun that is to try hack me like ooh just figure it out try to learn something new and really be able to sanity check check our answers here I like that one of the users seems to have executed a program to remove traces from disk what is the name of that program uh well we have the path application file path and the last execution time and count and Sid in here so uh path should include the file name right let's go ahead and select path from user assist how about that clear the screen so we have a little bit more real estate let's select path from user assist semicolon there and oh goodness there's a lot um trying to scroll up here oh James has a weird one disk wipe disk wipe diskwipe that e is trailing off diskwipe.exe that sure sounds like uh Hey remove traces from disk let me paste in diskwipe and that is correct let's search for a VPN installed on this host okay so we can do some critical thinking we know how to do this right we could select select uh what is it all let's just try to get everything from our programs where we know things are installed where the name is like again a strings here with percent signs as a wild card and VPN I want to have inside of the name how about that um can I just use that with the semicolon there oh proton proton VPN we can see that proton VPN is the name of the VPN that they have installed let's see if that is the correct answer I'm going to assume it is looking good how many services are running on this host well we know the count syntax really easy now right let me select count all uh and we want that to be from Services probably a table that works fine yep 214. dunzo a table auto exec contains a list of executables that are automatically executed on the target machine or like startup entries there seems to be a batch file that runs automatically what is the name of that batch file with its file extension okay so let's just select select name is name a thing that we could use from auto exec let's just try it let's explore um taking a little bit of time to return the results on that one is it just slow hit and enter maybe auto exec is just a giant table oh yep okay just pulling stuff down oh there we go okay name is bat startup.bat I'm gonna assume that's it right batch script that would run on Startup probably a bad startup.bat submit what is the full path of the file found in the above question that is the last in the list um could we just get path from there I think that's worth an educated guess select path from auto exec where name is equal to bat startup.bat that's a fine Syntax for us right we can try that cruising through it I'm sure we'll get a result in just a second here okay cool looks like uh okay the path is included uh and because there are multiple entries right we can see it was listed twice in the names it actually gave us two results we could use our limit one there if we only wanted one result rather than the duplicate but take a look uh path is this thing program data Microsoft Windows start menu program startup batch crop submit what my answer is incorrect what what am I forgetting something oh oh wait a second it means last in the list as in like the real legitimate actual location not the program data one that's put in startup but like the original file itself oh okay cool so let me try to use this one submit and there we go hey we did it cool we absolutely crushed this room this exercise this activity to learn and play with OS query and hey you know if I may I think try hack me made that super easy and super fun because look it gave us the exercise it gave us everything that we might be able to play with and learn in an environment that is already set up for us and then a couple of things to Tinker with a couple things to try different tasks and try to interrogate some information that's already present like the data is there we just get to have fun and learn and do some of our own self learning and training if you haven't you should totally go check out try hack me again I'll have a link in the description and really appreciate them helping sponsor this video and if you enjoyed this video please do some of the YouTube algorithm stuff like comment subscribe it helps the channel grow if you're up for hey other patreon PayPal links Below in the description thanks so much I hope you enjoyed this video see in the next one take care everyone

Channel: John Hammond

Views: 30,341

Keywords: cybersecurity, learn, programming, coding, capture the flag, ctf, malware, analysis, dark web, how to learn cybersecurity, beginners

Id: YpmGZseJbJY

Length: 20min 4sec (1204 seconds)

Published: Mon Jul 03 2023