AppActivityId

[hashimoto62563]

Hi, Eric san,

This is Yuya Hashimoto. I work with @kdogi.

Regarding the issue #4, I also confirmed the followings;

• An accessed file name was recorded only once in the Payload column in the Activity table when the file was accessed for the first time at least within a certain period of time.
• The AppActivityId column is useful to specify the accessed file name for the records holding no file-name-information.

Here is the scenario I went through to check this.

1. Create a file named 'test.txt' using notepad.exe and close it at around 2019/02/19 06:16.
2. Open, modify and close it several time quickly, right after 1.

Select AppActivityId,
       AppId,
       datetime(StartTime,'unixepoch') as StartTime,
       datetime(EndTime,'unixepoch') as EndTime,
       Payload
from Activity where Payload like '%test.txt%'

The table shown below is the execution result of the query above.

AppActivityId	AppId	StartTime	EndTime	Payload
ECB32AF3-1440-4086-94E3-5311F97F89C4\f72d8c310b018e277f225ec2da4721797830ffa6	[{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"windows_win32"},{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"packageId"},{"application":"","platform":"alternateId"},{"application":"","platform":"windows_universal"}]	2019-02-19 06:16:38	1970-01-01 00:00:00	{"displayText":"test.txt","activationUri":"ms-shellactivity:","appDisplayName":"メモ帳","description":"C:\Users\yhashimoto\Desktop\test.txt",(snipped)}

The result illustrates that it seemed that only one activity holding the file name information 'test.txt' was recorded in the Actibity table even though the file was accessed several times.

Below is the query to extract the records whose AppActivityId is the same as that of the record containing 'test.txt' shown above.

Select AppActivityId,
       AppId,
       datetime(StartTime,'unixepoch') as StartTime,
       datetime(EndTime,'unixepoch') as EndTime,
       Payload
from Activity where AppActivityId = 'ECB32AF3-1440-4086-94E3-5311F97F89C4\f72d8c310b018e277f225ec2da4721797830ffa6'

The execution result is as follows.

AppActivityId	AppId	StartTime	EndTime	Payload
ECB32AF3-1440-4086-94E3-5311F97F89C4\f72d8c310b018e277f225ec2da4721797830ffa6	[{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"windows_win32"},{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"packageId"},{"application":"","platform":"alternateId"},{"application":"","platform":"windows_universal"}]	2019-02-19 06:16:38	1970-01-01 00:00:00	{"displayText":"test.txt","activationUri":"ms-shellactivity:","appDisplayName":"メモ帳","description":"C:\Users\yhashimoto\Desktop\test.txt",(snipped)}
ECB32AF3-1440-4086-94E3-5311F97F89C4\f72d8c310b018e277f225ec2da4721797830ffa6	[{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"windows_win32"},{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"packageId"},{"application":"","platform":"alternateId"},{"application":"","platform":"windows_universal"}]	2019-02-19 06:16:38	2019-02-19 06:16:39	{"type":"UserEngaged","reportingApp":"ShellActivityMonitor","activeDurationSeconds":4,"shellContentDescription":{"MergedGap":600},"userTimezone":"Asia/Tokyo"}
ECB32AF3-1440-4086-94E3-5311F97F89C4\f72d8c310b018e277f225ec2da4721797830ffa6	[{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"windows_win32"},{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"packageId"},{"application":"","platform":"alternateId"},{"application":"","platform":"windows_universal"}]	2019-02-19 06:16:46	2019-02-19 06:16:50	{"type":"UserEngaged","reportingApp":"ShellActivityMonitor","activeDurationSeconds":4,"shellContentDescription":{"MergedGap":600},"userTimezone":"Asia/Tokyo"}
ECB32AF3-1440-4086-94E3-5311F97F89C4\f72d8c310b018e277f225ec2da4721797830ffa6	[{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"windows_win32"},{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"packageId"},{"application":"","platform":"alternateId"},{"application":"","platform":"windows_universal"}]	2019-02-19 06:16:51	2019-02-19 06:16:58	{"type":"UserEngaged","reportingApp":"ShellActivityMonitor","activeDurationSeconds":4,"shellContentDescription":{"MergedGap":600},"userTimezone":"Asia/Tokyo"}
ECB32AF3-1440-4086-94E3-5311F97F89C4\f72d8c310b018e277f225ec2da4721797830ffa6	[{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"windows_win32"},{"application":"{1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\notepad.exe","platform":"packageId"},{"application":"","platform":"alternateId"},{"application":"","platform":"windows_universal"}]	2019-02-19 06:17:02	2019-02-19 06:17:06	{"type":"UserEngaged","reportingApp":"ShellActivityMonitor","activeDurationSeconds":4,"shellContentDescription":{"MergedGap":600},"userTimezone":"Asia/Tokyo"}

Judging from their AppId, StartTime and EndTime, all the records above seem to be related to 'test.txt' although those records except the first one don't have the accessed file name information.

What do you think about it?