Amanda: Hey everyone, It’s Amanda Mahan with Oxygen Forensics’ training team. Welcome back to Oxygen Forensic Detective’s weekly knowledge nuggets. This one’s going to be on our SQLite database viewer and our query builder that’s built into the tool.
Hey guys. So let’s first navigate into our file section, into whichever device you have extracted and pulled over and parsed with Detective, and then tab over to your databases and look for that SQLite database that you need to dig down into. Go ahead and use your find text box to look for that specific database that you’re looking for.
So I’m looking for the address book, database address, book.sqlite.db. We can open up the external viewer or we can open up the internal viewer. So this is new, in that we can now use a query builder inside of the internal viewer, but first I’m going to show you the external one. So we’ll right click on it and open SQLite viewer. And here we go. This is the external viewer.
We can now go through these tables. And maybe we’re validating information, maybe we’re pulling data from a database that did not parse, or we are pulling data from — a database from — maybe an updated application. And you want to validate that what is being parsed is true. You just need to get in here and do some validation.
Come inside of the database, right click for that external viewer, and then thumb through your tables here. So we’re going to look at ABPerson. Immediately, I see some information that perhaps I want to take this out of the tool. I need it in a report. I need to export this. First, last name. We’ve got organization notes, lots of good information here. Creation and modification date of these contacts. Now we’re looking at people saved in an address book.
So now I want to show you what this looks like in the internal viewer. So I’m going to go back to Detective and I want to simply double click on that database. And then you are looking at that same database, but within the Detective tool, not with the external SQLite viewer.
So let’s look at some of these options here. You see these three tabs here. You can look at it in hex. You can look at the table as it stands — now this is with active data — or you can thumb over to the SQLite database with recovered data. So you grab this to recover data as you are parsing. So you’re pulling all of the raw data. For example, this is an iTunes backup and it has that database living inside the iTunes backup. And I choose to recover deleted application data. Now that’s one of the options at import.
So I did choose to recover deleted data. And if I scroll down a little bit, you guys can see in the left-hand column the red numbers. This indicates that Detective carved deleted data out of this database. And now it is here for us to review.
So let’s look at that ABPerson table. And I see the same information: first name, last name, organization. And let’s say that that’s some of the data that I need to pull out of this database.
Look at our options up at the top. We can search and let’s look at some of our search options. Let’s say we need to search for ‘Steven.’ And we want to search in all tables inside of this database, we have the option to search in text fields, binary fields, number fields, whether or not it’s going to be case sensitive, whole words only. So let’s go ahead and add this search.
And you see at the bottom of my screen that it’s picked up the name ‘Steven’ in three tables. We can click this dropdown and open up our SQLite editor. When you do this, you’ve got a table here that you can put search queries in. So as you can see we’re on the query tab, and here’s a library tab, I have queried from this database before.
So here’s what I want to do. I need to pull some data from ABPerson. I want my first, my last, and organization. So that is what we’re going to do. So up at the top, you’re going to place your query here.
So we’re selecting from ABPerson, the first, ABPerson last, ABPerson organization. And we’re going to come over and click ‘Execute.’ And you can see that we have those three items pulled from that database, from that specific table, and from those specific columns.
Now we can do some validating. Do we need to get in the tool and validate that this information is parsed correctly? Or do we just need to pull this data from the database itself to present this? Now you have that option. And it’s built inside of Detective.
When you’re parsing from the same database over and over again, as I do inside of the address book — there’s address book images, which are going to be the photographs that you save along with the address book entries — you can always save this query. So if you know you’re going to use it again over and over, you’re pulling in iTunes backups and you query the same database over and over, why not save the queries and name them?
So here’s what we’re going to do. Click on, save query, name it. So I’m going to do ‘First/Last/Organization,’ and I’m going to save this. And now I have two queries saved in my library.
And if you need to delete one, let’s go up and delete this first and last names, because now we have one that includes all three pieces of data that I need. So I’m going to delete this one. You just click delete, and it will delete that query.
Also, you can pin to quick access. When you choose this, you’ll see at the top of your first column, that SQLite query will be the first thing on your dropdown menu. You can see exactly what it contains, what it’s querying, what are those queries? First, last, and organization, from the ABPerson table.
Now let’s have a look at our export options. These are the following formats that you can now export the data that you parsed from that specific database.
For more information on Oxygen Forensic Detective, and for training opportunities, please contact us.
