-
-
Notifications
You must be signed in to change notification settings - Fork 11
/
Copy pathClipboardHistory.Service.sql
79 lines (64 loc) · 4.27 KB
/
ClipboardHistory.Service.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
-- Diagnostic ClipboardHistory
-- Included events:
-- 'Microsoft.Windows.ClipboardHistory.Service.CopyActionDetected_Compliant',
-- 'Microsoft.Windows.ClipboardHistory.Service.PasteActionDetected_Compliant',
-- 'Microsoft.Windows.ClipboardHistory.Service.AddItemActivity',
-- 'Microsoft.WindowsInternal.ComposableShell.Experiences.SuggestionUI.ClipboardHistoryChangeDetected',
-- 'Microsoft.WindowsInternal.ComposableShell.Experiences.SuggestionUI.ClipboardUpdateDetected'
--
-- from C:\ProgramData\Microsoft\Diagnosis\EventTranscript\EventTranscript.db
-- For more info visit https://github.com/rathbuna/EventTranscript.db-Research
SELECT
--Timestamp from db field
json_extract(events_persisted.payload,'$.time') as 'UTC TimeStamp',
-- Timestamp from json payload
datetime((timestamp - 116444736000000000)/10000000, 'unixepoch','localtime') as 'Local TimeStamp',
json_extract(events_persisted.payload,'$.ext.loc.tz') as 'TimeZome',
json_extract(events_persisted.payload,'$.ext.utc.seq') as 'seq',
json_extract(events_persisted.payload,'$.data.Origin') as 'Origin',
case json_extract(events_persisted.payload,'$.data.IsCurrentClipboardData')
when 1 then 'Yes'
when 0 then 'No'
end as 'Is Current',
producers.producer_id_text as 'Producer',
-- Actions
replace(replace(replace(replace(replace(full_event_name,'Microsoft.Windows.ClipboardHistory.Service.',''),'_Compliant',''),'Detected',''),'Activity',''),'Microsoft.WindowsInternal.ComposableShell.Experiences.SuggestionUI.','') as 'Event',
coalesce(json_extract(events_persisted.payload,'$.data.sourceApplicationName'),json_extract(events_persisted.payload,'$.data.SourceApplicationName'),json_extract(events_persisted.payload,'$.data.AppExecutableName')) as 'Copy Source',
json_extract(events_persisted.payload,'$.data.targetApplicationName') as 'Paste Target',
json_extract(events_persisted.payload,'$.data.TargetProcessId') as 'Target pid',
-- JSON of copied items info
case
when json_extract(events_persisted.payload,'$.data.Formats') like '%Bitmap%' then 'Bitmap'
when json_extract(events_persisted.payload,'$.data.Formats') like '%Text%' then 'Text'
when json_extract(events_persisted.payload,'$.data.Formats') like '{}' then '--'
when json_extract(events_persisted.payload,'$.data.Formats') like '%DropEffectFolderList%' then 'DropEffectFolderList'
when json_extract(events_persisted.payload,'$.data.Formats') like '%Clipboard#MoveToScrap%' then 'MoveToScrap'
else json_extract(events_persisted.payload,'$.data.Formats')
end as 'Clip Type',
json_extract(events_persisted.payload,'$.data.ItemDataCreationStatus') as 'Creation Status',
-- Appear to link clipboard events
json_extract(events_persisted.payload,'$.data.wilActivity.threadId') as 'ThreadId',
trim(json_extract(events_persisted.payload,'$.ext.protocol.ticketKeys'),'"[]"') as 'ticketKeys',
trim(coalesce(json_extract(events_persisted.payload,'$.data.ItemId'),json_extract(events_persisted.payload,'$.data.itemId')),'{}') as'ItemId',
-- Local, MS or AAD account
trim(json_extract(events_persisted.payload,'$.ext.user.localId'),'m:') as 'UserId',
sid as 'User SID',
-- Device class (same as the type used in ActivitiesCache.db
TRIM(json_extract(events_persisted.payload,'$.ext.device.deviceClass'),'Windows.') as 'Device Class',
-- json_extract(events_persisted.payload,'$.ext.device.localId') as 'localId',
-- Device info from registry
json_extract(events_persisted.payload,'$.ext.os.name') as 'OS',
json_extract(events_persisted.payload,'$.ext.protocol.devMake') as 'Device Make',
json_extract(events_persisted.payload,'$.ext.protocol.devModel') as 'Device Model',
logging_binary_name
from events_persisted
join producers on producers.producer_id = events_persisted.producer_id
-- include events:
where events_persisted.full_event_name in
('Microsoft.Windows.ClipboardHistory.Service.CopyActionDetected_Compliant',
'Microsoft.Windows.ClipboardHistory.Service.PasteActionDetected_Compliant',
'Microsoft.Windows.ClipboardHistory.Service.AddItemActivity',
'Microsoft.WindowsInternal.ComposableShell.Experiences.SuggestionUI.ClipboardHistoryChangeDetected',
'Microsoft.WindowsInternal.ComposableShell.Experiences.SuggestionUI.ClipboardUpdateDetected')
-- Sort by event sequence number descending (newest first)
order by cast(seq as integer) desc