Join Us!

browser history tim...
 
Notifications
Clear all

browser history time stamp question  

  RSS
creatureman
(@creatureman)
New Member

PostPosted Sat Oct 11, 2014 110 am Post subject Internet Browser history timestamp question Reply with quote
Hello, I was wondering, if say I saw three websites url"s in the Chrome browser history that were accessed 15 mins apart in Chrome google and windows 8.1. This is according to the browser history timestamps. Is there anyway to tell If one of the URLs was open and the last one visited was closed.? Say like hitting a back button on a web browser, and going to a new website. Or can you tell if there were multiple tabs were open simultaneously. In a nut shell I guess Im asking is it possible to tell when a webpage was closed.? Should be the next url time stamp shouldn't it? Even if you open new tabs, It still registers a new time stamp. But is there anyway to say a web page was left open also ? Thanks This is not my field but need to know if I should refer this to a long wait in a lab, Thanks

Quote
Posted : 10/10/2014 11:57 pm
a.nham
(@a-nham)
Junior Member

If you are just asking if there is a way to find out when a tab was closed or how long it was open. The simple answer to that is no, or at least there is not one I am currently aware of.

That said I do have several suggestions that may solve your problem. If you use a sql lite reader, you can see the tie stamp of when the tabs were open, down to the microseconds. Thus, even if tabs are open simultaneously, they will likely have different timestamps, as the time they connect to the site's server will likely differ. Sql lite readers can also tell you how the link was opened (a new tab, new window, or on top of the page); off the top of my head, I don't remember the labels but if you do a simple search of "Chrome internet artifacts" it should give you some good results. Thus, you can compile a timeline of all the tabs that were opened and what their last visited page.

Hope that was helpful.

ReplyQuote
Posted : 11/10/2014 5:16 am
creatureman
(@creatureman)
New Member

Hey sure was, Thanks, ill give that a go!

ReplyQuote
Posted : 11/10/2014 5:47 am
PaulSanderson
(@paulsanderson)
Senior Member

If you are going to look directly at the SQLite database then as the different tables are normalised then you will need to do some SQL joins to get a usable result. Also the chrome date is microseconds since January 1, 1601 UTC which as a large number is not really very readable without a conversion program to do this on-mass.

You could use Craig Wilsons NetAnalysis to look at this or my new software - The Forensic Browser for SQLite (part of the Forensic Toolkit for SQLite) - could help you with this and other similar queries. Amongst other things it will let you join tables visually and the latest version will decode Chrome dates and times. The screenshot below shows a join on the visits table (which only holds a URL number) with the URL table to get the actual URL. I have also shown a raw visit_time along with the decoded visit time. You can choose which fields you want displayed in a results column and decode/display different values (such as dates) in different formats. You even can create a query and display all the different icons from the favicons database as graphics.

Feel free to drop me an email and I'll send you a fully functional Demo licence

http//sandersonforensics.com/forum/content.php?198-Forensic-Browser-for-SQLite

ReplyQuote
Posted : 11/10/2014 3:04 pm
FoxtonForensics
(@foxtonforensics)
New Member

In the 'History' SQLite database the 'visits' table contains two columns that may help you.

You can determine if a user navigated away from a webpage by opening a link within the same tab (therefore effectively "closing" the webpage). This can be determined using the 'from_visit' column which contains the ID of the previous page viewed (if applicable). This data allows you to build up a chain of navigations as you can see in the following screenshot

The other column that may be of use is the 'visit_duration' column. I haven't looked at this column before but it may be possible to use this data along with the 'visit_time' column to determine when a tab/window was closed.

Lastly, if any of the URLs visited were in a recent session then you may be able to find the info you need in the session and tabs files
http//www.cclgroupltd.com/chrome-session-and-tabs-files-and-the-puzzle-of-the-pickle/

ReplyQuote
Posted : 11/10/2014 3:33 pm
Chris_Ed
(@chris_ed)
Active Member

..Also the chrome date is microseconds since January 1, 1601 UTC which as a large number is not really very readable without a conversion program to do this on-mass.

You could use Craig Wilsons NetAnalysis to look at this or my new software…

Or alternatively, you could just decode them "en masse" using native SQLite datetime statements in a freeware or open source SQLite browser (or your favourite programming language of choice!). Modifying your example


select datetime((visits.visit_time/1000000)-11644473600, 'unixepoch', 'localtime') AS Decoded_Visit_time,
visits.visit_duration,
urls.url,
urls.visit_count,
datetime((urls.last_visit_time/1000000)-11644473600, 'unixepoch', 'localtime') AS Decoded_Last_Visit_Time
FROM visits
LEFT JOIN urls ON visits.url = urls.id

With thanks to this marvellous blog post regarding the conversion, as well as this obligatory stack overflow link.

ReplyQuote
Posted : 12/10/2014 3:03 pm
PaulSanderson
(@paulsanderson)
Senior Member

You are of course correct Chris and with a bit of integer fooey you can of course convert any integer date into another and you rightly point out that this is built into SQLite (I don't use the SQLite date routines because I need more control over the formatted dates for timezone conversion etc.)

However the thrust of my post and the reason for the link was to highlight the need for SQLite joins. The initial picture and query was reasonably simple, but I have created (all visually, i.e. drag and drop, without typing anything other than slightly more meaningful alias's) another query that the OP might want to use, that quite frankly I would have had trouble crafting by hand at an SQLite command line because of the complexity (and I generally can't get my head around it ) )

The SQLite history table contains two tables that I have used - visits from which I get the ID, visit time and the URLID of the current page and the ID of the referring page (the From URL) these I need to use to get the actual URL (and page titles) from the URL's table along with the visit count. So by just introducing the referrer the SQL gets much more complex, i.e.

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
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
*see footnote

But using my software I was able to generate the visual query below and hence the corresponding SQL in a matter of a couple of minutes.

By way of further explanation the URL field in the visits table points to the ID field in the URLs table so we can look up the actual URL and page name along with the visit count. The from_visit field in the visits table points back to an earlier entry in the visits table which then references the actual "from URL" and the time of the visit to this. So to get at this we need to create a derived table (query2).

A picture paints a thousand words so here are a couple of screenshots showing the main visual query

And just for completeness here's the derived table, query2

And as you say you can use my code above add your unix epoch code and get the date from any sqlite command line tool using the code above.

*incidentally if you open a Chrome history DB in the Forensic Browser for SQLite and paste in the text of the SQL query in the box above, The Browser will automatically create the visual elements for you.

ReplyQuote
Posted : 13/10/2014 1:13 am
AlexC
(@alexc)
Active Member

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 https://code.google.com/p/chromium/codesearch#chromium/src/ui/base/page_transition_types_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.

ReplyQuote
Posted : 13/10/2014 3:43 pm
PaulSanderson
(@paulsanderson)
Senior Member

Excellent link Alex.

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

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 )

There is an article I worte about these case expressions last week at this link
http//sandersonforensics.com/forum/content.php?199-Creating-a-custom-display-for-an-integer-data-type-in-the-Forensic-Browser-for-SQLite

*Edit to add that there can be more than one qualifier, the above code will only display one of them.

ReplyQuote
Posted : 13/10/2014 4:27 pm
AlexC
(@alexc)
Active Member

Excellent link Alex.

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

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 )

There is an article I worte about these case expressions last week at this link
http//sandersonforensics.com/forum/content.php?199-Creating-a-custom-display-for-an-integer-data-type-in-the-Forensic-Browser-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?

ReplyQuote
Posted : 13/10/2014 4:50 pm
PaulSanderson
(@paulsanderson)
Senior Member

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.

ReplyQuote
Posted : 13/10/2014 4:52 pm
AlexC
(@alexc)
Active Member

Hi Paul,

One approach could be to create a subquery along the lines of

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?

ReplyQuote
Posted : 13/10/2014 5:43 pm
PaulSanderson
(@paulsanderson)
Senior Member

Hi Alex

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

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

ReplyQuote
Posted : 13/10/2014 10:46 pm
AlexC
(@alexc)
Active Member

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

ReplyQuote
Posted : 14/10/2014 3:21 pm
creatureman
(@creatureman)
New Member

Thanks for the great info and education everyone. Very informative

ReplyQuote
Posted : 14/10/2014 8:52 pm
Share: