±Forensic Focus Partners

Become an advertising partner

±Your Account


Username
Password

Forgotten password/username?

Site Members:

New Today: 0 Overall: 35742
New Yesterday: 3 Visitors: 109

±Follow Forensic Focus

Forensic Focus Facebook PageForensic Focus on TwitterForensic Focus LinkedIn GroupForensic Focus YouTube Channel

RSS feeds: News Forums Articles

±Latest Articles

±Latest Videos

±Latest Jobs

browser history time stamp question

Computer forensics discussion. Please ensure that your post is not better suited to one of the forums below (if it is, please post it there instead!)
Reply to topicReply to topic Printer Friendly Page
Forum FAQSearchView unanswered posts
Page Previous  1, 2, 3  Next 
  

AlexC
Senior Member
 

Re: browser history time stamp question

Post Posted: Oct 13, 14 14:43

One of the other fields in the History database in chrome which might be worth looking at is the "Transition" for each item in the "visits" table.

The meaning of those values are explained in the Chromium source here: code.google.com/p/chro...pes_list.h (don't worry, it's more comments than code!).

If you mask the least significant byte and compare it to the "core transitions" values, then similarly mask the most significant 3 bytes and mask them against the qualifiers you can find some additional context about how the visit was arrived at (was it a link that was clicked? Was it typed into the address bar? Was it a search? Was it accessed via the forward/back bar?).

If you combine this with the "from visit" stuff that's already been mentioned, you can add a lot of behavioral context to the browsing - browsing history is not a linear set of records; it branches for different reasons, and Chrome gives you lots of information about how and why this happens.  
 
  

PaulSanderson
Senior Member
 

Re: browser history time stamp question

Post Posted: Oct 13, 14 15:27

Excellent link Alex.

Using the values from it - a quick hack of my SQL above to make this a bit more readable is:

Code:
SELECT visits.visit_time,
  visits.id,
  urls.url,
  urls.title,
  visits.url AS URLID,
  visits.from_visit,
  Query2.visit_time AS FromVisitTime,
  Query2.url AS FromURL,
  Query2.title AS FromTitle,
  urls.visit_count,
  visits.transition,
  CASE visits.transition & 0xFF 
   WHEN 0 THEN 'Link' 
   WHEN 1 THEN 'Typed' 
   WHEN 2 THEN 'Auto_Bookmark' 
   WHEN 3 THEN 'Auto_Subframe' 
   WHEN 4 THEN 'Manual_Subframe'
   WHEN 5 THEN 'Generated' 
   WHEN 6 THEN 'Auto_Toplevel' 
   WHEN 7 THEN 'Form_Submit' 
   WHEN 8 THEN 'Reload' 
   WHEN 9 THEN 'Keyword' 
   WHEN 20 THEN 'Keyword_Generated'
  END AS CoreTransition,
  CASE visits.transition & 0xFFFFFF00 
   WHEN 0x00800000 THEN 'Blocked' 
   WHEN 0x01000000 THEN 'Forward_Back' 
   WHEN 0x02000000 THEN 'From_Address_Bar'
   WHEN 0x04000000 THEN 'Home_Page' 
   WHEN 0x08000000 THEN 'From_API' 
   WHEN 0x10000000 THEN 'Chain_Start' 
   WHEN 0x20000000 THEN 'Chain_End'
   WHEN 0x40000000 THEN 'Client_Redirect' 
   WHEN 0x80000000 THEN 'Server_Redirect' 
   WHEN 0xC0000000 THEN 'Is_Redirect_Mask' 
  END AS Qualifiers
FROM visits
  LEFT JOIN urls ON visits.url = urls.id
  LEFT JOIN (SELECT urls.url,
    urls.title,
    visits.visit_time,
    visits.id
  FROM visits
    LEFT JOIN urls ON visits.url = urls.id) Query2 ON visits.from_visit = Query2.id

This displays three new columns. The "raw" transition, the decoded core transition and the decoded qualifier. The mix of hex and decimal in the case statements is intentional Smile

There is an article I worte about these case expressions last week at this link
sandersonforensics.com...for-SQLite

*Edit to add that there can be more than one qualifier, the above code will only display one of them.
_________________
Paul Sanderson
SQLite Forensics Book
www.amazon.com/SQLite-...entries*=0

Forensic Toolkit for SQLite
sandersonforensics.com...for-SQLite 
 
  

AlexC
Senior Member
 

Re: browser history time stamp question

Post Posted: Oct 13, 14 15:50

- PaulSanderson
Excellent link Alex.

Using the values from it - a quick hack of my SQL above to make this a bit more readable is:

Code:
SELECT visits.visit_time,
  visits.id,
  urls.url,
  urls.title,
  visits.url AS URLID,
  visits.from_visit,
  Query2.visit_time AS FromVisitTime,
  Query2.url AS FromURL,
  Query2.title AS FromTitle,
  urls.visit_count,
  visits.transition,
  CASE visits.transition & 0xFF 
   WHEN 0 THEN 'Link' 
   WHEN 1 THEN 'Typed' 
   WHEN 2 THEN 'Auto_Bookmark' 
   WHEN 3 THEN 'Auto_Subframe' 
   WHEN 4 THEN 'Manual_Subframe'
   WHEN 5 THEN 'Generated' 
   WHEN 6 THEN 'Auto_Toplevel' 
   WHEN 7 THEN 'Form_Submit' 
   WHEN 8 THEN 'Reload' 
   WHEN 9 THEN 'Keyword' 
   WHEN 20 THEN 'Keyword_Generated'
  END AS CoreTransition,
  CASE visits.transition & 0xFFFFFF00 
   WHEN 0x00800000 THEN 'Blocked' 
   WHEN 0x01000000 THEN 'Forward_Back' 
   WHEN 0x02000000 THEN 'From_Address_Bar'
   WHEN 0x04000000 THEN 'Home_Page' 
   WHEN 0x08000000 THEN 'From_API' 
   WHEN 0x10000000 THEN 'Chain_Start' 
   WHEN 0x20000000 THEN 'Chain_End'
   WHEN 0x40000000 THEN 'Client_Redirect' 
   WHEN 0x80000000 THEN 'Server_Redirect' 
   WHEN 0xC0000000 THEN 'Is_Redirect_Mask' 
  END AS Qualifiers
FROM visits
  LEFT JOIN urls ON visits.url = urls.id
  LEFT JOIN (SELECT urls.url,
    urls.title,
    visits.visit_time,
    visits.id
  FROM visits
    LEFT JOIN urls ON visits.url = urls.id) Query2 ON visits.from_visit = Query2.id

This displays three new columns. The "raw" transition, the decoded core transition and the decoded qualifier. The mix of hex and decimal in the case statements is intentional Smile

There is an article I worte about these case expressions last week at this link
sandersonforensics.com...for-SQLite


Thanks for churning out the CASE statements Paul, I would have done it myself if I'd had more time. One thing to point out however - I don't think your Qualifiers case will work properly - the upper 3 bytes qualifier bit works as a flag bitmap - you need to 'and' against each of the potential values. You could then concatenate the resultant values perhaps? Or maybe have a column for each qualifier?  
 
  

PaulSanderson
Senior Member
 

Re: browser history time stamp question

Post Posted: Oct 13, 14 15:52

Thanks Alex - I edited my post about 2 minutes ago with the footer to essentially say that. Additional clumns would be untidy, I'll tryand get my head around it - but just popping out for a little while.
_________________
Paul Sanderson
SQLite Forensics Book
www.amazon.com/SQLite-...entries*=0

Forensic Toolkit for SQLite
sandersonforensics.com...for-SQLite 
 
  

AlexC
Senior Member
 

Re: browser history time stamp question

Post Posted: Oct 13, 14 16:43

Hi Paul,

One approach could be to create a subquery along the lines of:
Code:
SELECT visits.id, 'CONSTANT_1_NAME' FROM visits WHERE visits.transition & CONSTANT_1_VALUE > 0
UNION
SELECT visits.id, 'CONSTANT_2_NAME' FROM visits WHERE visits.transition & CONSTANT_2_VALUE > 0
UNION
SELECT visits.id, 'CONSTANT_3_NAME' FROM visits WHERE visits.transition & CONSTANT_3_VALUE > 0
etc...

Then join visits with the subquery, GROUP BY visits_id and group-concat what you get back from the subquery?

It's a bit long-winded but might be neater than hard-coded concatenation?  
 
  

PaulSanderson
Senior Member
 

Re: browser history time stamp question

Post Posted: Oct 13, 14 21:46

Hi Alex

Finally ended up with this, SQL isn't really one of my stronger skills Smile

Code:
SELECT visits.visit_time,
  visits.id,
  urls.url,
  urls.title,
  visits.url AS URLID,
  visits.from_visit,
  Query2.visit_time AS FromVisitTime,
  Query2.url AS FromURL,
  Query2.title AS FromTitle,
  urls.visit_count,
  printf('%08x',visits.transition),
  CASE visits.transition & 0xFF 
   WHEN 0 THEN 'Link' 
   WHEN 1 THEN 'Typed' 
   WHEN 2 THEN 'Auto_Bookmark' 
   WHEN 3 THEN 'Auto_Subframe' 
   WHEN 4 THEN 'Manual_Subframe'
   WHEN 5 THEN 'Generated' 
   WHEN 6 THEN 'Auto_Toplevel' 
   WHEN 7 THEN 'Form_Submit' 
   WHEN 8 THEN 'Reload' 
   WHEN 9 THEN 'Keyword' 
   WHEN 20 THEN 'Keyword_Generated'
  END AS CoreTransition, 
  (CASE WHEN visits.transition & 0x00800000 THEN 'Blocked, ' ELSE '' END ||
   CASE WHEN visits.transition & 0x01000000 THEN 'Forward_Back, ' ELSE '' END ||
   CASE WHEN visits.transition & 0x02000000 THEN 'From_Address_Bar, ' ELSE '' END ||
   CASE WHEN visits.transition & 0x04000000 THEN 'Home_Page, ' ELSE '' END ||
   CASE WHEN visits.transition & 0x08000000 THEN 'From_API, ' ELSE '' END ||
   CASE WHEN visits.transition & 0x10000000 THEN 'Chain_Start, ' ELSE '' END ||
   CASE WHEN visits.transition & 0x20000000 THEN 'Chain_end, ' ELSE '' END ||
   CASE WHEN visits.transition & 0x40000000 THEN 'Client_Redirect, ' ELSE '' END ||
   CASE WHEN visits.transition & 0x80000000 THEN 'Server_Redirect, ' ELSE '' END ||
   CASE WHEN visits.transition & 0xC0000000 THEN 'Is_Redirect_Mask, ' ELSE '' END )
  AS Qualifiers
FROM visits
  LEFT JOIN urls ON visits.url = urls.id
  LEFT JOIN (SELECT urls.url,
    urls.title,
    visits.visit_time,
    visits.id
  FROM visits
    LEFT JOIN urls ON visits.url = urls.id) Query2 ON visits.from_visit = Query2.id

Which looks like this


_________________
Paul Sanderson
SQLite Forensics Book
www.amazon.com/SQLite-...entries*=0

Forensic Toolkit for SQLite
sandersonforensics.com...for-SQLite 
 
  

AlexC
Senior Member
 

Re: browser history time stamp question

Post Posted: Oct 14, 14 14:21

Paul - that looks like to does the trick, nicely done! Very Happy  
 

Page 2 of 3
Page Previous  1, 2, 3  Next