Title
#general
s

Sree

09/10/2020, 5:50 PM
How to convert last_execution_time to a human readable time format ? With unix, we can use the datetime(local_time, ‘unixepoch’, ‘localtime’) .. however, this logic is not working in windows
z

Zach Zeid

09/10/2020, 6:00 PM
select local_time from time;
> local_time = 1529608143 <-- in unix epoch time
select datetime(local_time, 'unixepoch', 'localtime') as
formatted_time from time;
> formatted_time = 2018-06-21 15:09:09
6:01 PM
This would also be a good question for #sql
f

fritz

09/10/2020, 6:48 PM
I am confused, this seems to convert just fine?
SELECT p.path, datetime(p.last_execution_time, 'unixepoch', 'localtime') AS human_readable_last_exec, p.count, u.username FROM userassist p join users u ON u.uuid = p.sid ORDER BY p.count DESC;
---
+-------------------------------------------------------------------------+--------------------------+-------+--------------+
| path                                                                    | human_readable_last_exec | count | username     |
+-------------------------------------------------------------------------+--------------------------+-------+--------------+
| Microsoft.*****************_8wekyb3d8bbwe!App                           | 2020-09-09 18:41:13      | 29    | kolide-razer |
| Microsoft.Windows.Explorer                                              | 2020-09-10 14:14:04      | 7     | kolide-razer |
| {1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\SnippingTool.exe                 | 2020-09-10 13:45:49      | 6     | kolide-razer |
| C:\Users\kolide-razer\***************\*****************.exe             | 2020-09-09 18:41:11      | 6     | kolide-razer |
| {0139D44E-6AFE-49F2-8690-3DAFCAE6FFB8}\Accessories\Snipping Tool.lnk    | 2020-09-10 13:45:49      | 6     | kolide-razer |
| {9E3995AB-1F9C-4F13-B827-48B24B6C7174}\TaskBar\File Explorer.lnk        | 2020-09-10 11:33:34      | 6     | kolide-razer |
| {1AC14E77-02E7-4E5D-B744-2EB1AE5198B7}\cmd.exe                          | 2020-09-10 13:22:46      | 5     | kolide-razer |
| {A77F5D77-2E2B-44C3-A6A2-ABA601054A51}\System Tools\Command Prompt.lnk  | 2020-09-10 13:22:46      | 5     | kolide-razer |
6:49 PM
@Sree your original query does not have the
datetime
function included, so I am not sure what it is supposed to demonstrate