Skip to content

Instantly share code, notes, and snippets.

@jhoneill

jhoneill/Chrome Databases.ipynb

Last active March 10, 2024 21:12
Show Gist options
  • Save jhoneill/e585bae781f3efa7ac1992b79e037713 to your computer and use it in GitHub Desktop.
Save jhoneill/e585bae781f3efa7ac1992b79e037713 to your computer and use it in GitHub Desktop.
Download ZIP
Exploring data saved by Chrome/Edge/Any Chromium (passwords done in another Gist)
Display the source blob
Display the rendered blob
Raw
Chrome Databases.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Set Exclcusions"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"$exclude='tax|pensions|life|retire|twitter'"
],
"outputs": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Load Get-SQL module, copy browser history, connect to it. \n",
"The chromium engine puts data in \n",
"```\n",
"<<userProfile>>\n",
" \\AppData\n",
" \\Local\n",
" \\Publisher {Google | Microsoft}\n",
" \\BrowserName {Chrome | Edge | Edge Dev | etc}\n",
" \\User Data\n",
" \\Default\n",
"```\n",
"The first file to look at is **History**"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"cd ~\n",
"$browserDir = '.\\AppData\\Local\\Microsoft\\Edge Dev\\User Data\\Default'\n",
"if (-not (Test-Path -PathType Container \".\\temp\")) {mkdir '.\\temp'}\n",
"\n",
"Import-Module getsql -PassThru | Format-Table name,version | Out-String #Out-string is better in beta notebooks\n",
"\n",
"Copy-Item \"$browserDir\\History\" -Destination '.\\temp'\n",
"$tables = Get-SQL -Connection .\\temp\\history -Lite -ShowTables\n",
"$tables -join '; '"
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\nName Version\r\n---- -------\r\ngetsql 1.3.0.1\r\n\r\n\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "downloads; downloads_slices; downloads_url_chains; keyword_search_terms; meta; segment_usage; segments; sqlite_sequence; typed_url_sync_metadata; urls; visit_source; visits\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"# use SQL as an alias, Examine the table 'visits'\n",
"sql -Describe 'visits' | Out-String"
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\nCOLUMN_NAME TYPE_NAME COLUMN_SIZE IS_NULLABLE\r\n----------- --------- ----------- -----------\r\nid False\r\nurl False\r\nvisit_time False\r\nfrom_visit True\r\ntransition False\r\nsegment_id True\r\nvisit_duration False\r\nincremented_omnibox_typed_score False\r\npublicly_routable False\r\n\r\n\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Oldest downloads in my history - from when I first got the machine."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"# Dates are stored as microseconds since 1st Jan 1601; \n",
"# So: convert to seconds, subtract time since '1601-01-01' (could optimize as -11644473600 but less readable)\n",
"# and convert to a string representing date.\n",
"$Downloads = sql @'\n",
" SELECT datetime( start_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch') AS StartTime,\n",
" datetime( end_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch') AS EndTime,\n",
" ((end_time - start_time)/ 1000000 ) AS Duration, \n",
" (received_bytes*1000000 /(end_time - start_time)) AS BytesPerSec,\n",
" received_bytes, state, tab_url, target_path, mime_type\n",
" FROM downloads\n",
" ORDER BY start_time \n",
" LIMIT 25\n",
"'@ \n",
"\n",
"$Downloads | where tab_Url -notmatch $exclude | Format-Table -Wrap -AutoSize -property StartTime, \n",
" @{n='Total KB'; e={$_.received_bytes/1kb}; a='Right';f='#,###' }, \n",
" @{n='KB/Sec' ; e={($_.BytesPerSec/1kb)}; a='right';f='0'; }, \n",
" @{n='URL';e='tab_url'} | Out-String -Width 190 #Out-String is just for beta notebooks"
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "25 row(s) returned\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\nStartTime Total KB KB/Sec URL\r\n--------- -------- ------ ---\r\n2019-11-04 11:57:26 1,757 https://www.microsoftedgeinsider.com/en-gb/download?form=…\r\n2019-11-04 12:03:25 54,688 404 https://github.com/PowerShell/PowerShell/releases/tag/v6.2.3\r\n2019-11-04 12:04:19 51,880 486 https://code.visualstudio.com/docs/?dv=win64\r\n2019-11-04 12:14:26 82,504 1417 https://statics.teams.microsoft.com/production-windows/1.2.00.27559/…\r\n2019-11-04 12:40:38 831 297 https://www.dell.com/support/home/uk/en/ukbsdt1/Drivers/DriversDetails?driverId=3V6JH\r\n2019-11-04 12:41:16 1,382 401 http://www.delldisplaymanager.com/ddmsetup.exe\r\n2019-11-04 14:00:42 121,411 291 https://dotnet.microsoft.com/download/thank-you/dotnet-sdk-3.0.100…\r\n2019-11-05 11:57:24 34,992 816 https://graphviz.gitlab.io/_pages/Download/Download_windows.html\r\n2019-11-06 09:49:56 3,615 264 https://notepad-plus-plus.org/downloads/v7.8.1/\r\n2019-11-06 10:03:13 744 74 https://docs.microsoft.com/en-us/sysinternals/downloads/autoruns\r\n2019-11-07 11:36:19 46,859 543 https://git-scm.com/download/win\r\n2019-11-11 11:18:05 146,693 287 https://dotnet.microsoft.com/download/thank-you/dotnet-sdk-2.2.402…\r\n2019-11-14 14:04:26 163,679 810 https://www.oracle.com/technetwork/java/javase/downloads/jdk13-downloads-5672538.html\r\n2019-11-20 11:23:54 7 1 https://sqldbtutorial.blob.core.windows.net/bacpacs/tailspindatabase…\r\n2019-11-21 10:38:43 1 0 https://www.tenforums.com/attachments/tutorials/19110d1431740240…\r\n2019-11-21 11:25:53 1,688 624 https://docs.microsoft.com/en-us/sysinternals/downloads/autoruns\r\n2019-11-26 09:38:08 4,888 316 https://msedgedriver.azureedge.net/80.0.344.0/edgedriver_win32.zip\r\n2019-11-28 20:26:29 1,327 260 http://www.delldisplaymanager.com/ddmsetup.exe\r\n\r\n\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Get Search terms from history"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"# This time build the query from the command line -table <<Tab to name>> -select <<Tab to column>> -distinct \"extra SQL\"\n",
"sql -Table \"keyword_search_terms\" -Select \"term\" -Distinct \"Limit 20\" | where term -notmatch $exclude | out-string -width 190\n",
"\n",
"$moreComplexQuery = @'\n",
" SELECT term,\n",
" urls.url,\n",
" datetime(urls.last_visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch') AS visitTime\n",
" FROM keyword_search_terms\n",
" JOIN urls on keyword_search_terms.url_id = urls.id\n",
"'@"
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "20 row(s) returned\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\nterm\r\n----\r\n\"Change the authentication level to Pkt_Privacy\"\r\n\"Change the authentication level to Pkt_Privacy\" TerminalServices \r\n\"Rosengrantz and Gildernsten\"\r\n\"TypeError: Cannot read property 'charAt' of undefined at\"\r\n\"TypeError: Cannot read property 'charAt' of undefined\" \r\n\"azure data studio\" marketplace\r\n\"jupyter notebook stop\"\r\n\"netstandard2.0\" how to list colors\r\n\"netstandard2.0\" knowncolor\r\n\"netstandard2.0\" list color names\r\n\"new relic\"\r\n\"new relic\" azure\r\n+\"Rosengrantz and Gildernsten\"\r\n+jupyter name origin\r\n.net get unix time\r\n.net interactive api\r\n.net interactive default juptyer\r\n.net interactive rest api\r\n01425 area code\r\n04 area code uk\r\n\r\n\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Most visited sites and most recently visited from History"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"sql -Describe \"visits\" | Out-String #out-string for notebook"
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\nCOLUMN_NAME TYPE_NAME COLUMN_SIZE IS_NULLABLE\r\n----------- --------- ----------- -----------\r\nid False\r\nurl False\r\nvisit_time False\r\nfrom_visit True\r\ntransition False\r\nsegment_id True\r\nvisit_duration False\r\nincremented_omnibox_typed_score False\r\npublicly_routable False\r\n\r\n\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"$Grouped = sql @\"\n",
" SELECT count (visit_Time) as visits , \n",
" urls.url\n",
" FROM visits JOIN urls\n",
" ON visits.url = urls.id\n",
" WHERE (visits.visit_time < 100000000000000000) \n",
" Group BY urls.url\n",
" Order by visits desc\n",
"\"@ | where url -NotMatch $exclude | Group-Object -Property @{e={$_.url -replace \"(?<!/)/(?!/).*$\",\"\"}}\n",
"\n",
"$totals = foreach ($g in $grouped) {$g.group | measure -sum visits | select @{n='name'; e={$g.name}} , Sum}\n",
"$totals | sort sum -Descending | select -first 20\n",
""
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "4369 row(s) returned\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "name Sum\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "---- ---\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://github.com 1629\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://docs.microsoft.com 391\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "http://localhost:8888 321\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://www.bing.com 308\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://loecda.visualstudio.com 293\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://login.microsoftonline.com 178\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://dev.azure.com 161\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://t.co 106\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://www.google.co.uk 98\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://webshell.suite.office.com 87\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://wd3-media.myworkdaycdn.com 81\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://portal.azure.com 68\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "http://localhost:9815 68\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "file:///C: 61\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://plotly.com 60\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://fslab.org 53\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://www.bbc.co.uk 53\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "http://localhost:8889 52\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "https://stackoverflow.com 51\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"SQL \"SELECT * FROM segments join segment_usage on segment_usage.segment_id = segments.id join urls on urls.id = segments.url_id limit 5\" |Out-String -Width 190"
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "5 row(s) returned\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "r\nid : 11\r\nname : http://bbc.co.uk/\r\nurl_id : 5973\r\nid1 : 11\r\nsegment_id : 11\r\ntime_slot : 13199151600000000\r\nvisit_count : 11\r\nid2 : 5973\r\nurl : https://www.bbc.co.uk/\r\ntitle : BBC - Home\r\nvisit_count1 : 162\r\ntyped_count : 66\r\nlast_visit_time : 13249934783194801\r\nhidden : 0\r\n\r\nid : 14\r\nname : http://google.com/\r\nurl_id : 5036\r\nid1 : 17\r\nsegment_id : 14\r\ntime_slot : 13199238000000000\r\nvisit_count : 1\r\nid2 : 5036\r\nurl : https://www.google.com/\r\ntitle : Google\r\nvisit_count1 : 106\r\ntyped_count : 14\r\nlast_visit_time : 13249699622150310\r\nhidden : 0\r\n\r\nid : 15\r\nname : http://go.microsoft.com/fwlink/\r\nurl_id : 62791\r\nid1 : 18\r\nsegment_id : 15\r\ntime_slot : 13199238000000000\r\nvisit_count : 1\r\nid2 : 62791\r\nurl : https://go.microsoft.com/fwlink/?linkid=2049807\r\ntitle : Hi, how may we assist you?\r\nvisit_count1 : 3\r\ntyped_count : 0\r\nlast_visit_time : 13245796525387302\r\nhidden : 0\r\n\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"# close the SQL connection after this one. Give it few seconds before deleting the file from \\temp - in the next block \n",
"sql @\"\n",
" SELECT datetime(visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch') AS visitTime , \n",
" urls.url, urls.title\n",
" FROM visits JOIN urls\n",
" ON visits.url = urls.id\n",
" WHERE (visit_time < 100000000000000000) \n",
" ORDER BY visit_time DESC\n",
" LIMIT 100\n",
"\"@ | where url -NotMatch $exclude | Select -first 20 | Out-String -Width 190\n",
"\n",
"sql -close "
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "100 row(s) returned\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\nvisitTime url\r\n--------- --- \r\n2020-11-06 11:34:50 https://community.idera.com/user/emailforgottenpassword?ReturnUrl=%2Fdatabase-tools%2Fpowershell%2Fpowershell_com_featured_blogs…\r\n2020-11-06 11:34:46 https://community.idera.com/login?ReturnUrl=%2Fdatabase-tools%2Fpowershell%2Fpowershell_com_featured_blogs%2Fb%2Ftobias%2Fposts%…\r\n2020-11-06 11:34:43 https://community.idera.com/msgs?MessageID=17&ReturnUrl=%2Fdatabase-tools%2Fpowershell%2Fpowershell_com_featured_blogs%2Fb%2Ftobias…\r\n2020-11-06 11:34:43 https://community.idera.com/login?ReturnUrl=%2fdatabase-tools%2fpowershell%2fpowershell_com_featured_blogs%2fb%2ftobias%2fposts…\r\n2020-11-06 11:34:06 http://web.archive.org/web/2019*/http://community.idera.com/powershell/powershell_com_featured_blogs/b/tobias/posts/cancelling…\r\n2020-11-06 11:34:04 http://web.archive.org/web/20170915000000*/http://community.idera.com/powershell/powershell_com_featured_blogs/b/tobias/posts/…\r\n2020-11-06 11:34:01 http://web.archive.org/web/20171101000000*/http://community.idera.com/powershell/powershell_com_featured_blogs/b/tobias/posts/…\r\n2020-11-06 11:33:59 http://web.archive.org/web/20171230124009/http://community.idera.com/login?ReturnUrl=%2Fpowershell%2Fpowershell_com_featured…\r\n2020-11-06 11:33:51 http://web.archive.org/web/20171230124009/http://community.idera.com/powershell/powershell_com_featured_blogs/b/tobias/posts/…\r\n2020-11-06 11:33:41 http://web.archive.org/web/20171101000000*/http://community.idera.com/powershell/powershell_com_featured_blogs/b/tobias/posts/…\r\n2020-11-06 11:33:36 http://web.archive.org/web/20170915000000*/http://community.idera.com/powershell/powershell_com_featured_blogs/b/tobias/posts/…\r\n2020-11-06 11:33:31 http://web.archive.org/web/2019*/http://community.idera.com/powershell/powershell_com_featured_blogs/b/tobias/posts/cancelling…\r\n2020-11-06 11:33:26 http://web.archive.org/web/*/http://community.idera.com/powershell/powershell_com_featured_blogs/b/tobias/posts/cancelling-a-…\r\n2020-11-06 11:33:15 http://web.archive.org/ \r\n2020-11-06 11:33:15 http://wayback.archive.org/ \r\n2020-11-06 11:33:12 https://www.bing.com/search?q=wayback+machine&cvid=09dce1856a8f4f5c9dd51f94e2c06c29&PC=U531 \r\n2020-11-06 11:32:34 https://github.com/PowerShell/PowerShell/issues/3821 \r\n2020-11-06 11:29:10 https://github.com/MicrosoftDocs/PowerShell-Docs/issues/5994 \r\n2020-11-06 11:28:55 https://github.com/PowerShell/PowerShell/issues/6070 \r\n2020-11-06 11:28:48 https://github.com/PowerShell/PowerShell/issues/5811 \r\n\r\n\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"cd ~\n",
"del '.\\temp\\history' #-ErrorAction SilentlyContinue\n",
"copy \"$browserDir\\Top Sites\" -Destination '.\\temp'\n",
"sql -ForceNew -Lite -Connection '.\\temp\\top sites'-Table \"top_sites\" -OrderBy \"url_rank\" -where \"url_rank\" -lt 10 -close | where url -NotMatch $exclude | Out-String"
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "10 row(s) returned\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\nurl url_rank title \r\n--- -------- ----- \r\nhttps://github.com/ 0 GitHub \r\nhttps://www.google.co.uk/ 3 Google \r\nhttps://bbc.co.uk/ 4 BBC - Home \r\nhttps://docs.microsoft.com/en-us/rest/api/azure/devops/processes/pages/… 5 Pages - Add (Azure… \r\nhttps://github.com/KevinMarquette/PSGraph/ 6 KevinMarquette/PSG… \r\nhttps://jhoneill.github.io/ 7 James O'Neill's Bl… \r\nhttp://bing.com/ 8 Bing \r\nhttps://github.com/TravisEz13/travisez13/blob/master/_notebooks/… 9 travisez13/…\r\n\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Collections in Microsft Edge (ChrEdge from versions ~80 onwards)"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"del '.\\temp\\top sites' -ErrorAction SilentlyContinue\n",
"\n",
"copy \"$browserDir\\Collections\\collectionsSQLite\" -Destination '.\\temp'\n",
"Get-SQL -Lite -Connection '.\\temp\\collectionsSQLite' -forcenew -ShowTables |Out-String"
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "collections\r\ncollections_items_relationship\r\ncollections_sync\r\ncomments\r\nfavicons\r\nitems\r\nitems_sync\r\nmeta\r\n\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"# close the SQL connection after this one. Give it few seconds before deleting the file from .\\temp - in the next block \n",
"Sql @'\n",
" select\n",
" collections_items_relationship.position,\n",
" collections.title AS Collection,\n",
" items.title,\n",
" items.source,\n",
" items.text_content\n",
" from collections_items_relationship\n",
" join items on items.id = collections_items_relationship.item_id\n",
" Join collections on collections.id = collections_items_relationship.parent_id\n",
" order by collections.position, collections_items_relationship.position\n",
"'@ -Close | select-object -Property Collection,Position, Title,\n",
" @{n='LinkSource'; e={(convertfrom-json ([string]::new($_.source))).url} },\n",
" Text_Content, \n",
" @{n='LinkSiteName'; e={(convertfrom-json ([string]::new($_.source))).websiteName} } | \n",
" ft -a -wrap | out-string -width 190"
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "96 row(s) returned\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\nCollection position title\r\n---------- -------- ----- \r\nTraining Pile 1 How I accidentally created a creepy Halloween bot ... and you can too! - DEV\r\nTraining Pile 3 Azure Functions: \"Azure Functions - live via Restream.io\"\r\nTraining Pile 4 Writing an Extension Vault for PowerShell SecretManagement Preview 4\r\nTraining Pile 5 Azure Web App Service and GitHub Actions (Video Tutorial) | Azure DevOps Blog\r\nTraining Pile 6 How to make your Azure DevOps CI/CD pipeline faster - Tim Deschryver\r\nTraining Pile 7 An API layer of PowerShell-based Function Apps - a few tips\r\nTraining Pile 8 Remote State Management with Terraform Cloud - YouTube\r\nTraining Pile 9 Using Terraform with Azure | Microsoft Docs\r\nTraining Pile 10 Create a Kubernetes cluster with Azure Kubernetes Service (AKS) using Terraform | Microsoft Docs\r\nTraining Pile 11 Tutorial - Terraform testing overview | Microsoft Docs\r\nTraining Pile 12 - Azure Global BootCamp 2020\r\nAzDo Site 1 Developer Documentation - Overview\r\nRDP Notes 1 Remote Desktop Services 2016, Standard Deployment – Part 4 – RD Web Access (Part2) – Nedim's IT CORNER\r\nRDP Notes 2 RemoteDesktop | Microsoft Docs\r\nRDP Notes 3 VBScript - List Terminal Services Session Directory Settings\r\nRDP Notes 4 Lifesaving notes for RDP 2012 farm deployments with RD brokers and everything else - Ondrej Sevecek's English Pages\r\nOdds and ends 1 Steal Chrome Passwords and Decrypt with Python - Geekswipe\r\nOdds and ends 2 How to Recover Saved Passwords in Google Chrome | XenArmor\r\nOdds and ends 3 ITIL 4 Managing Professional in 1,000 words\r\nOdds and ends 4 Causeway | Microsoft Teams\r\nOdds and ends 5 DevOps%20by%20Corndel%20Digital.pdf?\r\nOdds and ends 6 Comparing ThreadJob to PSJobs in PowerShell 7 on Linux - Petri\r\nOdds and ends 7 How to Use the BITS Module to Transfer Files using PowerShell – CloudSavvy IT\r\nOdds and ends 8 Automating Visual Studio Code with PowerShell - Ironman Software\r\nOdds and ends 9 Write PowerShell Online using Visual Studio Codespaces - Thomas Maurer\r\nOdds and ends 10 Project Natick Phase 2\r\nOdds and ends 11 How To Restrict Internet Access Using Group Policy (GPO) - the Sysadmin Channel\r\nOdds and ends 12 Auto-Learning Auto-Completion - powershell.one\r\nOdds and ends 13 Extra Evening June MC2MC - MC2MC\r\nOdds and ends 14 Project Jupyter | Installing the Jupyter Software\r\nOdds and ends 15 .NET Interactive is here! | .NET Notebooks Preview 2 | .NET Blog\r\nOdds and ends 16 Windows Virtual Desktop: The Best Step-by-Step Walkthrough - Security Boulevard\r\nOdds and ends 17 Unlocking the power of location:The UK’s geospatial strategy - GOV.UK\r\nOdds and ends 18 Resolving PowerShell Module Assembly Dependency Conflicts | PowerShell\r\nOdds and ends 19 One Ops Question: Should I use Azure Resource Manager Templates or Terraform?\r\nOdds and ends 21 https://ico.org.uk/media/about-the-ico/documents/2617838/ico-report-on-mpe-in-england-and-wales-v1_1.pdf\r\nOdds and ends 22 \"Loading\" of Windows Runtime assemblies fails in 7.1 Preview 4 · Issue #13042 · PowerShell/PowerShell\r\nOdds and ends 23 Add a switch to Select-String that returns the matching parts only, analogous to grep -o · Issue #7712 · PowerShell/PowerShell\r\nOdds and ends 24 Publish a Post for a Jekyll Site on a Schedule - PowerShell: What A Novel Idea\r\nAzure notes 1 Azure Boards & Repos app for Teams\r\n \r\nAzure notes 2 Link GitHub commits & PRs to work items on Azure Boards\r\nAzure notes 3 Understand approvals and gates in Azure Pipelines \r\nAzure notes 4 Building an Azure DevOps Pipeline [Soup to Nuts]\r\nAzure notes 5 Catalog of the built-in tasks for Azure Pipelines\r\nAzure notes 6 Expressions - Azure Pipelines reference\r\nAzure notes 7 Conditions - Azure Pipelines reference\r\nAzure notes 8 Is there a description of the wiql that covers WorkItemLink Queries?\r\nAzure notes 9 WIQL syntax for Link Query | Microsoft Docs\r\nAzure notes 10 WIQL reference syntax - Azure Boards | Microsoft Docs\r\nAzure notes 11 REST APIs for Azure DevOps\r\nAzure notes 12 Variables - Azure Pipelines | Microsoft Docs\r\nAzure notes 13 YAML schema - Azure Pipelines | Microsoft Docs\r\nAzure notes 14 Hidden Gems in Azure Pipelines: Creating Your Own $(Rev) Variable Using Counter Expression in Azure Pipelines | Kasun Kodagoda\r\nAzure notes 16 Download Secure File task - Azure Pipelines | Microsoft Docs\r\nAzure notes 17 Use an Azure file share with Windows | Microsoft Docs\r\nAzure notes 18 Desired State Configuration extension with Azure Resource Manager templates | Microsoft Docs\r\nAzure notes 19 Desired State Configuration for Azure overview | Microsoft Docs\r\nAzure notes 20 Microsoft-hosted agents for Azure Pipelines - Azure Pipelines | Microsoft Docs\r\nAzure notes 21 How to Run DSC Scripts on Azure VMs with ARM Templates\r\nAzure notes 22 Logging commands - Azure Pipelines | Microsoft Docs\r\nAzure notes 24 Azure Resource Manager Templates - Part One - VM Extensions\r\nAzure notes 25 Using PowerShell DSC in ARM Templates - Mavention\r\nAzure notes 26 ExpressRoute: Link a VNet to a circuit: Azure portal | Microsoft Docs\r\nAzure notes 34 Azure ExpressRoute: Add a gateway to a VNet: PowerShell | Microsoft Docs\r\nAzure notes 38 Virtual network for Azure services | Microsoft Docs\r\nAzure notes 39 Quickstart - Create a Terraform configuration for Azure | Microsoft Docs\r\nAzure notes 41 Interpolation Syntax - 0.11 Configuration Language - Terraform by HashiCorp\r\nAzure notes 42 How to hide or edit the reason field in Azure DevOps | Azure DevOps Blog\r\nAzure notes 43 Deploy Any Resource With The New Kubernetes Provider for HashiCorp Terraform\r\nAzure training 1 How to pick the right Azure Exam Certification Path\r\nAzure training 2 How to Prepare and Pass a Microsoft Azure Exam\r\nAzure training 3 How to Learn Microsoft Azure in 2020 - Thomas Maurer\r\nAzure training 4 Microsoft Certified: Azure DevOps Engineer Expert - Learn\r\nAzure training 5 Azure DevOps AZ-400 Exam – Study Notes – Azure Greg\r\nAzure training 6 Azure DevOps Hands-On Labs\r\nAzure training 7 Browse all - Learn\r\nAzure training 8 Azure fundamentals learning path - Learn | Microsoft Docs\r\nAzure training 9 Build applications with Azure DevOps learning path - Learn\r\nAzure training 10 Deploy applications with Azure DevOps learning path - Learn | Microsoft Docs\r\nAzure training 11 Provision infrastructure in Azure Pipelines - Learn - Learn | Microsoft Docs\r\nAzure training 12 Deploy a website to Azure with Azure App Service learning path - Learn | Microsoft Docs\r\nAzure training 13 Azure/azure-api-management-devops-resource-kit: Azure API Management DevOps Resource Kit\r\nAzure training 14 Introduction to Microsoft 365 core services and features - Learn | Microsoft Docs\r\nAzure training 15 Core Cloud Services - Introduction to Azure compute - Learn | Microsoft Docs\r\nAzure training 16 Architect migration, business continuity, and disaster recovery in Azure learning path - Learn | Microsoft Docs\r\nAzure training 17 What is Microsoft 365? - Learn | Microsoft Docs\r\nAzure training 18 How to Deploy your First AKS Cluster [Detailed Guide]\r\nAzure training 19 Azure DevOps documentation | Microsoft Docs\r\nAzure training 20 Azure Pipelines - Build | Visual Studio Toolbox | Channel 9\r\nAzure training 21 Azure Pipelines - Release | Visual Studio Toolbox | Channel 9\r\nAzure training 26 Build a CI/CD pipeline for Azure API Management - YouTube\r\nAzure training 27 AZ-304 Study Guide: Microsoft Azure Architect Design - Thomas Maurer\r\nAzure training 28 Azure Administrator (AZ-104) Accelerated Exam Prep Workshop\r\nAzure training 29 Learning Path – Microsoft Azure: AZ-400 Azure DevOps Solutions – Linux Academy\r\nAzure training 30 Get Started with Terraform by Building an Azure VM [Tutorial]\r\nAzure training 31 AZ-900T00-A - Microsoft Azure Fundamentals | Skillpipe\r\n\r\n\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Bookmarks are a JSON file. Let's Parse that :-) "
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"#!pwsh\n",
"del '.\\temp\\collectionsSQLite' -ErrorAction SilentlyContinue\n",
" \n",
"$b = ConvertFrom-Json -AsHashtable -InputObject (Get-Content \"$browserDir\\Bookmarks\" -raw) -Depth 10\n",
"$links = $(foreach ($k in $b.roots.Keys) {foreach ($c in $b.roots[$k].children) {[pscustomObject]$c}} ) \n",
"$links | where url -notmatch $exclude | Format-Table @{w=50;e='name'},@{w=50;e='url'} -Wrap "
],
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "name url\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "---- ---\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Tips on how to take Microsoft Azure Certification https://www.thomasmaurer.ch/2019/09/tips-on-how-\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Exams - Thomas Maurer to-take-microsoft-azure-certification-exams/\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "My Visual Studio https://my.visualstudio.com/Benefits?wt.mc_id=o~\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "StartAutomating/PSDevOps: Tools for DevOps https://github.com/StartAutomating/PSDevOps\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Azure/azure-api-management-devops-resource-kit: https://github.com/Azure/azure-api-management-de\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Azure API Management DevOps Resource Kit vops-resource-kit\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Azure Pipelines YAML schema https://docs.microsoft.com/en-gb/azure/devops/pi\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": " pelines/yaml-schema?view=azure-devops \r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Automated Web Testing with Selenium and https://ironmansoftware.com/automated-web-testin\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "PowerShell - Ironman Software g-with-selenium-and-powershell/\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Azure DevOps documentation | Microsoft Docs https://docs.microsoft.com/en-us/azure/devops/?W\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": " T.mc_id=vstoolbox-c9-niner&view=azure-devops\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "My Visual studio Information https://app.vsaex.visualstudio.com/me\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Noisli https://www.noisli.com/\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "how to export a class in powershell v5 module - https://stackoverflow.com/questions/31051103/how\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Stack Overflow -to-export-a-class-in-powershell-v5-module\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Azure Devops fires Pipeline triggers defined in https://developercommunity.visualstudio.com/cont\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Yaml when it should not - Developer Community ent/problem/913773/...\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Azure/network-security-group/azurerm | Terraform https://registry.terraform.io/modules/Azure/netw\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Registry ork-security-group/azurerm/3.0.1?tab=inputs\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Special Offers | Bargain & Discount Gifts - https://www.virginexperiencedays.co.uk/special-o\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Virgin Experience Days ffers \r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "What to do when your build hangs on the Hosted https://jessehouwing.net/what-to-do-when-your-bu\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "Pool... ild-hangs-on-the-hosted-pool/\r\n"
},
"execution_count": 1,
"metadata": {}
},
{
"output_type": "execute_result",
"data": {
"text/plain": "\r\n"
},
"execution_count": 1,
"metadata": {}
}
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Things for another time ... "
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
"<#\n",
"#Downloads\n",
"sql -Table \"autofill\" -OrderBy \"count\" | select name,count,@{n='value';e={Unprotect $_.value}} | export-excel\n",
"\n",
"get-sql -Lite -Connection 'C:\\temp\\Web Data'\n",
"sql -Describe \"credit_cards\" \n",
"COLUMN_NAME TYPE_NAME COLUMN_SIZE IS_NULLABLE\n",
"----------- --------- ----------- -----------\n",
"guid False\n",
"name_on_card True\n",
"expiration_month True\n",
"expiration_year True\n",
"card_number_encrypted True\n",
"date_modified False\n",
"origin True\n",
"use_count False\n",
"use_date False\n",
"billing_address_id True\n",
"nickname True\n",
"\n",
"get-sql -Lite -Connection 'C:\\temp\\History' -ShowTables -ForceNew\n",
"SQL \"SELECT visits.id, visits.visit_time,visits.visit_duration, urls.url, urls.title FROM visits join urls limit 10\" \n",
"\n",
"SQL \"SELECT * FROM urls order by visit_count desc limit 10\" | ft visit_count,url\n",
"\n",
"SQL \"SELECT * FROM segments join segment_usage on segment_usage.segment_id = segments.id join urls on urls.id = segments.url_id limit 5\"\n",
"\n",
"\n",
"sql -lite -connection C:\\temp\\History -ForceNew -sql @\"\n",
" SELECT visit_count, typed_count, title,\n",
" datetime(last_visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch') AS lastVisitTime,\n",
" url\n",
" FROM urls\n",
" ORDER BY visit_count DESC\n",
"\"@ -close | where url -NotMatch \"job|mail|purple\" | ogv -Title \"My top urls\"\n",
"\n",
"\n",
"\n",
"sql -lite -connection C:\\temp\\History -ForceNew -sql @\"\n",
" SELECT datetime(visit_time / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch') AS visitTime,\n",
" urls.url, urls.title, visit_duration\n",
" FROM visits\n",
" JOIN urls on visits.url = urls.id\n",
" ORDER BY visit_time DESC\n",
" limit 100\n",
"\"@ -close -GridView\n",
"\n",
"sql -lite -connection C:\\temp\\History -ForceNew -sql @\"\n",
" SELECT term,\n",
" urls.url\n",
" FROM keyword_search_terms\n",
" JOIN urls on keyword_search_terms.url_id = urls.id\n",
"\n",
"\"@ -close | where url -notmatch \"wives|revelations|tax|pensions|life|retire\" | ogv -Title \"keyword search terms\"\n",
"\n",
"sql -Lite -Connection \"C:\\temp\\Cookies\" -sql \"Select host_key, name,path, has_expires, datetime(expires_utc / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch') AS expires, datetime(last_access_utc / 1000000 + (strftime('%s', '1601-01-01')), 'unixepoch') AS lastUse, samesite,source_scheme from cookies limit 50\" -grid -close \n",
"\n",
"sql -Lite -Connection \"C:\\temp\\Network Action Predictor\" -Table \"network_action_predictor\" -grid -Close\n",
"\n",
"#top sites - most visited\n",
"\n",
"Sql @\"\n",
" select\n",
" collections_items_relationship.position,\n",
" collections.title,\n",
" items.title,\n",
" items.source,\n",
" items.text_content\n",
" from collections_items_relationship\n",
" join items on items.id = collections_items_relationship.item_id\n",
" Join collections on collections.id = collections_items_relationship.parent_id\n",
" order by collections.position, collections_items_relationship.position\n",
"\"@ | ogv\n",
"\n",
"$b = ConvertFrom-Json -AsHashtable -InputObject (Get-Content C:\\temp\\Bookmarks -raw) -Depth 10\n",
"$links = foreach ($c in $b.roots.Values.children) {New-Object psobject -Property $c }\n",
"$links | ft name,url -AutoSize -Wrap\n",
"\n",
"get-sql -Connection \"Driver={SQLite3 ODBC Driver};Database=C:\\temp\\Collections\\collectionsSQLite\" -forcenew\n",
"Sql @\"\n",
" select\n",
" collections_items_relationship.position,\n",
" collections.title as Collection,\n",
" items.title,\n",
" items.source,\n",
" items.text_content\n",
" from collections_items_relationship\n",
" join items on items.id = collections_items_relationship.item_id\n",
" Join collections on collections.id = collections_items_relationship.parent_id\n",
" order by collections.position , collections_items_relationship.position\n",
"\"@ | select -Property Position,Collection,Title, Text_Content, @{n='LinkSource'; e={\n",
" (ConvertFrom-Json (($_.source -replace \"^x'\", '' -replace \"'\",'' -replace \"(\\S\\S)\",'$1 ').trim().split(\" \").ForEach({[char][System.Convert]::ToInt16($_,16)}) -join \"\")).url} }\n",
"\n",
"Web Data autofill incl credit card info\n",
"# https://www.dataforensics.org/retrieve-saved-password-in-google-chrome/\n",
"#>\n",
"#..\\safe browsing cookies\n",
"#..\\adblocking\\blocklist json file\n",
"#..\\Functional Data"
],
"outputs": []
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"source": [
""
],
"outputs": []
}
],
"metadata": {
"kernelspec": {
"display_name": ".NET (PowerShell)",
"language": "PowerShell",
"name": ".net-powershell"
},
"language_info": {
"file_extension": ".ps1",
"mimetype": "text/x-powershell",
"name": "PowerShell",
"pygments_lexer": "powerShell",
"version": "7.0"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment