-
-
Notifications
You must be signed in to change notification settings - Fork 23
/
WindowsTimeline1903.sql
343 lines (335 loc) · 19 KB
/
WindowsTimeline1903.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
-- SQLite query to get any useful results from MS Windows 1903 Timeline feature's database (ActivitiesCache.db).
-- Dates/Times in the database are stored in Unixepoch and UTC by default.
-- Using the 'localtime" converts it to our TimeZone.
-- The 'DeviceID' may be found in the user’s NTUSER.dat at
-- Software\Microsoft\Windows\CurrentVersion\TaskFlow\DeviceCache\
--
-- The Query uses the SQLite JSON1 extension to parse information from the BLOBs found at
-- the Activity and ActivityOperation tables.
--
-- HKLM: \SOFTWARE\Mozilla\Firefox\TaskBarIDs :
-- 308046B0AF4A39CB is Mozilla Firefox 64bit
-- E7CF176E110C211B is Mozilla Firefox 32bit
--
-- Known folder GUIDs
-- "https://docs.microsoft.com/en-us/dotnet/framework/winforms/controls/known-folder-guids-for-file-dialog-custom-places"
--
-- Duration or totalEngagementTime += e.EndTime.Value.Ticks - e.StartTime.Ticks)
-- https://docs.microsoft.com/en-us/uwp/api/windows.applicationmodel.useractivities
--
-- StartTime: The start time for the UserActivity
-- EndTime: The time when the user stopped engaging with the UserActivity
--
-- Costas Katsavounidis (kacos2000 [at] gmail.com)
-- May 2018
SELECT -- This the ActivityOperation Table Query
ActivityOperation.ETag as 'Etag',
ActivityOperation.OperationOrder as 'Order',
case
when ActivityOperation.ActivityType = 5
then json_extract(ActivityOperation.Payload, '$.appDisplayName')
else ''
end as 'Program Name',
case
when ActivityOperation.ActivityType in (2,11,12,15) then json_extract(ActivityOperation.AppId, '$[0].application')
when json_extract(ActivityOperation.AppId, '$[0].application') = '308046B0AF4A39CB'
then 'Mozilla Firefox-64bit'
when json_extract(ActivityOperation.AppId, '$[0].application') = 'E7CF176E110C211B'
then 'Mozilla Firefox-32bit'
when json_extract(ActivityOperation.AppId, '$[1].application') = '308046B0AF4A39CB'
then 'Mozilla Firefox-64bit'
when json_extract(ActivityOperation.AppId, '$[1].application') = 'E7CF176E110C211B'
then 'Mozilla Firefox-32bit'
when length (json_extract(ActivityOperation.AppId, '$[1].application')) between 17 and 22
then
replace(replace(replace(replace(replace
(json_extract(ActivityOperation.AppId, '$[0].application'),
'{'||'6D809377-6AF0-444B-8957-A3773F02200E'||'}', '*ProgramFiles (x64)'),
'{'||'7C5A40EF-A0FB-4BFC-874A-C0F2E0B9FA8E'||'}', '*ProgramFiles (x32)'),
'{'||'1AC14E77-02E7-4E5D-B744-2EB1AE5198B7'||'}', '*System' ),
'{'||'F38BF404-1D43-42F2-9305-67DE0B28FC23'||'}', '*Windows'),
'{'||'D65231B0-B2F1-4857-A4CE-A8E7C6EA7D27'||'}', '*System32')
else replace(replace(replace(replace(replace(json_extract(ActivityOperation.AppId,
'$[1].application'),
'{'||'6D809377-6AF0-444B-8957-A3773F02200E'||'}', '*ProgramFiles (x64)' ),
'{'||'7C5A40EF-A0FB-4BFC-874A-C0F2E0B9FA8E'||'}', '*ProgramFiles (x32)'),
'{'||'1AC14E77-02E7-4E5D-B744-2EB1AE5198B7'||'}', '*System' ),
'{'||'F38BF404-1D43-42F2-9305-67DE0B28FC23'||'}', '*Windows'),
'{'||'D65231B0-B2F1-4857-A4CE-A8E7C6EA7D27'||'}', '*System32')
end as 'Application',
case when ActivityOperation.ActivityType not in (2,10,11,12,15,16) then
json_extract(ActivityOperation.Payload, '$.displayText') else '' end as 'File Opened',
case when ActivityOperation.ActivityType not in (2,10,11,12,15,16) then
json_extract(ActivityOperation.Payload, '$.description')||')' else '' end as 'Full Path',
case when ActivityOperation.ActivityType not in (2,10,11,12,15,16) then
json_extract(ActivityOperation.Payload, '$.contenturi') else '' end as 'Content',
trim(ActivityOperation.AppActivityId,'ECB32AF3-1440-4086-94E3-5311F97F89C4\') as 'AppActivityId',
case
when ActivityOperation.ActivityType = 2 then ActivityOperation.Payload
when ActivityOperation.ActivityType = 10 and json_extract(ActivityOperation.Payload,'$') notnull
then json_extract(ActivityOperation.Payload,'$.1[0].content') --Base64 encoded
when ActivityOperation.ActivityType = 5 and json_extract(ActivityOperation.Payload, '$.shellContentDescription') like '%FileShellLink%'
then json_extract(ActivityOperation.Payload, '$.shellContentDescription.FileShellLink')
when ActivityOperation.ActivityType = 6
then case
when json_extract(ActivityOperation.Payload,'$.devicePlatform') notnull
then json_extract(ActivityOperation.Payload, '$.type')||' - ' ||json_extract(ActivityOperation.Payload,'$.devicePlatform')
else json_extract(ActivityOperation.Payload, '$.type')||' - ' ||json_extract(ActivityOperation.Payload,'$.userTimezone') end
else ''
end as 'Payload/Timezone',
case ActivityOperation.ActivityType
when 2 then 'Notification'
when 5 then 'Open App/File/Page'
when 6 then 'App In Use/Focus'
when 10 then 'Clipboard'
when 16 then 'Copy/Paste'
else ActivityOperation.ActivityType
end as 'ActivityType',
case json_extract(ActivityOperation.AppId, '$[0].platform')
when 'afs_crossplatform' then 'Yes'
when 'host' then
(case json_extract(ActivityOperation.AppId, '$[1].platform')
when 'afs_crossplatform' then 'Yes' else null end)
else null
end as 'Synced',
case
when json_extract(ActivityOperation.AppId, '$[0].platform') = 'afs_crossplatform'
then json_extract(ActivityOperation.AppId, '$[1].platform')
else json_extract(ActivityOperation.AppId, '$[0].platform')
end as 'Platform',
case ActivityOperation.OperationType
when 1 then 'Active'
when 2 then 'Updated'
when 3 then 'Deleted'
when 4 then 'Ignored'
end as 'TileStatus',
case
when ActivityOperation.Id in
(select Activity.Id from Activity where Activity.Id = ActivityOperation.Id)
then 'Removed'
end as 'WasRemoved',
case
when ActivityOperation.Id
in(select Activity.Id from Activity where Activity.Id = ActivityOperation.Id)
then null else 'In Queue'
end as 'UploadQueue',
'' as 'IsLocalOnly',
case when ActivityOperation.ActivityType in (12,0,11,12,15,16) then ''
else coalesce(json_extract(ActivityOperation.Payload, '$.activationUri'),json_extract(ActivityOperation.Payload, '$.reportingApp')) end as 'App/Uri',
ActivityOperation.Priority as 'Priority',
case when ActivityOperation.ActivityType !=6 then ''
else time(json_extract(ActivityOperation.Payload, '$.activeDurationSeconds'),'unixepoch') end as 'Active Duration',
case
when cast((ActivityOperation.EndTime - ActivityOperation.StartTime) as integer) < 0 then '-'
else time(cast((ActivityOperation.EndTime - ActivityOperation.StartTime) as integer),'unixepoch')
end as 'Calculated Duration',
datetime(ActivityOperation.StartTime, 'unixepoch', 'localtime') as 'StartTime',
datetime(ActivityOperation.LastModifiedTime, 'unixepoch', 'localtime') as 'LastModified',
case
when ActivityOperation.OriginalLastModifiedOnClient > 0
then datetime(ActivityOperation.OriginalLastModifiedOnClient, 'unixepoch', 'localtime')
else ' - '
end as 'LastModifiedOnClient',
case
when ActivityOperation.EndTime > 0
then datetime(ActivityOperation.EndTime, 'unixepoch', 'localtime')
else null
end as 'EndTime',
case
when ActivityOperation.CreatedInCloud > 0
then datetime(ActivityOperation.CreatedInCloud, 'unixepoch', 'localtime')
else null
end as 'CreatedInCloud',
cast((ActivityOperation.ExpirationTime - ActivityOperation.LastModifiedTime)
as integer) / '86400' as 'Expires In days',
datetime(Activity_PackageId.ExpirationTime, 'unixepoch', 'localtime') as 'Expiration on PackageID',
datetime(ActivityOperation.ExpirationTime, 'unixepoch', 'localtime') as 'Expiration',
ActivityOperation.Tag as 'Tag',
ActivityOperation.MatchId as 'MatchID',
ActivityOperation.PackageIdHash as 'PackageIdHash',
case when ActivityOperation.ActivityType in (10,16) then json_extract(ActivityOperation.Payload, '$.clipboardDataId') else '' end as 'clipboardDataId',
'{' || substr(hex(Activity_PackageId.ActivityId), 1, 8) || '-' ||
substr(hex(Activity_PackageId.ActivityId), 9, 4) || '-' ||
substr(hex(Activity_PackageId.ActivityId), 13, 4) || '-' ||
substr(hex(Activity_PackageId.ActivityId), 17, 4) || '-' ||
substr(hex(Activity_PackageId.ActivityId), 21, 12) || '}' as 'ActivityId',
case when hex(ActivityOperation.ParentActivityId) = '00000000000000000000000000000000'
then '' else
'{' || substr(hex(ActivityOperation.ParentActivityId), 1, 8) || '-' ||
substr(hex(ActivityOperation.ParentActivityId), 9, 4) || '-' ||
substr(hex(ActivityOperation.ParentActivityId), 13, 4) || '-' ||
substr(hex(ActivityOperation.ParentActivityId), 17, 4) || '-' ||
substr(hex(ActivityOperation.ParentActivityId), 21, 12) || '}' end as 'ParentActivityId',
ActivityOperation.DdsDeviceId as 'DdsDeviceId',
ActivityOperation.PlatformDeviceId as 'Device ID',
case ActivityOperation.UploadAllowedByPolicy when 0 then 'No' when 1 then 'Yes' else ActivityOperation.UploadAllowedByPolicy end as 'UploadAllowedByPolicy',
ActivityOperation.PatchFields as 'PatchFields',
ActivityOperation.UserActionState as 'UserActionState',
json_extract(ActivityOperation.ClipboardPayload,'$') as 'ClipboardPayload',
case ActivityOperation.IsRead
when 0 then 'No'
when 1 then 'Yes'
else ActivityOperation.IsRead
end as 'Is_Read',
ActivityOperation."Group" as 'Group',
ActivityOperation.GroupAppActivityId as 'GroupAppActivityId',
ActivityOperation.GroupItems as 'GroupItems',
ActivityOperation.EnterpriseId as 'EnterpriseId',
case when ActivityOperation.ActivityType not in (2,10,11,12,15,16) then json_extract(ActivityOperation.OriginalPayload, '$.appDisplayName') else ActivityOperation.OriginalPayload end as 'Original Displayed Name',
case when ActivityOperation.ActivityType not in (2,10,11,12,15,16) then json_extract(ActivityOperation.OriginalPayload, '$.displayText') end as 'Original File/title opened',
case when ActivityOperation.ActivityType not in (2,10,11,12,15,16) then json_extract(ActivityOperation.OriginalPayload, '$.description') end as 'Original Full Path /Url',
case when ActivityOperation.ActivityType not in (2,10,11,12,15,16) then coalesce(json_extract(ActivityOperation.OriginalPayload, '$.activationUri'),json_extract(ActivityOperation.OriginalPayload, '$.reportingApp')) end as 'Original_App/Uri',
case when ActivityOperation.ActivityType not in (2,10,11,12,15,16) then time(json_extract(ActivityOperation.OriginalPayload, '$.activeDurationSeconds'),'unixepoch') end as 'Orig.Duration'
from Activity_PackageId
join ActivityOperation on Activity_PackageId.ActivityId = ActivityOperation.Id
where Activity_PackageId.Platform = json_extract(ActivityOperation.AppId, '$[0].platform')
and Activity_PackageId.ActivityId = ActivityOperation.Id
union -- Join Activity & ActivityOperation Queries to get results from both Tables
select -- This the Activity Table Query
Activity.ETag as 'Etag',
null as 'Order',
case
when Activity.ActivityType =5
then json_extract(Activity.Payload, '$.appDisplayName')
else ''
end as 'Program Name',
case
when Activity.ActivityType in (2,11,12,15) then json_extract(Activity.AppId, '$[0].application')
when json_extract(Activity.AppId, '$[0].application') = '308046B0AF4A39CB'
then 'Mozilla Firefox-64bit'
when json_extract(Activity.AppId, '$[0].application') = 'E7CF176E110C211B'
then 'Mozilla Firefox-32bit'
when json_extract(Activity.AppId, '$[1].application') = '308046B0AF4A39CB'
then 'Mozilla Firefox-64bit'
when json_extract(Activity.AppId, '$[1].application') = 'E7CF176E110C211B'
then 'Mozilla Firefox-32bit'
when length (json_extract(Activity.AppId, '$[0].application')) between 17 and 22
then replace(replace(replace(replace(replace(json_extract(Activity.AppId, '$[1].application'),
'{'||'6D809377-6AF0-444B-8957-A3773F02200E'||'}', '*ProgramFiles (x64)' ),
'{'||'7C5A40EF-A0FB-4BFC-874A-C0F2E0B9FA8E'||'}', '*ProgramFiles (x32)'),
'{'||'1AC14E77-02E7-4E5D-B744-2EB1AE5198B7'||'}', '*System' ),
'{'||'F38BF404-1D43-42F2-9305-67DE0B28FC23'||'}', '*Windows'),
'{'||'D65231B0-B2F1-4857-A4CE-A8E7C6EA7D27'||'}', '*System32')
else replace(replace(replace(replace(replace
(json_extract(Activity.AppId, '$[0].application'),
'{'||'6D809377-6AF0-444B-8957-A3773F02200E'||'}', '*ProgramFiles (x64)'),
'{'||'7C5A40EF-A0FB-4BFC-874A-C0F2E0B9FA8E'||'}', '*ProgramFiles (x32)'),
'{'||'1AC14E77-02E7-4E5D-B744-2EB1AE5198B7'||'}', '*System'),
'{'||'F38BF404-1D43-42F2-9305-67DE0B28FC23'||'}', '*Windows'),
'{'||'D65231B0-B2F1-4857-A4CE-A8E7C6EA7D27'||'}', '*System32')
end as 'Application',
case when Activity.ActivityType not in (2,10,11,12,15,16) then
json_extract(Activity.Payload, '$.displayText') else '' end as 'File Opened',
case when Activity.ActivityType not in (2,10,11,12,15,16) then
json_extract(Activity.Payload, '$.description')||')' else '' end as 'Full Path',
case when Activity.ActivityType not in (2,10,11,12,15,16) then
json_extract(Activity.Payload, '$.contentUri') else '' end as 'Content',
trim(Activity.AppActivityId,'ECB32AF3-1440-4086-94E3-5311F97F89C4\') as 'AppActivityId',
case
when Activity.ActivityType = 2 then Activity.Payload
when Activity.ActivityType = 10 and json_extract(Activity.Payload,'$') notnull
then json_extract(Activity.Payload,'$.1[0].content') --Base64 encoded
when Activity.ActivityType = 5 and json_extract(Activity.Payload, '$.shellContentDescription') like '%FileShellLink%'
then json_extract(Activity.Payload, '$.shellContentDescription.FileShellLink')
when Activity.ActivityType = 6
then case
when json_extract(Activity.Payload,'$.devicePlatform') notnull
then json_extract(Activity.Payload, '$.type')||' - ' ||json_extract(Activity.Payload,'$.devicePlatform')
else json_extract(Activity.Payload, '$.type')||' - ' ||json_extract(Activity.Payload,'$.userTimezone') end
else ''
end as 'Payload/Timezone',
case Activity.ActivityType
when 2 then 'Notification'
when 5 then 'Open App/File/Page'
when 6 then 'App In Use/Focus'
when 10 then 'Clipboard'
when 16 then 'Copy/Paste'
else Activity.ActivityType
end as 'ActivityType',
case json_extract(Activity.AppId, '$[0].platform')
when 'afs_crossplatform' then 'Yes'
when 'host' then (case json_extract(Activity.AppId, '$[1].platform')
when 'afs_crossplatform' then'Yes' else null end) else null
end as 'Synced',
case
when json_extract(Activity.AppId, '$[0].platform') = 'afs_crossplatform'
then json_extract(Activity.AppId, '$[1].platform')
else json_extract(Activity.AppId, '$[0].platform')
end as 'Platform',
case Activity.ActivityStatus
when 1 then 'Active' when 2 then 'Updated' when 3 then 'Deleted' when 4 then 'Ignored'
end as 'TileStatus',
null as 'WasRemoved',
'No' as 'UploadQueue',
case Activity.IsLocalOnly
when 0 then 'No'
when 1 then 'Yes'
else Activity.IsLocalOnly
end as 'IsLocalOnly',
case when Activity.ActivityType in (2,10,11,12,15,16) then ''
else coalesce(json_extract(Activity.Payload, '$.activationUri'),json_extract(Activity.Payload, '$.reportingApp')) end as 'App/Uri',
Activity.Priority as 'Priority',
case when Activity.ActivityType !=6 then ''
else time(json_extract(Activity.Payload, '$.activeDurationSeconds'),'unixepoch') end as 'Active Duration',
case
when cast ((Activity.EndTime - Activity.StartTime) as integer) < 0 then '-'
else time(cast((Activity.EndTime - Activity.StartTime) as integer),'unixepoch')
end as 'Calculated Duration',
datetime(Activity.StartTime, 'unixepoch', 'localtime') as 'StartTime',
datetime(Activity.LastModifiedTime, 'unixepoch', 'localtime') as 'LastModified',
case
when Activity.OriginalLastModifiedOnClient > 0
then datetime(Activity.OriginalLastModifiedOnClient, 'unixepoch', 'localtime')
else ' - '
end as 'LastModifiedOnClient',
case
when Activity.EndTime > 0
then datetime(Activity.EndTime, 'unixepoch', 'localtime')
else "-"
end as 'EndTime',
case
when Activity.CreatedInCloud > 0
then datetime(Activity.CreatedInCloud, 'unixepoch', 'localtime')
else "-"
end as 'CreatedInCloud',
cast((Activity.ExpirationTime - Activity.LastModifiedTime) as integer) / '86400' as 'Expires In days',
datetime(Activity_PackageId.ExpirationTime, 'unixepoch', 'localtime') as 'Expiration on PackageID',
datetime(Activity.ExpirationTime, 'unixepoch', 'localtime') as 'Expiration',
Activity.Tag as 'Tag',
Activity.MatchId as 'MatchID',
Activity.PackageIdHash as 'PackageIdHash',
case when Activity.ActivityType in (10,16) then json_extract(Activity.Payload, '$.clipboardDataId') else '' end as 'clipboardDataId',
'{' || substr(hex(Activity_PackageId.ActivityId), 1, 8) || '-' ||
substr(hex(Activity_PackageId.ActivityId), 9, 4) || '-' ||
substr(hex(Activity_PackageId.ActivityId), 13, 4) || '-' ||
substr(hex(Activity_PackageId.ActivityId), 17, 4) || '-' ||
substr(hex(Activity_PackageId.ActivityId), 21, 12) || '}' as 'ActivityId',
case when hex(Activity.ParentActivityId) = '00000000000000000000000000000000'
then '' else
'{' || substr(hex(Activity.ParentActivityId), 1, 8) || '-' ||
substr(hex(Activity.ParentActivityId), 9, 4) || '-' ||
substr(hex(Activity.ParentActivityId), 13, 4) || '-' ||
substr(hex(Activity.ParentActivityId), 17, 4) || '-' ||
substr(hex(Activity.ParentActivityId), 21, 12) || '}' end as 'ParentActivityId',
Activity.DdsDeviceId as 'DdsDeviceId',
Activity.PlatformDeviceId as 'Device ID',
'' as 'UploadAllowedByPolicy',
'' as 'PatchFields',
Activity.UserActionState as 'UserActionState',
json_extract(Activity.ClipboardPayload,'$') as 'ClipboardPayload',
case Activity.IsRead when 0 then 'No' when 1 then 'Yes' else Activity.IsRead end as 'Is_Read',
Activity."Group" as 'Group',
Activity.GroupAppActivityId as 'GroupAppActivityId',
Activity.GroupItems as 'GroupItems',
Activity.EnterpriseId as 'EnterpriseId',
case when Activity.ActivityType not in (2,10,11,12,15,16) then json_extract(Activity.OriginalPayload, '$.appDisplayName') else Activity.OriginalPayload end as 'Original Program Name',
case when Activity.ActivityType not in (2,10,11,12,15,16) then json_extract(Activity.OriginalPayload, '$.displayText') end as 'Original File/title opened',
case when Activity.ActivityType not in (2,10,11,12,15,16) then json_extract(Activity.OriginalPayload, '$.description') end as 'Original Full Path /Url',
case when Activity.ActivityType not in (2,10,11,12,15,16) then coalesce(json_extract(Activity.OriginalPayload, '$.activationUri'),json_extract(Activity.OriginalPayload, '$.reportingApp')) end as 'Original_App/Uri',
case when Activity.ActivityType not in (2,10,11,12,15,16) then time(json_extract(Activity.OriginalPayload, '$.activeDurationSeconds'),'unixepoch' ) end as 'Orig.Duration'
from Activity_PackageId
join Activity on Activity_PackageId.ActivityId = Activity.Id
where Activity_PackageId.Platform = json_extract(Activity.AppId, '$[0].platform')
and Activity_PackageId.ActivityId = Activity.Id
order by Etag desc; -- Edit this line to change the sorting
-- EOF