OK, lab 1.3 is different than lab 1.1 and 1.2.
It is manual.
There are different ways of doing this.
And I'm going to show you some tricks
after you write the query and answer the questions on what
to do.
When you look at the lab data for this--
so if we go to a Lab 1.3, you're gaining experience with SQLite.
We tell you how to load this file
into DB Browser for SQLite.
This file is called CallHistory.storedata.
So if you don't change Select All Files from the dropdown,
you're not going to see it.
You're going to think it's missing from your lab
directory.
Apple names databases with weird file extensions.
CallHistory.storedata is the primary database
on iOS devices storing calls and FaceTime, all the good things.
So what you're going to do is you
are going to write a query meeting these specific items
here.
If you get stuck, remember, you can always copy and paste.
If you're, like, I really can't get
it to work, copy this, and paste it into DB Browser for SQLite,
and it will work.
Once you get your query, don't get excited and celebrate.
You have to actually do some analysis on it.
So we want you to do some ordering,
some saying where something exists,
all the different things.
We are now going to review lab 1.3.
If you are not ready, simply press pause on this recording,
and continue when you are ready for the review.
I have open lab 1.3.
I'm going to start with Browse Data.
And you can see under ZCALLRECORD,
I have a lot of information.
So what I need to do is start writing my query.
And what I like to do is start, just
like I did before, with SELECT star FROM ZCALLRECORD,
and I Play.
Now, the reason I don't do one line--
and you're going to see this as a habit--
you can actually copy and paste your query
into a tool like Physical Analyzer,
and it would say error line 10 position 18.
So then you scroll, and you're, like, oh, I have a comma.
Or this, if you put everything in one line,
it's going to say error line 1, position 342--
not ideal.
So I like to have it just clean so
I can also look at the colorization in kind
of a line going down.
Now what we need to do is we need
to follow what the lab is telling us to do.
So what do we want it to identify for us?
As we look, keep in mind if this is
about to make you uncomfortable with what we're doing,
just copy and paste from the e-workbook.
That's what it's there for.
So the lab is telling you, you want z_pk.
We want the primary key because this
is a key way we can detect deletion,
and that will make more sense to you as the course continues.
So zaddress-- and I forgot the z in front of it.
We need FROM ZCALLRECORD no comma.
Excellent.
So now we have a little bit of information here.
Now we want to keep adding onto it.
Under zaddress, I'm going to add ZORIGINATED.
And you'll notice capitalization and such do not matter.
ZDURATION and ZANSWERED no comma--
now we have additional columns.
Excellent.
Things are going well.
So now we want to do the date.
So we're going to add zdate.
And if I could quiz you, it starts with 4.
Some of them start with 5.
It's a Mac datetime stamp.
Also, it's from an Apple device.
Now, keep in mind, Apple will use Unix Epoch datetime stamps.
That's very important that you realize that.
A lot of the third-party apps will as well.
Apple native apps will do it too.
So don't assume that every single thing recovered
from an iPhone is going to be a Mac datetime stamp.
You will see the 14, 15, 16, and so on.
You will not see Mac datetime stamps on any Android ever.
If you do, that's really weird.
That does not belong.
OK, so now what we have to do, let's convert this datetime.
So I'm going to do datetime, zdate.
And the magic math, 978307200 comma single tick unixepoch,
single tick as date.
Beautiful.
We have timestamps now.
Excellent.
And if you wanted to do local time, you could.
I'm keeping with UTC in the VM.
So if any of my timestamps just look slightly different,
that's why.
And now we have to rename these columns.
You should do what makes sense to you.
So if mine differ from the lab, I'm
just telling you how I would do it.
Primary key AS from.
Now ZORIGINATED, this is where this gets a little tricky.
We tell you in the lab what it means.
We say, if ZORIGINATED equals o, then it's incoming.
If it's 1, it's outgoing.
So I'm going to call this ZORIGINATED AS 0 equals
incoming; 1 equals outgoing.
And I like to do Control-Enter a few times to see how it looks.
OK.
ZDURATION here, this is going to be duration in seconds.
So what I like to do is, I would say Duration in Seconds,
so the person knows.
If it steps, in meters, steps in feet--
include as much information as you can.
And then for ZANSWERED, if it's 0, it is missed, if it's 1,
it is answered.
So 0 equals missed; 1 equals answered.
Now, you may look at this and be, like, excellent; I am done.
No.
I want you to take this a step further.
I want you to make this pretty.
Where?
I say this.
If you have thousands, and you export this to a spreadsheet,
how many people are going to remember that 1 or 0 equals
incoming or outgoing?
Wouldn't it be better if it didn't even
have a typo like outoing and said a word?
So I'm going to fix this.
First, I'm going to switch this so it says outgoing.
And what I want to do is I want to add a CASE statement.
So I'm going to say CASE when ZORIGINATED equals 0, then
Incoming.
When ZORIGINATED equals 1 then Outgoing.
And then you have to end it, so I'm
going to say end AS Call Direction.
And do you notice how it has both.
It has the old, ugly way and now the pretty way.
You can verify, OK, 0 is incoming, one is outgoing,
and then get rid of the one that is no longer needed.
That looks a lot better.
Let's do the same thing with answered.
So I'm going to do CASE when ZANSWERED equals 0, then
Missed.
When ZANSWERED equals 1, then Answered.
End AS Call Status, maybe?
I don't know what we want to call it.
Comma, now you have that.
You verify.
You get rid of the one that is ugly,
and now you have a lovely query to work with.
It's not done, though.
Now we have to do some additional things.
Now, what I want to show you--
and I mentioned this just briefly--
if I do order by PK ascending, you want to look for gaps.
What happened to 1?
What happened to 4 through 13?
That's a good way to know the user deleted call logs.
And that is coming in this course.
That's not what you're expected to know right now.
But that is why I always include that primary key.
It's a number one example to show
you gaps in calls or any database
on things that could be missing.
OK, so now we have to answer some questions.
We did our query.
Everything is great.
So we need to know which call log lasted the longest.
So I'm going to change this to order by Duration in Seconds
descending.
And we can see we have one that is 21 seconds in length.
And the next one, how many incoming calls
were on the device, and how many of those were answered?
So I'm going to do a different order by.
I'm going to do order by ZORIGINATED ascending comma
ZANSWERED descending.
Let's see how this works.
It did not like that.
Oh, I have a quote there that does not make sense.
It made everything very unhappy.
OK, so now when I zoom out, we can
see we have 42 calls in total.
We can see that 39 of them-- now, remember,
you're not looking at PK, you're looking
at this number over here.
39 of them are incoming, and only three of those
were answered.
You could also do order by ZORIGINATED,
or you could do where Call Status is Answered.
There's so many things you can do.
There's no right or wrong.
And then, what is the date and time for the last outgoing call
made on the device?
So I'm going to do order by the ZDATE, or just "Date"
in quotes, descending.
And what we're looking for is outgoing call.
And we see 2/21 at 15:56.
So be careful that you wouldn't choose incoming.
We're actually asking for this one.
And I'm sure many of you did many different things
in this lab.
Something that people always want to do is try to--
like, oh, I hope Heather zooms in,
so I can take a picture of that.
You don't have to.
This is in your notebook for you.
So any time I could include a CASE statement for you, I did.
You will see sometimes I may name columns, things
that you don't like the name.
Or you may find that you want something else.
Like a good example, if you had this,
you may look and say, why didn't she include ZLOCATION?
I want that.
Add it.
No one's stopping you from adding it.
All you would have to do is say ZLOCATION AS Location.
And now you have the location of where the call took place from.
So here we can see the location of these items.
It's pretty cool what you can do.
OK, now I want to show you something else.
This is my preferred way of working in this lab.
I'm going to Close this.
I am going to open Physical Analyzer
and show you one of the pros of the SQLite Query Builder
that I did want to show you earlier because you
have to earn easier things.
I personally do not use this.
Yes, I work at Cellebrite, I prefer the DB Browser
that Ihad just shown you.
I stressed to Magnet so many times that I prefer the DB
Browser that they also agreed.
And you can export straight from axiom into DB Browser
instead of staying in the tool.
But what I'm about to show you is a unique capability
that you can do in Oxygen as well as Physical Analyzer.
I think you can do it in AXIOM too;
it's just slightly different.
And how I'm going to load-- this is going to look weird.
I'm going to say File, Open Case, Add, Common Source,
Storage Device, SD Card.
Now, this is weird because that's not what we're doing.
But this is one of the few that give you the option
to just select a folder.
I'm going to go out to G, lab 1.3,
and I'm going to select that folder.
It's not going to parse anything.
I'm just going to say Examine Data.
And it should take a second to run.
This trick that Ihad just shown you
is a great way to get any folder into Physical Analyzer.
You will see that you're going to have
to do this in section 5 a lot because we take away
all the tools from you.
And you'll look at it and be, like, what do I do now?
Well, you have a database.
So let's look at that database.
So I am going to say, I want to open this in the SQLite Wizard.
I'm not even going to name it.
You can watch all my Tip Tuesdays
and Ask the Expert if you want to see how to name it.
I just want to show you a trick.
You click on ZCALLRECORD.
That table comes over to the middle.
Something else I learned, even though this is so
grayed out, it makes no sense, if you click on it,
it actually works.
And what it does is it makes the table smaller so that you
can see more things to check.
But it's kind of blurring the view a little bit,
and I don't love that.
But I'm going to say Z_PK, I'm going to checkbox this--
ZANSWERED, ZCALLTYPE, ZORIGINATED, ZDATE, and I'm
going to click Preview.
And this is where people are, like, are you kidding me?
There's all the answers.
So now what I need to do is get--
let me Enlarge this.
What do we need?
ZNAME.
And let's run ZLOCATION because I did like that one too.
And then we do Preview.
It was not ZNAME.
ZADDRESS?
Yes, there we go.
I want to get rid of ZNAME.
I'm going to Uncheck it.
So now you can see all of the information at the bottom.
Now, what you can do--
and this is where people usually get mad at me.
They're like, are you kidding me?
You can rename it right here.
You can say PK Call Status, Call Type--
I don't know what you want to call it--
whatever you want to call it.
Oh, I know we're missing ZDURATION.
I have to make this a little bit larger.
I can't even see it.
There we go.
I can say Preview.
And you'll notice it starts renaming them as I did that.
ZDATE, we can call it just Date.
Location-- and you get the point.
It's making it pretty without having to go through
and manually do all of it.
So it's doing all these things, and the address,
we can do From.
And you could do Duration in Seconds.
So now we have all that.
So it looks nice and pretty.
Now, Call Type, Call Status--
you could also clean that up right here.
You could say, you know what?
I want to do a CASE statement, and I want to write it here.
You could also do your date timestamp conversion
right here.
Or what you can do is press Next,
and you're going to get these options here.
You can choose from these models.
If you're like, OK, I think this is a call log,
and you could say this is a timestamp.
You drag it in, and it's going to say, hey,
this is what I think it is.
I think this is going to be the number of seconds UTC.
And you're, like, yeah, that's a Mac timestamp,
and it's going to fix it for you.
So there are so many things you could do.
This is a different way.
It's not my preferred way, but if it
makes you more comfortable to sit there and check the boxes
until you learn, awesome.
Do what makes sense for you, so you can take the most away
from this course.